CODEWITHSUNDEEP
c#excelopenxml
Toni
Toni

Reputation: 57

Changing background colour of a cell in excel

I need to change the background colour of every cell with text "Change". Any ideas how I can achieve that using OpenXML? I have achieved only for changing for one specific cell(columnName + rowIndex), but not for all cells which value is "Change".

Is it doable with OpenXML or I need to use a different approach?

Upvotes: 1

Views: 3652

Answers (2)

petelids
petelids

Reputation: 12815

You can create conditional formats using the ConditionalFormatting class, adding ConditionalFormattingRules for each rule you wish to match.

The format to be applied needs to be defined in a DifferentialFormat which needs to be added to the DifferentialFormats collection.

The following code will create a new spreadsheet with the conditional format of a red background if the cell contains "Changes". It also populates cells A1:J20 with either "Changes" or "a" to show that the conditional format is working.

public static void CreateConditionalWorkbook(string filepath)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart workbookPart = document.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet();

        Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

        Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet" };
        sheets.Append(sheet);

        workbookPart.Workbook.Save();

        var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

        WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
        stylesPart.Stylesheet = new Stylesheet();

        Fills fills = new Fills() { Count = 1U };

        DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)1U };

        ConditionalFormatting conditionalFormatting = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1:XFD1048576" } };

        DifferentialFormat differentialFormat = new DifferentialFormat();
        Fill fill = new Fill();
        PatternFill patternFill = new PatternFill();
        BackgroundColor backgroundColor = new BackgroundColor() { Rgb = new HexBinaryValue() { Value = "ff0000" } };
        patternFill.Append(backgroundColor);
        fill.Append(patternFill);
        differentialFormat.Append(fill);
        differentialFormats.Append(differentialFormat);

        Formula formula1 = new Formula();
        formula1.Text = "\"Change\"";

        ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
        {
            Type = ConditionalFormatValues.CellIs,
            FormatId = 0U,
            Priority = 1,
            Operator = ConditionalFormattingOperatorValues.Equal
        };

        conditionalFormattingRule.Append(formula1);

        conditionalFormatting.Append(conditionalFormattingRule);

        worksheetPart.Worksheet.Append(conditionalFormatting);
        stylesPart.Stylesheet.Append(differentialFormats);

        Random r = new Random();
        for (uint rowId = 1; rowId <= 20; rowId++)
        {
            Row row = new Row() { RowIndex = rowId };

            for (int cellId = 0; cellId < 10; cellId++)
            {
                Cell cell = new Cell();
                cell.CellReference = string.Format("{0}{1}", (char)(65 + cellId), rowId);
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(r.Next(2) % 2 == 0 ? "a" : "Change");
                row.Append(cell);
            }

            sheetData.Append(row);
        }

        workbookPart.Workbook.Save();

        document.Close();
    }
}

An example output after running the above is:Example output

Upvotes: 2

dwirony
dwirony

Reputation: 5450

Just use conditional formatting. Go to Conditional Formatting, New Rule, Format only cells that contain, change "Cell Value" to "Specific Text", enter the word "Change". Format the fill color to your choosings, hit apply, OK.

Upvotes: -1

Related Questions