使用 C# 写入 Excel 文件的最佳和最快方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/871115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best and fastest way to write into Excel file using C#?
提问by
I am trying to write into excel file using OLEDB (without automation). I have around 500 rows of data which I get from some other application and then write into Excel file one by one using 'INSERT INTO..' Query. I am sure that there is no delay in reading data from the other application. I checked that. The total time taken to write into the excel file for 500 rows in around 3 minutes. This is too much. This is definitely because of the file write operation.
我正在尝试使用 OLEDB（没有自动化）写入 excel 文件。我有大约 500 行数据，这些数据是从其他应用程序获取的，然后使用“INSERT INTO..”查询一一写入 Excel 文件。我确信从其他应用程序读取数据没有延迟。我查了一下。在大约 3 分钟内写入 500 行的 excel 文件所需的总时间。这太多了。这肯定是因为文件写操作。
What would be the best way to make this fast? Should I use some other technique for writing? Should I try a technique with automation?
加快速度的最佳方法是什么？我应该使用其他一些写作技巧吗？我应该尝试一种自动化技术吗？
http://support.microsoft.com/kb/306023This link show many techniques, but not sure which one to use.
http://support.microsoft.com/kb/306023此链接展示了许多技术，但不确定使用哪一种。
回答by johnofcross
Well, the most basic (but also the most complicated) way to write Excel is creating a binary file following the BIFF format. With the BIFF format, you need to outline how the Excel file is structured. It's very complex, but you have control, on a per cell basis, on what to write.
好吧，编写 Excel 的最基本（但也是最复杂）的方法是创建一个遵循 BIFF 格式的二进制文件。对于 BIFF 格式，您需要概述 Excel 文件的结构。这非常复杂，但您可以在每个单元格的基础上控制要写入的内容。
There are other CodeProject examples on exporting a DataSet into an Excel file. The one in mind creates an XML file that follows the Excel XML format. It also writes on a per cell basis. Look at:
还有其他关于将数据集导出到 Excel 文件的 CodeProject 示例。头脑中的人会创建一个遵循 Excel XML 格式的 XML 文件。它还基于每个单元进行写入。看着：
http://www.codeproject.com/KB/office/ExportDataSetToExcel.aspx
http://www.codeproject.com/KB/office/ExportDataSetToExcel.aspx
回答by MicTech
I recommended FlexCellibrary, but it's not free :(
我推荐了FlexCel库，但它不是免费的 :(
But if you want Office Open XML format you can use http://excelpackage.codeplex.com/
但是如果你想要 Office Open XML 格式，你可以使用http://excelpackage.codeplex.com/
回答by JohnnyLambada
Excel already comes with everything you need to read/write data in XML.
Excel 已经提供了读取/写入 XML 数据所需的一切。
I've used the SpreadsheetMLformat on occasion and it worked well for me. With it, you write out an xml formatted document and name it with the .xls extension. Mostof the excel functionality is available. Microsoft's reference documentation is here. Google for tutorials in various languages such as Rails, PHP. The book Excel Hackstalks about it as well.
我偶尔使用过SpreadsheetML格式，它对我来说效果很好。使用它，您可以写出一个 xml 格式的文档并使用 .xls 扩展名命名它。大多数excel功能都可用。Microsoft 的参考文档在这里。谷歌搜索各种语言的教程，如Rails、PHP。Excel Hacks一书也谈到了它。
回答by kenny
If it's just data, a CSV file import can work. Create the file in code and use automation to make Excel import the file.
如果它只是数据，CSV 文件导入可以工作。在代码中创建文件并使用自动化使 Excel 导入文件。
UPDATE: After writing this I like @MicTech's suggestionwhich I didn't know about.
更新：写完这篇文章后，我喜欢@MicTech 的建议，但我不知道。
回答by Chris Brandsma
Another way to "write" to Excel is to create your data as an HTML page and load that. Excel can actually read html. A bonus point for doing this via html is that you can format the data a bit better than you can with CSV.
另一种“写入”到 Excel 的方法是将数据创建为 HTML 页面并加载它。Excel实际上可以读取html。通过 html 执行此操作的一个好处是您可以比使用 CSV 更好地格式化数据。
But that said, if you are only exporting simple data (small text fields and numbers), go with CSV.
但这就是说，如果您只导出简单数据（小文本字段和数字），请使用 CSV。
回答by Fry
If you can COM into excel, you can query directly from excel via COM, or create an array of data and drop it directly into a range equal to the size of your array. Even though excel isn't great for small COM calls, it works rather well with few large COM calls :)
如果可以COM转excel，就可以通过COM直接从excel中查询，或者创建一个数据数组，直接放到一个与你的数组大小相等的范围内。尽管 excel 不适用于小型 COM 调用，但它对于很少的大型 COM 调用也能很好地工作:)
DataSet ds = new DataSet();
da.Fill(ds);
int width = ds.Tables[0].Columns.Count;
int height = ds.Tables[0].Rows.Count;
object[,] retList = new object[height, width];
for (int i = 0; i < height; i++)
{
DataRow r = ds.Tables[0].Rows[i];
for (int j = 0; j < width; j++)
retList[i, j] = r.ItemArray[j];
}
Excel.Range range = mWs.get_Range(destination, mWs.Cells[destination.Row + height - 1, destination.Column + width - 1]);
range.set_Value(Missing.Value, retList);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
This is an example of getting data and inserting it as an array into excel in one COM call
这是在一个 COM 调用中获取数据并将其作为数组插入到 excel 中的示例
回答by rmeador
I've been using the Aspose.Cellslibrary with great success to write Excel files. It's far from cheap though.
我一直在使用Aspose.Cells库来编写 Excel 文件并取得了巨大成功。不过价格远不便宜。
回答by Kelly
Are you looking for a one time dump to Excel or to remain connected and push data to Excel more than once?
您是否正在寻找一次性转储到 Excel 或保持连接并不止一次将数据推送到 Excel？
If your looking to push data to Excel more than once you should investigate making an RTD server, I've used them many times and they work RTD Server
如果您希望多次将数据推送到 Excel，您应该研究制作 RTD 服务器，我已经多次使用它们并且它们可以运行RTD 服务器
If your looking for a one time push to Excel you need to use COM. Using COM and C#
如果您想一次性推送到 Excel，则需要使用 COM。使用 COM 和 C#
C# version 4 has some nice improvements to managed COM and you will no longer need primary interop assemblies when working with Excel. C# 4 isn't in production yet but you can download a beta of it if it works for you.
C# 版本 4 对托管 COM 进行了一些很好的改进，并且在使用 Excel 时您将不再需要主互操作程序集。C# 4 尚未投入生产，但如果它适合您，您可以下载它的测试版。
回答by Kelly
This library is free and pretty fast to create xls files, it also works if you dont have the MS Office installed. http://code.google.com/p/excellibrary/downloads/list
这个库是免费的，而且创建 xls 文件的速度非常快，如果您没有安装 MS Office，它也可以使用。 http://code.google.com/p/excellibrary/downloads/list
回答by David Robbins
We use SpreadsheetGear.
我们使用SpreadsheetGear。
