logo标签列表

使用Epplus导入包含所有条件格式规则的Excel文件

c#epplusepplus-4
3
我有一个包含大量数据的Excel文件，其中包含基于单元格值的图标集和数据条。它看起来像这样：enter image description here 我想导入这个带有条件格式的Excel表格。是否有相应的库可以实现这一功能？我查阅了此链接http://www.sitecorecleveland.com/resources/blogs-posts/easy_excel_interaction_pt6，但仅可以导入数据而无法导入格式。
如果不可能的话，epplus中是否有代码可以在Excel表格中使用这些图标集。我可以使用箭头、交通灯等，但无法使用这些图标集。
1个回答
3
我认为EPP不支持自定义条件格式，这些格式存储在Excel文件的xml中作为“Workbook Extensions”。您可以将包含自定义格式的“extLst”节点的xml从一个工作表复制到另一个工作表。请确保该节点中除了您不想复制的条件格式xml之外没有其他内容，否则您必须仅选择要复制的子节点。
为了测试，我创建了以下Excel表格（temp.xlsx），仅复制粘贴了值并保存到新文件（temp2.xlsx）：
然后运行以下命令，成功地复制了格式：
public void Custom_Condition_Copy_Test()
{
    //https://dev59.com/94fca4cB1Zd3GeqPoNM2

    //File with custom conditional formatting
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");

    //Copy of the file with the conditonal formatting removed
    var existingFile2 = new FileInfo(@"c:\temp\temp2.xlsx");

    using (var package = new ExcelPackage(existingFile))
    using (var package2 = new ExcelPackage(existingFile2))
    {
        //Make sure there are document element for the source
        var worksheet = package.Workbook.Worksheets.First();
        var xdoc = worksheet.WorksheetXml;

        if (xdoc.DocumentElement == null)
            return;

        //Make sure there are document element for the destination
        var worksheet2 = package2.Workbook.Worksheets.First();
        var xdoc2 = worksheet2.WorksheetXml;

        if (xdoc2.DocumentElement == null)
            return;

        //get the extension list node 'extLst' from the ws with the formatting 
        var extensionlistnode = xdoc
            .DocumentElement
            .GetElementsByTagName("extLst")[0];

        //Create the import node and append it to the end of the xml document
        var newnode = xdoc2.ImportNode(extensionlistnode, true);
        xdoc2.LastChild.AppendChild(newnode);

        package2.Save();

    }
}

我建议您在代码中加入一些try语句，但这应该可以让您接近目标。

更新：根据OP的评论。

如果您想添加自定义条件格式而无需原始文件，则有两个选项。

选项1，您可以使用DocumentFormat.OpenXml命名空间以更“正确”的方式进行操作。但是，这需要您拥有Office Open XML库，这可能取决于您运行此功能的环境是否容易。您可以从此处获取它，并附带一个反射工具，可以生成所需代码，从而获得以下内容：

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
using Excel = DocumentFormat.OpenXml.Office.Excel;  

......

WorksheetExtensionList worksheetExtensionList1 = new WorksheetExtensionList();

WorksheetExtension worksheetExtension1 = new WorksheetExtension(){ Uri = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}" };
worksheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");

X14.ConditionalFormattings conditionalFormattings1 = new X14.ConditionalFormattings();

X14.ConditionalFormatting conditionalFormatting1 = new X14.ConditionalFormatting();
conditionalFormatting1.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main");

X14.ConditionalFormattingRule conditionalFormattingRule1 = new X14.ConditionalFormattingRule(){ Type = ConditionalFormatValues.IconSet, Priority = 2, Id = "{CD6B2710-0474-449D-881A-22CFE15D011D}" };

X14.IconSet iconSet1 = new X14.IconSet(){ IconSetTypes = X14.IconSetTypeValues.FiveArrows, Custom = true };

X14.ConditionalFormattingValueObject conditionalFormattingValueObject1 = new X14.ConditionalFormattingValueObject(){ Type = X14.ConditionalFormattingValueObjectTypeValues.Percent };
Excel.Formula formula1 = new Excel.Formula();
formula1.Text = "0";

conditionalFormattingValueObject1.Append(formula1);

X14.ConditionalFormattingValueObject conditionalFormattingValueObject2 = new X14.ConditionalFormattingValueObject(){ Type = X14.ConditionalFormattingValueObjectTypeValues.Percent };
Excel.Formula formula2 = new Excel.Formula();
formula2.Text = "20";

conditionalFormattingValueObject2.Append(formula2);

X14.ConditionalFormattingValueObject conditionalFormattingValueObject3 = new X14.ConditionalFormattingValueObject(){ Type = X14.ConditionalFormattingValueObjectTypeValues.Percent };
Excel.Formula formula3 = new Excel.Formula();
formula3.Text = "40";

conditionalFormattingValueObject3.Append(formula3);

X14.ConditionalFormattingValueObject conditionalFormattingValueObject4 = new X14.ConditionalFormattingValueObject(){ Type = X14.ConditionalFormattingValueObjectTypeValues.Percent };
Excel.Formula formula4 = new Excel.Formula();
formula4.Text = "60";

conditionalFormattingValueObject4.Append(formula4);

X14.ConditionalFormattingValueObject conditionalFormattingValueObject5 = new X14.ConditionalFormattingValueObject(){ Type = X14.ConditionalFormattingValueObjectTypeValues.Percent };
Excel.Formula formula5 = new Excel.Formula();
formula5.Text = "80";

conditionalFormattingValueObject5.Append(formula5);
X14.ConditionalFormattingIcon conditionalFormattingIcon1 = new X14.ConditionalFormattingIcon(){ IconSet = X14.IconSetTypeValues.ThreeSymbols, IconId = (UInt32Value)0U };
X14.ConditionalFormattingIcon conditionalFormattingIcon2 = new X14.ConditionalFormattingIcon(){ IconSet = X14.IconSetTypeValues.ThreeTrafficLights1, IconId = (UInt32Value)0U };
X14.ConditionalFormattingIcon conditionalFormattingIcon3 = new X14.ConditionalFormattingIcon(){ IconSet = X14.IconSetTypeValues.ThreeTriangles, IconId = (UInt32Value)0U };
X14.ConditionalFormattingIcon conditionalFormattingIcon4 = new X14.ConditionalFormattingIcon(){ IconSet = X14.IconSetTypeValues.ThreeTriangles, IconId = (UInt32Value)1U };
X14.ConditionalFormattingIcon conditionalFormattingIcon5 = new X14.ConditionalFormattingIcon(){ IconSet = X14.IconSetTypeValues.ThreeTriangles, IconId = (UInt32Value)2U };

iconSet1.Append(conditionalFormattingValueObject1);
iconSet1.Append(conditionalFormattingValueObject2);
iconSet1.Append(conditionalFormattingValueObject3);
iconSet1.Append(conditionalFormattingValueObject4);
iconSet1.Append(conditionalFormattingValueObject5);
iconSet1.Append(conditionalFormattingIcon1);
iconSet1.Append(conditionalFormattingIcon2);
iconSet1.Append(conditionalFormattingIcon3);
iconSet1.Append(conditionalFormattingIcon4);
iconSet1.Append(conditionalFormattingIcon5);

conditionalFormattingRule1.Append(iconSet1);
Excel.ReferenceSequence referenceSequence1 = new Excel.ReferenceSequence();
referenceSequence1.Text = "A1:C201";

conditionalFormatting1.Append(conditionalFormattingRule1);
conditionalFormatting1.Append(referenceSequence1);

conditionalFormattings1.Append(conditionalFormatting1);

worksheetExtension1.Append(conditionalFormattings1);

worksheetExtensionList1.Append(worksheetExtension1);
....
worksheet1.Append(worksheetExtensionList1);

方案二是按照您的要求执行字符串操作。这种方法更简单，但它有点不太干净，因为您正在处理字符串而不是对象，但如果您只需要设置单元格范围，那么似乎并不太糟糕。我使用上面的测试方法提取了带有=extensionlistnode.OuterXml的字符串：

[TestMethod]
public void Custom_Condition_From_String_Test()
{
    //https://dev59.com/94fca4cB1Zd3GeqPoNM2

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.Add(new DataColumn("Col1", typeof(int)));
    datatable.Columns.Add(new DataColumn("Col2", typeof(int)));
    datatable.Columns.Add(new DataColumn("Col3", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row["Col1"] = i;
        row["Col2"] = i * 10;
        row["Col3"] = i * 100;
        datatable.Rows.Add(row);
    }

    //Copy of the file with the conditonal formatting removed
    var existingFile2 = new FileInfo(@"c:\temp\temp2.xlsx");
    if (existingFile2.Exists)
        existingFile2.Delete();

    using (var package2 = new ExcelPackage(existingFile2))
    {
        //Add the data
        var ws = package2.Workbook.Worksheets.Add("Content");
        ws.Cells.LoadFromDataTable(datatable, true);

        //The XML String extracted from the orginal excel doc using '= extensionlistnode.OuterXml'
        var cellrange = "A1:C201";
        var rawxml = String.Format(
            "<extLst xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><ext uri=\"{{78C0D931-6437-407d-A8EE-F0AAD7539E65}}\" xmlns:x14=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main\"><x14:conditionalFormattings><x14:conditionalFormatting xmlns:xm=\"http://schemas.microsoft.com/office/excel/2006/main\"><x14:cfRule type=\"iconSet\" priority=\"2\" id=\"{{CD6B2710-0474-449D-881A-22CFE15D011D}}\"><x14:iconSet iconSet=\"5Arrows\" custom=\"1\"><x14:cfvo type=\"percent\"><xm:f>0</xm:f></x14:cfvo><x14:cfvo type=\"percent\"><xm:f>20</xm:f></x14:cfvo><x14:cfvo type=\"percent\"><xm:f>40</xm:f></x14:cfvo><x14:cfvo type=\"percent\"><xm:f>60</xm:f></x14:cfvo><x14:cfvo type=\"percent\"><xm:f>80</xm:f></x14:cfvo><x14:cfIcon iconSet=\"3Symbols\" iconId=\"0\" /><x14:cfIcon iconSet=\"3TrafficLights1\" iconId=\"0\" /><x14:cfIcon iconSet=\"3Triangles\" iconId=\"0\" /><x14:cfIcon iconSet=\"3Triangles\" iconId=\"1\" /><x14:cfIcon iconSet=\"3Triangles\" iconId=\"2\" /></x14:iconSet></x14:cfRule><xm:sqref>{0}</xm:sqref></x14:conditionalFormatting></x14:conditionalFormattings></ext></extLst>"
            , cellrange);

        var newxdoc = new XmlDocument();
        newxdoc.LoadXml(rawxml);

        //Create the import node and append it to the end of the xml document
        var xdoc2 = ws.WorksheetXml;
        var newnode = xdoc2.ImportNode(newxdoc.FirstChild, true);
        xdoc2.LastChild.AppendChild(newnode);

        package2.Save();
    }
}
您提供的答案很好，是一个不错的起点。但是，是否可以将包含来自file1的格式的xml初始化为测试方法中的字符串，并且我们可以使用此字符串修改file2的innerxml，而无需使用file1进行操作？因为在上面的代码中，如果对excel中的c3：f4进行格式化，则它将对file2执行相同的操作。也就是说，它不会格式化f23处的数据，例如？ - sanmis
如果我理解你的问题正确的话，你想进行字符串操作，所以除了获取原始的 XML 之外，你不需要访问第一个文件吗？请参阅我上面的最后一次编辑。 - Ernie S
正是我想要的。干杯。 - sanmis
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接