C# OpenXML 电子表格中的单元格样式 (SpreadsheetML)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11116176/
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
Cell styles in OpenXML spreadsheet (SpreadsheetML)
提问by Polynomial
I've generated a .xlsx spreadsheet in C# using the OpenXML SDK, but can't figure out how to get cell styles working. I've been studying files produced by Excel, and can't quite figure out how it's done.
我已经使用 OpenXML SDK 在 C# 中生成了一个 .xlsx 电子表格，但无法弄清楚如何让单元格样式正常工作。我一直在研究 Excel 生成的文件，但不太清楚它是如何完成的。
Right now, I'm creating a fill, creating a CellStyleFormatthat points at the fill, creating a CellFormatthat points at the index of the CellStyleFormat, then creating a CellStylethat points to the CellFormat.
现在，我正在创建一个填充，创建一个CellStyleFormat指向填充，创建一个CellFormat指向 的索引CellStyleFormat，然后创建一个CellStyle指向CellFormat.
Here's the code I'm using to generate the document:
这是我用来生成文档的代码：
Console.WriteLine("Creating document");
using (var spreadsheet = SpreadsheetDocument.Create("output.xlsx", SpreadsheetDocumentType.Workbook))
{
Console.WriteLine("Creating workbook");
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new Workbook();
Console.WriteLine("Creating worksheet");
var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
wsPart.Worksheet = new Worksheet();
var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
stylesPart.Stylesheet.Fills = new Fills();
// create a solid red fill
var solidRed = new PatternFill() { PatternType = PatternValues.Solid };
solidRed.AppendChild(new BackgroundColor { Rgb = HexBinaryValue.FromString("FF00FF00") });
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill() { PatternType = PatternValues.None } });
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed });
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat { FillId = 0, ApplyFill = false });
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat { FillId = 1, ApplyFill = true });
stylesPart.Stylesheet.CellFormats = new CellFormats();
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0 });
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 1 });
stylesPart.Stylesheet.CellStyles = new CellStyles();
stylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "None", FormatId = 0 });
stylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "Solid Red", FormatId = 1 });
stylesPart.Stylesheet.Save();
Console.WriteLine("Creating sheet data");
var sheetData = wsPart.Worksheet.AppendChild(new SheetData());
Console.WriteLine("Adding rows / cells...");
var row = sheetData.AppendChild(new Row());
row.AppendChild(new Cell() { CellValue = new CellValue("This"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("is"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("a"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("test."), DataType = CellValues.String });
sheetData.AppendChild(new Row());
row = sheetData.AppendChild(new Row());
row.AppendChild(new Cell() { CellValue = new CellValue("Value:"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("123"), DataType = CellValues.Number });
row.AppendChild(new Cell() { CellValue = new CellValue("Formula:"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellFormula = new CellFormula("B3"), StyleIndex = 1 }); //
Console.WriteLine("Saving worksheet");
wsPart.Worksheet.Save();
Console.WriteLine("Creating sheet list");
var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Test" });
Console.WriteLine("Saving workbook");
spreadsheet.WorkbookPart.Workbook.Save();
Console.WriteLine("Done.");
}
Here's the generated XML:
这是生成的 XML：
workbook.xml
工作簿.xml
<?xml version="1.0" encoding="utf-8"?>
<x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:sheets>
<x:sheet name="Test" sheetId="1" r:id="Rbad86b8c80844a16" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
</x:sheets>
</x:workbook>
styles.xml
样式文件
<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:fills>
<x:fill>
<x:patternFill patternType="none" />
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FF00FF00" />
</x:patternFill>
</x:fill>
</x:fills>
<x:cellStyleXfs>
<x:xf fillId="0" applyFill="0" />
<x:xf fillId="1" applyFill="1" />
</x:cellStyleXfs>
<x:cellXfs>
<x:xf xfId="0" />
<x:xf xfId="1" />
</x:cellXfs>
<x:cellStyles>
<x:cellStyle name="None" xfId="0" />
<x:cellStyle name="Solid Red" xfId="1" />
</x:cellStyles>
</x:styleSheet>
worksheets/sheet.xml
工作表/sheet.xml
<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:sheetData>
<x:row>
<x:c t="str"><x:v>This</x:v></x:c>
<x:c t="str"><x:v>is</x:v></x:c>
<x:c t="str"><x:v>a</x:v></x:c>
<x:c t="str"><x:v>test.</x:v></x:c>
</x:row>
<x:row />
<x:row>
<x:c t="str"><x:v>Value:</x:v></x:c>
<x:c t="n"><x:v>123</x:v></x:c>
<x:c t="str"><x:v>Formula:</x:v></x:c>
<x:c s="1"><x:f>B3</x:f></x:c>
</x:row>
</x:sheetData>
</x:worksheet>
The last cell of the last row is where I'm trying to add the style.
最后一行的最后一个单元格是我尝试添加样式的地方。
This all validates properly when I run it through the OpenXML SDK Productivity Tool, but, when I attempt to open the file in Excel, I get the following error:
当我通过 OpenXML SDK Productivity Tool 运行它时，这一切都正确验证，但是，当我尝试在 Excel 中打开文件时，出现以下错误：
Repaired Records: Format from /xl/styles.xml part (Styles)
修复的记录：格式来自 /xl/styles.xml 部分（样式）
The spreadsheet then shows, but the fill isn't applied.
然后显示电子表格，但未应用填充。
Any idea how to go about fixing this?
知道如何解决这个问题吗？
采纳答案by Polynomial
Right, I managed to figure this out, after a lot of experimentation.
是的，经过大量实验，我设法弄清楚了这一点。
It turns out that excel reserves styles 0 and 1 for normal cells and "Gray125" pattern fill respectively. Most of the above code can be removed, as we only need a CellFormatreally.
事实证明，excel 分别为普通单元格和“Gray125”图案填充保留了样式 0 和 1。上面的大部分代码都可以去掉，因为我们只需要一个CellFormat真正的。
Working code:
工作代码：
Console.WriteLine("Creating document");
using (var spreadsheet = SpreadsheetDocument.Create("output.xlsx", SpreadsheetDocumentType.Workbook))
{
Console.WriteLine("Creating workbook");
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new Workbook();
Console.WriteLine("Creating worksheet");
var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
wsPart.Worksheet = new Worksheet();
var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
Console.WriteLine("Creating styles");
// blank font list
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.Count = 1;
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
// create fills
stylesPart.Stylesheet.Fills = new Fills();
// create a solid red fill
var solidRed = new PatternFill() { PatternType = PatternValues.Solid };
solidRed.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFF0000") }; // red fill
solidRed.BackgroundColor = new BackgroundColor { Indexed = 64 };
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed });
stylesPart.Stylesheet.Fills.Count = 3;
// blank border list
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild(new Border());
// blank cell format list
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
// cell format list
stylesPart.Stylesheet.CellFormats = new CellFormats();
// empty one for index 0, seems to be required
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
// cell format references style format 0, font 0, border 0, fill 2 and applies the fill
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 0, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });
stylesPart.Stylesheet.CellFormats.Count = 2;
stylesPart.Stylesheet.Save();
Console.WriteLine("Creating sheet data");
var sheetData = wsPart.Worksheet.AppendChild(new SheetData());
Console.WriteLine("Adding rows / cells...");
var row = sheetData.AppendChild(new Row());
row.AppendChild(new Cell() { CellValue = new CellValue("This"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("is"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("a"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("test."), DataType = CellValues.String });
sheetData.AppendChild(new Row());
row = sheetData.AppendChild(new Row());
row.AppendChild(new Cell() { CellValue = new CellValue("Value:"), DataType = CellValues.String });
row.AppendChild(new Cell() { CellValue = new CellValue("123"), DataType = CellValues.Number });
row.AppendChild(new Cell() { CellValue = new CellValue("Formula:"), DataType = CellValues.String });
// style index = 1, i.e. point at our fill format
row.AppendChild(new Cell() { CellFormula = new CellFormula("B3"), DataType = CellValues.Number, StyleIndex = 1 });
Console.WriteLine("Saving worksheet");
wsPart.Worksheet.Save();
Console.WriteLine("Creating sheet list");
var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Test" });
Console.WriteLine("Saving workbook");
spreadsheet.WorkbookPart.Workbook.Save();
Console.WriteLine("Done.");
}
Some advice:
一些忠告：
Use ClosedXML if you want to avoid this insanity.
如果您想避免这种疯狂，请使用 ClosedXML。
I cannot recommend ClosedXMLhighly enough if you're doing this kind of work.The OpenXML API and format is horribly tedious to work with on its own, with all sorts of undocumented cases. ClosedXML does so much of the leg work for you. They're also really great at getting bugs fixed quickly.
如果您正在做这种工作，我不能高度推荐ClosedXML。OpenXML API 和格式单独使用非常乏味，有各种未记录的案例。ClosedXML 为您做了大量的工作。他们也非常擅长快速修复错误。
回答by Luizgrs
A more generic answer, all this I found after testing, so no documentation to point to.
一个更通用的答案，所有这些都是我在测试后发现的，所以没有文档可以指出。
Once you set a CellFormatscollection in the stylesheet Excel runs a deeper validation on it.
CellFormats在样式表中设置集合后，Excel 会对其进行更深入的验证。
CellFormatscannot be empty, it must have at least one CellFormatthere.
CellFormats不能为空，它必须至少有一个CellFormat。
Once you add a CellFormat, Excel will complain if Fills, Fontsor Borderscollections are empty.
一旦你添加一个CellFormat，Excel将抱怨，如果Fills，Fonts或Borders集合是空的。
First Fontis used as default for whole workbook and also Column/Row headers in Excel.
FirstFont用作整个工作簿以及 Excel 中的列/行标题的默认值。
Excel will ignore first CellFormat, so just add an empty one.
Excel 将忽略 first CellFormat，因此只需添加一个空的。
If you need a Borderor Fillin your format, Excel will also ignore first Borderand Fill, so also add empty ones as first child in Bordersand Fills.
如果您需要Border或Fill在您的格式中，Excel 也将首先忽略Borderand Fill，因此还要在Bordersand 中添加空的作为第一个子项Fills。
Finally, starting in the second CellFormat(s = "1") you're good to go.
最后，从第二个CellFormat( s = "1")开始，您就可以开始了。
Tested in Excel 2010.
在 Excel 2010 中测试。
