OpenXML电子表格中的单元格样式（SpreadsheetML） [英] Cell styles in OpenXML spreadsheet (SpreadsheetML)
本文介绍了OpenXML电子表格中的单元格样式（SpreadsheetML）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在C＃中使用OpenXML SDK生成了一个.xlsx电子表格，但无法弄清楚如何使单元格样式正常工作。我一直在研究由Excel生成的文件，并不能弄清楚它是如何完成的。
现在，我正在创建一个填充，创建一个 CellStyleFormat 指向填充，创建一个 CellFormat ，它们指向 CellStyleFormat ，然后创建一个 CellStyle ，指向 CellFormat 。
以下是我用于生成文档的代码：
Console.WriteLine（创建文档）;
使用（var spreadsheet = SpreadsheetDocument.Create（output.xlsx，SpreadsheetDocumentType.Workbook））
{
Console.WriteLine（创建工作簿）;
spreadsheet.AddWorkbookPart（）;
spreadsheet.WorkbookPart.Workbook = new Workbook（）;
Console.WriteLine（创建工作表）;
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（）;
//创建一个坚实的红色填充
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（创建表格数据）;
var sheetData = wsPart.Worksheet.AppendChild（new SheetData（））;
Console.WriteLine（添加行/单元格...）;
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（保存工作表）;
wsPart.Worksheet.Save（）;
Console.WriteLine（创建工作表列表）;
var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild（new Sheets（））;
sheets.AppendChild（new Sheet（）{Id = spreadsheet.WorkbookPart.GetIdOfPart（wsPart），SheetId = 1，Name =Test}）;
Console.WriteLine（保存工作簿）;
spreadsheet.WorkbookPart.Workbook.Save（）;
Console.WriteLine（完成）;
}
以下是生成的XML：
workbook.xml
<？xml version =1.0encoding = UTF-8\" >？;
< x：workbook xmlns：x =http://schemas.openxmlformats.org/spreadsheetml/2006/main>
< x：sheets>
< x：sheet name =TestsheetId =1r：id =Rbad86b8c80844a16xmlns：r =http://schemas.openxmlformats.org/officeDocument/2006/relationships/>
< / x：sheets>
< / x：workbook>
styles.xml
<？xml version =1.0encoding =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 =0applyFill =0/>
< x：xf fillId =1applyFill =1/>
< / x：cellStyleXfs>
< x：cellXfs>
< x：xf xfId =0/>
< x：xf xfId =1/>
< / x：cellXfs>
< x：cellStyles>
< x：cellStyle name =NonexfId =0/>
< x：cellStyle name =Solid RedxfId =1/>
< / x：cellStyles>
< / x：styleSheet>
工作表/ sheet.xml
<？xml version =1.0encoding =utf-8？>
< x：工作表xmlns：x =http://schemas.openxmlformats.org/spreadsheetml/2006/main>
< x：sheetData>
< x：row>
< x：c t =str>< x：v>这个< / 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>值：< / x：v>< / x：c>
< x：c t =n>< x：v> 123< / x：v>< / x：c>
< x：c t =str>< x：v>公式：< / x：v>< / x：c>
< x：c s =1>< x：f> B3< / x：f>< / x：c>
< / x：row>
< / x：sheetData>
< / x：工作表>
最后一行的最后一个单元格是我要添加样式的地方。 >
当我通过OpenXML SDK生产力工具运行时，这一切都正常验证，但是当我尝试在Excel中打开该文件时，我收到以下错误：
修复记录：/xl/styles.xml部分（样式）中的格式
然后电子表格显示，但填写不适用。
任何想法如何解决这个问题？
解决方案
正确的是，经过大量的实验，我设法弄清楚了。
那么excel可以分别为正常单元格和Gray125图案填充保留样式0和1。大部分上述代码可以被删除，因为我们只需要一个 CellFormat 。
工作代码： p>
Console.WriteLine（创建文档）;
使用（var spreadsheet = SpreadsheetDocument.Create（output.xlsx，SpreadsheetDocumentType.Workbook））
{
Console.WriteLine（创建工作簿）;
spreadsheet.AddWorkbookPart（）;
spreadsheet.WorkbookPart.Workbook = new Workbook（）;
Console.WriteLine（创建工作表）;
var wsPart = spreadsheet.WorkbookPart.AddNewPart< WorksheetPart>（）;
wsPart.Worksheet = new Worksheet（）;
var stylesPart = spreadsheet.WorkbookPart.AddNewPart< WorkbookStylesPart>（）;
stylesPart.Stylesheet = new Stylesheet（）;
Console.WriteLine（创建样式）;
//空白字体列表
stylesPart.Stylesheet.Fonts = new Fonts（）;
stylesPart.Stylesheet.Fonts.Count = 1;
stylesPart.Stylesheet.Fonts.AppendChild（new Font（））;
//创建填充
stylesPart.Stylesheet.Fills = new Fills（）;
//创建一个坚实的红色填充
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}}）; //必需，由Excel保留
stylesPart.Stylesheet.Fills.AppendChild（new Fill {PatternFill = new PatternFill {PatternType = PatternValues.Gray125}}）; //必需，由Excel
stylesPart.Stylesheet.Fills.AppendChild（new Fill {PatternFill = solidRed}）保留;
stylesPart.Stylesheet.Fills.Count = 3;
//空白边框列表
stylesPart.Stylesheet.Borders = new Borders（）;
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild（new Border（））;
//空白单元格格式列表
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats（）;
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellStyleFormats.AppendChild（new CellFormat（））;
//单元格格式列表
stylesPart.Stylesheet.CellFormats = new CellFormats（）;
//空一个索引0，似乎是必需的
stylesPart.Stylesheet.CellFormats.AppendChild（new CellFormat（））;
//单元格格式引用样式格式0，字体0，边框0，填充2并应用填充
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（创建表格数据）;
var sheetData = wsPart.Worksheet.AppendChild（new SheetData（））;
Console.WriteLine（添加行/单元格...）;
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，即指向我们的填充格式
row.AppendChild（new Cell（）{CellFormula = new CellFormula（B3），DataType = CellValues.Number，StyleIndex = 1} ）;
Console.WriteLine（保存工作表）;
wsPart.Worksheet.Save（）;
Console.WriteLine（创建工作表列表）;
var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild（new Sheets（））;
sheets.AppendChild（new Sheet（）{Id = spreadsheet.WorkbookPart.GetIdOfPart（wsPart），SheetId = 1，Name =Test}）;
Console.WriteLine（保存工作簿）;
spreadsheet.WorkbookPart.Workbook.Save（）;
Console.WriteLine（完成）;
}
有些建议：
我不能推荐 ClosedXML 如果你正在做这样的工作，那么OpenXML API和格式是非常繁琐的。自己的，有各种无证件的情况。 ClosedXML为您做了很多工作。他们也很快就能够迅速修复错误。
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.
Right now, I'm creating a fill, creating a CellStyleFormat that points at the fill, creating a CellFormat that points at the index of the CellStyleFormat, then creating a CellStyle that points to the 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:
workbook.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
<?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:
Repaired Records: Format from /xl/styles.xml part (Styles)
The spreadsheet then shows, but the fill isn't applied.
Any idea how to go about fixing this?
解决方案
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 CellFormat really.
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.
I cannot recommend ClosedXML highly 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.
这篇关于OpenXML电子表格中的单元格样式（SpreadsheetML）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
