How to export a List to Excel as the result of a web request

Question

I want to know how to export a List (like a datagrid) to excel.

I am not talking about a CSV, but a real xlsx file that is XML and compressed, etc.

I have only seen a few examples about Open XML from Microsoft.

Second thing and the most important, how do you save that excel not on disk at all, but instead write it back to the client on the web using response.write....

Bill.

Answer 1

I usually do it by rendering a DataGrid control that is bound to the list and I set the response content-type to "application/vnd.ms-excel". The file then opens in Excel. Excel gives a warning about the file being of the wrong type, but it opens it nevertheless.

The code I use is something like this in Page.Render():

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=myfilename.xls");

var grid = new DataGrid();
grid.DataSource = myList;
grid.DataBind();
grid.Render(writer);

Response.End();

I know that this does not answer your question directly, but maybe it will help anyway.

Answer 2

You will want to use the Office Open XML SDK (or a library which implements this SDK for you) in either scenario. I recommend getting familiar with the file formats first, and the Open XML Developer site can help. This can often be a non-trivial task, but you can make your life easier using XLINQ if your data is truly that simple.

List<Person> people = new List<Person>(sourceData);
XNamespace ns = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";

Func<Person, XElement> nameColumn =
    (person) => new XElement(ns + "c", new XAttribute("r", "A1"),
                    new XElement(ns + "v", person.Name);
Func<Person, XElement> ageColumn =
    (person) => new XElement(ns + "c", new XAttribute("r", "A2"),
                    new XElement(ns + "v", person.Age.ToString());

var worksheet = new XDocument(
    new XElement(ns + "worksheet",
        new XElement(ns + "sheetData",
            people.Select((pp,rr) =>
                new XElement(ns + "row",
                             new XAttribute("r", (rr + 1).ToString()),
                             nameColumn(pp),
                             ageColumn(pp))
            ).ToArray()
        )
    )
);

Answer 3

Based on my knowledge I would:

1. Create a new XLS file using Office Interop.
2. Write file to disk.
3. Send it to the client using Response.WriteFile.
4. Delete file from disk.

I'm sure there's a more slick way.

Edit: You should obviously look at this instead. :)