将数据表转换为 excel 2007(.xlsx) [英] Convert datatable to excel 2007(.xlsx)

问题描述

我有一个 DataTable 我需要将其转换为 Excel 2007 格式并将其另存为 excel 文件 (.xlsx) 2007.

I have an DataTable I need to put into Excel 2007 format and save it as an excel file(.xlsx) 2007.

谁能帮我实现这个目标?

Can anyone help me to achieve this?

推荐答案

您可以使用 OLEDB 数据提供程序并将 Excel 视为另一个 ADO.NET 数据源，以便循环遍历您的 DataTable 行并将它们插入到 Excel 电子表格中.这是一篇 Microsoft KB 文章，向您介绍了许多细节.

You can use an OLEDB data provider and just treat Excel as another ADO.NET data source in order to loop through your DataTable rows and insert them into the Excel spreadsheet. Here's a Microsoft KB article that walks you through a lot of the details.

http://support.microsoft.com/kb/316934/en-us

要记住的重要一点是，您可以在工作簿中创建工作簿和工作表，并且可以通过在名称末尾附加$"来引用现有工作表.如果省略工作表名称末尾的$"，OLEDB 提供程序将假定它是一个新工作表并尝试创建它.

The big thing to keep in mind is that you can create workbooks and sheets within the workbook, and you can reference existing sheets by appending a '$' at the end of the name. If you omit the '$' at the end of the sheet name, the OLEDB provider will assume that it's a new sheet and will try to create it.

后面的美元符号工作表名称表明该表存在.如果您正在创建一个新表，如在创建新的工作簿和表格本文的部分，请勿使用美元符号.

The dollar sign following the worksheet name is an indication that the table exists. If you are creating a new table, as discussed in the Create New Workbooks and Tables section of this article, do not use the dollar sign.

您可以创建 2003 (.xls) 或 2007 格式 (xlsx) 的电子表格，这在您的连接字符串中定义——您指定要写入的文件，只需指定扩展名.确保您使用正确的 OLEDB 提供程序版本.

You can create and spreadsheet in 2003 (.xls) or 2007 format (xlsx), and that's defined on your connection string -- you specify the file that you're going to write to, and just specify the extension. Make sure you use the right OLEDB provider version.

如果要创建 2003 (.xls) 版本，请使用此连接字符串:

If you want to create a 2003 (.xls) version, you use this connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Book1.xls;Extended Properties="Excel 8.0;HDR=YES

如果要创建 2007 (.xlsx) 版本，请使用此连接字符串:

If you want to create a 2007 (.xlsx) version, you use this connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Book1.xlsx;Extended Properties="Excel 12.0;HDR=YES

您可能必须从 Microsoft 下载 ACE 提供程序才能创建 XLSX 文件.您可以在此处.

You may have to download the ACE provider from Microsoft in order to create XLSX files. You can find it here.

我通常使用 XLS 提供程序，因此我与 XLSX 提供程序的合作不多.

I usually use the XLS provider, so I haven't worked with the XLSX provider as much.

希望这会有所帮助.如果您有其他问题，请告诉我.

Hope this helps. Let me know if you have other questions.

这篇关于将数据表转换为 excel 2007(.xlsx)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！