How to set cells' background?

Question

How to set the background of several cells within a row (or of a whole row) in OpenXml?

Having read several articles:

1. Coloring cells in excel sheet using openXML in C#
2. Advanced styling in Excel Open XML

I still cannot make it work.

My task is actually at first glance seems to be somewhat easier and a little bit different from what is written in those articles. The mentioned tutorials predominantly show how to create a new document and style it. While I need to change the styling of the existing one.

That is, I have an existing xlsx document (a report template). I populate the report with the necessary values (managed to do it thanks to SO open xml excel read cell value and MSDN Working with sheets (Open XML SDK)). But next I need to mark several rows with, say, red background.

I am neither sure whether to use CellStyle nor if I should use CellFormat or something else...This is what I have got up to now:

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

Actually I would greatly appreciate a more light-weight and easy example of how to style, say, cell A20 or range from A20 to J20. Or probably a link to some more consecutive tutorial.

Answer 1

cSimple solution: Try this: (it works with the nuget package ClosedXML v 0.95.4)

using ClosedXML.Excel;

XLWorkbook wb = new XLWorkbook();

IXLWorksheet ws = wb.Worksheets.Add("Test Background Color");

ws.Cell("A1").Style.Fill.BackgroundColor = XLColor.LightBlue;

ws.Cell("A1").Value = "This cell should have light blue background";

wb.SaveAs(@"c:\Test\test.xlsx");

Answer 2

You have 3 options:

1. Use MS lib ExcelDataReader which requires your server installing Office and usually does not work if your program is running in IIS.

2. Use closed source libs.

3. Use OpenXML.

Try my code using pure OpenXML: https://stackoverflow.com/a/59806422/6782249

Answer 3

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

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