Save Excel Document in ASP.NET

Question

Issue:

I am using the Microsoft.Office.Interop.Excel library in an ASP.NET application, and I have populated a Worksheet in a Workbook. I want the user to be prompted to Save the document to their machine, but I cannot find a way to do it (myWorkbook.SaveAs(...) doesn't work).

Question:

How can I get the Workbook saved to the user's machine? I've used Response.Write(...) to prompt the user to save a text-based file (CSV) before, but I cannot figure out how to do this for .xls files.

Bonus:

If this is not possible to do using the Microsoft.Office.Interop.Excel library, is there a free (Lesser GPL-ish) library I can use instead?

I would assume that Microsoft would have a tool for this, but we all know what assuming does...

possible duplicate of [asp.net web service using office 2010 COM](http://stackoverflow.com/questions/7382704/asp-net-web-service-using-office-2010-com) — John Saunders, Jan 31 '12 at 19:11
You cannot use Office Automation from a service process. See http://stackoverflow.com/questions/7382704/asp-net-web-service-using-office-2010-com. — John Saunders, Jan 31 '12 at 19:12

score 2 · Answer 1 · answered Jan 31 '12 at 19:06

if you are referencing the excel interops then you will need excel installed on the server to create the excel files.

you cannot directly prompt the user to save the excel file. the file is generated on the server. you would need to save the file on the server, then load the file into memory, delete the file and send the memory stream to the client. the client can then choose to save the file.

instead i would use a 3rd party library that can generate excel files without excel. typically they generate an xml-excel document. works the same, just creates the file as xml instead of a binary.

I have used http://www.carlosag.net/tools/excelxmlwriter/ for years with great success. I'm sure there are other options as well. same rules apply using this method. construct the workbook. then save to a stream (not file) and send the stream to the client. the client can then choose what to do with the file.

I have tried to use this (creating a Test file using the ASP.NET Help Files they provided), but I get strange errors: `Cannot have a DOCTYPE declaration outside of a prolog. Error processing resource 'Blah.aspx` — Onion-Knight, Jan 31 '12 at 20:46
yes, because webforms expect you to return html. you are returning a different kind of content. for that you can use a simple generic handler. ashx — Jason Meckley, Jan 31 '12 at 21:35

score 2 · Accepted Answer · answered Jan 31 '12 at 19:16

Like @Jason Meckley said, in order to use the interop to create Excel files on the server, you need Office installed on the machine. The workaround to this is to use Open XML:

From the MSDN page:

Open XML SDK 2.0 for Microsoft Office

This SDK is a superset of the Open XML SDK 1.0. In addition to the functionality provided by the Open XML SDK 1.0, it leverages.NET Language-Integrated Query (LINQ) and provides classes to construct and query the contents of parts within a package. You can use functional construction for composing documents, and LINQ queries for extracting information from documents.

Here are a couple of 'getting started' tutorials that I found useful:

Formatted Excel using SDK 2.0 and .NET

OpenXML SDK 2.0: Export a DataTable to Excel

I'm adding this functionality to an old project that uses Visual Studio 2005. Am I still able to use OpenXML with this? — Onion-Knight, Jan 31 '12 at 19:37
@Onion-Knight - Yep - Here's some snippets right from the MSDN site: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=14386 — zeroef, Jan 31 '12 at 20:22

score 0 · Answer 3 · answered Mar 09 '17 at 10:25

I like using EPPlus in this instance. It is simple to use and has almost the same structure as Excel.

For saving the document, you must use a Generic Handler:

Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
    Using pck As New ExcelPackage()
       Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add("MySheet")
       'Put your code here
       context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
       context.Response.AddHeader("content-disposition", "attachment;  filename=" & filename.text & ".xlsx")
       context.Response.BinaryWrite(pck.GetAsByteArray())
    End Using
End Sub

Marcos · Answer 4 · 2012-01-31T19:10:45.157

Try this...

loConectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFullFilePath +
                      ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=2\"";
fillSourceFromExcel(loConectionString);
private DataTable _loDtArchivoOrigen = null;
private void fillSourceFromExcel(string pConectionString)
{
        var loColumnasSelect = new StringBuilder();
        using (OleDbConnection oleDbConnection = new OleDbConnection(pConectionString))
        {
            if (oleDbConnection.State == ConnectionState.Closed) oleDbConnection.Open();
            DataTable dbSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
            DataTable dbColumns = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, dbSchema.Rows[0]["TABLE_NAME"], null });
            foreach (DataRow loRow in dbColumns.Rows)
            {
                loColumnasSelect.Append(" " + loRow["COLUMN_NAME"].ToString() + " as " + loRow["COLUMN_NAME"].ToString().Replace("F", "a") + ",");
            }
            using (OleDbCommand oleDbCommand = new OleDbCommand())
            {
                oleDbCommand.Connection = oleDbConnection;
                oleDbCommand.CommandText =
                    string.Format(
                        "SELECT " + loColumnasSelect.Remove(loColumnasSelect.Length - 1, 1) +
                        " FROM [{0}]", dbSchema.Rows[0]["TABLE_NAME"].ToString());
                using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter())
                {
                    oleDbDataAdapter.SelectCommand = oleDbCommand;
                    oleDbDataAdapter.Fill(_loDtArchivoOrigen);
                }
            }
            oleDbConnection.Close();
        }
}

-1 for the worthless try/catch block. Remove that and I'll upvote. — John Saunders, Jan 31 '12 at 19:08

Save Excel Document in ASP.NET

4 Answers4