Excel and ASP.NET: Converting from HTML, Gridlines, etc.

Posted April 6, 2009 * Comments(4)

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.

4 Comments so far

  1. software development uk on August 20th, 2009

    Quite inspiring,

    I didn’t knew that xml tables can be read with exel, that was excelent

    Thanks

  2. Daniel Rivas on September 30th, 2009

    Hello,
    I have a requirement that is driving me crazy and I think that you might be able to help me solve my problem.
    I have a Byte[] object extracted from an excel and I can generate another excel file from it. But what I need is to keep that “excel Byte[]” in the code to extract data WITHOUT GENERATING A PHISICAL FILE. Something like:

    byte[] byteExcelContent = *Something here*
    Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();
    myExcel.Workbooks.Add(byteExcelContent);

    Only that this does not work…
    Any ideas will be very welcome.

    Thanks,
    Daniel Rivas.

  3. M on September 30th, 2009

    Daniel,
    What error message do you get when you run your code?
    If I understand you correctly: you want to convert a byte[] into an Excel Workbook without having to save\write the file to disk, correct?

    M

  4. Daniel Rivas on October 2nd, 2009

    Hello,

    The error I get in the line myExcel.Workbooks.Add(byteExcelContent); is an out of memory dialogue. It takes more than 30 seconds to get there and during that time the CPU use is of more than 50%.

    That’s exactly right, I need to convert byte[] to excel without having to save\write the file. (I copied your line just for clarity :D )

    Thanks,
    Daniel Rivas.

Leave a reply