Generate Excel Spreadsheet from CSV (ASP.NET C#)

Question

Possible Duplicate:
Create Excel (.XLS and .XLSX) file from C#

I have some code that generates a zip file that contains multiple CSV files and streams it back to the user (no file is saved on the server). However, I want to create an excel workbook instead (can be traditional xls or Office Open XML xlsx format) with each CSV 'file' being a spreadsheet.

How can I do this, without resorting to Office Automation on the server or a commercial 3rd party component?

Answer 1

You can use OleDB to generate simple tables in Excel files.

Note that you will need to generate a temp file on the server.

Example.

Note that their example is incorrect and needs to use an OleDbConnectionStringBuilder, like this:

OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();

if (isOpenXML)
    builder.Provider = "Microsoft.ACE.OLEDB.12.0";
else
    builder.Provider = "Microsoft.Jet.OLEDB.4.0";

builder.DataSource = fileName;
builder["Extended Properties"] = "Extended Properties=\"Excel 8.0;HDR=YES;\""

using (var con = new OleDbConnection(builder.ToString())) {
    ...
}

Answer 2

You can write the excel xml by yourself. Here is a nice lib for the task, maybe it is something for you.

// Edit Link: http://www.carlosag.net/Tools/ExcelXmlWriter/Generator.aspx

Answer 3

The XML format for Excel is quite simple and there's absolutely no need to do any automation. The full reference is up on MSDN: http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

Answer 4

        Response.ContentType = "application/vnd.ms-excel";

The ContentType property specifies the HTTP content type for the response. If no ContentType is specified, the default is text/HTML.

Get all your data in a DataGrid and then get it from it can be done with:

DataGrid.RenderControl

Outputs server control content to a provided HtmlTextWriter object and stores tracing information about the control if tracing is enabled.

        SqlConnection cn = new SqlConnection("yourconnectionstring");
        cn.Open();
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Users", cn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        DataGrid dg = new DataGrid();
        dg.DataSource = dt;
        dg.DataBind();

        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

        dg.RenderControl(hw);
        cn.Close();

        Response.Clear();
        Response.ContentType = "application/vnd.ms-excel";
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();