使用openXml将样式表添加到excel时出错

Question

我正在尝试将样式添加到excel文件中。在电子表格中可以正确下载纯数据。但是，一旦我添加了添加样式表的代码，甚至不使用任何这些样式，它就会在打开文件时出错——

修复的记录：来自/xl/styles.xml部分(样式(的格式

我已经尝试在样式表中包含最少四个(默认(参数——字体、填充、边框、单元格格式——这是对其他帖子的解决方案的建议，但仍然无法修复。

我真的很感谢你的帮助。

private void CreateExcelFile(IEnumerable<DataModel> models)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = "rId1", SheetId = (UInt32Value)1U, Name = "Product Data" };
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//below 3 lines need a fix; 
//if I remove these 3 lines, there is no error on opening the excel file
WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rId3"); 
stylesPart.Stylesheet = GenerateStyleSheet();  
stylesPart.Stylesheet.Save();

Row firstRow = new Row() { RowIndex = 1 };
firstRow.Append(ConstructCell("Some Message", CellValues.String)); 
firstRow.CustomHeight = true;
firstRow.CustomFormat = true;
firstRow.Height = 100;
sheetData.AppendChild(firstRow);
MergeCells mergeCells = new MergeCells();
mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:E1") });               
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
var row = new Row(ConstructCell("Some Cell Data", CellValues.String)));
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
document.Close();
}
}
private Stylesheet GenerateStyleSheet() {
var stylesheet = new Stylesheet();
//tried adding attributes too
/*
var stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
*/

var fonts = new Fonts() { Count = (UInt32Value)1U, KnownFonts = BooleanValue.FromBoolean(true) };
var font = new Font
{
FontSize = new FontSize() { Val = 11D },
FontName = new FontName() { Val = "Calibri" },
Color = new Color() { Theme = (UInt32Value)1U},
FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
FontScheme = new FontScheme() { Val = new EnumValue<FontSchemeValues>(FontSchemeValues.Minor) }
};
fonts.Append(font);
var fills = new Fills() { Count = 1 };
var fill = new Fill();
fill.PatternFill = new PatternFill() { PatternType = new EnumValue<PatternValues>(PatternValues.None) };
fills.Append(fill);
var borders = new Borders() { Count = 1 };
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
var cellFormats = new CellFormats(
new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 0, FillId = 0, BorderId = 0 }, // Index 0
new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true }, //Index 1 Bold
new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true }, //Index 2 Italics
new CellFormat() { NumberFormatId = 22, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true) } //Index 3 Date
);


stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellFormats);

return stylesheet;
}

private Cell ConstructCell(string value, CellValues dataType, uint styleIndex = 0U)
{
return new Cell()
{
CellValue = new CellValue(value),                
DataType = new EnumValue<CellValues>(dataType),
StyleIndex = styleIndex
};
}

Answer 1

在对上面的代码进行了一些修改后，我意识到问题出在CellFormat-NumberFormatId=22和FontId=2中。当将这两个值都设置为0时，样式表运行良好。我的代码没有对应的指定值的Font和NumberFormat。