Save a DataTable to Excel Format

Users of my applications like to get reports directly in native Excel so they can use them quickly. I typically save files in CSV, which is fast and reliable but Excel doesn't support it as thoroughly as it should.

On the other hand, using Excel automation to populate a file takes an eternity and doesn't work well on a server. So here's a nice middleground - I can save the file to an XML/HTML format that Excel supports natively.

This strategy solves a few problems:

1) Performance - This format is just as fast to create as CSV.
2) Dependence - Does not require using any Excel libraries.
3) Compatibility - Excel opens the file and it looks properly formatted and usable.
4) Data Integrity - CSV files tend to damage numeric or date values if they are formatted in a way Excel does not expect; this format converts everything to text which means the values are represented exactly.

You also have the option of using some of Microsoft Excel's number formatting tools. These are surprisingly hard to find documented anywhere! But, here they are:

http://www.ozgrid.com/Excel/CustomFormats.htm

Without further ado, here's the code:

/// 

/// Save a DataTable to an XML file
/// 
/// 
/// 
public static void SaveDataTableToExcelCompatibleFile(string filename, DataTable dt)
{
   StreamWriter sw = null;
   try {

       // Create the file and write the header
       sw = new StreamWriter(filename);
       sw.WriteLine(@"");

       // Write column names
       sw.Write("");
       for (int i = 0; i < dt.Columns.Count; i++) {
           sw.Write("");
       }
       sw.Write("");

       // Write data
       for (int i = 0; i < dt.Rows.Count; i++) {
           DataRow dr = dt.Rows[ i ];
           sw.Write("");
           for (int j = 0; j < dt.Columns.Count; j++) {
               sw.Write("");
           }
           sw.Write("");
       }

       // Write footer
       sw.WriteLine(@"
");
           sw.Write(dt.Columns[ i ].ColumnName);
           sw.Write("
");
               sw.Write(dr[j].ToString());
               sw.Write("


Confidential Information - Do Not Distribute");

   } finally {
       if (sw != null) sw.Close();
   }
}

(http://www.tedspence.com/index.php?entry=entry090724-095142)