How to Create an XLS from a CSV, List, or DataSet using C#?

Question

I currently have a CSV that I have built using StringBuilder in C#.

This CSV is generated by:

Querying the Database using LINQ
Dumping the Resulting entries into a List
Looping through the List and pulling out the needed Entities for the CSV
Formatting and adding the lines to the StringBuilder Variable
Using StreamWriter Method to export the CSV

Recently I have been informed that I need this file to be a XLS file instead of a CSV file. I have done some research, but I am a beginner coder, and am unsure of how to convert this file to an XLS or create an XLS from my Queried Data. Keeping in mind that after I retrieve the results of my query,the Data must be formatted properly before the export.

Ex.
Database Product Number: 9999-0000
Adjacent Systems Product #: 99990000BA

Database Date: 12/05/2010
Adjacent Systems Date: 2010/05/12

What is the best way I can go about creating an XLS or converting to an XLS from my current Code/Data?

I've solved this problem in the past by explaining to people that csv files can be opened by Excel, but I understand if they won't listen ;) — juharr, Dec 09 '10 at 23:35
Thanks for your opinion juharr. Initially this was my plan but I was recently informed that the file must be in .xls format to comply with the System it is being imported into. Otherwise I would just leave it in .csv where it opens nicely in Excel. — Lando, Dec 13 '10 at 16:36

score 1 · Answer 1 · answered Dec 09 '10 at 23:05

1

If you can produce an xlsx rather than xls, my preference is for the Open XML SDK from Microsoft.

answered Dec 09 '10 at 23:05

Tim Almond

12,088
10
40
50

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

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

edited May 23 '17 at 10:33

Community

1
1

answered Dec 09 '10 at 23:05

PMC

4,698
3
37
57

score 1 · Answer 3 · answered Dec 09 '10 at 23:11

i faced the same challenge few days ago and i found the solution (code below)

you will see a seesion["dsource"] which contains the datasource from the search / filtered search page

dont forget to add EnableEventValidation ="false"

this is the aspx page

<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation ="false"     CodeFile="csresults.aspx.cs" Inherits="csresults" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0     Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
<asp:Button ID="Button1" runat="server" Text="back" />
<asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="Export" />
<asp:GridView ID="gridview1" runat="server">
</asp:GridView>
</form>
</body>
</html>

here is the code behind

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Threading;
using System.IO;
using System.Reflection;

public partial class csresults : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

    gridview1.DataSource = Session["dsource"];
    gridview1.DataBind();




}


  public override void VerifyRenderingInServerForm(Control control)
{

}



protected void Button2_Click(object sender, EventArgs e)
{
    HtmlForm form = new HtmlForm();
    string attachment = "attachment; filename=Patients.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";
    StringWriter stw = new StringWriter();
    HtmlTextWriter htextw = new HtmlTextWriter(stw);
    form.Controls.Add(gridview1);
    this.Controls.Add(form);
    form.RenderControl(htextw);
    Response.Write(stw.ToString());
    Response.End();
}
}

score 1 · Accepted Answer · answered Dec 10 '10 at 21:15

Thanks for the Help Guys!

I ended up using mokokamello's code as a base, and that ended up leading me to the answer in a somewhat different light. In the end, I created a DataTable, added it to a DataSet and used a HttpResponse with a nested DataGrid to Export the Excel File. I was able to make the most sense out of this.

 public static void ExportDStoExcel(DataSet ds, string filename)
    {
        HttpResponse response = HttpContext.Current.Response;
        response.Clear();
        response.Charset = "";

        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                DataGrid dg = new DataGrid();
                dg.DataSource = ds.Tables[0];
                dg.DataBind();
                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }

    }

score 0 · Answer 5 · answered Aug 03 '11 at 13:43

You can go even simpler by writing your own xml file in MS Excel format which will open seamlessly in Excel.

Read more at: http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats#Excel_XML_Spreadsheet_example

Check the sample and note that you can strip down the code even further.

How to Create an XLS from a CSV, List, or DataSet using C#?

5 Answers5