How to generate MS-Excel file using ASP web application?

Question

As per my application I need to generate Excel files.

I am able to generate excel files with:

Response.ContentType = "application/vnd.ms-excel" in header and populating data by creating HTML Table.

But while trying to create a file fetching it from server location and saving data into it(here we are creating excel object to generate file), the web page opens with DONE status with blank page and the source file shows data in HTML format. Where as I try to open the file explicitly using the path I am getting the following errors:

it shows multiple errors like as follows: Note: ( MS-Excel DLL) is installed in the Server location.

Error message-1: Macros may contain viruses. It is usually safe to disable macros, but if the macros are legitimate, you might lose some functionality. Note: There is no macro present in the file On Clicking Enable Macros gets the second Error message:

The file could not be accessed. Try one of the following:

1. Make sure the specified folder exists.
2. Make sure the folder that contains the file is not read-only.
3. Make sure the file name does not contain any of the following characters < > ? [ ] : | or *
4. Make sure the file/path name doesn't contain more than 218 characters.

Note: All of the above conditions holds true.

And finally on clicking ok it shows the error message:

This file is not in a recognizable format.

Then it opens with encrypted format.

Could anyone let me know what could be the problem?

Answer 1

I would be tempted to build up the Excel file in XML formal (.xlsx). You wouldn't need to have excel installed or use it's object model at all, you could start with a template .xlsx document, which would contain any static items and page layout type stuff, then just insert yout content in the right places.

If you have an existing Excel (.xlsx) document, you can break it open and see what's inside by renaming it and adding a .zip extension (office 2007 documents are actually zip files).

Answer 2

I did this once, it was freaking hard. Basically there are all sorts of security restrictions in place such that making callouts to the excel COM API is very difficult from a web context. You would be best to generate an excel 2007 file using an xml writer. I haven't used it but some people suggest this ExcelXmlWriter to make that easier

Another option is to write out a CSV file rather than an excel file. You won't get cool formulas or formatting but it is the most compatible.