C# 使用 OpenXml sdk 2.0 创建 Excel 文档
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1012547/
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
Creating Excel document with OpenXml sdk 2.0
提问by JAiro
I have created an Excel document using OpenXml SDK 2.0, now I have to style It, but I can`t.
我已经使用 OpenXml SDK 2.0 创建了一个 Excel 文档，现在我必须设置它的样式，但我不能。
I don't know how to paint the background color or change the font size in different cells.
我不知道如何在不同的单元格中绘制背景颜色或更改字体大小。
My code to create a cell is:
我创建单元格的代码是：
private static Cell CreateTextCell(string header, string text, UInt32Value index)
{
Cell c = new Cell();
c.DataType = CellValues.InlineString;
c.CellReference = header + index;
InlineString inlineString = new InlineString();
DocumentFormat.OpenXml.Spreadsheet.Text t = new DocumentFormat.OpenXml.Spreadsheet.Text();
t.Text = text;
inlineString.AppendChild(t);
c.AppendChild(inlineString);
return c;
}
采纳答案by foson
Note: OpenXML 2.0 SDK is currently in CTP and is not licensed for production use until Office2010.
注意：OpenXML 2.0 SDK 目前在 CTP 中，直到 Office2010 才被许可用于生产。
My general methodoloy to deal with OpenXML SDK is to create a blank document and a document with just the features you'd like to learn how to implement (like background color) and use the SDK's OpenXmlDiff to see what changes need to be made to implement the feature.
我处理 OpenXML SDK 的一般方法是创建一个空白文档和一个仅包含您想要学习如何实现的功能（如背景颜色）的文档，并使用 SDK 的 OpenXmlDiff 来查看需要进行哪些更改才能实现功能。
If you are creating a document from scratch, you can use DocumentReflector to generate the code for the default Stylesheet object and then add the styles you need.
如果您是从头开始创建文档，您可以使用 DocumentReflector 为默认的 Stylesheet 对象生成代码，然后添加您需要的样式。
Starting with the default:
从默认值开始：
new Stylesheet(
new Fonts(
new Font(
new FontSize() { Val = 10D },
new Color() { Theme = (UInt32Value)1U },
new FontName() { Val = "Arial" },
new FontFamilyNumbering() { Val = 2 })
) { Count = (UInt32Value)1U },
new Fills(
new Fill(
new PatternFill() { PatternType = PatternValues.None }),
new Fill(
new PatternFill() { PatternType = PatternValues.Gray125 })
) { Count = (UInt32Value)2U },
new Borders(...
...
...
new CellFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }) { Count = (UInt32Value)1U }, ...
I've added a new Font of size 12 and a new Fill with red background (Indexed value 64), and added new CellFormats that reference the index of the new Font and Fill. (Make sure to update the Counts too)
我添加了大小为 12 的新字体和红色背景的新填充（索引值 64），并添加了引用新字体和填充索引的新 CellFormat。（确保也更新计数）
new Stylesheet(
new Fonts(
new Font(
new FontSize() { Val = 10D },
new Color() { Theme = (UInt32Value)1U },
new FontName() { Val = "Arial" },
new FontFamilyNumbering() { Val = 2 }),
new Font(
new FontSize() { Val = 12D },
new Color() { Theme = (UInt32Value)1U },
new FontName() { Val = "Arial" },
new FontFamilyNumbering() { Val = 2 })
) { Count = (UInt32Value)2U },
new Fills(
new Fill(
new PatternFill() { PatternType = PatternValues.None }),
new Fill(
new PatternFill() { PatternType = PatternValues.Gray125 }),
new Fill(
new PatternFill() { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor() { Rgb = "FFFF0000" }, BackgroundColor = new BackgroundColor() { Indexed = 64 } })
) { Count = (UInt32Value)3U },
new Borders(
new Border(
new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder())
) { Count = (UInt32Value)1U },
new CellStyleFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }
) { Count = (UInt32Value)1U },
new CellFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U },
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U },
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }
) { Count = (UInt32Value)3U },
new CellStyles(
new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U }
) { Count = (UInt32Value)1U },
new DifferentialFormats() { Count = (UInt32Value)0U },
new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" });
Then, in code, I apply the CellStyle index to the cells I want to format: (There was already data in cells A2 and A3. Cell A2 gets the larger size, A3 gets red background)
然后，在代码中，我将 CellStyle 索引应用于要格式化的单元格：（单元格 A2 和 A3 中已经有数据。单元格 A2 获得更大的尺寸，A3 获得红色背景）
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
sheetData.Descendants<Row>().Where(r => r.RowIndex == 2U).First().Descendants<Cell>().First().StyleIndex = 1U;
sheetData.Descendants<Row>().Where(r => r.RowIndex == 3U).First().Descendants<Cell>().First().StyleIndex = 2U;
回答by Hailiang Wang
How to specify a cell style?
如何指定单元格样式？
new Cell() { CellReference = "B6", StyleIndex = 11U }
Here "11U" is a zero-based index of StylesPart.Stylesheet.CellFormats, in which each CellFormat defines a combination of NumberFormat, Font, Fill and Border styles.
这里的“11U”是 StylesPart.Stylesheet.CellFormats 的从零开始的索引，其中每个 CellFormat 定义了 NumberFormat、Font、Fill 和 Border 样式的组合。
You do not have to add all the styles by program, instead you can create a template xlsx file with all the formats you need in it, and then specify the style index in your program.
您不必按程序添加所有样式，而是可以创建包含您需要的所有格式的模板 xlsx 文件，然后在程序中指定样式索引。
回答by Mike Gledhill
Many thanks for this article.
非常感谢这篇文章。
After a lot of struggling (and Googling), I finally managed to create a veryeasy-to-use C# class, which takes a DataSet and a Filename, and creates an Office 2007 .xlsx containing the DataSet's data.
经过大量的努力（和谷歌搜索），我终于设法创建了一个非常易于使用的 C# 类，它接受一个数据集和一个文件名，并创建一个包含数据集数据的 Office 2007 .xlsx。
Suddenly, the process of adding a function to your application to "Export to Excel" becomes as easy as...
突然间，向您的应用程序添加函数以“导出到 Excel”的过程变得如此简单......
DataSet ds = CreateSampleData(); // Your code here !
string excelFilename = "C:\Sample.xlsx";
CreateExcelFile.CreateExcelDocument(ds, excelFilename);
I've posted the full source code, plus an example of using it, on the following website.
我已经在以下网站上发布了完整的源代码以及使用它的示例。
It's a Visual Studio 2008 C# WinForms application, but you can let Visual Studio upgrade this project, if you're running VS2010.
这是一个 Visual Studio 2008 C# WinForms 应用程序，但如果您运行的是 VS2010，您可以让 Visual Studio 升级此项目。
Enjoy.
享受。
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
