我该怎么在C#中使用Windows Forms将 DataTable 导出到Excel？ DataTable 与 DataGridView 控件相关联。 我需要将 DataTable 的记录导出到Excel中。
如何将DataTable导出到Excel
129
- user1057221
3
最简单的方法是在items和subitems上进行嵌套的foreach循环。 - Dumbo
注意：如果您正在尝试从数据表传递值到对象，然后传递到Excel，则还应进行数据类型错误处理。例如，Guids会导致HRESULT：0x800A03EC异常而中断您的赋值。一个不需要测试数据类型的解决方法是在填充对象时使用“ToString()”。Excel将自动将数字转换回数字格式。FlashTrev已经解决了相关的日期/时间问题。 - u8it
我建议查看这个问题的答案：https://dev59.com/1HRB5IYBdhLWcg3wuZU1#536699 - Troy Cosentino
23个回答
163
我会推荐使用ClosedXML -
你可以用一些非常易读的代码将DataTable转换成Excel工作表:
你可以用一些非常易读的代码将DataTable转换成Excel工作表:
XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");
这位开发者非常负责，乐于助人。该项目正在积极开发中，并且文档非常好。
- hmqcnoesy
6
7添加约6MB的相关库会让应用程序变得更加沉重吗？ - ʞᴉɯ
6好问题@MicheleVirgilio。我没有进行任何测试来量化其影响。但是就我个人而言，在我使用的所有项目中，它并没有困扰过我，事实上我甚至从未注意过它。 - hmqcnoesy
这段代码返回了一个只有一列值为
ClosedXML.Excel.XLWorkbook 的 Excel。 - It's a trap3使用 ClosedXML 处理大量数据表格（例如我这里的 25 万行）容易导致内存不足问题，这是一个已知的开放问题。 - glant
如何在当前打开的Excel中填充数据？最好将Excel保存在指定位置。 - singhswat
这解决了我的问题，只是别忘了保存文件：XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName"); wb.SaveAs(filepath); - Ruben
107
尝试使用简单的代码将DataTable转换为csv格式的Excel文件：
var lines = new List<string>();
string[] columnNames = dataTable.Columns
.Cast<DataColumn>()
.Select(column => column.ColumnName)
.ToArray();
var header = string.Join(",", columnNames.Select(name => $"\"{name}\""));
lines.Add(header);
var valueLines = dataTable.AsEnumerable()
.Select(row => string.Join(",", row.ItemArray.Select(val => $"\"{val}\"")));
lines.AddRange(valueLines);
File.WriteAllLines("excel.csv", lines);
这将会在当前工作目录中写入一个新文件excel.csv，通常这个目录要么是.exe所在的位置，要么是你从其中启动它的位置。
- cuongle
13
2太棒了，伙计。我没有更多的范围来给你的答案超过一个赞，否则我可以给你超过100个赞。 - Ashok kumar
2@Cuong Le - 如果单元格中有两个逗号，则在"string.Join（","）"时会出现问题。 - suneel ranga
2如果一个单元格（即在row.ItemArray中）包含逗号“，”，那么按照CSV标准，该单元格应该用引号“”“”括起来，然后像往常一样出现在文件中。因此，是的-这会导致问题，因为此代码无法检测到“，”并应用引号。 - Tom Leys
2File.WriteAllLines(ExcelFilePath, lines, Encoding.UTF8); 是用于Unicode的。 - Mehdi Khademloo
1一旦保存，您可以使用 Process.Start(Your File) 打开文件。我相信这是你能够做到的最接近的了。 - TimmRH
显示剩余8条评论
45
一种优雅的选择是为 .NET Framework 的 DataTable 类编写扩展方法（如下所示）。
可以按以下方式调用此扩展方法：
using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;
DataTable dt;
// fill table data in dt here
...
// export DataTable to excel
// save excel file without ever making it visible if filepath is given
// don't save excel file, just make it visible if no filepath is given
dt.ExportToExcel(ExcelFilePath);
DataTable类的扩展方法：
public static class My_DataTable_Extensions
{
// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this DataTable tbl, string excelFilePath = null) {
try {
if (tbl == null || tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
var excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (var i = 0; i < tbl.Columns.Count; i++) {
workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
}
// rows
for (var i = 0; i < tbl.Rows.Count; i++) {
// to do: format datetime values before printing
for (var j = 0; j < tbl.Columns.Count; j++) {
workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
}
}
// check file path
if (!string.IsNullOrEmpty(excelFilePath)) {
try {
workSheet.SaveAs(excelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex) {
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
} else { // no file path is given
excelApp.Visible = true;
}
}
catch (Exception ex) {
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}
- tuncalik
6
25需要注意的是，这需要安装 Excel。 - Banshee
5
ExcelFilePath != null && ExcelFilePath != "" 可以翻译为 !String.IsNullOrEmpty(ExcelFilePath)，意思相同，但后者更加通俗易懂。 - Mark Schultheiss2另外需要注意的是：微软不建议在服务器上使用Interop https://support.microsoft.com/zh-cn/help/257757/considerations-for-server-side-automation-of-office - alex.pulver
@alex.pulver 当我尝试在服务器上使用它时，它也无法正常工作。值得一提的好点。 - Si8
这个方法可以运行，但速度很慢。最好将其复制到剪贴板中，然后粘贴到Excel中。如果您处理的记录超过1000条，这将需要一些时间。 - Alex M
这太棒了！ - Jonathan DS
31
本解决方案基于tuncalik的文章（感谢提供灵感），但在处理大型表格时速度更快（只是相对来说不太清晰）。
public static class My_DataTable_Extensions
{
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="DataTable">Source DataTable</param>
/// <param name="ExcelFilePath">Path to result file name</param>
public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
{
try
{
int ColumnsCount;
if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");
// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks.Add();
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
object[] Header = new object[ColumnsCount];
// column headings
for (int i = 0; i < ColumnsCount; i++)
Header[i] = DataTable.Columns[i].ColumnName;
Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
HeaderRange.Font.Bold = true;
// DataCells
int RowsCount = DataTable.Rows.Count;
object[,] Cells = new object[RowsCount, ColumnsCount];
for (int j = 0; j < RowsCount; j++)
for (int i = 0; i < ColumnsCount; i++)
Cells[j, i] = DataTable.Rows[j][i];
Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
System.Windows.MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
Excel.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}
- Tomasz Wiśniewski
4
tuncalik的回答对我来说几乎花了一分钟，如果它需要很长时间，那么1秒钟就可以了...我实际上感到惊讶。 - Wilsu
3这是我尝试过的最快的示例，做得很棒。之后我不得不使用Marshal来释放文件。`Excel.Quit(); Marshal.FinalReleaseComObject(Worksheet); Marshal.FinalReleaseComObject(HeaderRange); Marshal.FinalReleaseComObject(Excel);` - Dave Kelly
需要安装Office吗？ - Parshuram Kalvikatte
除此之外，它完美地工作，但是在控制台应用程序中使用此解决方案时，我的标题背景颜色始终设置为黑色。可能的原因是什么？ - Zaveed Abbasi
15
尝试使用此函数，将数据表和要导出的文件路径作为参数传入。
public void CreateCSVFile(ref DataTable dt, string strFilePath)
{
try
{
// Create the CSV file to which grid data will be exported.
StreamWriter sw = new StreamWriter(strFilePath, false);
// First we will write the headers.
//DataTable dt = m_dsProducts.Tables[0];
int iColCount = dt.Columns.Count;
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
// Now write all the rows.
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}
catch (Exception ex)
{
throw ex;
}
}
- Mayur Borad
3
2请注意，这并不会真正使用Excel文档中的表格单元格，相反，每行的所有内容都将打印在每行的第一个单元格中。 - Banshee
@Banshee 不，Excel 完全理解 CSV 文件。 - NickG
1我的 Excel 也无法工作。每行数据都在第一个单元格中。 - Mitulát báti
10
使用 DocumentFormat.OpenXml nuget 包，我创建了一个单例类，可以从 DataTable 或 DataSet 导出到 Excel。各个表将作为工作簿中的单独工作表。
主要类
public sealed class OfficeOpenXML
{
private static Lazy<OfficeOpenXML> _instance = new Lazy<OfficeOpenXML>(() => new OfficeOpenXML());
private OfficeOpenXML()
{
}
public static OfficeOpenXML GetInstance()
{
return _instance.Value;
}
public MemoryStream GetExcelStream(DataSet ds, bool firstRowAsHeader = false)
{
if (ds == null || ds.Tables.Count == 0)
{
return null;
}
MemoryStream stream = new MemoryStream();
using (var excel = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
//create doc and workbook
WorkbookPart workbookPart = excel.AddWorkbookPart();
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
//loop all tables in the dataset
for (int iTable = 0; iTable < ds.Tables.Count; iTable++)
{
var table = ds.Tables[iTable];
//create sheet part
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData data = new SheetData();
List<Row> allRows = new List<Row>();
//setting header of the sheet
Row headerRow = new Row() { RowIndex = 1 };
for (int iColumn = 0; iColumn < table.Columns.Count; iColumn++)
{
var col = table.Columns[iColumn];
//if first row of table is not the header then set columns of table as header of sheet
if (!firstRowAsHeader)
{
headerRow.Append(new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(col.ColumnName)
});
}
else
{
headerRow.Append(new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(Convert.ToString(table.Rows[0][col]))
});
}
}
allRows.Add(headerRow);
//setting other data rows
if (table.Rows != null && table.Rows.Count != 0)
{
for (int iRow = firstRowAsHeader ? 1 : 0; iRow < table.Rows.Count; iRow++)
{
var row = table.Rows[iRow];
Row valueRow = new Row { RowIndex = (uint)(iRow + (firstRowAsHeader ? 1 : 2)) };
for (int iColumn = 0; iColumn < table.Columns.Count; iColumn++)
{
var col = table.Columns[iColumn];
valueRow.Append(new Cell
{
DataType = Format(col.DataType),
CellValue = new CellValue(Convert.ToString(row[col]))
});
}
allRows.Add(valueRow);
}
}
//add rows to the data
data.Append(allRows);
worksheet.Append(data);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//add worksheet to main sheets
sheets.Append(new Sheet
{
Name = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet" + (iTable + 1) : table.TableName,
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)iTable + 1
});
}//single table processing ends here
//add created sheets to workbook
workbook.Append(sheets);
excel.WorkbookPart.Workbook = workbook;
excel.WorkbookPart.Workbook.Save();
excel.Close();
}
stream.Seek(0, SeekOrigin.Begin);
stream.Capacity = (int)stream.Length;
return stream;
}
public MemoryStream GetExcelStream(DataTable dt, bool firstRowAsHeader = false)
{
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return GetExcelStream(ds, firstRowAsHeader);
}
#region Excel Helpers
CellValues Format(Type t)
{
switch (t.ToString())
{
case "System.String":
return CellValues.String;
case "System.DateTime":
return CellValues.Date;
case "System.Boolean":
return CellValues.Boolean;
case "System.Int16":
return CellValues.Number;
case "System.Int32":
return CellValues.Number;
case "System.Int64":
return CellValues.Number;
case "System.UInt16":
return CellValues.Number;
case "System.UInt32":
return CellValues.Number;
case "System.UInt64":
return CellValues.Number;
case "System.Decimal":
return CellValues.Number;
case "System.Double":
return CellValues.Number;
case "System.Single":
return CellValues.Number;
default:
return CellValues.String;
}
}
#endregion
}
保存到文件
var excelApp = OfficeOpenXML.GetInstance();
var dt = GetDataTableFromDB();
using (var stream = excelApp.GetExcelStream(dt, false))//use true to hide datatable columns from excel
{
using (FileStream fs = new FileStream(@"C:\Users\Public\myexcel.xlsx", FileMode.Create))
{
stream.CopyTo(fs);
fs.Flush();
}
}
MVC 应用程序中的下载
public ActionResult DownloadReport()
{
var ds = GetDataSetFromDB();
var excelApp = OfficeOpenXML.GetInstance();
var file = excelApp.GetExcelStream(ds, false);
return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", Guid.NewGuid().ToString() + ".xlsx");
}
- Beingnin
2
1我使用这段代码下载Excel文件，但我必须使用
stream.Seek(0, SeekOrigin.Begin) 重置流，否则文件大小为零。在从函数 GetExcelStream 返回流之前添加此内容。我还需要在某些地方使用 InlineString ，否则 Excel会警告正在从文件中恢复数据。 - Andy G是的...我忘记处理那个了。会在代码中添加。 - Beingnin
6
最好且最简单的方法
private void exportToExcel(DataTable dt)
{
/*Set up work book, work sheets, and excel application*/
Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
try
{
string path = AppDomain.CurrentDomain.BaseDirectory;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();
// obook.Worksheets.Add(misValue);
osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"];
int colIndex = 0;
int rowIndex = 1;
foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[1, colIndex] = dc.ColumnName;
}
foreach (DataRow dr in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn dc in dt.Columns)
{
colIndex++;
osheet.Cells[rowIndex, colIndex] = dr[dc.ColumnName];
}
}
osheet.Columns.AutoFit();
string filepath = "C:\\Temp\\Book1";
//Release and terminate excel
obook.SaveAs(filepath);
obook.Close();
oexcel.Quit();
releaseObject(osheet);
releaseObject(obook);
releaseObject(oexcel);
GC.Collect();
}
catch (Exception ex)
{
oexcel.Quit();
log.AddToErrorLog(ex, this.Name);
}
}
- Tim
2
1
private void releaseObject(object o) { try { while (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0) { } } catch { } finally { o = null; } } - Tim如果有多个打开的Excel文件，这个释放函数会销毁所有文件还是仅销毁作为参数传递的一个文件？ - Elliott Addi
5
你可以使用我的SwiftExcel库。它在需要优化性能和低内存使用的情况下表现尤为出色，因为它直接将数据写入文件中：
using (var ew = new ExcelWriter("C:\\temp\\test.xlsx"))
{
for (var row = 1; row <= 100; row++)
{
for (var col = 1; col <= 10; col++)
{
ew.Write($"row:{row}-col:{col}", col, row);
}
}
}
NuGet命令安装：
Install-Package SwiftExcel
- Roman.Pavelko
3
只需使用
ClosedXML.Excel 库。它易于使用且速度相当快。
https://github.com/ClosedXML/ClosedXML
类
private DataTable getAllList()
{
string constr = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT EmpId, gender, EmpName, pOnHold FROM Employee WHERE EmpId= '"+ AnyVariable + "' ORDER BY EmpName"))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
DataTable dt = new DataTable();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
dt.Columns[0].ColumnName = "Employee Id";
dt.Columns[1].ColumnName = "Gender";
dt.Columns[2].ColumnName = "Employee Name";
dt.Columns[3].ColumnName = "On Hold";
return dt;
}
}
}
}
然后是另一个获取数据集的方法
public DataSet getDataSetExportToExcel()
{
DataSet ds = new DataSet();
DataTable dtEmp = new DataTable("CLOT List");
dtEmp = getAllList();
ds.Tables.Add(dtEmp);
ds.Tables[0].TableName = "Employee"; //If you which to use Mutliple Tabs
return ds;
}
现在您的按钮点击事件
protected void btn_Export_Click(object sender, EventArgs e)
{
DataSet ds = getDataSetExportToExcel();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(ds);
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=EmployeeonHoldList.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
- PatsonLeaner
2
我想添加这个答案，因为我花了很多时间寻找一种快速、可靠的方法来完成这个任务，但我无法在任何地方找到使用OpenXMLWriter进行此操作的完整示例。
首先，COM/Interop（许多其他答案使用）可以完成此任务，但它存在一些敏感性问题。我已经使用它数十年了，它大部分时间都很稳定，但当我为数百个用户实现数据仓库前端时，我发现它会受到太多机器和用户所做的事情的影响，因此我转向了OpenXML。 OpenXML DOM对于此目的相当不错，但它比使用OpenXMLWriter慢。当涉及到有大量列的大型数据集（100K+）时，DOM比OpenXMLWriter慢得多，因此我使用后者。下面的方法在不到30秒的时间内写入了30多个字段的420K+行。
我希望注释足以指导任何人完成它正在做的事情。它是简化的，因为它将所有值都作为字符串写入文件，但您可以根据数据内容实现逻辑来编写各种数据类型（并使用各种单元格格式）。您还可以通过更改几个内容（即遍历列/行的循环），将其调整为在DataGridView上使用（而不是DataTable）。
需要引用DocumentFormat.OpenXML（使用OpenXML SDK下载）和WindowsBase。
首先，COM/Interop（许多其他答案使用）可以完成此任务，但它存在一些敏感性问题。我已经使用它数十年了，它大部分时间都很稳定，但当我为数百个用户实现数据仓库前端时，我发现它会受到太多机器和用户所做的事情的影响，因此我转向了OpenXML。 OpenXML DOM对于此目的相当不错，但它比使用OpenXMLWriter慢。当涉及到有大量列的大型数据集（100K+）时，DOM比OpenXMLWriter慢得多，因此我使用后者。下面的方法在不到30秒的时间内写入了30多个字段的420K+行。
我希望注释足以指导任何人完成它正在做的事情。它是简化的，因为它将所有值都作为字符串写入文件，但您可以根据数据内容实现逻辑来编写各种数据类型（并使用各种单元格格式）。您还可以通过更改几个内容（即遍历列/行的循环），将其调整为在DataGridView上使用（而不是DataTable）。
需要引用DocumentFormat.OpenXML（使用OpenXML SDK下载）和WindowsBase。
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Spreadsheet
Imports DocumentFormat.OpenXml.Packaging
Public Sub ExportToExcelXML(ByRef dt As DataTable, filename As String)
Dim wbp As WorkbookPart, wsp As WorksheetPart
'If this DataTable has more rows in it than can fit in Excel, throw an exception
If dt.Rows.Count > 1048575 Then Throw New Exception("The DataTable is too large to export to Excel.")
'Delete any previous file of the same name that may exist.
File.Delete(filename)
'Create an OpenXML SpreadsheetDocument...
Using xls = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook)
'Add a WorkbookPart to the Spreadsheet Doc, then add a WorksheetPart to the WorkbookPart.
wbp = xls.AddWorkbookPart()
wsp = wbp.AddNewPart(Of WorksheetPart)
'Now we need to add the "StyleSheet" to the WorkbookPart (that we just added above). This will allow us to apply formatting to our Cells.
'Add the WbStylesPart and the StyleSheet.
Dim stp As WorkbookStylesPart = wbp.AddNewPart(Of WorkbookStylesPart)
Dim ss As New Stylesheet
'Create the only two Fonts we're going to use (Regular and Bold).
Dim fBold As New Font
fBold.Append(New Bold)
Dim fnts As New Fonts
fnts.Append(New Font) 'This creates the default (unmodified, regular) Font. It's added first, so its index is 0.
fnts.Append(fBold) 'This creates the Bold font. It's added second, so its index is 1.
'Create the default Fill/Border settings (these have to be here, even though I don't set any custom fills/borders).
Dim flls As New Fills
Dim brdrs As New Borders
flls.Append(New Fill)
brdrs.Append(New Border)
'Now I have to add formats (NumberFormat and CellFormat). First, you create a NumberFormat. This is basically the pattern of
' the format (i.e. "@" for Text). For now, I only need a Text format, but I can add more patterns if needed.
' I give the format an ID of 164, since 163 is where the built-in Excel formats end.
Dim nbrfmts As New NumberingFormats
nbrfmts.Append(New NumberingFormat With {.NumberFormatId = 164, .FormatCode = "@"})
'Create the first two CellFormats: Default, which will have an index of 0 and "Header" (Bold/Centered) with an index of 1.
Dim cellfmts As New CellFormats()
cellfmts.Append(New CellFormat() With {.FontId = 0, .NumberFormatId = 164, .FillId = 0, .BorderId = 0})
cellfmts.Append(New CellFormat() With {.FontId = 1, .NumberFormatId = 164,
.Alignment = New Alignment() With {.WrapText = True, .Horizontal = HorizontalAlignmentValues.Center}})
'Add all of the Fonts/Fills/Borders/etc to the StyleSheet and add it all to the WorkbookStylesPart.
ss.Append(fnts)
ss.Append(flls)
ss.Append(brdrs)
ss.Append(cellfmts)
ss.NumberingFormats = nbrfmts
stp.Stylesheet = ss
stp.Stylesheet.Save()
'Now create an OpenXMLWriter using the WorksheetPart to write the cells to the worksheet.
Using oxw As OpenXmlWriter = OpenXmlWriter.Create(wsp)
'Write the start element for the Worksheet and the Columns...
oxw.WriteStartElement(New Worksheet)
oxw.WriteStartElement(New Columns())
'Now I'm going to loop through the columns in the DataTable...
For c As Integer = 0 To dt.Columns.Count - 1
'Now we'll get the width for the column. To do this, we loop through all of the rows and measure the width of the text
' using the default Excel Font (currently Font: Calibri Size: 11) and return the largest width (in pixels) to use below.
' Why not do this loop below (when I loop through the rows to write the Cells)? Because you can't. You have to
' write the Column XML first before writing the SheetData/Row/Cell XML (I confirmed this by trying it), so there's
' no way (that I'm aware of) to avoid looping through all of the rows twice if you want to AutoFit.
'Setup vars we'll use for getting the column widths (below).
Dim g = System.Drawing.Graphics.FromHwnd(IntPtr.Zero)
Dim fnt = New System.Drawing.Font("Calibri", 11)
Dim wid As Double = 0
'Get the width of the header (because if this is wider than the widest value, we'll use the header text's width).
' I found that adding 2 pixels to the width was necessary to get the column as wide as Excel would make it.
Dim tmp As Double = g.MeasureString(dt.Columns(c).ColumnName, New System.Drawing.Font(fnt, System.Drawing.FontStyle.Bold)).Width + 2
'Loop through the rows in the dt and get the width of the value in that row/col. If it's wider than the widest
' width we've encountered thus far, use the new wider width as our basis.
For Each row As DataRow In dt.Rows
If tmp > wid Then wid = tmp
tmp = g.MeasureString(row(c).ToString, fnt).Width
Next
'Set the column attributes and write it to the file. The Width is set using a formula that converts from pixels to Excel's column width values.
Dim oxa As New List(Of OpenXmlAttribute) From {New OpenXmlAttribute("min", Nothing, c + 1), New OpenXmlAttribute("max", Nothing, c + 1),
New OpenXmlAttribute("width", Nothing, System.Math.Round((wid - 12 + 5) / 7D + 1, 2))}
oxw.WriteStartElement(New Column(), oxa)
oxw.WriteEndElement()
Next
'CLose out the Columns collection.
oxw.WriteEndElement()
'Write the start element for the SheetData...
oxw.WriteStartElement(New SheetData)
'Write the start element for the Header row.
oxw.WriteStartElement(New Row)
'Loop through the Columns in the dt.
For Each col As DataColumn In dt.Columns
'Write a cell for this column's Header. All Header cells are written with a DataType of String ("str").
' I ALSO apply the "Header" CellFormat (StyleIndex 1) to all of the Header Cells. This makes them Bold and Centered.
WriteCell(oxw, col.ColumnName, "str", 1)
Next
'Close out the Header row.
oxw.WriteEndElement()
'Loop through all of the rows in the dt...
For Each row As DataRow In dt.Rows
'Write a StartElement for this row...
oxw.WriteStartElement(New Row)
'Loop through all of the columns in the dt...
For c As Integer = 0 To dt.Columns.Count - 1
'Write a value in this row/column to the Excel file. I use the datatype of "String" and the default CellFormat/StyleIndex.
WriteCell(oxw, row(c).ToString, "str", 0)
Next
'Close out this row.
oxw.WriteEndElement()
Next
'Close out the Worksheet and SheetData elements...
oxw.WriteEndElement()
oxw.WriteEndElement()
End Using
'Now we're going to create an OpenXMLWriter using the WorkbookPart (that we created above)...
Using oxw As OpenXmlWriter = OpenXmlWriter.Create(wbp)
'Add starting elements for the Workbook and Sheets collection.
oxw.WriteStartElement(New Workbook())
oxw.WriteStartElement(New Sheets())
'Add the Sheet (name the Sheet after the file name minus the extension).
oxw.WriteElement(New Sheet() With {.Name = Path.GetFileNameWithoutExtension(filename), .SheetId = 1, .Id = xls.WorkbookPart.GetIdOfPart(wsp)})
'Write End elements for the Workbook/Sheets
oxw.WriteEndElement()
oxw.WriteEndElement()
End Using
End Using
End Sub
'This Sub is used to write a value to a Cell using OpenXMLWriter.
Private Sub WriteCell(ByRef oxw As OpenXmlWriter, value As String, datatype As String, style As UInt32Value)
Dim oxa As New List(Of OpenXmlAttribute) From {New OpenXmlAttribute("t", Nothing, datatype), New OpenXmlAttribute("s", Nothing, style)}
oxw.WriteStartElement(New Cell(), oxa)
If value <> Nothing Then oxw.WriteElement(New CellValue(value))
oxw.WriteEndElement()
End Sub
- WATYF
2
1非常感谢您花时间回答我的问题。我有一个客户在使用Excel Interop时遇到了速度缓慢的问题。我看到其他一些答案引导我去使用OpenXML，但很高兴有一个快速入门的捷径。 - Brandon Barkley
1没问题。我仍然在使用COM，但是只在我完全控制的环境中使用。我已经在一个拥有几百个用户的应用程序中使用了这种OpenXML方法几个月了，与使用COM时每周出现错误相比，没有遇到任何问题。我也研究过第三方解决方案，比如在这里提到的那些，但我更喜欢自己编写它，这样我就可以尽可能地使它高效。 - WATYF
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接