I have an DataTable I need to put into Excel 2007 format and save it as an excel file(.xlsx) 2007.
我需要将一个DataTable放入Excel 2007格式，并将其保存为Excel文件(.xlsx) 2007。
Can anyone help me to achieve this?
谁能帮我实现这个目标吗?
6 个解决方案
#1
8
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.
您可以使用OLEDB数据提供程序，并将Excel作为另一个ADO。NET数据源，以便循环您的数据表行并将它们插入到Excel电子表格中。这里有一篇Microsoft KB文章，介绍了许多细节。
http://support.microsoft.com/kb/316934/en-us
http://support.microsoft.com/kb/316934/en-us
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.
需要记住的最重要的一点是，您可以在工作簿中创建工作簿和工作表，并且您可以通过在名称末尾添加“$”来引用现有的工作表。如果您在表名的末尾省略了“$”，OLEDB提供者将假定它是一个新表单，并将尝试创建它。
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.
工作表名称后面的美元符号表示该表存在。如果您正在创建一个新表，如本文“创建新工作簿和表”一节中所讨论的，请不要使用美元符号。
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)或2007年(xlsx)创建和电子表格，这在您的连接字符串中定义——您指定要写入的文件，并指定扩展名。确保您使用了正确的OLEDB提供程序版本。
If you want to create a 2003 (.xls) version, you use this connection string:
如果您想创建一个2003 (.xls)版本，请使用以下连接字符串:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties="Excel 8.0;HDR=YES
If you want to create a 2007 (.xlsx) version, you use this connection string:
如果您想创建一个2007 (.xlsx)版本，请使用以下连接字符串:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties="Excel 12.0;HDR=YES
You may have to download the ACE provider from Microsoft in order to create XLSX files. You can find it here.
为了创建XLSX文件，您可能需要从Microsoft下载ACE提供程序。你可以在这里找到。
I usually use the XLS provider, so I haven't worked with the XLSX provider as much.
我通常使用XLS提供程序，所以我还没有与XLSX提供程序合作那么久。
Hope this helps. Let me know if you have other questions.
希望这个有帮助。如果你有其他问题，请告诉我。
#2
3
I wrote the following code for the company some time back. It takes Enumerable of any class type and exports all its (get)properties to Excel and also open Excel. You should be able to do something similar for a DataTable. Remember you need to add reference to Microsoft.Office.Interop.Excel
我为公司写了下面的代码。它取任何类类型的可枚举值，并将其所有(get)属性导出为Excel，并打开Excel。您应该能够为DataTable做类似的事情。记住，你需要添加对Microsoft.Office.Interop.Excel的引用
public static void ExportToExcel<T>(IEnumerable<T> exportData)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
PropertyInfo[] pInfos = typeof(T).GetProperties();
if (pInfos != null && pInfos.Count() > 0)
{
int iCol = 0;
int iRow = 0;
foreach (PropertyInfo eachPInfo in pInfos.Where(W => W.CanRead == true))
{
// Add column headings...
iCol++;
excel.Cells[1, iCol] = eachPInfo.Name;
}
foreach (T item in exportData)
{
iRow++;
// add each row's cell data...
iCol = 0;
foreach (PropertyInfo eachPInfo in pInfos.Where(W => W.CanRead == true))
{
iCol++;
excel.Cells[iRow + 1, iCol] = eachPInfo.GetValue(item, null);
}
}
// Global missing reference for objects we are not defining...
object missing = System.Reflection.Missing.Value;
// If wanting to Save the workbook...
string filePath = System.IO.Path.GetTempPath() + DateTime.Now.Ticks.ToString() + ".xlsm";
workbook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
// If wanting to make Excel visible and activate the worksheet...
excel.Visible = true;
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
excel.Rows.EntireRow.AutoFit();
excel.Columns.EntireColumn.AutoFit();
((Excel._Worksheet)worksheet).Activate();
}
}
#3
2
I have an DataTable I need to put into Excel 2007 format and save it as an excel file(.xlsx) 2007.
我需要将一个DataTable放入Excel 2007格式，并将其保存为Excel文件(.xlsx) 2007。
Can anyone help me to achieve this?
谁能帮我实现这个目标吗?
You just need to add my free C# class to your project, and one line of code.
您只需将我的免费c#类添加到您的项目中，并添加一行代码。
Full details (with free downloadable source code, and an example project) here:
详细信息(免费下载源代码和示例项目)如下:
Mikes Knowledge Base - Export to Excel
Mikes知识库-出口到Excel。
My library uses the free Microsoft OpenXML libraries (also provided in my downloads) to write the file, so you don't have to use the heavyweight VSTO libraries, or have Excel installed on your server.
我的库使用免费的Microsoft OpenXML库(也提供在我的下载中)来编写文件，所以您不必使用重量级的VSTO库，或者在您的服务器上安装Excel。
Also, it creates a real .xlsx file, rather than some other methods which write a stream of data to a comma-separated text file, but name it as a .xls file.
此外，它创建一个真正的.xlsx文件，而不是将数据流写入逗号分隔的文本文件，而是将其命名为.xls文件。
By the way, I had loads of difficulties writing to Excel files using OLEDB, not least because I was running Windows 7 64-bit, with Office 2007 (which is 32-bit) and the Microsoft ACE provider has to be the 64-bit edition... but you can't install this, if you have the 32-bit version of Office installed.
顺便说一下，我在使用OLEDB编写Excel文件时遇到了很多困难，尤其是因为我运行的是64位的Windows 7，使用的是Office 2007(32位)，而Microsoft ACE提供程序必须是64位的……但是如果你安装了32位版本的Office，你就不能安装这个。
So, you have to uninstall Office, install the ACE driver, and then re-install Office.
But even then, I gave up using OLEDB.. it just wasn't stable enough.
因此，您必须卸载Office，安装ACE驱动程序，然后重新安装Office。但即便如此，我还是放弃了使用OLEDB。只是不够稳定。
#4
1
Found this in some old code I did like 5 years ago that should work...
在我5年前做的一些旧代码中发现了这一点。
public static void DataTableToExcel(DataTable tbl)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn c in tbl.Columns)
{
context.Response.Write(c.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow r in tbl.Rows)
{
for (int i = 0; i < tbl.Columns.Count; i++)
{
context.Response.Write(r[i].ToString().Replace(";", string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition",
"attachment; filename=export.csv");
context.Response.End();
}
This will output from ASP.NET a response with a CSV file that Excel 2007 can open. If you want you can change the extension to mimic excel and it should work just by replacing the following lines:
这将从ASP中输出。使用Excel 2007可以打开的CSV文件创建一个响应。如果你想要的话，你可以把扩展名改成模拟excel，只要替换以下几行就可以了:
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AppendHeader("Content-Disposition",
"attachment; filename=export.xlsx");
A CSV is the easiest way if you don't need to do anything complex. If you do require it to truly be a Excel 2007 file in the native format, you will need to use an Office library to build it or convert it from the CSV and then serve/save it.
如果不需要做任何复杂的事情，CSV是最简单的方式。如果您确实要求它是一个真正的本地格式的Excel 2007文件，您将需要使用一个Office库来构建它，或者从CSV转换它，然后服务/保存它。
This link might also be useful:
这个链接也可能有用:
How to avoid the Excel prompt window when exporting data to Excel 2007
如何避免在导出数据到Excel 2007时出现Excel提示窗口
#5
0
you'll have to do lots of interop, here is a tutorial, hope it helps. link text
你将不得不进行大量的互操作，这里有一个教程，希望它能有所帮助。链接文本
#6
0
Saw that someone else posted a "save to csv" option. While that didn't seem to be the answer the OP was looking for, here is my version that includes the table's headers
看到有人发布了“保存到csv”选项。虽然这似乎不是OP要寻找的答案，但是我的版本包含了表的标题
public static String ToCsv(DataTable dt, bool addHeaders)
{
var sb = new StringBuilder();
//Add Header Header
if (addHeaders)
{
for (var x = 0; x < dt.Columns.Count; x++)
{
if (x != 0) sb.Append(",");
sb.Append(dt.Columns[x].ColumnName);
}
sb.AppendLine();
}
//Add Rows
foreach (DataRow row in dt.Rows)
{
for (var x = 0; x < dt.Columns.Count; x++)
{
if (x != 0) sb.Append(",");
sb.Append(row[dt.Columns[x]]);
}
sb.AppendLine();
}
return sb.ToString();
}
#1
8
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.
您可以使用OLEDB数据提供程序，并将Excel作为另一个ADO。NET数据源，以便循环您的数据表行并将它们插入到Excel电子表格中。这里有一篇Microsoft KB文章，介绍了许多细节。
http://support.microsoft.com/kb/316934/en-us
http://support.microsoft.com/kb/316934/en-us
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.
需要记住的最重要的一点是，您可以在工作簿中创建工作簿和工作表，并且您可以通过在名称末尾添加“$”来引用现有的工作表。如果您在表名的末尾省略了“$”，OLEDB提供者将假定它是一个新表单，并将尝试创建它。
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.
工作表名称后面的美元符号表示该表存在。如果您正在创建一个新表，如本文“创建新工作簿和表”一节中所讨论的，请不要使用美元符号。
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)或2007年(xlsx)创建和电子表格，这在您的连接字符串中定义——您指定要写入的文件，并指定扩展名。确保您使用了正确的OLEDB提供程序版本。
If you want to create a 2003 (.xls) version, you use this connection string:
如果您想创建一个2003 (.xls)版本，请使用以下连接字符串:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties="Excel 8.0;HDR=YES
If you want to create a 2007 (.xlsx) version, you use this connection string:
如果您想创建一个2007 (.xlsx)版本，请使用以下连接字符串:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx;Extended Properties="Excel 12.0;HDR=YES
You may have to download the ACE provider from Microsoft in order to create XLSX files. You can find it here.
为了创建XLSX文件，您可能需要从Microsoft下载ACE提供程序。你可以在这里找到。
I usually use the XLS provider, so I haven't worked with the XLSX provider as much.
我通常使用XLS提供程序，所以我还没有与XLSX提供程序合作那么久。
Hope this helps. Let me know if you have other questions.
希望这个有帮助。如果你有其他问题，请告诉我。
#2
3
I wrote the following code for the company some time back. It takes Enumerable of any class type and exports all its (get)properties to Excel and also open Excel. You should be able to do something similar for a DataTable. Remember you need to add reference to Microsoft.Office.Interop.Excel
我为公司写了下面的代码。它取任何类类型的可枚举值，并将其所有(get)属性导出为Excel，并打开Excel。您应该能够为DataTable做类似的事情。记住，你需要添加对Microsoft.Office.Interop.Excel的引用
public static void ExportToExcel<T>(IEnumerable<T> exportData)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Application.Workbooks.Add(true);
PropertyInfo[] pInfos = typeof(T).GetProperties();
if (pInfos != null && pInfos.Count() > 0)
{
int iCol = 0;
int iRow = 0;
foreach (PropertyInfo eachPInfo in pInfos.Where(W => W.CanRead == true))
{
// Add column headings...
iCol++;
excel.Cells[1, iCol] = eachPInfo.Name;
}
foreach (T item in exportData)
{
iRow++;
// add each row's cell data...
iCol = 0;
foreach (PropertyInfo eachPInfo in pInfos.Where(W => W.CanRead == true))
{
iCol++;
excel.Cells[iRow + 1, iCol] = eachPInfo.GetValue(item, null);
}
}
// Global missing reference for objects we are not defining...
object missing = System.Reflection.Missing.Value;
// If wanting to Save the workbook...
string filePath = System.IO.Path.GetTempPath() + DateTime.Now.Ticks.ToString() + ".xlsm";
workbook.SaveAs(filePath, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
// If wanting to make Excel visible and activate the worksheet...
excel.Visible = true;
Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
excel.Rows.EntireRow.AutoFit();
excel.Columns.EntireColumn.AutoFit();
((Excel._Worksheet)worksheet).Activate();
}
}
#3
2
I have an DataTable I need to put into Excel 2007 format and save it as an excel file(.xlsx) 2007.
我需要将一个DataTable放入Excel 2007格式，并将其保存为Excel文件(.xlsx) 2007。
Can anyone help me to achieve this?
谁能帮我实现这个目标吗?
You just need to add my free C# class to your project, and one line of code.
您只需将我的免费c#类添加到您的项目中，并添加一行代码。
Full details (with free downloadable source code, and an example project) here:
详细信息(免费下载源代码和示例项目)如下:
Mikes Knowledge Base - Export to Excel
Mikes知识库-出口到Excel。
My library uses the free Microsoft OpenXML libraries (also provided in my downloads) to write the file, so you don't have to use the heavyweight VSTO libraries, or have Excel installed on your server.
我的库使用免费的Microsoft OpenXML库(也提供在我的下载中)来编写文件，所以您不必使用重量级的VSTO库，或者在您的服务器上安装Excel。
Also, it creates a real .xlsx file, rather than some other methods which write a stream of data to a comma-separated text file, but name it as a .xls file.
此外，它创建一个真正的.xlsx文件，而不是将数据流写入逗号分隔的文本文件，而是将其命名为.xls文件。
By the way, I had loads of difficulties writing to Excel files using OLEDB, not least because I was running Windows 7 64-bit, with Office 2007 (which is 32-bit) and the Microsoft ACE provider has to be the 64-bit edition... but you can't install this, if you have the 32-bit version of Office installed.
顺便说一下，我在使用OLEDB编写Excel文件时遇到了很多困难，尤其是因为我运行的是64位的Windows 7，使用的是Office 2007(32位)，而Microsoft ACE提供程序必须是64位的……但是如果你安装了32位版本的Office，你就不能安装这个。
So, you have to uninstall Office, install the ACE driver, and then re-install Office.
But even then, I gave up using OLEDB.. it just wasn't stable enough.
因此，您必须卸载Office，安装ACE驱动程序，然后重新安装Office。但即便如此，我还是放弃了使用OLEDB。只是不够稳定。
#4
1
Found this in some old code I did like 5 years ago that should work...
在我5年前做的一些旧代码中发现了这一点。
public static void DataTableToExcel(DataTable tbl)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn c in tbl.Columns)
{
context.Response.Write(c.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow r in tbl.Rows)
{
for (int i = 0; i < tbl.Columns.Count; i++)
{
context.Response.Write(r[i].ToString().Replace(";", string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition",
"attachment; filename=export.csv");
context.Response.End();
}
This will output from ASP.NET a response with a CSV file that Excel 2007 can open. If you want you can change the extension to mimic excel and it should work just by replacing the following lines:
这将从ASP中输出。使用Excel 2007可以打开的CSV文件创建一个响应。如果你想要的话，你可以把扩展名改成模拟excel，只要替换以下几行就可以了:
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AppendHeader("Content-Disposition",
"attachment; filename=export.xlsx");
A CSV is the easiest way if you don't need to do anything complex. If you do require it to truly be a Excel 2007 file in the native format, you will need to use an Office library to build it or convert it from the CSV and then serve/save it.
如果不需要做任何复杂的事情，CSV是最简单的方式。如果您确实要求它是一个真正的本地格式的Excel 2007文件，您将需要使用一个Office库来构建它，或者从CSV转换它，然后服务/保存它。
This link might also be useful:
这个链接也可能有用:
How to avoid the Excel prompt window when exporting data to Excel 2007
如何避免在导出数据到Excel 2007时出现Excel提示窗口
#5
0
you'll have to do lots of interop, here is a tutorial, hope it helps. link text
你将不得不进行大量的互操作，这里有一个教程，希望它能有所帮助。链接文本
#6
0
Saw that someone else posted a "save to csv" option. While that didn't seem to be the answer the OP was looking for, here is my version that includes the table's headers
看到有人发布了“保存到csv”选项。虽然这似乎不是OP要寻找的答案，但是我的版本包含了表的标题
public static String ToCsv(DataTable dt, bool addHeaders)
{
var sb = new StringBuilder();
//Add Header Header
if (addHeaders)
{
for (var x = 0; x < dt.Columns.Count; x++)
{
if (x != 0) sb.Append(",");
sb.Append(dt.Columns[x].ColumnName);
}
sb.AppendLine();
}
//Add Rows
foreach (DataRow row in dt.Rows)
{
for (var x = 0; x < dt.Columns.Count; x++)
{
if (x != 0) sb.Append(",");
sb.Append(row[dt.Columns[x]]);
}
sb.AppendLine();
}
return sb.ToString();
}