问题描述
我试图在EPPlus 4.0.4中创建可靠的数据栏，并遇到两个问题。
I am trying to create solid databars in EPPlus 4.0.4, and am running into two problems.
- 首先，我还没有
- 第二，至少对于较小的值，条形图没有按照我期望的方式显示。 >
- First, I haven't been able to figure out how to create a solid fill color.
- Second, at least for small values, the bars aren't showing up the way I expect them to.
以下屏幕截图说明了这两个问题。在这两种情况下，期望的结果都是我在Excel中手动添加的数据栏的结果：
The screenshot below illustrates both issues. In both cases, the desired outcome is that of the databar I've added manually in Excel:
这是我当前正在使用的代码：
This is the code I'm currently using:
var bars = doc.ConditionalFormatting.AddDatabar(range, Color.FromArgb(99,195,132));
bars.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
bars.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
bars.HighValue.Value = numResponses; //82
bars.LowValue.Value = 0;
对于纯色，我一直在尝试针对<$的不同属性更改值c $ c> bars.Style.Fill ，无济于事。如果实现了这一点，那很简单，就是我找不到合适的财产。
For the solid color, I've been trying out variations of values for the different properties of bars.Style.Fill, to no avail. If this is implemented, it is a simple matter of me not finding the right property.
我很难理解第二个问题。如果我在Excel中进入管理规则，则正确设置了高值和低值，但是我发现没有值可以更改为使它们的外观与手动创建的条形相匹配。
I'm having a harder time understanding the second issue. If I go into "Manage rule" in Excel, the high and low values are properly set, and I have found no value I can change them to that will make their appearance match that of the manually created bars.
推荐答案
这是扩展列表问题。当进入更复杂的出口时，这很多。条件格式可能是更困难的一种，因为细微差别太多，而且多年来变化很大。
This is an extension list problem. This comes up alot when getting into more complex exports. Conditional formatting is probably one of the tougher ones because there are so many nuances and it has changed so much over the years.
扩展名列表（ extLst xml中的标记）是一种包罗万象的存储桶， OpenOfficeXml 标准可用于添加新功能和格式。在您的情况下，Excel将填充扩展名列表部分，以允许扩展的最小/最大限制。 Epplus不支持此功能，这就是为什么您会看到差异。
Extension list (extLst tags in xml) is kind of a catchall bucket that the OpenOfficeXml standard can use to added new features and formatting. In your case Excel populates the extension list section to allow for the extended min/max limit. Epplus does not support this which is why you see the difference.
您最简单的选择只是通过xml / string操作自己注入它，虽然很漂亮，但它能胜任完成：
Your simplest option would be just to inject it yourself via xml/string manipulation Not pretty but it gets the job done:
var bars = doc.ConditionalFormatting.AddDatabar(range, Color.FromArgb(99, 195, 132));
bars.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
bars.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
bars.HighValue.Value = numResponses; //82
bars.LowValue.Value = 0;
//Get reference to the worksheet xml for proper namespace
var xdoc = doc.WorksheetXml;
var nsm = new XmlNamespaceManager(xdoc.NameTable);
nsm.AddNamespace("default", xdoc.DocumentElement.NamespaceURI);
//Create the conditional format extension list entry
var extLstCf = xdoc.CreateNode(XmlNodeType.Element, "extLst", xdoc.DocumentElement.NamespaceURI);
extLstCf.InnerXml = @"<ext uri=""{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main""><x14:id>{3F3F0E19-800E-4C9F-9CAF-1E3CE014ED86}</x14:id></ext>";
var cfNode = xdoc.SelectSingleNode("/default:worksheet/default:conditionalFormatting/default:cfRule", nsm);
cfNode.AppendChild(extLstCf);
//Create the extension list content for the worksheet
var extLstWs = xdoc.CreateNode(XmlNodeType.Element, "extLst", xdoc.DocumentElement.NamespaceURI);
extLstWs.InnerXml = @"<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=""dataBar"" id=""{3F3F0E19-800E-4C9F-9CAF-1E3CE014ED86}""><x14:dataBar minLength=""0"" maxLength=""100"" gradient=""0""><x14:cfvo type=""num""><xm:f>0</xm:f></x14:cfvo><x14:cfvo type=""num""><xm:f>82</xm:f></x14:cfvo><x14:negativeFillColor rgb=""FFFF0000""/><x14:axisColor rgb=""FF000000""/></x14:dataBar></x14:cfRule><xm:sqref>B2:B11</xm:sqref></x14:conditionalFormatting></x14:conditionalFormattings></ext>";
var wsNode = xdoc.SelectSingleNode("/default:worksheet", nsm);
wsNode.AppendChild(extLstWs);
pck.Save();
请注意 gradient = 0 会将颜色条设置为纯色而不是渐变以及最小/最大设置，以获取所需的价差。
Note the gradient=""0"" which will set the color bars to solid instead of a gradient as well as the min/max settings to get the spread you are looking for.
更合适一种方法是将逐个节点重新创建xml对象，逐个属性重新创建xml对象，这将花费一些时间，但只需执行一次即可。
A more "proper" way would be to would to recreate the xml objects node by node and attribute by attribute which will take a while but only have to do it once.
这篇关于固态数据栏的手动和编码版本与数据栏最小值之间的外观不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！