SandboxM

Here’s how to generate Excel (xls\xlsx) files on-the-fly.

All search results talked about setting the Page.Content-Type to “vnd.xls” or “vnd.ms-excel” and using the obsolete “content-disposition” meta tag. But I just wanted to save the data without popping up the confirmation dialog. Luckily Excel can read HTML tables very well, so all I had to do is take the HTML buffer and save it to disk with an XLS file extension.

protected void Page_Load(EventArgs e)
{
string strData = GetDataFromWhereEver();
ExportToExcel(strData, "C:\ExcelFiles", "MyExcelFile.xls");
}

private void ExportToExcel(string strHTMLbuffer, string path, string filename)
{
Byte[] bytes = System.Text.Encoding.ASCII.GetBytes(strHTMLbuffer);
FileStream fs = new FileStream(path + "\\" + filename, (FileMode)FileAccess.Write);
fs.Write(bytes, 0, bytes.Length);
fs.Close();
}

But once converted, I didn’t like the whitespace in Excel. So using the VBA code that I found here and here, I managed to turn on the Gridlines in the Excel file.

Here you go:

Excel.ApplicationClass  app = new Excel.ApplicationClass();
app.Visible = false;
Excel.Workbook workbook = app.Workbooks.Open(Filename, 0, false, 5, '', '', false, Excel.XlPlatform.xlWindows, '', true, false, 0, true, false, false);

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
worksheet.Activate();
app.ActiveWindow.GridlineColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
app.ActiveWindow.DisplayGridlines = true;
worksheet.PageSetup.Draft = false;
workbook.Save();
app.Workbooks.Close();
app.Quit();

Make sure you’ve added a reference to the Office 11.0 Object Library.

That’s it!

Comments\Suggestions always welcome.