添加条件格式OpenXML C# [英] Add Conditional Formatting OpenXML C#

问题描述

如何使用C#.Net在OpenXML中添加条件格式.我希望满足以下条件:

How can I add conditional formatting in OpenXML using C# .Net. I would like the following conditions applied:

= INDIRECT("D"&(ROW())=已拒登"，则该规则应适用于:= $ 1:$ 3，$ N $ 4:$ XFD $ 4，$ 5:$ 1048576

=INDIRECT("D"&ROW())="Disapproved" then the rule should apply to: =$1:$3,$N$4:$XFD$4,$5:$1048576

我的功能设置如下:

using (SpreadsheetDocument document = SpreadsheetDocument.Open(openFileDialog1.FileName, true))
{
   // apply conditions here
}

推荐答案

要添加条件格式，您需要添加 ConditionalFormattingRule 定义要使用的规则，而规则又通过

To add conditional formats, you need to add a ConditionalFormatting instance to the Worksheet. This object will hold the list of references that the conditional format should be applied to. The ConditionalFormatting instance needs a ConditionalFormattingRule to define the rule to be used which in turn is defined via a Formula.

为了使条件格式对电子表格有效，您还需要定义要使用的样式.这需要添加到 DifferentialFormat ，它又被添加到

In order for the conditional format to have an effect on the spreadsheet you'll also need to define a style to be used. This needs to be added to a DifferentialFormat, which in turn is added to a DifferentialFormats.

以下代码将获取一个现有文档并添加您要使用的条件格式:

The following code will take an existing document and add the conditional format that you're after:

using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, true))
{
    WorkbookPart workbookPart = document.WorkbookPart;
    //get the correct sheet
    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
    WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;
    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

    //grab the stylesPart so we can add the style to apply (create one if one doesn't already exist)
    WorkbookStylesPart stylesPart = document.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().FirstOrDefault();
    if (stylesPart == null)
    {
        stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
        stylesPart.Stylesheet = new Stylesheet();
    }

    //create a fills object to hold the background colour we're going to apply
    Fills fills = new Fills() { Count = 1U };

    //grab the differential formats part so we can add the style to apply (create one if one doesn't already exist)
    bool addDifferentialFormats = false;
    DifferentialFormats differentialFormats = stylesPart.Stylesheet.GetFirstChild<DifferentialFormats>();
    if (differentialFormats == null)
    {
        differentialFormats = new DifferentialFormats() { Count = 1U };
        addDifferentialFormats = true;
    }

    //create the conditional format reference
    ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
    {
        SequenceOfReferences = new ListValue<StringValue>()
        {
            InnerText = "A1:XFD3 N4:XFD4 A5:XFD1048576"
        }
    };

    //create a style to assign to the conditional format
    DifferentialFormat differentialFormat = new DifferentialFormat();
    Fill fill = new Fill();
    PatternFill patternFill = new PatternFill();
    BackgroundColor backgroundColor = new BackgroundColor() { Rgb = new HexBinaryValue() { Value = "0000ff00" } };
    patternFill.Append(backgroundColor);
    fill.Append(patternFill);
    differentialFormat.Append(fill);
    differentialFormats.Append(differentialFormat);

    //create the formula
    Formula formula1 = new Formula();
    formula1.Text = "INDIRECT(\"D\"&ROW())=\"Disapproved\"";

    //create a new conditional formatting rule with a type of Expression
    ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
    {
        Type = ConditionalFormatValues.Expression,
        FormatId = 0U,
        Priority = 1
    };

    //append the formula to the rule
    conditionalFormattingRule.Append(formula1);

    //append th formatting rule to the formatting collection
    conditionalFormatting.Append(conditionalFormattingRule);

    //add the formatting collection to the worksheet
    //note the ordering is important; there are other elements that should be checked for here really.
    //See the spec for all of them and see https://stackoverflow.com/questions/25398450/why-appending-autofilter-corrupts-my-excel-file-in-this-example/25410242#25410242
    //for more details on ordering
    PageMargins margins = worksheetPart.Worksheet.GetFirstChild<PageMargins>();
    if (margins != null)
        worksheetPart.Worksheet.InsertBefore(conditionalFormatting, margins);
    else
        worksheetPart.Worksheet.Append(conditionalFormatting);

    //add the differential formats to the stylesheet if it didn't already exist
    if (addDifferentialFormats)
        stylesPart.Stylesheet.Append(differentialFormats);
}

请注意，OpenXml对元素的顺序很挑剔，因此(特别是在处理现有文档时)，您需要确保将元素添加到树中的正确位置.

Note that OpenXml is fussy about the order of elements so (when working with an existing document particularly) you need to ensure you are adding elements in the correct place within the tree.

这篇关于添加条件格式OpenXML C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！