How to create an Excel file (Without Excel)

A client of mine recently wanted to export a bunch of records from SQL Server to a file that employees in his company can make use of. The project kicked off with the CSV file format which was simple. All I had to do was iterate through the columns of the SqlDataReader like so:

        string strOut = "";
        string delimiter = "\t";
        DataTable dtSchema = reader.GetSchemaTable();

        for (int i = 0; i < dtSchema.Rows.Count; i++)
        {
            strOut += dtSchema.Rows[i][0].ToString();
            if (i < dtSchema.Rows.Count - 1)
            {
                strOut += delimiter;
            }

        }
        writer.WriteLine(strOut);

Then write all rows from the SqlDataReader like so:

        while (reader.Read())
        {
            strRow = "";
            for (int i = 0; i < reader.FieldCount; i++)
            {
                strRow += reader.GetValue(i).ToString();
                if (i < reader.FieldCount - 1)
                {
                    strRow += separator;
                }
            }

            writer.Write(strRow);

        }


That seemed to work pretty well until some not-so-technical users had to access the CSV files. The process of importing the file to Excel then formatting it seemed a daunting task. That meant I had to come up with a way to create a file that Excel would open and interpret natively. A little bit of Google led me to this article by Jason Haley. All I had to do was create  an xml formatted file, name it "whatever.xls" and voila!!