Excel Conditional Formatting - How do I apply the conditions that are providing the fill color?

[DonFrazier]

I have a large Excel workbook with multiple sheets. We are trying to save the workbook with the resolved values and removing the formulas. Part of the resolution includes conditionally formatting the fill color of some cells. I'm trying to find a way to get the fill color that the Excel app would use to render the cell in a window. This process is running on hundreds of sheets with no UI though.

The desired end result is a set of sheets that have no formulas, no conditional formatting, etc. but the result of calculating the value of each cell and applying the conditional style. I've gotten the value but haven't had any luck following the model to find the fill color. Any suggestions? Links, snippets, etc. all welcome.

[mikeebowen]

Hi @DonFrazier ,

Here is a link to the OOXML SDK documentation for conditional formatting: https://docs.microsoft.com/en-us/office/open-xml/working-with-conditional-formatting.

There is also the openxml-sdk tag on stackoverflow, which is also a good place to post questions about the SDK: https://stackoverflow.com/questions/tagged/openxml-sdk?tab=Newest

I will also try to find some sample code for you.

[DonFrazier]

Thanks. I found ConditionalFormatValueObject which looks interesting. I'll try posting on SO but find it is deteriorating in quality and increasing in bickering and petty territorial / legalistic fiefdoms. If I can find some free time I'll probably poke through the repo and hope to find a unit test with some hints or the answer.

[mikeebowen]

This isn't totally complete, but it shows you how to get the cell id and find the differential styles that should be applied to them:

using SpreadsheetDocument spd = SpreadsheetDocument.Open(path, true);

WorkbookPart? wbp = spd.WorkbookPart;

IEnumerable<ExternalWorkbookPart>? ewbp = wbp?.GetPartsOfType<ExternalWorkbookPart>();

IEnumerable<WorksheetPart>? worksheetParts = wbp?.WorksheetParts;

if (worksheetParts is not null)
{
    foreach (WorksheetPart worksheetPart in worksheetParts)
    {
        IEnumerable<OpenXmlElement> conditionalFormatting = worksheetPart.Worksheet.ChildElements.Where(w => w is ConditionalFormatting);

        foreach (ConditionalFormatting cf in conditionalFormatting)
        {
            // Get the cell/cell range to be styled
            string? cellLocation = cf.GetAttribute("sqref", "").Value;

            IEnumerable<OpenXmlElement> conditionalFormattingRules = cf.ChildElements.Where(r => r is ConditionalFormattingRule);

            foreach (ConditionalFormattingRule cfr in conditionalFormattingRules)
            {
                // determine what operation is applied
                EnumValue<ConditionalFormattingOperatorValues>? oper = cfr.Operator;
                // get the index of the DifferentialFormat to apply
                UInt32Value? dxfId = cfr.FormatId;
                // the formula to calucate and use the operator on
                OpenXmlElement? formula = cfr.ChildElements.Where(c => c is Formula).FirstOrDefault();

                OpenXmlElement? differentialFormats = wbp?.WorkbookStylesPart?.Stylesheet.ChildElements.Where(s => s is DifferentialFormats).FirstOrDefault();

                if (differentialFormats is not null)
                {
                    DifferentialFormats? dxfs = differentialFormats as DifferentialFormats;

                    if (dxfs is not null)
                    {
                        foreach (var (df, i) in dxfs.Select((df, i) => (df, i)))
                        {
                            // if the index matches the dxfId
                            if (dxfId is not null && i == dxfId)
                            {
                                IEnumerable<OpenXmlElement> elems = df.Elements();
                                foreach (OpenXmlElement elem in elems)
                                {
                                    // Each elem is a formatting option font, fill etc.
                                    // 
                                }

                                break;
                            }
                        }
                    }
                }

            }
        }
    }
}

[DonFrazier]

Wow! I'll give this a try. My thanks may be premature but thanks for a comprehensive snippet.

[DonFrazier]

Your code works fine but I believe I am the latest one (of many) to finally stumble across these statements in the OpenXml docs:

You do not have to specify the value, and if you omit it, it is the responsibility of the Open XML reader to compute the value based on the formula definition when the worksheet is opened.

A SpreadsheetML formula is the syntactic representation of a series of calculations that is parsed or interpreted by the spreadsheet applicationinto a function that calculates a value or array of values based upon zero-to-many inputs.

Based on this I think my goal is not practical without implementing far more parsing and custom coding that we are looking to do.

We will ask that Excel input be saved without conditional styles and with formula values calculated on save. The conditional formats we are trying to accommodate are fairly simple so we may consider just adding some hard-coded ones into the destination sheet and ask our partners to work within that limitation.

Thanks for the time. It has been an interesting weekend.