比较来自世界各地的卖家的域名和 IT 服务价格

标识电子表格中的单元格 OpenXML /SpreadsheetML/

我创建了一个电子表格 .xlsx 在 C#, 使用 OpenXML SDK, 但我无法理解如何制作细胞样式。 我研究了创建的文件 Excel, 我无法理解这是如何完成的。

现在我创建填充,创建
CellStyleFormat

, 哪个指向填充,创建
CellFormat

, 这表示索引
CellStyleFormat

, 然后创造
CellStyle

, 这表明了
CellFormat

.

以下是我用来创建文档的代码:


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."/;
}


这里是生成的 XML:

workbook.xml


<?xml version="1.0" encoding="utf-8"?>
<x:workbook xmlns:x="[url=http://schemas.openxmlformats.org/spreadsheetml/2006/main">]http://schemas.openxmlformats. ... gt%3B[/url]
<x:sheets>
<x:sheet name="Test" r:id="Rbad86b8c80844a16" sheetid="1" xmlns:r="[url=http://schemas.openxmlformats.org/officeDocument/2006/relationships"></x:sheet>]http://schemas.openxmlformats. ... gt%3B[/url]
</x:sheets>
</x:workbook>


styles.xml


<?xml version="1.0" encoding="utf-8"?>
<x:stylesheet xmlns:x="[url=http://schemas.openxmlformats.org/spreadsheetml/2006/main">]http://schemas.openxmlformats. ... gt%3B[/url]
<x:fills>
<x:fill>
<x:patternfill patterntype="none"></x:patternfill>
</x:fill>
<x:fill>
<x:patternfill patterntype="solid">
<x:bgcolor rgb="FF00FF00"></x:bgcolor>
</x:patternfill>
</x:fill>
</x:fills>
<x:cellstylexfs>
<x:xf applyfill="0" fillid="0"></x:xf>
<x:xf applyfill="1" fillid="1"></x:xf>
</x:cellstylexfs>
<x:cellxfs>
<x:xf xfid="0"></x:xf>
<x:xf xfid="1"></x:xf>
</x:cellxfs>
<x:cellstyles>
<x:cellstyle name="None" xfid="0"></x:cellstyle>
<x:cellstyle name="Solid Red" xfid="1"></x:cellstyle>
</x:cellstyles>
</x:stylesheet>


worksheets/sheet.xml


<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:x="[url=http://schemas.openxmlformats.org/spreadsheetml/2006/main">]http://schemas.openxmlformats. ... gt%3B[/url]
<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: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>


最后一行最后一行小区 - 这是我正在尝试添加风格的地方。

当我通过生产力工具运行时,正确检查所有这些。 OpenXML SDK, 但是当我尝试打开文件时 Excel, 我收到以下错误:

恢复的记录:格式 / xl/styles.xml 部分 /风格/

然后显示电子表格,但填充不适用。

有想法如何解决它?
</workbookstylespart></worksheetpart>
已邀请:

卫东

赞同来自:

是的,在长期实验后,我设法弄清楚了它。

事实证明 excel 储备风格 0 和 1 对于正常细胞和 "Gray125" pattern fill 分别。 上面的大多数代码可以删除,因为实际上我们只需要
CellFormat

.

工作代码:


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."/;
}


几个提示:

使用 ClosedXML, 如果你想避免这种疯狂。

我不能推荐
https://github.com/closedxml/closedxml
如果你做出这样的工作,足够高。

格式 OpenXML API 对自己的工作非常繁琐,有各种无证案件。 ClosedXML 为你制作这么多的腿。 它们在快速纠正时也非常擅长。
</workbookstylespart></worksheetpart>

冰洋

赞同来自:

一个更常见的答案,我发现它全部在测试后,所以没有您可以指定的文档。

安装集合后
CellFormats

在风格的表中 Excel 跑得更深的支票。


CellFormats

不能是空的,它应该至少一个
CellFormat

.

只要你添加
CellFormat

, Excel 如果收藏品会抱怨
Fills

,
Fonts

或者
Borders

空的。

第一的
Font

默认情况下用于整本书,以及列标题/线B. Excel.

Excel 将忽略第一个
CellFormat

,所以只需添加空“。

如果你需要
Border

或者
Fill

在你的格式 Excel 也忽略了第一
Border


Fill

, 因此,还将空插入第一次后代
Borders


Fills

.

最后,从第二个开始
CellFormat

/
s = "1"

/, 你可以走了。

测试B. Excel 2010 年。

要回复问题请先登录注册