How to Create an XLS from a CSV, List, or DataSet using C#?_问答_开发者

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 Da开发者_JAVA百科te: 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?

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

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

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

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

    }

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.