xlsx - 将数据栏修改为实心填充时,如何阻止其他条件格式消失?
问题描述
EPPlus 不支持使用实心填充制作数据栏条件格式所需的extLst东西。它们本身是渐变的,无需修改。
我对此进行了编码以直接修改工作表的 xml(这从工作表 XML 获取数据栏,然后添加所需的 extLst 节点):
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>";
}
}
这确实使数据栏成为实心填充,问题是任何其他条件格式,如 GreaterThan 在 true 时都会失去它的样式。
例如,我添加了数据栏和 GreaterThan 123(绿色)条件格式。Excel 仍会看到代码格式规则 GreaterThan 123,但为 true 时未设置样式(未设置绿色)。同时正确显示数据栏。
我不知道在哪里看...有人帮忙!
解决方案
这就是黑客的问题——它们太脆弱了!:)
我能够让它与另一个 hack 一起工作 - 设置样式差异格式 (dxf) 参考,这似乎在 epplus 保存时被删除。可能发生的事情是 epplus 只认为保存时有一个 dxf,所以它没有设置值,因为 excel 会假设它是第一个 dxf 样式(索引 0),但这有点猜测。
无论如何,如果您dxfid
手动设置 via XML,它会找到它。但是这里的订单很重要,你必须最后应用数据栏黑客,否则它会打错参考:
[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();
}
}
我明白了:
不确定这对您来说有多可行,具体取决于您拥有多少条件格式,但值得一试。
推荐阅读
- html - 为什么两种字体显示相同字体大小的不同高度的文本?
- java - 使用SDK清除Azure门户中定义的容器的现有策略后,如何上传新创建的sharedAccessPolicy?
- ios - 将 segue 动画更改为“显示”,但它卡在“模态”中
- javascript - JavaScript 将前 5 个字符替换为 *
- ruby - 简单的程序,但非常卡住——Ruby 中的循环
- raspberry-pi3 - 尝试使用 DFRobot 红外摄像头寻找红外灯
- c++ - qt.bluetooth:虚拟后端运行。Qt 蓝牙模块在 Raspbian 上不起作用
- functional-programming - 函数式编程中的并行函数组合
- vue.js - 如何使用 vue svg loader 继承 svg 的 css 属性
- python - 为什么这个 de Bruijn 代码的最后几位总是返回 0