Export dynamically created table from javascript to Excel

Question

I am working on a project that dynamically creates an html table with javascript. Once this table is created I need to be able to export it to excel through a button click. I have tried a few things already, but they haven't worked for me.

1. I tried doing a simple export from javascript by creating the Active X object, but that setting is locked down in IE so our browsers will not work with Active X.

2. I tried exporting to excel from a code behind function using the HttpContext class but since the table is created dynamically, the server doesn't see it.

3. My final method, and I was sure that this was going to work, was I used AJAX via a pagemethod to export the table to excel. I was going to create an array of the table in javascript and pass it to the pagemethod. But before I got to this step, I created a pagemethod that exported a simpe "Test" file to excel. It worked as a method called from a button click, so I figured it would from a pagemethod as well. It didn't :( It runs and completes the pagemethod, but doesn't open excel or export anything. No errors and the success function is executed.

Here is the code:

<System.Web.Service.WebMethod()>
Public Shared Function exportTable(ByVal title As String) As String
HttpContext.Current.Response.ClearContext()
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename="TEST.xls")
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
HttpContext.Current.Response.Write("<?xml version='1.0'?>")
HttpContext.Current.Response.Write("<ss:Workbook xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>")
HttpContext.Current.Response.Write("<ss:Worksheet ss:Name='sheet1'>")
HttpContext.Current.Response.Write("<ss:Table>")
HttpContext.Current.Response.Write("<ss:Row>")
HttpContext.Current.Response.Write("<ss:Cell><ss:Data ss:Type='String'>TEST</ss:Data></ss:Cell>")
HttpContext.Current.Response.Write("</ss:Row>")
HttpContext.Current.Response.Write("</ss:Table>")
HttpContext.Current.Response.Write("</ss:Worksheet>")
HttpContext.Current.Response.Write("</ss:Workbook>")
HttpContext.Current.ApplicationInstance.CompleteRequest()

Return 0
End Function

My page method call is:

function exportToExcel(title) {
PageMethods.exportTable(title, exportSuccess, exportFailure)
}

The exportSuccess function is a simple alert, which is firing. I take that to mean that the pageMethod is executing without errors, but that it can't open excel or ... something.

I eventually want to pass in an array and loop through it to add the rows and data. I've done this with a non dynamic table and it works. I'm also going to use the title to define the filename, but for testing reasons, I've named it TEST.xls.

I copied and pasted the HttpContext code into a button click event and it worked fine with a static table, so I don't think there are any problems with that. But I have been known to be wrong.

I guess my question is can a PageMethod export to excel or use the HttpContext class? Is there a better/easier way to do this?

Please keep in mind that I cannot use ActiveX objects and all customers are required to use IE.

Thanks for your help, and I'm sorry if this has already been covered. I searched but didn't find anything. If it has, could you please point me to the post?

Answer 1

Well, I've buckled under pressure and decided to do it a different way. I think that ajax does not play well with response.write.

I created an asp:hiddenfield control. Then when the ajax was called to generate the table, I populated it's value with a pipe delimeted string representing the rows and columns of the table. So cell1|cell2||cell1|cell2||cell1|cell2.

Then I created an on_click method for a button and was able to use the same code from the function above. To populate the table I just replace the "TEST" by parsing the hiddenfield value.

I decided to post this just in case anyone else was having an issue with it.

Answer 2

If you send out the Excel MIME header, followed by an HTML table, EXCEL should load it as it it were a spreadsheet.