How to set cell background?

Question

How to set the background of several cells inside a row (or an entire row) in OpenXml?

After reading a few articles:

• Coloring cells in an excel sheet using openXML in C #
• Advanced style in Excel Open XML

I still can't get it to work.

At first glance, my task seems to be somewhat simpler and slightly different from what is written in these articles. The textbooks mentioned predominantly show how to create a new document and create it. Although I need to change the style of the existing one.

That is, I have an existing xlsx document (report template). I fill out the report with the necessary values ​​(I managed to do this thanks to SO open xml excel read cell value and MSDN Working with Sheets (Open XML SDK) ). But then I need to mark a few lines, for example, with a red background.

I'm not sure whether to use CellStyle , and if I have to use CellFormat or something else ... This is what I got so far:

 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(); 

In fact, I would really appreciate an easier and simpler example of how to style, say, an A20 cell, or vary from A20 to J20 . Or perhaps a link to another sequential tutorial.

Answer 1

In the end, I changed my mind about using cell background and fonts used. Thanks to foson's answer in SO Creating an Excel document using OpenXml sdk 2.0, I managed to add a new Font and a new CellFormat , preserving the original cell formatting (i.e. changing only the font color):

 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, "A", 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();