How to set background color in excel cell using c# openxml

by

How to Set Background Color in Excel Cell using C# OpenXML

To set the background color of an Excel cell using C# OpenXML, you need to follow the steps below:

  1. Load the existing Excel file using the OpenXML SDK.
  2. Select the worksheet where you want to set the background color.
  3. Select the cell or range of cells you want to format.
  4. Create a new Fill object and set its properties.
  5. Assign the Fill object to the corresponding cell or cells.
  6. Save the modified Excel file.

Example:


using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public void SetCellBackgroundColor(string filePath, string sheetName, string cellReference, string color)
{
  using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(filePath, true))
  {
    WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.GetPartByName(sheetName) as WorksheetPart;
    Worksheet worksheet = worksheetPart.Worksheet;
    
    Cell cell = worksheet.GetCell(cellReference);
    
    // Create a new Fill object and set its properties
    Fill fill = new Fill();
    PatternFill patternFill = new PatternFill() { PatternType = PatternValues.Solid };
    ForegroundColor foregroundColor = new ForegroundColor() { Rgb = new HexBinaryValue() { Value = color } };
    
    patternFill.Append(foregroundColor);
    fill.Append(patternFill);
    
    // Assign the Fill object to the cell
    cell.StyleIndex = InsertOpenXmlStyleElement(workbookPart.WorkbookStylesPart.Stylesheet, fill);
    
    // Save the modified Excel file
    worksheetPart.Worksheet.Save();
    workbookPart.Workbook.Save();
  }
}

private uint InsertOpenXmlStyleElement(Stylesheet stylesheet, OpenXmlElement element)
{
  stylesheet.Append(element);
  stylesheet.Count = (uint)stylesheet.ChildElements.Count;
  stylesheet.UniqueCount = (uint)stylesheet.ChildElements.Count;
  
  return (uint)stylesheet.ChildElements.Count - 1;
}

To use the above code, you need to pass the following parameters:
- filePath: The path to the Excel file.
- sheetName: The name of the worksheet.
- cellReference: The reference of the cell(s) to be formatted (e.g., "A1" or "A1:B5").
- color: The RGB color value to be used as the background color (e.g., "FF0000" for red).

This code will open the specified Excel file, select the desired worksheet, and set the background color of the specified cell or range of cells. After making the necessary modifications, it will save the modified Excel file.

Note: Make sure to add the required namespaces and reference the necessary assemblies for the OpenXML SDK. Additionally, ensure that the Excel file you are trying to modify is in the correct format compatible with the OpenXML specifications.

Leave a comment