在 ASP.NET 中生成 Excel 文件

I would just create a CSV file based on the data, because I see that as the cleanest, and Excel has good support for it. But if you require a more flexible format, I'm sure there's some 3rd party tools for generating real excel files.

CSV is easiest way. Most of the time it is linked to Excel. Otherwise you have to use the automation APIs or XML format. The APIs and XML are not that hard to use.

Information about generating XML for Excel

I've done this a couple of times and each time the easiest way was to simply return a CSV (Comma Separated Value) file. Excel imports it perfectly, and it's relatively fast to do.

If coming from a DataTable:

public static void DataTabletoXLS(DataTable DT, string fileName)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "utf-16";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xls", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";


string tab = "";
foreach (DataColumn dc in DT.Columns)
{
HttpContext.Current.Response.Write(tab + dc.ColumnName.Replace("\n", "").Replace("\t", ""));
tab = "\t";
}
HttpContext.Current.Response.Write("\n");


int i;
foreach (DataRow dr in DT.Rows)
{
tab = "";
for (i = 0; i < DT.Columns.Count; i++)
{
HttpContext.Current.Response.Write(tab + dr[i].ToString().Replace("\n", "").Replace("\t", ""));
tab = "\t";
}
HttpContext.Current.Response.Write("\n");
}
HttpContext.Current.Response.End();
}

From a Gridview:

public static void GridviewtoXLS(GridView gv, string fileName)
{
int DirtyBit = 0;
int PageSize = 0;
if (gv.AllowPaging == true)
{
DirtyBit = 1;
PageSize = gv.PageSize;
gv.AllowPaging = false;
gv.DataBind();
}


HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}.xls", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";


using (StringWriter sw = new StringWriter())
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
//  Create a table to contain the grid
Table table = new Table();


//  include the gridline settings
table.GridLines = gv.GridLines;


//  add the header row to the table
if (gv.HeaderRow != null)
{
Utilities.Export.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}


//  add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
Utilities.Export.PrepareControlForExport(row);
table.Rows.Add(row);
}


//  add the footer row to the table
if (gv.FooterRow != null)
{
Utilities.Export.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}


//  render the table into the htmlwriter
table.RenderControl(htw);


//  render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString().Replace("£", ""));
HttpContext.Current.Response.End();
}


if (DirtyBit == 1)
{
gv.PageSize = PageSize;
gv.AllowPaging = true;
gv.DataBind();
}
}


private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}


if (current.HasControls())
{
Utilities.Export.PrepareControlForExport(current);
}
}
}

You can output the data as html table cells, stick a .xls or .xlsx extension on it, and Excel will open it as if it were a native document. You can even do some limited formatting and formula calculations this way, so it's much more powerful than CSV. Also, outputting an html table ought to be pretty easy to do from a web platform like ASP.Net ;)

If you need multiple worksheets or named worksheets within your Excel Workbook, you can do something similar via an XML schema called SpreadSheetML. This is not the new format that shipped with Office 2007, but something completely different that works as far back as Excel 2000. The easiest way to explain how it works is with an example:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Your_name_here</Author>
<LastAuthor>Your_name_here</LastAuthor>
<Created>20080625</Created>
<Company>ABC Inc</Company>
<Version>10.2625</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6135</WindowHeight>
<WindowWidth>8445</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>


<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom" />
<Borders />
<Font />
<Interior />
<NumberFormat />
<Protection />
</Style>
</Styles>


<Worksheet ss:Name="Sample Sheet 1">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table1">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
<Cell><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">2</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">3</Data></Cell>
<Cell><Data ss:Type="Number">4</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">5</Data></Cell>
<Cell><Data ss:Type="Number">6</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">7</Data></Cell>
<Cell><Data ss:Type="Number">8</Data></Cell>
</Row>
</Table>
</Worksheet>


<Worksheet ss:Name="Sample Sheet 2">
<Table ss:ExpandedColumnCount="2" x:FullColumns="1" x:FullRows="1" ID="Table2">
<Column ss:Width="150" />
<Column ss:Width="200" />
<Row>
<Cell><Data ss:Type="String">A</Data></Cell>
<Cell><Data ss:Type="String">B</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">C</Data></Cell>
<Cell><Data ss:Type="String">D</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">E</Data></Cell>
<Cell><Data ss:Type="String">F</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">G</Data></Cell>
<Cell><Data ss:Type="String">H</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

since Excel understands HTML you can just write the data out as an HTML table to a temp file with an .xls extension, get the FileInfo for the file, and blow it back using

Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + fi.Name);
Response.AddHeader("Content-Length", fi.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(fi.FullName);
Response.End();

if you wanted to avoid the temp file, you could write to an in-memory stream and write the bytes back instead of using WriteFile

if the content-length header is omitted you could just write the html back directly, but this may not work correctly all the time in all browsers

If you fill a GridView with data you can use this function to get the HTML formatted data, but indicating the browser it's an excel file.

 Public Sub ExportToExcel(ByVal fileName As String, ByVal gv As GridView)


HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
HttpContext.Current.Response.ContentType = "application/ms-excel"


Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
Dim table As Table = New Table


table.GridLines = gv.GridLines


If (Not (gv.HeaderRow) Is Nothing) Then
PrepareControlForExport(gv.HeaderRow)
table.Rows.Add(gv.HeaderRow)
End If


For Each row As GridViewRow In gv.Rows
PrepareControlForExport(row)
table.Rows.Add(row)
Next


If (Not (gv.FooterRow) Is Nothing) Then
PrepareControlForExport(gv.FooterRow)
table.Rows.Add(gv.FooterRow)
End If


table.RenderControl(htw)


HttpContext.Current.Response.Write(sw.ToString)
HttpContext.Current.Response.End()


End Sub




Private Sub PrepareControlForExport(ByVal control As Control)


Dim i As Integer = 0


Do While (i < control.Controls.Count)


Dim current As Control = control.Controls(i)


If (TypeOf current Is LinkButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))


ElseIf (TypeOf current Is ImageButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))


ElseIf (TypeOf current Is HyperLink) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))


ElseIf (TypeOf current Is DropDownList) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))


ElseIf (TypeOf current Is CheckBox) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))


End If


If current.HasControls Then
PrepareControlForExport(current)
End If


i = i + 1


Loop


End Sub

If you have to use Excel instead of a CSV file you will need to use OLE automation on an Excel instance one the server. The easiest way to do this is to have a template file and programatically fill it in with the data. You save it to another file.

Tips:

• Don't do it interactively. Have the user kick off the process and then post a page with the link to the file. This mitigates potential performance issues while the spreadsheet is generated.
• Use the template as I described before. It makes it easier to modify it.
• Make sure that Excel is set to not pop up dialogs. On a web server this will hang the whole excel instance.
• Keep the Excel instance on a separate server, preferably behind a firewall, so it is not exposed as a potential security hole.
• Keep an eye on resource usage. Generating a spreadhseet over the OLE automation interface (the PIA's are just shims over this) is a fairly heavyweight process. If you need to scale this to high data volumes you may need to be somewhat clever with your architecture.

Some of the 'use mime-types to trick excel into opening HTML table' approaches would work if you don't mind the format of the file being a bit basic. These approaches also fob the CPU heavy work off onto the client. If you want fine-graned control over the format of the spreadsheet you will probably have to use Excel itself to generate the file as described above.

I either go the CSV route (as described above), or more often these days, I use Infragistics NetAdvantage to generate the file. (The very vast majority of the time where Infragistics is in play, we're just exporting an existing UltraWebGrid, which is essentially a one-LOC solution unless extra formatting tweaks are needed. We could manually generate an Excel/BIFF file as well, but there's rarely a need to.)

man, in .net i guess you could have a component that could do that, but in classic asp I have already done it creating an html table and changing the mime tipe of the page to vnd/msexcel. I guess that if you use a gridview and change the mime type maybe it should work, because the gridview is an html table.

The only bulletproof way of avoiding the "It looks like these numbers are stored as text" green triangles is to use the Open XML format. It is worth using it, just to avoid the inevitable green triangles.

The best method i've seen for excel reports is to write out the data in XML with a XML extension and stream it to clients with the correct content type. (application/xls)

This works for any report which requires basic formating, and allows you to compare against existing spreadsheets by using text comparison tools.

I personally prefer the XML method. I'll return the data from the database in a Dataset, save it to XMl, then I create an xslt file that contains a transformation rule that will format a proper document, and a simple XML transform will finish the job up. The best part of about this you can format cells, do conditional formatting, setup headers and footers, and even set print ranges.

CSV

Pros:

• Simple

Cons:

• It may not work in other locales or in different Excel configurations (i.e. List separator)
• Can't apply formatting, formulas, etc

HTML

Pros:

• Still pretty Simple
• Supports simple formating and formulas

Cons:

• You have to name the file as xls and Excel may warn you about opening a non native Excel file
• One worksheet per workbook

OpenXML (Office 2007 .XLSX)

Pros:

• Native Excel format
• Supports all Excel features
• Do not require an install copy of Excel
• Can generate Pivot tables
• Can be generated using open source project EPPlus

Cons:

• Limited compatibility outside Excel 2007 (shouldn't be a problem nowadays)
• Complicated unless you're using a third party component

SpreadSheetML (open format XML)

Pros:

• Simple compared to native Excel formats
• Supports most Excel features: formating, styles, formulas, multiple sheets per workbook
• Excel does not need to be installed to use it
• No third party libraries needed - just write out your xml
• Documents can be opened by Excel XP/2003/2007

Cons:

• Lack of good documentation
• Not supported in older versions of Excel (pre-2000)
• Write-only, in that once you open it and make changes from Excel it's converted to native Excel.

XLS (generated by third party component)

Pros:

• Generate native Excel file with all the formating, formulas, etc.

Cons:

• Cost money
• Add dependencies

COM Interop

Pros:

• Uses native Microsoft libraries
• Read support for native documents

Cons:

• Very slow
• Dependency/version matching issues
• Concurrency/data integrity issues for web use when reading
• Very slow
• Scaling issues for web use (different from concurrency): need to create many instances of heavy Excel app on the server
• Requires Windows
• Did I mention that it's slow?

Just avoid COM Interop via Microsoft.Office.Interop namespace. It is so damn slow and unreliable and unscalable. Not applicable for masochists.

Based on the answers given, and consultation with coworkers, it appears that the best solution is to generate either an XML file or HTML tables and push it down as an attachment. The one change recommended by my co-workers is that the data (i.e. the HTML tables) can be written directly to the Response object, thus eliminating the need to write out a file, which can be troublesome due to permissions problems, I/O contention, and ensuring that scheduled purging occurs.

Here's a snippet of the code... I haven't checked this yet, and I haven't supplied all the called code, but I think it represents the idea well.

    Dim uiTable As HtmlTable = GetUiTable(groupedSumData)


Response.Clear()


Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", String.Format("inline; filename=OSSummery{0:ddmmssf}.xls", DateTime.Now))


Dim writer As New System.IO.StringWriter()
Dim htmlWriter As New HtmlTextWriter(writer)
uiTable.RenderControl(htmlWriter)
Response.Write(writer.ToString)


Response.End()

Assuming this is for an intranet, where you can set permissions and mandate IE, you can generate the workbook client side with JScript/VBScript driving Excel. This gives you native Excel formatting, without the hassle of trying to automate Excel on the server.

I'm not sure I'd really recommend this approach anymore except in fairly niche scenarios, but it was fairly common during the classic ASP heydays.

You can of course always go for a third party components. Personally, I have had a good experience with Spire.XLS http://www.e-iceblue.com/xls/xlsintro.htm

The component is pretty easy to use within your application:

        Workbook workbook = new Workbook();


//Load workbook from disk.
workbook.LoadFromFile(@"Data\EditSheetSample.xls");
//Initailize worksheet
Worksheet sheet = workbook.Worksheets[0];


//Writes string
sheet.Range["B1"].Text = "Hello,World!";
//Writes number
sheet.Range["B2"].NumberValue = 1234.5678;
//Writes date
sheet.Range["B3"].DateTimeValue = System.DateTime.Now;
//Writes formula
sheet.Range["B4"].Formula = "=1111*11111";


workbook.SaveToFile("Sample.xls");

we export data from a datagrid to excel all the time. Converting it to HTML then writing to an excel file

Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Response.AddHeader("content-disposition", "fileattachment;filename=YOURFILENAME.xls")
Me.EnableViewState = False
Dim sw As System.IO.StringWriter = New System.IO.StringWriter
Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
ClearControls(grid)
grid.RenderControl(hw)
Response.Write(sw.ToString())
Response.End()

The only gotcha with this method was that a lot of our grids had buttons or links in them so you need this too:

'needed to export grid to excel to remove link button control and represent as text
Private Sub ClearControls(ByVal control As Control)
Dim i As Integer
For i = control.Controls.Count - 1 To 0 Step -1
ClearControls(control.Controls(i))
Next i


If TypeOf control Is System.Web.UI.WebControls.Image Then
control.Parent.Controls.Remove(control)
End If


If (Not TypeOf control Is TableCell) Then
If Not (control.GetType().GetProperty("SelectedItem") Is Nothing) Then
Dim literal As New LiteralControl
control.Parent.Controls.Add(literal)
Try
literal.Text = CStr(control.GetType().GetProperty("SelectedItem").GetValue(control, Nothing))
Catch
End Try
control.Parent.Controls.Remove(control)
Else
If Not (control.GetType().GetProperty("Text") Is Nothing) Then
Dim literal As New LiteralControl
control.Parent.Controls.Add(literal)
literal.Text = CStr(control.GetType().GetProperty("Text").GetValue(control, Nothing))
control.Parent.Controls.Remove(control)
End If
End If
End If
Return
End Sub

I found that somewhere, it works well.

This is a free wrapper around SpreadML--it works great.

http://www.carlosag.net/Tools/ExcelXmlWriter/

One of the problems I've ran across using one of the solutions suggested above which are similar to this answer is that if you push the content out as an attachment (what I've found to be the cleanest solution for non-ms browsers), then open it in Excel 2000-2003, its type is an "Excel Web Page" and not a native Excel document.

Then you have to explain to users how to use "Save as type" from within Excel to convert it to an Excel document. This is a pain if users need to edit this document and then re-upload it to your site.

My recommendation is to use CSV. It's simple and if users do open it from within Excel, Excel at least prompts them to save it in its native format.

Here's a report that pulls from a stored procedure. The results are exported to Excel. It uses ADO instead of ADO.NET and the reason why is this line

oSheet.Cells(2, 1).copyfromrecordset(rst1)

It does most of the work and isn't available in ado.net.

‘Calls stored proc in SQL Server 2000 and puts data in Excel and ‘formats it


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


Dim cnn As ADODB.Connection
cnn = New ADODB.Connection
cnn.Open("Provider=SQLOLEDB;data source=xxxxxxx;" & _
"database=xxxxxxxx;Trusted_Connection=yes;")


Dim cmd As New ADODB.Command




cmd.ActiveConnection = cnn




cmd.CommandText = "[sp_TomTepley]"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
cmd.CommandTimeout = 0
cmd.Parameters.Refresh()




Dim rst1 As ADODB.Recordset
rst1 = New ADODB.Recordset
rst1.Open(cmd)


Dim oXL As New Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet


'oXL = CreateObject("excel.application")
oXL.Visible = True
oWB = oXL.Workbooks.Add
oSheet = oWB.ActiveSheet


Dim Column As Integer
Column = 1


Dim fld As ADODB.Field
For Each fld In rst1.Fields


oXL.Workbooks(1).Worksheets(1).Cells(1, Column).Value = fld.Name
oXL.Workbooks(1).Worksheets(1).cells(1, Column).Interior.ColorIndex = 15
Column = Column + 1


Next fld


oXL.Workbooks(1).Worksheets(1).name = "Tom Tepley Report"
oSheet.Cells(2, 1).copyfromrecordset(rst1)
oXL.Workbooks(1).Worksheets(1).Cells.EntireColumn.AutoFit()




oXL.Visible = True
oXL.UserControl = True


rst1 = Nothing


cnn.Close()
Beep()


End Sub

Here's a solution the streams the datatable out as a CSV. Fast, clean, and easy, and it handles commas in the input.

public static void ExportToExcel(DataTable data, HttpResponse response, string fileName)
{
response.Charset = "utf-8";
response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
response.Cache.SetCacheability(HttpCacheability.NoCache);
response.ContentType = "text/csv";
response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);


for (int i = 0; i < data.Columns.Count; i++)
{
response.Write(data.Columns[i].ColumnName);
response.Write(i == data.Columns.Count - 1 ? "\n" : ",");
}
foreach (DataRow row in data.Rows)
{
for (int i = 0; i < data.Columns.Count; i++)
{
response.Write(String.Format("\"{0}\"", row[i].ToString()));
response.Write(i == data.Columns.Count - 1 ? "\n" : ",");
}
}


response.End();
}

I recommend free opensource excel generation libruary which is based on OpenXML

It helped me several months ago.

You can create nicely formatted Excel files using this library, quite easily: http://officehelper.codeplex.com/documentation.

Microsoft Office does not need to be installed on the webserver!

just created a function to export from a web form C# to excel hope it helps others

    public void ExportFileFromSPData(string filename, DataTable dt)
{
HttpResponse response = HttpContext.Current.Response;


//clean up the response.object
response.Clear();
response.Buffer = true;
response.Charset = "";


// set the response mime type for html so you can see what are you printing
//response.ContentType = "text/html";
//response.AddHeader("Content-Disposition", "attachment;filename=test.html");


// set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
response.ContentEncoding = System.Text.Encoding.UTF8;
response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());


//style to format numbers to string
string style = @"<style> .text { mso-number-format:\@; } </style>";
response.Write(style);


// create a string writer
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// instantiate a datagrid
GridView dg = new GridView();
dg.DataSource = dt;
dg.DataBind();


foreach (GridViewRow datarow in dg.Rows)
{
//format specific cell to be text
//to avoid 1.232323+E29 to get 1232312312312312124124
datarow.Cells[0].Attributes.Add("class", "text");
}


dg.RenderControl(htw);
response.Write(sw.ToString());
response.End();
}
}
}