How to create excel dynamically and download in c#?

Question

I have the sample code below which will create a excel file. I dont want to save this in server.

  if (app == null)
                    return;
                app.Visible = true;
                object loc;

                workbook = app.Workbooks.Add(1);
                workbook.SaveAs("D:\\Sample.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing);

without saving how to download this on the fly?

score 4 · Answer 1 · answered Nov 18 '14 at 18:22

You're violating the most basic rule of Office automation on a server -- don't do it! Microsoft Office is not designed for non-interactive login sessions such as used by a web server. You may find that this works now given that your code above doesn't do much, but Microsoft can pretty much guarantee that you'll have all sorts of issues over time as your code changes, your server is configured and Office products are updated.

Instead, Office documents should be generated by one of the many tools that aren't directly tied to the Office suite itself (Office documents have been using an open format called OpenXML since Office 2007). Those tools directly generate the XML files and the packaging metadata that are ultimately stored in, say, an xslx file.

I usually like to use the Microsoft OpenXML SDK, which is a swiss-army-knife for these things. It's easy to use improperly, but you can create pretty much any Office document with it. http://msdn.microsoft.com/en-us/library/office/bb448854.aspx

The Open XML SDK Productivity tool is a must-have if you'll be using the OpenXML SDK. Both the SDK and the Productivity Tool are available for download: http://www.microsoft.com/en-us/download/details.aspx?id=30425.

You fell victim to one of the classic blunders - The most famous of which is "never get involved in a land war in Asia" - but only slightly less well-known is this: "Never use office automation in a non-interactive session"! — Mitch, Nov 18 '14 at 18:24

score 1 · Answer 2 · edited May 23 '17 at 11:57

1

Take a look at EPPlus I'm using it for a number of project right now. If all you are doing is creating excel files it's a little easier to get up and going then using the OpenXML SDK.

also take a look at this question Create Excel (.XLS and .XLSX) file from C#

edited May 23 '17 at 11:57

Community

1
1

answered Nov 18 '14 at 18:46

Bob The Janitor

20,292
10
49
72

score 0 · Answer 3 · answered Nov 20 '14 at 13:20

You need to save the file to server and download using this code.

  public void Downloadfile(string sFileName, string sFilePath)
 {
    var file = new System.IO.FileInfo(sFilePath);

    Response.Clear();
    Response.AddHeader("Content-Disposition", "attachment; filename=" + sFileName);
    Response.AddHeader("Content-Length", file.Length.ToString(CultureInfo.InvariantCulture));
    Response.ContentType = "application/octet-stream";
    Response.WriteFile(file.FullName);
    Response.End();
  }

How to create excel dynamically and download in c#?

3 Answers3