"Export to Excel" without using components in a WinForms app

Way back in 2004, I wrote a program to generate a Word file dynamically without using any components in a Web application. The technique I used makes use of the fact that documents can be converted from Word/Excel to HTML (File->Save As) and vice versa.  When you manually save an Office document as a Web page, you can see from its source that it renders some interesting HTML, CSS & Office XML. By understanding and emulating this conversion technique programmatically, we can generate Word or Excel documents through web pages.

This snippet shows how you can generate a report in Excel format in a web application after fetching data from a data source using the Office XML technique -

System.Data.DataTable workTable = new System.Data.DataTable();

//The tablename specified here will be set as the worksheet name of the generated Excel file.
workTable.TableName = "Customers";
workTable.Columns.Add("Id");
workTable.Columns.Add("Name");
System.Data.DataRow workRow;

for (int i = 0; i <= 9; i++)
{
workRow = workTable.NewRow();
workRow[0] = i;
workRow[1] = "CustName" + i.ToString();
workTable.Rows.Add(workRow);
}

string strBody = DataTable2ExcelString(workTable);

Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");
Response.Write(strBody);

A complete working sample & a detailed explanation of the code are available.

A developer wanted to know how this method of converting a datatable to Excel can be adapted for a Windows Forms application.

The trick to generating the Excel file in a web application involves creating a HTML page containing some special CSS & Office XML and triggering a file download with the help of the following Response.AppendHeader statements -
Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");

To use this technique in a Winforms application, the string content representing the HTML page (containing the special CSS & Office XML) needs to be saved as a text file and the file should be given an extension of ".xls". The last 3 lines in the snippet above which are irrelevant in a WinForms app  can be replaced with this line -
System.IO.File.WriteAllText(@"C:\Report.xls", strBody);

Also see:
HOW TO send an email with a Word or Excel file attachment built on the fly

60

Tweet