Office2010.Excel - ConditionalFormatting example

[gergelyurbancsik]

Describe the API or scenario A clear and concise description of the scenario you'd like documented: I would like to apply conditional formatting to the 'C' column in an Excel sheet using the ConditionalFormatting class from the DocumentFormat.OpenXml.Office2010.Excel namespace. The conditional formatting should display icons based on the cell values as follows: • A green check mark for a value of 1 • A red X for values greater than 1 • No icon for a value of 0 Attempts Please include any attempts you have made to figure this out: I have attempted to implement this using the Open-XML-SDK, but I am having trouble with the correct setup of the ConditionalFormatting and ConditionalFormattingRule classes.

Related scenarios Any related documentation/samples/StackOverflow posts you have found: I have not found any specific examples or documentation that cover this exact scenario. Most examples I found are for basic conditional formatting without custom icons. Additional context Add any other context about the problem here: I am using the Open-XML-SDK to generate Excel files programmatically. Detailed documentation or a sample code snippet demonstrating the correct usage of ConditionalFormatting with custom icons would be very helpful.

the expected result, something like this:

<x14:conditionalFormattings xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
		<x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
			<x14:cfRule type="iconSet" priority="1" id="{39D2A77C-A416-446B-91A5-8D3E3C96830E}">
				<x14:iconSet iconSet="3Symbols" showValue="0" custom="1">
					<x14:cfvo type="percent">
						<xm:f>0</xm:f>
					</x14:cfvo>
					<x14:cfvo type="num" gte="0">
						<xm:f>0</xm:f>
					</x14:cfvo>
					<x14:cfvo type="num" gte="0">
						<xm:f>1</xm:f>
					</x14:cfvo>
					<x14:cfIcon iconSet="NoIcons" iconId="0"/>
					<x14:cfIcon iconSet="3Symbols" iconId="2"/>
					<x14:cfIcon iconSet="3Symbols" iconId="0"/>
				</x14:iconSet>
			</x14:cfRule>
			<xm:sqref>C1:C1048576</xm:sqref>
		</x14:conditionalFormatting>
	</x14:conditionalFormattings>

[mikeebowen]

Hi @gergelyurbancsik, can you share the code from what you've already tried?

[gergelyurbancsik]

Hi @mikeebowen Some code snippet available here: https://github.com/dotnet/Open-XML-SDK/issues/1783

[mikeebowen]

Thanks @gergelyurbancsik, The below code example will add the conditional formatting the way you describe. You will have to adjust the values in the ReferenceSequence instance to reflect the cells you want to style

using DocumentFormat.OpenXml.Office.Excel;
using DocumentFormat.OpenXml.Office2010.Excel;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Xl2010 = DocumentFormat.OpenXml.Office2010.Excel;
using Xl = DocumentFormat.OpenXml.Office.Excel;
using S = DocumentFormat.OpenXml.Spreadsheet;

string xlsxPath = @"C:\path\to\a\file.xlsx";

using (var spreadsheetDocument = SpreadsheetDocument.Open(xlsxPath, true))
{
    var worksheetParts = spreadsheetDocument.WorkbookPart?.WorksheetParts;

    if (worksheetParts is not null && worksheetParts.Count() > 0)
    {
        var worksheetPart = worksheetParts.First();
        var worksheet = worksheetPart?.Worksheet;

        if (worksheet is not null)
        {
            worksheet.AppendChild(new S.ExtensionList(new Extension(
                new ConditionalFormattings(new Xl2010.ConditionalFormatting(
                new Xl2010.ConditionalFormattingRule(
                    new Xl2010.IconSet(
                        new ConditionalFormattingValueObject(
                            new Xl.Formula("0"))
                        { Type = ConditionalFormattingValueObjectTypeValues.Numeric },
                        new ConditionalFormattingValueObject(
                            new Xl.Formula("1"))
                        { Type = ConditionalFormattingValueObjectTypeValues.Numeric },
                        new ConditionalFormattingValueObject(
                            new Xl.Formula("2"))
                        { Type = ConditionalFormattingValueObjectTypeValues.Numeric },
                        new ConditionalFormattingIcon() { IconSet = IconSetTypeValues.NoIcons, IconId = 0 },
                        new ConditionalFormattingIcon() { IconSet = IconSetTypeValues.ThreeSymbols2, IconId = 2 },
                        new ConditionalFormattingIcon() { IconSet = IconSetTypeValues.ThreeSymbols2, IconId = 0 })
                    { IconSetTypes = IconSetTypeValues.ThreeSymbols2, Custom = true })
                { Priority = 1, Type = ConditionalFormatValues.IconSet },
                new ReferenceSequence("A1:A3"))))
            { Uri = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}" }));
        }
    }
}