使用OpenXML在.NET 6中导出Excel数据：详细指南
很多时候，我们需要将数据导出到外部文件中。我们可以使用 .csv 文件，但它没有格式化数据的能力;突出显示数据等。 在 .NET Framework 中，我们可以使用 Office Interop。但是，在 .NET 6 中，不再支持 Office 互操作。为了将数据导出为Excel格式，我们可以使用OpenXML来实现它。
在 Excel 2007 及更高版本中，.xlsx 文件是基于 XML 的格式，称为 Office Open Xml （OpenXML）。.xlsx 文件是一个 zip 包，其中包含 Excel 文件的每个主要部分（工作表、样式、图表、数据透视表等）的 XML 文件。您可以将文件的扩展名从 .xlsx 更改为 .zip，然后您将在 .zip 文件中看到内部文件/文件夹结构。
.xlsx 文件的内部结构
安装 Nuget 包
为了在 .NET 6 应用程序中使用 OpenXML，您需要添加以下 nuget 包：
Install-Package DocumentFormat.OpenXml -Version 2.19.0
安装包后，可以添加以下程序集指令以使用 OpenXML。
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
OpenXML Excel 对象结构
我发现OpenXML SDK中定义的对象结构/关系非常混乱且难以理解。下面是用于更好地理解 OpenXML 对象模型的类图
OpenXML SpreadSheet 类图
电子表格文档
SpreadsheetDocument 类表示一个 Excel 文档包。您可以调用静态 Create（） 方法来创建 SpreadsheetDocument 对象。这是您必须创建的第一个对象。
/// <summary>
/// Creates a new instance of the SpreadsheetDocument class from the specified file.
/// </summary>
/// <param name="path">The path and file name of the target SpreadsheetDocument.</param>
/// <param name="type">The type of the SpreadsheetDocument.</param>
/// <returns>A new instance of SpreadsheetDocument.</returns>
/// <exception cref="ArgumentNullException">Thrown when "path" is null reference.</exception>
public static SpreadsheetDocument Create(string path, SpreadsheetDocumentType type)
{
return Create(path, type, true);
}
SpreadsheetDocument excel = SpreadsheetDocument.Create("demo.xlsx", SpreadsheetDocumentType.Workbook);
SpreadsheetDocumentType 枚举可以是以下值。
/// <summary>
/// Defines SpreadsheetDocumentType - type of SpreadsheetDocument.
/// </summary>
public enum SpreadsheetDocumentType
{
Workbook, /// Excel Workbook (*.xlsx).
Template, /// Excel Template (*.xltx).
MacroEnabledWorkbook, /// Excel Macro-Enabled Workbook (*.xlsm).
MacroEnabledTemplate, /// Excel Macro-Enabled Template (*.xltm).
AddIn, /// Excel Add-In (*.xlam).
}
工作簿部分
这是 Excel 文档的顶级容器。只允许一个实例。
WorkbookPart workbookpart = excel.AddWorkbookPart();
练习 册
Workbook 是 WorkbookPart 中的子元素。Workbook 和 WorkbookPart 具有一对一关系。
workbookpart.Workbook = new Workbook();
工作表部分
您可以通过调用 WorkbookPart 对象的 AddNewPart
WorksheetPart worksheetPart1 = workbookpart.AddNewPart<WorksheetPart>();
WorksheetPart worksheetPart2 = workbookpart.AddNewPart<WorksheetPart>();
工作表
每个 WorksheetPart 只有一个 Worksheet。
worksheetPart.Worksheet = new Worksheet();
列
Worksheet 包含 Columns 子元素，该子元素是 Column 对象的集合。Column 对象定义工作表列的元数据，例如列宽。
SheetData（表数据）
Worksheet 包含 SheetData 子元素，该子元素是 Row 对象的集合。每行都有一个 Cell 对象的集合。SheetData 是 Excel 文档中的实际数据。
string[] Sheet2Headers = new string[] { "Timestamp", "Workstation", "Unit", "Amount" };
string[][] Sheet2Data = new string[][] {
new string[] { "2023-01-26T15:10:32.1339754-05:00", "Workstation 1", "MB", "300.40" },
new string[] { "2023-01-26T15:13:59.3092106-05:00", "Workstation 2", "Minutes", "17808.40" },
new string[] { "2023-01-26T15:52:43.7308915-05:00", "Workstation 3", "Percentage", "7.47" },
new string[] { "2023-01-26T15:31:43.8670790-05:00", "Workstation 4", "Minutes", "1.40" },
new string[] { "2023-01-26T15:32:15.0373524-05:00", "Workstation 5", "Percentage", "0.19" }
};
SheetData data = new SheetData();
int rowId = 0;
Row row = new Row();
for (int i = 0; i < Sheet2Headers.Length; i++)
{
row.InsertAt<Cell>(new Cell()
{
DataType = CellValues.InlineString,
InlineString = new InlineString() { Text = new Text(Sheet2Headers[i]) },
}, i);
}
data.InsertAt(row, rowId++);
for (int i = 0; i < Sheet2Data.Length; i++)
{
row = new Row();
for (int j = 0; j < Sheet2Data[i].Length; j++)
{
row.InsertAt<Cell>(new Cell()
{
DataType = CellValues.InlineString,
InlineString = new InlineString() { Text = new Text(Sheet2Data[i][j]) },
}, j);
}
data.InsertAt(row, rowId++);
}
worksheetPart2.Worksheet.Append(data); 单元格数据类型可以是以下值：
public enum CellValues
{
Boolean,
Number,
Error,
SharedString,
String,
InlineString,
[OfficeAvailability(FileFormatVersions.Office2007)]
Date
}
日期类型仅在 Office2007 及更高版本中受支持。String 和 InlineString 之间的区别在于在单元格中插入公式时应使用;while 用于 reguarl 字符串文本，如果您不想将字符串存储在 .CellValues.StringCellValues.InlineStringSharedStringTable
片材和片材
一个 Workbook 有一个 Sheets 对象，该对象是许多 Sheet 对象的集合。每个 Sheet 对象都与之前由 theId 属性创建的每个 WorksheetPart 相关联。
// Add Sheets to the Workbook.
Sheets sheets = excel.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
sheets.Append(new Sheet()
{
Id = excel.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = 1,
Name = "Sheet 1"
});
sheets.Append(new Sheet()
{
Id = excel.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "Sheet 2"
});
WorkbookStylesPart
WorkbookStylesPart 是 Excel 文档的容器样式信息。您可以使用 WorkbookPart 对象的 AddNewPart
var stylesPart = excel.WorkbookPart.AddNewPart
样式表
WorkbookStylesPart 有一个 StyleSheet 属性，该属性包含所有与样式相关的集合。
您可以将以下元素添加到样式表中。
字体 边界 充满 编号格式 CellStyleFormats 细胞格式 细胞样式
添加这些元素的顺序很重要（奇怪，我同意）。以错误的顺序插入它们会导致打开 Excel 文件时出现错误。
设置 Excel 单元格的格式
您可以在 StyleSheet 对象中定义字体、填充、边框、编号格式、单元格格式等。将 Cells 插入 Row 对象时，可以通过 StyleIndex 属性引用这些定义的样式。
以下是用于创建 StyleSheet 对象的代码片段：
ForegroundColor TranslateForeground(System.Drawing.Color fillColor)
{
return new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value =
System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
fillColor.A,
fillColor.R,
fillColor.G,
fillColor.B)).Replace("#", "")
}
};
}
Stylesheet CreateStyleSheet()
{
Stylesheet stylesheet = new Stylesheet();
#region Number format
uint DATETIME_FORMAT = 164;
uint DIGITS4_FORMAT = 165;
var numberingFormats = new NumberingFormats();
numberingFormats.Append(new NumberingFormat // Datetime format
{
NumberFormatId = UInt32Value.FromUInt32(DATETIME_FORMAT),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
});
numberingFormats.Append(new NumberingFormat // four digits format
{
NumberFormatId = UInt32Value.FromUInt32(DIGITS4_FORMAT),
FormatCode = StringValue.FromString("0000")
});
numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
#endregion
#region Fonts
var fonts = new Fonts();
fonts.Append(new DocumentFormat.OpenXml.Spreadsheet.Font() // Font index 0 - default
{
FontName = new FontName { Val = StringValue.FromString("Calibri") },
FontSize = new FontSize { Val = DoubleValue.FromDouble(11) }
});
fonts.Append(new DocumentFormat.OpenXml.Spreadsheet.Font() // Font index 1
{
FontName = new FontName { Val = StringValue.FromString("Arial") },
FontSize = new FontSize { Val = DoubleValue.FromDouble(11) },
Bold = new Bold()
});
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
#endregion
#region Fills
var fills = new Fills();
fills.Append(new Fill() // Fill index 0
{
PatternFill = new PatternFill { PatternType = PatternValues.None }
});
fills.Append(new Fill() // Fill index 1
{
PatternFill = new PatternFill { PatternType = PatternValues.Gray125 }
});
fills.Append(new Fill() // Fill index 2
{
PatternFill = new PatternFill {
PatternType = PatternValues.Solid,
ForegroundColor = TranslateForeground(System.Drawing.Color.LightBlue),
BackgroundColor = new BackgroundColor { Rgb = TranslateForeground(System.Drawing.Color.LightBlue).Rgb }
}
});
fills.Append(new Fill() // Fill index 3
{
PatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = TranslateForeground(System.Drawing.Color.LightSkyBlue),
BackgroundColor = new BackgroundColor { Rgb = TranslateForeground(System.Drawing.Color.LightBlue).Rgb }
}
});
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
#endregion
#region Borders
var borders = new Borders();
borders.Append(new Border // Border index 0: no border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
});
borders.Append(new Border //Boarder Index 1: All
{
LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
});
borders.Append(new Border // Boarder Index 2: Top and Bottom
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
});
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
#endregion
#region Cell Style Format
var cellStyleFormats = new CellStyleFormats();
cellStyleFormats.Append(new CellFormat // Cell style format index 0: no format
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
});
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
#endregion
#region Cell format
var cellFormats = new CellFormats();
cellFormats.Append(new CellFormat()); // Cell format index 0
cellFormats.Append(new CellFormat // CellFormat index 1
{
NumberFormatId = 14, // 14 = 'mm-dd-yy'. Standard Date format;
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
cellFormats.Append(new CellFormat // Cell format index 2: Standard Number format with 2 decimal placing
{
NumberFormatId = 4, // 4 = '#,##0.00';
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
cellFormats.Append(new CellFormat // Cell formt index 3
{
NumberFormatId = DATETIME_FORMAT, // 164 = 'dd/mm/yyyy hh:mm:ss'. Standard Datetime format;
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
cellFormats.Append(new CellFormat // Cell format index 4
{
NumberFormatId = 3, // 3 #,##0
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
cellFormats.Append(new CellFormat // Cell format index 5
{
NumberFormatId = 4, // 4 #,##0.00
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
cellFormats.Append(new CellFormat // Cell format index 6
{
NumberFormatId = 10, // 10 0.00 %,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
cellFormats.Append(new CellFormat // Cell format index 7
{
NumberFormatId = DIGITS4_FORMAT, // Format cellas 4 digits. If less than 4 digits, prepend 0 in front
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
});
cellFormats.Append(new CellFormat // Cell format index 8: Cell header
{
NumberFormatId = 49,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center }
});
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
#endregion
stylesheet.Append(numberingFormats);
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
#region Cell styles
var css = new CellStyles();
css.Append(new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
});
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
stylesheet.Append(css);
#endregion
var dfs = new DifferentialFormats { Count = 0 };
stylesheet.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
stylesheet.Append(tss);
return stylesheet;
}
var stylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = CreateStyleSheet();
stylesPart.Stylesheet.Save();
注意：
在 StyleSheet 对象中创建和追加这些子元素的顺序很重要。如果做错了，打开生成的Excel文件会导致错误。 Cell 对象的 StyleIndex 属性引用 cellFormats 数组中 CellFormat 对象的索引。例如，StyleIndex 8 表示应用 CellFormat Fonts、Fills、Borders 和 CellFormats 等的所有 Count 属性都是 UInt32Value 的类型。它将为 null，并且在附加元素时不会更新。您必须手动为此属性赋值（再次是 Stragne）。但是，我确实发现，即使我注释掉了这些 Count 赋值语句，生成的 Excel 也能正常工作。
编号格式
下面是系统定义的 Excel NumberFormatId 的列表：
ID FORMAT CODE 0 General 1 0 2 0.00 3 #,##0 4 #,##0.00 9 0% 10 0.00% 11 0.00E+00 12 # ?/? 13 # ??/?? 14 d/m/yyyy 15 d-mmm-yy 16 d-mmm 17 mmm-yy 18 h:mm tt 19 h:mm:ss tt 20 H:mm 21 H:mm:ss 22 m/d/yyyy H:mm 37 #,##0 ;(#,##0) 38 #,##0 ;Red 39 #,##0.00;(#,##0.00) 40 #,##0.00;Red 45 mm:ss 46 [h]:mm:ss 47 mmss.0 48 ##0.0E+0 49 @
系统定义的编号格式 ID 小于 164。您可以定义自定义编号格式。自定义的格式 ID 必须为 164 及以上。
数字格式代码中最常见的字符是 ？、# 和 0。
0将显示源中的数字或零。这主要用于在数字前面加上 0。例如，如果数字为 124，格式代码为“0000”，则最终显示为 0124。 #将显示源中的数字或空格。 ?将显示源中的数字或空白区域。这主要用于对齐相邻行中的数字。 ,将使用特定于区域设置的分隔符分隔 1000 的每个倍数。
var numberingFormats = new NumberingFormats();
numberingFormats.Append(new NumberingFormat // Datetime format
{
NumberFormatId = UInt32Value.FromUInt32(164),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
});
numberingFormats.Append(new NumberingFormat // four digits format
{
NumberFormatId = UInt32Value.FromUInt32(165),
FormatCode = StringValue.FromString("0000")
});
numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
单元格格式
这是样式表的核心部分。Cell 对象的 StyleIndex 属性引用 cellFormats 数组中 CellFormat 对象的索引。cellFormats 数组中的第一个 CellFormat 应始终为空。
FontId 属性引用 fonts 数组中 Font 对象的索引。 FillId 属性引用 fills 数组中 Fill 对象的索引。 BorderId 属性引用 borders 数组中 Border 对象的索引。 NumberFormatId 属性引用 NumberFormat 对象的 NumberFormatId。它可以是系统定义的 NumberFormatId（小于 164）或自定义的 NumberFormatId（164 及以上）。
日期格式
在 OpenXml 中设置日期格式有两种方法：
将 Cell 设置为 using 方法中的数字。以这种方式设置日期格式将与 Excel 2007 兼容。DataTypeCellValues.NumberCellValueToOADate() 将单元格设置为 ISO 8601 格式的日期。 仅在 Office 2007 及更高版本中受支持。DataTypeCellValues.DateCellValueCellValues.Date
计算和设置列宽
生成的 Excel 具有每列的默认列宽。它将无法自动调整大小以适应其内容。您必须自己计算宽度以适应内容。
宽度的计算公式为：
width = 截断（[{字符数} * {最大数字宽度} + {5 像素填充}] / {最大数字宽度} * 256） / 256
准确计算内容宽度是非常困难和不必要的。例如，数字格式将添加额外的字符（如逗号、美元符号、百分号等）。
以下代码片段可以计算列宽，并且足够好使用：
Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
{
//iterate over all cells getting a max char value for each column
Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
var rows = sheetData.Elements<Row>();
UInt32[] numberStyles = new UInt32[] { 5, 6, 7, 8 }; //styles that will add extra chars
UInt32[] boldStyles = new UInt32[] { 1, 2, 3, 4, 6, 7, 8 }; //styles that will bold
foreach (var r in rows)
{
var cells = r.Elements<Cell>().ToArray();
//using cell index as my column
for (int i = 0; i < cells.Length; i++)
{
var cell = cells[i];
var cellValue = cell.CellValue == null ? cell.InnerText : cell.CellValue.InnerText;
var cellTextLength = cellValue.Length;
if (cell.StyleIndex != null && numberStyles.Contains(cell.StyleIndex))
{
int thousandCount = (int)Math.Truncate((double)cellTextLength / 4);
//add 3 for '.00'
cellTextLength += (3 + thousandCount);
}
if (cell.StyleIndex != null && boldStyles.Contains(cell.StyleIndex))
{
//add an extra char for bold - not 100% acurate but good enough for what i need.
cellTextLength += 1;
}
if (maxColWidth.ContainsKey(i))
{
var current = maxColWidth[i];
if (cellTextLength > current)
{
maxColWidth[i] = cellTextLength;
}
}
else
{
maxColWidth.Add(i, cellTextLength);
}
}
}
return maxColWidth;
}
Columns AutoSizeCells(SheetData sheetData)
{
var maxColWidth = GetMaxCharacterWidth(sheetData);
Columns columns = new Columns();
//this is the width of my font - yours may be different
double maxWidth = 7;
foreach (var item in maxColWidth)
{
//width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;
Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
columns.Append(col);
}
return columns;
}
worksheetPart1.Worksheet = new Worksheet();
SheetData data1 = CreateShee1Data();
Columns columns1 = AutoSizeCells(data1);
worksheetPart1.Worksheet.Append(columns1);
worksheetPart1.Worksheet.Append(data1);