2 回答

• 3

  是的，经过大量的实验，我成功地解决了这个问题 .

  事实证明，excel保留了正常单元格的样式0和1以及"Gray125"模式填充 . 上面的大部分代码都可以删除，因为我们只需要一个 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 .

  I cannot recommend ClosedXML highly enough if you're doing this kind of work. OpenXML API和格式非常繁琐，可以单独使用各种未记录的案例 . ClosedXML为您完成了大量的工作 . 他们也非常擅长快速修复错误 .

  回复于 2024-05-14T15:03:13+08:00
• 80

  一个更通用的答案，我在测试后发现的所有这些，所以没有文档指出 .

  在样式表中设置 CellFormats 集合后，Excel会对其进行更深入的验证 .

  CellFormats 不能为空，它必须至少有一个 CellFormat .

  添加 CellFormat 后，如果 Fills ， Fonts 或 Borders 集合为空，Excel将会发出警告 .

  首先 Font 用作整个工作簿的默认值，也用作Excel中的列/行 Headers .

  Excel将首先忽略 CellFormat ，因此只需添加一个空的 .

  如果您的格式需要 Border 或 Fill ，Excel也会先忽略 Border 和 Fill ，因此在 Borders 和 Fills 中添加空的第一个孩子 .

  最后，从第二个 CellFormat （ s = "1" ）开始，你很高兴 .

  在Excel 2010中测试 .

  回复于 2024-05-14T15:03:13+08:00