如何在OpenXml中设置一行(或整行)中几个单元格的背景？
阅读了几篇文章：
> Coloring cells in excel sheet using openXML in C#
> Advanced styling in Excel Open XML
我仍然无法使它工作.
我的任务实际上乍一看似乎有点容易,与这些文章中的内容略有不同.上述教程主要展示了如何创建新文档并对其进行样式化.虽然我需要改变现有的样式.
也就是说,我有一个现有的xlsx文档(报告模板).我使用必要的值填充报告(由于SO open xml excel read cell value和MSDN Working with sheets (Open XML SDK)设法完成).但接下来我需要用红色背景标记几行.
我不确定是否要使用CellStyle,也不应该使用CellFormat或其他东西……这就是我现在所做的：
SpreadsheetDocument doc = SpreadsheetDocument.Open("ole.xlsx",true);
Sheet sheet = (Sheet)doc.WorkbookPart
.Workbook
.Sheets
.FirstOrDefault();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart
.GetPartById(sheet.Id);
Worksheet worksheet = worksheetPart.Worksheet;
CellStyle cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
//where are the style values?
WorkbookStylesPart wbsp = doc.WorkbookPart
.GetPartsOfType<WorkbookStylesPart>()
.FirstOrDefault();
wbsp.Stylesheet.CellStyles.Append(cs);
wbsp.Stylesheet.Save();
Cell cell = GetCell(worksheet,"A",20);
cell.StyleIndex = 1U;//get the new cellstyle index somehow
doc.Close();
实际上,我非常欣赏一个更轻巧简单的如何设计风格的例子,例如A20或A20到J20.或者可能是一些连续教程的链接.
解决方法
最后我改变主意使用单元格背景并使用了字体.感谢foson在
SO Creating Excel document with OpenXml sdk 2.0的回答,我设法添加了一个新的Font和一个新的CellFormat,保留了原始单元格的格式(即仅改变了字体颜色)：
SpreadsheetDocument doc = SpreadsheetDocument.Open("1.xlsx",true);
Sheet sheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.FirstOrDefault();
WorksheetPart worksheetPart = (WorksheetPart)doc.WorkbookPart
.GetPartById(sheet.Id);
Worksheet worksheet = worksheetPart.Worksheet;
WorkbookStylesPart styles = doc.WorkbookPart.WorkbookStylesPart;
Stylesheet stylesheet = styles.Stylesheet;
CellFormats cellformats = stylesheet.CellFormats;
Fonts fonts = stylesheet.Fonts;
UInt32 fontIndex = fonts.Count;
UInt32 formatIndex = cellformats.Count;
Cell cell = GetCell(worksheet,19);
cell.CellValue = new CellValue(DateTime.Now.ToLongTimeString());
cell.DataType = new EnumValue<CellValues>(CellValues.String);
CellFormat f = (CellFormat)cellformats.ElementAt((int)cell.StyleIndex.Value);
var font = (Font)fonts.ElementAt((int)f.FontId.Value);
var newfont = (Font)font.Clone();
newfont.Color = new Color() { Rgb = new HexBinaryValue("ff0000") };
fonts.Append(newfont);
CellFormat newformat = (CellFormat)f.Clone();
newformat.FontId = fontIndex;
cellformats.Append(newformat);
stylesheet.Save();
cell.StyleIndex = formatIndex;
doc.Close();
