blocks|key|1856956|text|我只会根据数据创建一个CSV文件，因为我认为这是最干净的，Excel对它有很好的支持。但如果你需要一种更灵活的格式，我相信有一些第三方工具可以生成真正的excel文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1856957|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|2071220|text|CSV是最简单的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2071221|Information+about+generating+XML+for+Excel|offset|length|2071222|entityMap|0|LINK|mutability|MUTABLE|url|http://blogs.msdn.com/brian_jones/archive/2005/06/27/433152.aspx^0|0|0|16|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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.

<a href="http://blogs.msdn.com/brian_jones/archive/2005/06/27/433152.aspx" rel="nofollow noreferrer">Information about generating XML for Excel</a>

blocks|key|1857360|text|我已经这样做了几次，每次最简单的方法都是简单地返回一个CSV+(逗号分隔值)文件。Excel完美地导入了它，而且它的执行速度相对较快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1857361|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1857394|text|由于Excel能够理解HTML，因此您可以将数据作为HTML表写出到一个扩展名为.xls的临时文件中，获取该文件的FileInfo，然后使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1857395|Response.Clear();
Response.AddHeader("Content-Disposition",+"attachment;+filename="+%2B+fi.Name);
Response.AddHeader("Content-Length",+fi.Length.ToString());
Response.ContentType+=+"application/octet-stream";
Response.WriteFile(fi.FullName);
Response.End();|code-block|syntax|javascript|1857396|如果要避免使用临时文件，可以写入内存中的流，然后将字节写回，而不是使用WriteFile|1857397|如果省略了content-length头，您可以直接写回html，但这并不是在所有浏览器中都能正常工作。|1857398|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>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();
</code></pre>

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

blocks|key|2071342|text|如果你用数据填充一个GridView，你可以使用这个函数来获得超文本标记语言格式的数据，但是要告诉浏览器它是一个excel文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2071343|+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+%2B+1

++++++++Loop

++++End+Sub|code-block|syntax|javascript|2071344|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code> 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 &lt; 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
</code></pre>

blocks|key|2071499|text|我要么走CSV路线(如上所述)，要么现在更多地使用Infragistics来生成文件。(在大多数情况下，我们只是导出一个现有的UltraWebGrid，这本质上是一个one-LOC解决方案，除非需要额外的格式调整。我们也可以手动生成Excel/BIFF文件，但很少需要这样做。)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2071500|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.)

blocks|key|1857751|text|在.net中，我猜你可能有一个组件可以做到这一点，但在经典的asp中，我已经创建了一个html表，并将页面的mime+tipe更改为vnd/msexcel。我猜想如果你使用一个网格视图并改变mime类型，它可能会起作用，因为网格视图是一个html表格。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1857752|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1857806|text|避免“这些数字看起来像是存储为文本”的绿色三角形的唯一防弹方法是使用Open+XML格式。它是值得使用的，只是为了避免不可避免的绿色三角形。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1857807|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|2071715|text|我所见过的处理excel报表的最佳方法是以XML格式写出带有XML扩展名的数据，然后以正确的内容类型将其流式传输给客户端。(应用程序/xls)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2071716|这适用于任何需要基本格式的报告，并允许您使用文本比较工具与现有电子表格进行比较。|2071717|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|2071740|text|我个人更喜欢XML方法。我将在Dataset中返回数据库中的数据，将其保存到XMl，然后创建一个xslt文件，其中包含一个转换规则，该转换规则将对适当的文档进行格式化，一个简单的XML转换就可以完成这项工作。其中最好的部分是你可以格式化单元格，设置条件格式，设置页眉和页脚，甚至设置打印范围。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2071741|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1857883|text|只要避免通过Microsoft.Office.Interop命名空间进行COM互操作即可。它太慢了，不可靠，也不能扩展。不适用于受虐狂。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1857884|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1857932|text|根据给出的答案，以及与同事的协商，似乎最好的解决方案是生成XML文件或HTML表，并将其作为附件下推。我的同事建议的一个更改是，数据(即HTML表)可以直接写入响应对象，从而消除了写出文件的需要，这可能会因为权限问题、I/O争用而麻烦，并确保发生计划的清除。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1857933|这是一段代码..。我还没有检查过这一点，也没有提供所有被调用的代码，但我认为它很好地代表了这个想法。|1857934|++++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()|code-block|syntax|javascript|1857935|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code> 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()
</code></pre>

blocks|key|2071906|text|假设这是针对intranet的，您可以在intranet中设置权限和强制IE，您可以使用JScript/VBScript+driving+Excel生成工作簿客户端。这为您提供了本机Excel格式，而无需尝试在服务器上自动执行Excel。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2071907|我不确定我是否真的会推荐这种方法，除非是在相当小众的场景中，但在经典的ASP全盛时期，它是相当普遍的。|2071908|entityMap|0|LINK|mutability|MUTABLE|url|http://www.codepedia.com/1/ASPFAQ_Open_Excel_Application_Client_Side^0|18|U|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Assuming this is for an intranet, where you can set permissions and mandate IE, you can generate the workbook client side with <a href="http://www.codepedia.com/1/ASPFAQ_Open_Excel_Application_Client_Side" rel="nofollow noreferrer">JScript/VBScript driving Excel</a>. 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.

blocks|key|2071949|text|当然，你也可以选择第三方组件。就我个人而言，我对Spire.XLS+http://www.e-iceblue.com/xls/xlsintro.htm有很好的体验。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2071950|该组件在您的应用程序中非常容易使用：|2071951|++++++++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");|code-block|syntax|javascript|2071952|entityMap|0|LINK|mutability|MUTABLE|url|http://www.e-iceblue.com/xls/xlsintro.htm^0|Y|15|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

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

The component is pretty easy to use within your application:

<pre><code> 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");
</code></pre>

blocks|key|1858071|text|我们从数据网格中导出数据，以始终保持出色。将其转换为HTML，然后写入excel文件|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1858072|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()|code-block|syntax|javascript|1858073|这种方法唯一的问题是，我们的很多网格都有按钮或链接，所以你也需要这样做：|1858074|'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|1858075|我发现在某些地方，它工作得很好。|1858076|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>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()
</code></pre>

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

<pre><code>'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
</code></pre>

I found that somewhere, it works well.

blocks|key|2072012|text|这是一个围绕SpreadML的免费包装器--它工作得很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2072013|http://www.carlosag.net/Tools/ExcelXmlWriter/|offset|length|2072014|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|19|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

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

<a href="http://www.carlosag.net/Tools/ExcelXmlWriter/" rel="noreferrer">http://www.carlosag.net/Tools/ExcelXmlWriter/</a>

blocks|key|1858231|text|我在使用上面建议的类似于this+answer的解决方案时遇到的一个问题是，如果您将内容作为附件推出(我发现对于非ms浏览器来说，这是最干净的解决方案)，然后在Excel2000-2003中打开它，它的类型是"Excel+Web+Page“，而不是本地Excel文档。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1858232|然后，您必须向用户解释如何在Excel中使用"Save+as+type“将其转换为Excel文档。如果用户需要编辑此文档，然后将其重新上传到您的站点，这将是一种痛苦。|1858233|我的建议是使用CSV。它很简单，如果用户在Excel中打开它，Excel至少会提示他们将其保存为本机格式。|1858234|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/150339/generating-an-excel-file-in-aspnet#153384^0|C|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

One of the problems I've ran across using one of the solutions suggested above which are similar to <a href="https://stackoverflow.com/questions/150339/generating-an-excel-file-in-aspnet#153384" title="Pushing Excel content through the response">this answer</a> 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.

blocks|key|1858418|text|这是一个从存储过程中提取的报告。结果将导出到Excel。它使用的是ADO而不是ADO.NET，原因是下面这行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1858419|oSheet.Cells(2,+1).copyfromrecordset(rst1)|code-block|syntax|javascript|1858420|它完成了大部分工作，并且在ado.net中不可用。|1858421|‘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+%2B+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|1858422|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>oSheet.Cells(2, 1).copyfromrecordset(rst1)
</code></pre>

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

<pre><code>‘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;" &amp; _
 "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
</code></pre>

blocks|key|1858624|text|这里有一个解决方案，它将datatable作为CSV流出。快速、整洁和简单，并且它可以处理输入中的逗号。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1858625|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="+%2B+fileName);

++++for+(int+i+=+0;+i+<+data.Columns.Count;+i%2B%2B)
++++{
+++++++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%2B%2B)
++++++++{
++++++++++++response.Write(String.Format("\"{0}\"",+row[i].ToString()));
++++++++++++response.Write(i+==+data.Columns.Count+-+1+?+"\n"+:+",");
++++++++}
++++}

++++response.End();
}|code-block|syntax|javascript|1858626|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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 &lt; 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 &lt; data.Columns.Count; i++)
 {
 response.Write(String.Format("\"{0}\"", row[i].ToString()));
 response.Write(i == data.Columns.Count - 1 ? "\n" : ",");
 }
 }

 response.End();
}
</code></pre>

blocks|key|1858696|text|我推荐基于OpenXML的free+opensource+excel+generation+libruary|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1858697|几个月前它帮了我大忙。|1858698|entityMap|0|LINK|mutability|MUTABLE|url|http://closedxml.codeplex.com/^0|D|15|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

I recommend <a href="http://closedxml.codeplex.com/" rel="nofollow">free opensource excel generation libruary</a> which is based on OpenXML 

It helped me several months ago.

blocks|key|2072679|text|您可以使用这个库很容易地创建格式良好的Excel文件：http://officehelper.codeplex.com/documentation。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2072680|Microsoft+Office不需要安装在does服务器上！|2072681|entityMap|0|LINK|mutability|MUTABLE|url|http://officehelper.codeplex.com/documentation^0|R|1A|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

You can create nicely formatted Excel files using this library, quite easily: <a href="http://officehelper.codeplex.com/documentation" rel="nofollow">http://officehelper.codeplex.com/documentation</a>.

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

blocks|key|2072792|text|我刚刚创建了一个从网页表单C#导出到excel的函数，希望能对其他人有所帮助|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2072793|++++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=\""+%2B+filename+%2B+"\"");
++++++++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%2BE29+to+get+1232312312312312124124
++++++++++++++++++++datarow.Cells[0].Attributes.Add("class",+"text");
++++++++++++++++}

++++++++++++++++dg.RenderControl(htw);
++++++++++++++++response.Write(sw.ToString());
++++++++++++++++response.End();
++++++++++++}
++++++++}
+++++}|code-block|syntax|javascript|2072794|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code> 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 = @"&lt;style&gt; .text { mso-number-format:\@; } &lt;/style&gt;";
 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();
 }
 }
 }
</code></pre>

I am about to add a section to an ASP.NET app (VB.NET codebehind) that will allow a user to get data returned to them as an Excel file, which I will generate based on database data. While there are several ways of doing this, each has its own drawbacks. How would you return the data? I'm looking for something that's as clean and straightforward as possible.

Generating an Excel file in ASP.NET

数据网格

SQL Server

数据库

服务器

我即将在ASP.NET应用程序(VB.NET代码后台)中添加一个部分，它将允许用户将数据以Excel文件的形式返回给他们，我将根据数据库数据生成该文件。虽然有几种方法可以做到这一点，但每种方法都有自己的缺点。您将如何返回数据？我正在寻找一个尽可能干净和直接的东西。

问在ASP.NET中生成Excel文件
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在ASP.NET中生成Excel文件EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在ASP.NET中生成Excel文件
EN