将数据条修改为实心填充时，如何阻止其他条件格式消失？ [英] How to stop other conditional formatting from disappearing when hackmodding databars into solid fills?
问题描述
EPPlus不支持 extLst 东西，而数据栏需要使用实心填充进行条件格式设置。它们本身是渐变的，无需修改。
EPPlus has no support for extLst thing which is needed to make databars conditional formatting with solid fill. They are gradient by themselves without modifications.
我编写了此代码，以直接修改工作表的xml（这是从工作表XML中获取数据条，然后添加所需的extLst节点）：
I coded this to modify worksheet's xml directly (this gets databars from worksheet XML and then adds required extLst nodes):
public static Random Rnd = new Random();
public static string GenerateXlsId()
{
//{29BD882A-B741-482B-9067-72CC5D939236}
string id = string.Empty;
for (int i = 0; i < 32; i++)
if (Rnd.NextDouble() < 0.5)
id += Rnd.Next(0, 10);
else
id += (char)Rnd.Next(65, 91);
id = id.Insert(8, "-");
id = id.Insert(13, "-");
id = id.Insert(18, "-");
id = id.Insert(23, "-");
return id;
}
public static void FixDatabarsAtWorksheet(OfficeOpenXml.ExcelWorksheet eworksheet)
{
System.Xml.XmlNodeList databars = eworksheet.WorksheetXml.GetElementsByTagName("dataBar");
if (databars.Count > 0)
{
string conditional_formattings_str = string.Empty;
for (int i = 0; i < databars.Count; i++)
{
string temp_databar_id = GenerateXlsId();
databars[i].ParentNode.InnerXml += @"<extLst>
<ext uri=""{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"">
<x14:id>{" + temp_databar_id + @"}</x14:id>
</ext>
</extLst>";
//--
string temp_sqref = databars[i].ParentNode.ParentNode.Attributes["sqref"].Value;
string left_type = string.Empty;
string left_val = string.Empty;
string right_type = string.Empty;
string right_val = string.Empty;
string color = string.Empty;
Color databar_fill_color = Color.Empty;
Color databar_border_color = Color.Empty;
for (int j = 0; j < databars[i].ChildNodes.Count; j++)
if (databars[i].ChildNodes[j].LocalName == "cfvo" && databars[i].ChildNodes[j].Attributes["type"] != null)
{
if (string.IsNullOrEmpty(left_type))
left_type = databars[i].ChildNodes[j].Attributes["type"].Value;
else if (string.IsNullOrEmpty(right_type))
right_type = databars[i].ChildNodes[j].Attributes["type"].Value;
if (databars[i].ChildNodes[j].Attributes["val"] != null)
if (string.IsNullOrEmpty(left_val))
left_val = databars[i].ChildNodes[j].Attributes["val"].Value;
else if (string.IsNullOrEmpty(right_val))
right_val = databars[i].ChildNodes[j].Attributes["val"].Value;
}
else if (databars[i].ChildNodes[j].LocalName == "color")
{
color = databars[i].ChildNodes[j].Attributes["rgb"].Value;
int argb = Int32.Parse(color, System.Globalization.NumberStyles.HexNumber);
databar_fill_color = Color.FromArgb(argb);
databar_border_color = Color.FromArgb(255,
databar_fill_color.R - 50 < 0 ? databar_fill_color.R + 50 : databar_fill_color.R - 50,
databar_fill_color.G - 50 < 0 ? databar_fill_color.R + 50 : databar_fill_color.G - 50,
databar_fill_color.B - 50 < 0 ? databar_fill_color.R + 50 : databar_fill_color.B - 50);
}
string temp_conditional_formatting_template = @"<x14:conditionalFormatting xmlns:xm=""http://schemas.microsoft.com/office/excel/2006/main"">
<x14:cfRule type=""dataBar"" id=""{" + temp_databar_id + @"}"">
<x14:dataBar minLength=""" + (string.IsNullOrEmpty(left_val) ? "0" : left_val) + "\" maxLength=\"" + (string.IsNullOrEmpty(right_val) ? "100" : right_val) + "\" gradient=\"0\" " + (databar_border_color.IsEmpty ? string.Empty : "border = \"1\"") + ">";
temp_conditional_formatting_template += Environment.NewLine + "<x14:cfvo type=\"" + (left_type.ToLower() == "min" ? "autoMin" : left_type) + "\" />";
temp_conditional_formatting_template += Environment.NewLine + "<x14:cfvo type=\"" + (right_type.ToLower() == "max" ? "autoMax" : right_type) + "\" />";
if (!databar_border_color.IsEmpty)
temp_conditional_formatting_template += Environment.NewLine + "<x14:borderColor rgb=\"" + BitConverter.ToString(new byte[] { databar_border_color.A, databar_border_color.R, databar_border_color.G, databar_border_color.B }).Replace("-", "") + "\" />";
temp_conditional_formatting_template += Environment.NewLine + @"</x14:dataBar>
</x14:cfRule>
<xm:sqref>" + temp_sqref + @"</xm:sqref>
</x14:conditionalFormatting>";
conditional_formattings_str += temp_conditional_formatting_template;
}
databars[0].ParentNode.ParentNode.ParentNode.InnerXml += @"<extLst>
<ext uri=""{78C0D931-6437-407d-A8EE-F0AAD7539E65}"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"">
<x14:conditionalFormattings>" + conditional_formattings_str + @"
</x14:conditionalFormattings>
</ext>
</extLst>";
}
}
这确实使数据栏变得可靠，问题是
And this really makes databars solid fill, the problem is any other conditional formatting like GreaterThan loses it's style when true.
例如，我添加了databar和GreaterThan 123（绿色）条件格式。
Excel仍然会看到标准格式规则大于123，但是样式为true时，则样式未设置（未设置绿色）。虽然同时正确显示了数据栏。
For example I add databar and GreaterThan 123 (green) conditional formattings. Excel still see coditional formatting rule GreaterThan 123, but the style is not set when it's true (green is not set). While databars is displayed correctly at same time.
我不知道在哪里看……有人帮助！
I don't know where to look... Someone help!
推荐答案
这就是骇客的问题-它们是如此脆弱！ ：）
Thats the problem with hacks - they are so fragile! :)
我能够使它与另一个技巧一起工作-设置样式差异格式（dxf）参考，当epplus保存时，该参考似乎已被删除。可能发生的情况是epplus仅认为保存时有一个dxf，所以它没有设置值，因为excel会假定它是第一个dxf样式（索引0），但这有点猜测。
I was able to get it work with another hack - setting the style differential formatting (dxf) reference which seems to be dropped when epplus saves. What might be happening is epplus only thinks there is one dxf on save so it doesnt set the value since excel will assume it is the first dxf style (index 0) but that is a bit of a guess.
无论如何，如果通过XML手动设置 dxfid ，它将找到它。但是这里的订单很重要，您必须最后应用数据栏技巧，否则它将击错引用：
Anyway, if you set the dxfid via XML manually it will find it. But order counts here, you have to apply the databar hack last otherwise it will hit the wrong reference:
[TestMethod]
public void FixDatabarsAtWorksheetTest()
{
//https://stackoverflow.com/questions/58417819/how-to-stop-other-conditional-formatting-from-disappearing-when-hackmodding-data
//Throw in some data
var datatable = new DataTable("tblData");
datatable.Columns.AddRange(new[]
{
new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object))
});
for (var i = 0; i < 10; i++)
{
var row = datatable.NewRow();
row[0] = i;
row[1] = i * 10;
row[2] = Path.GetRandomFileName();
datatable.Rows.Add(row);
}
//Create a test file
var fi = new FileInfo(@"c:\temp\FixDatabarsAtWorksheetTest.xlsx");
if (fi.Exists)
fi.Delete();
using (var pck = new ExcelPackage(fi))
{
var workbook = pck.Workbook;
var doc = workbook.Worksheets.Add("Sheet1");
doc.Cells.LoadFromDataTable(datatable, true);
//Set the greater than
var gtConditional = doc
.ConditionalFormatting
.AddGreaterThan(doc.Cells["A2:A11"]);
gtConditional.Formula = "2";
gtConditional.Style.Fill.BackgroundColor.Color = Color.GreenYellow;
//Fix the gt
var xdoc = doc.WorksheetXml;
var nsm = new XmlNamespaceManager(xdoc.NameTable);
nsm.AddNamespace("default", xdoc.DocumentElement.NamespaceURI);
var gtNode = xdoc.SelectSingleNode("/default:worksheet/default:conditionalFormatting[@sqref=\"A2:A11\"]", nsm);
//Create the new attribute for table
var att = xdoc.CreateAttribute("dxfId");
att.Value = "0";
gtNode
.FirstChild
.Attributes.Append(att);
//Set the bar condition LAST
var barConditional = doc
.ConditionalFormatting
.AddDatabar(doc.Cells["B2:B11"], Color.FromArgb(99, 195, 132));
barConditional.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
barConditional.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
barConditional.HighValue.Value = 82;
barConditional.LowValue.Value = 0;
FixDatabarsAtWorksheet(doc);
pck.Save();
}
}
我明白了：
不确定您的条件格式取决于您多少条件但是值得一试。
Not sure how feasible this is for you depending on how many conditional formats you have but its worth a shot.
这篇关于将数据条修改为实心填充时，如何阻止其他条件格式消失？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
