OpenXML Issue with CellStyle

Question

我正在尝试在一个完全空的电子表格中定义一个CellStyle。对于 CellFormat，我使用以下代码：

var workbookStylesPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().First();
//create default font for links
Font font2 = new Font();
Underline underline1 = new Underline();
FontSize fontSize2 = new FontSize() { Val = 11D };
Color color2 = new Color() { Theme = (UInt32Value)10U };
FontName fontName2 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };
font2.Append(underline1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
workbookStylesPart.Stylesheet.Fonts.Append(font2);
int fontId = workbookStylesPart.Stylesheet.Fonts.Count() - 1;
CellFormat copy = (CellFormat)workbookStylesPart.Stylesheet.CellFormats.FirstOrDefault().Clone();
copy.FontId = Convert.ToUInt32(fontId);
workbookStylesPart.Stylesheet.CellFormats.Append(copy);

这一切都工作正常，我可以将此单元格格式应用于单元格并且格式正确，但是一旦我打开尝试将其添加到 CellStyle 中，我的电子表格就坏了，我无法再打开它了。这就是我添加单元格样式的方式：

workbookStylesPart.Stylesheet.CellFormats.Append(copy);
int cellFormatId = workbookStylesPart.Stylesheet.CellFormats.Count() - 1;
CellStyle cellStyle2 = new CellStyle() { Name = "linkformat", FormatId = Convert.ToUInt32(cellFormatId), BuiltinId = (UInt32Value)0U };

如果我将 FormatId 更改回值"1U"，它会再次工作。 有没有人知道我如何让它工作？

Answer 1

我已经将CellStyle应用于电子表格，它工作正常。请尝试以下操作：

using (var excel = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true))
{
workbookPart = excel.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
uint sheetId = 1;
excel.WorkbookPart.Workbook.Sheets = new Sheets();
Sheets sheets = excel.WorkbookPart.Workbook.GetFirstChild<Sheets>();
WorkbookStylesPart stylesPart = excel.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
Worksheet worksheet = new Worksheet();
wSheetPart.Worksheet = worksheet;
SheetData sheetData = new SheetData();
worksheet.Append(sheetData);
excel.Close();
}

下面是 GenerateStyleSheet 方法：

public Stylesheet GenerateStyleSheet()
{
return new Stylesheet(
new DocumentFormat.OpenXml.Spreadsheet.Fonts(
new DocumentFormat.OpenXml.Spreadsheet.Font(new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),// Index 0 - The default font.
new Font(new Bold(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 1 - The bold font.
new Font(new Italic(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 2 - The Italic font.
new Font(new FontSize() { Val = 18 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 3 - The Times Roman font. with 16 size
new Font(new Bold(), new FontSize() { Val = 18 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),  // Index 4 - The Times Roman font. with 16 size
new Font(new Bold(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } }, new FontName() { Val = "Calibri" })  // Index 5 - The bold font.
),
new Fills(
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 0 - The default fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.None }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 1 - The default fill of gray 125 (required)
new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.Gray125 }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 2 - The yellow fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill(
new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }
)
{ PatternType = PatternValues.Solid }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 3 - The Blue fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill(
new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = new HexBinaryValue() { Value = "8EA9DB" } }
)
{ PatternType = PatternValues.Solid })
),
new Borders(
new Border( // Index 0 - The default border.
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
new DiagonalBorder()),
new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DiagonalBorder()),
new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.None },
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.None },
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.None },
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
new Color() { Rgb = new HexBinaryValue() { Value = "70AD47" } }
)
{ Style = BorderStyleValues.Thin },
new DiagonalBorder())
),
new CellFormats(
new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead
new CellFormat() { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 1 - Bold
new CellFormat() { FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 2 - Italic
new CellFormat() { FontId = 3, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 3 - Times Roman
new CellFormat() { FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }, // Index 4 - Yellow Fill
new CellFormat( // Index 5 - Alignment
new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
)
{ FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
new CellFormat() { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // Index 6 - Border
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) // Index 7 - Alignment
{ FontId = 1, FillId = 0, BorderId = 0, ApplyAlignment = true },
new CellFormat() { FontId = 4, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 8 - Times Roman
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) { FontId = 0, FillId = 0, BorderId = 2, ApplyFont = true }, // Index 9 - Bottom Border with Color 70AD47
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) // Index 10 - Alignment
{ FontId = 5, FillId = 3, BorderId = 0, ApplyAlignment = true }

)
); // return
}

我创建了一个添加到单元格中的方法：

public void AddToCell(SheetData sheetData, UInt32Value styleIndex, UInt32 uint32rowIndex, string strColumnName, DocumentFormat.OpenXml.EnumValue<CellValues> CellDataType, string strCellValue)
{
Row row = new Row() { RowIndex = uint32rowIndex };
Cell cell = new Cell();
cell = new Cell() { StyleIndex = styleIndex };
cell.CellReference = strColumnName + row.RowIndex.ToString();
cell.DataType = CellDataType;
cell.CellValue = new CellValue(strCellValue);
row.AppendChild(cell);
sheetData.Append(row);
}

现在只需调用它：

AddToCell(sheetData, 1, 3, "B", CellValues.String, "Model");