Exporting HTML to Excel Without Losing Formatting

Question

Hi,

I have a asp.net page that generates a report. For better or for worse, the entire thing is generated using nested tables. I am able to export the page to excel, however I lose all my formatting (can't set column widths, etc). Is there a way to handle this? I'm open to adding any goofy MS Office -specific tags that are required to the html side of it.

If this isn't feasible and if anyone has any other ideas, the requirement is that users need a report that:

a) They can manually touch up with some personalization / area-specific data

b) Needs to be hide/show columns based on user's location

c) Needs to run from the web site.

Any help or recommendations for an alternate approach would be greatly appreciated.

Thanks Joe

Answer 1

You can't really without a third party tool unfortunately.

If the end users are known to have Office 2007+ then you can use open XML format.

Microsoft Open XML

If you go the third party route, they are generally quite expensive, at least $130

Check this Stack Overflow thread on that

How can I read MS Office files in a server without installing MS Office and without using Interop Library?

Although you can hide/show columns in your code to manipulate the columns before exporting, that's not a big deal, just the formatting of the look and feel will be difficult.

Answer 2

SpreadsheetGear for .NET will let you generate formatted Excel workbooks from ASP.NET.

You can see our live ASP.NET samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

The code below works for me and preserves all table formatting. The source html table need to have attribute runat="Server" and an id for this to work, obviously.

Essentially, it creates a new (dummy) form which contains only your table and writes that out to an excel file. If you are using Excel 2007, you will get the irritating error message that "the file you are trying to open is in a diferent format from that specified by the extension," but you can safely ignore this and say "yes" you wanna open it.

Public Sub exportTableExcel(ByVal aTable, ByVal filename)


    Dim sw As New StringWriter()
    Dim htw As New HtmlTextWriter(sw)

    HttpContext.Current.Response.ClearContent()
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" & filename)
    HttpContext.Current.Response.ContentType = "application/vnd.xls"
    HttpContext.Current.Response.Charset = ""

    ' Create a form to contain the table 
    Dim frm As New HtmlForm()
    aTable.Parent.Controls.Add(frm)
    frm.Attributes("runat") = "server"
    'prevent additional code from being exported
    frm.attributes("maintainScrollPositionOnPostBack") = "false"

    frm.Controls.Add(aTable)
    frm.RenderControl(htw)
    HttpContext.Current.Response.Write(sw.ToString())
    HttpContext.Current.Response.End()

    frm.Dispose()
    htw.Dispose()
    sw.Dispose()
End Sub