从ASP.NET将数据导出为Excel文件

I have data like below

我有如下数据

AAAAAA
BBBBBB
CCCCCC
DDDDDD
EEEEEE

Now there is a button on the page, and when I click the button, the browser would download an Excel file with the data above, and stay on the current page. Is there any simple way to do it? The data is very simple. Only one column, and not huge.

现在页面上有一个按钮，当我点击按钮时，浏览器会下载上面数据的Excel文件，并停留在当前页面上。有什么简单的方法吗?数据非常简单。只有一列，而不是很大。

Best Regards,

最好的问候,

5 个解决方案

#1

You can write out the data directly to the response stream. Set the mime type to excel and write the data out as :

您可以直接将数据写到响应流。将mime类型设置为excel，并将数据写为:

HTML
HTML
CSV
CSV
Spreadsheet XML
电子表格XML
OOXML (.xlsx)
OOXML(.xlsx)

If you want to use OOXML there are libraries such as Simple OOXML. Note this is the .xlsx format.

如果您想使用OOXML，有一些库，比如简单的OOXML。注意这是.xlsx格式。

The following code sets the headers required for a .xls file

下面的代码设置.xls文件所需的头文件

'Send response with content type to display as MS Excel
context.Response.Clear()
context.Response.Buffer = True

context.Response.AddHeader("content-disposition", String.Format( "attachment;filename={0}", fileName))
context.Response.ContentEncoding = Encoding.UTF8

context.Response.Cache.SetCacheability(HttpCacheability.Private)
context.Response.ContentType = "application/vnd.ms-excel"

'Write to response
context.Response.Write("csv,data,goes,here")

context.Response.End()

#2

Yes, you can export the information as a csv file, and give it an Excel file extension. Excel will recognize it's not native Excel format and allows to import with a warning message. For the download, you can search the internet for regular file downloads with a custom MIME type in ASP.NET.

是的，您可以将信息导出为csv文件，并为其提供Excel文件扩展名。Excel将识别它不是本地的Excel格式，并允许导入一个警告消息。对于下载，您可以在internet上搜索常规文件下载，并在ASP.NET中使用自定义MIME类型。

Because of the warning message, the above is not the preferred way. You could also use a library to generate a native Excel file. I've used Aspose.Cells successfully in past projects.

由于警告消息，上面的方法不是首选的方法。还可以使用库生成本地Excel文件。我用Aspose。单元格在过去的项目中获得成功。

#3

Short of automating excel on your server, which is not recommended, the easiest way is to use a string builder to create the required output and then write this to the HttpResponse setting the content type to "text/csv", setting the appropriate header information.

除了不建议在服务器上自动化excel之外，最简单的方法是使用字符串构建器创建所需的输出，然后将其写入HttpResponse设置为“text/csv”，设置适当的头信息。

While this is not strictly an excel document the user downloading the file will be prompted to open it in excel if they have it installed or alternatively any other spreadsheet based editor.

虽然这并不是一个严格意义上的excel文档，但是下载该文件的用户如果安装了该文件，或者安装了其他基于电子表格的编辑器，就会被提示在excel中打开该文件。

The following snippet should get you going:

下面的代码片段应该会让你:

string docName  = "MyExcelDoc"
StringBuilder sb = new StringBuilder();
sb.AppendLine("AAAAAA");
sb.AppendLine("BBBBBB");

context.Response.ClearContent();
context.Response.ContentType = "text/csv";
context.Response.AddHeader("content-disposition", "attachment; filename=" + docName + ".csv");
context.Response.Write(sb.ToString());            
context.Response.Flush();

#4

An alternative to the above mentioned solutions is to hook into the Excel COM object and generate a spreadsheet using their API.

上述解决方案的一个替代方案是挂钩到Excel COM对象，并使用它们的API生成电子表格。

This would mean you would have to have excel installed on the server so the other solutions are probably better.

这意味着您必须在服务器上安装excel，以便其他解决方案可能更好。

#5

NPOI is an open source project which can help you read/write xls, doc, ppt files.

NPOI是一个开源项目，可以帮助您阅读/编写xls、doc、ppt文件。

Very simple and give right Excel format.

非常简单，并提供正确的Excel格式。

#1