CODEWITHSUNDEEP
c#asp.netvb.netexcelsave
Onion-Knight
Onion-Knight

Reputation: 3545

Save Excel Document in ASP.NET

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...

Upvotes: 1

Views: 4732

Answers (4)

Christiaan du Plessis
Christiaan du Plessis

Reputation: 9

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

Upvotes: 0

zeroef
zeroef

Reputation: 1959

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

Upvotes: 2

Marcos
Marcos

Reputation: 90

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();
        }
}

Upvotes: 0

Jason Meckley
Jason Meckley

Reputation: 7591

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.

Upvotes: 2

Related Questions