public void FontNotBoldAndNotItalic() { var nodeXml = "<dxf xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" + "<font>" + "<b val=\"0\"/>" + "<i val=\"0\"/>" + "<color theme=\"0\"/>" + "</font>" + "<fill>" + "<patternFill>" + "<bgColor rgb=\"FF002060\"/>" + "</patternFill>" + "</fill>" + "</dxf>"; using (var excelPackage = new ExcelPackage()) { var document = new XmlDocument(); document.LoadXml(nodeXml); XmlNode dxfNode = document.DocumentElement; var namespaceManager = new XmlNamespaceManager(document.NameTable); var excelDxfStyle = new ExcelDxfStyleConditionalFormatting(excelPackage.Workbook.NameSpaceManager, dxfNode, excelPackage.Workbook.Styles); Assert.IsFalse(excelDxfStyle.Font.Bold.Value); Assert.IsFalse(excelDxfStyle.Font.Italic.Value); } }
/****************************************************************************************/ /// <summary> /// Initialize the <see cref="ExcelConditionalFormattingRule"/> /// </summary> /// <param name="type"></param> /// <param name="address"></param> /// <param name="priority">Used also as the cfRule unique key</param> /// <param name="worksheet"></param> /// <param name="itemElementNode"></param> /// <param name="namespaceManager"></param> internal ExcelConditionalFormattingRule( eExcelConditionalFormattingRuleType type, ExcelAddress address, int priority, ExcelWorksheet worksheet, XmlNode itemElementNode, XmlNamespaceManager namespaceManager) : base( namespaceManager, itemElementNode) { Require.Argument(address).IsNotNull("address"); // While MSDN states that 1 is the "highest priority," it also defines this // field as W3C XML Schema int, which would allow values less than 1. Excel // itself will, on occasion, use a value of 0, so this check will allow a 0. Require.Argument(priority).IsInRange(0, int.MaxValue, "priority"); Require.Argument(worksheet).IsNotNull("worksheet"); _type = type; _worksheet = worksheet; SchemaNodeOrder = _worksheet.SchemaNodeOrder; if (itemElementNode == null) { // Create/Get the <cfRule> inside <conditionalFormatting> itemElementNode = CreateComplexNode( _worksheet.WorksheetXml.DocumentElement, string.Format( "{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'", //{0} ExcelConditionalFormattingConstants.Paths.ConditionalFormatting, // {1} ExcelConditionalFormattingConstants.Paths.SqrefAttribute, // {2} address.AddressSpaceSeparated, //CF node don't what to have comma between multi addresses, use space instead. // {3} ExcelConditionalFormattingConstants.Paths.CfRule, //{4} ExcelConditionalFormattingConstants.Paths.PriorityAttribute, //{5} priority)); } // Point to <cfRule> TopNode = itemElementNode; Address = address; Priority = priority; Type = type; if (DxfId >= 0) { worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true; //This Id is referenced by CF, so we can use it when we save. _style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone(); //Clone, so it can be altered without effecting other dxf styles } }
internal int CloneDxfStyle(ExcelStyles style, int styleID) { var copy = style.Dxfs[styleID]; var ix = Dxfs.FindIndexByID(copy.Id); if (ix < 0) { var parent = GetNode(dxfsPath); var node = _styleXml.CreateElement("d:dxf", ExcelPackage.schemaMain); parent.AppendChild(node); node.InnerXml = copy._helper.TopNode.InnerXml; var dxf = new ExcelDxfStyleConditionalFormatting(_nameSpaceManager, node, this); Dxfs.Add(copy.Id, dxf); return(Dxfs.Count - 1); } else { return(ix); } }
/// <summary> /// Loads the style XML to memory /// </summary> private void LoadFromDocument() { //NumberFormats ExcelNumberFormatXml.AddBuildIn(NameSpaceManager, NumberFormats); XmlNode numNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager); if (numNode != null) { foreach (XmlNode n in numNode) { ExcelNumberFormatXml nf = new ExcelNumberFormatXml(_nameSpaceManager, n); NumberFormats.Add(nf.Id, nf); if (nf.NumFmtId >= NumberFormats.NextId) NumberFormats.NextId=nf.NumFmtId+1; } } //Fonts XmlNode fontNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager); foreach (XmlNode n in fontNode) { ExcelFontXml f = new ExcelFontXml(_nameSpaceManager, n); Fonts.Add(f.Id, f); } //Fills XmlNode fillNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager); foreach (XmlNode n in fillNode) { ExcelFillXml f; if (n.FirstChild != null && n.FirstChild.LocalName == "gradientFill") { f = new ExcelGradientFillXml(_nameSpaceManager, n); } else { f = new ExcelFillXml(_nameSpaceManager, n); } Fills.Add(f.Id, f); } //Borders XmlNode borderNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager); foreach (XmlNode n in borderNode) { ExcelBorderXml b = new ExcelBorderXml(_nameSpaceManager, n); Borders.Add(b.Id, b); } //cellStyleXfs XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager); if (styleXfsNode != null) { foreach (XmlNode n in styleXfsNode) { ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this); CellStyleXfs.Add(item.Id, item); } } XmlNode styleNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager); for (int i = 0; i < styleNode.ChildNodes.Count; i++) { XmlNode n = styleNode.ChildNodes[i]; ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this); CellXfs.Add(item.Id, item); } //cellStyle XmlNode namedStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager); if (namedStyleNode != null) { foreach (XmlNode n in namedStyleNode) { ExcelNamedStyleXml item = new ExcelNamedStyleXml(_nameSpaceManager, n, this); NamedStyles.Add(item.Name, item); } } //dxfsPath XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager); if (dxfsNode != null) { foreach (XmlNode x in dxfsNode) { ExcelDxfStyleConditionalFormatting item = new ExcelDxfStyleConditionalFormatting(_nameSpaceManager, x, this); Dxfs.Add(item.Id, item); } } }
/// <summary> /// Loads the style XML to memory /// </summary> private void LoadFromDocument() { //NumberFormats ExcelNumberFormatXml.AddBuildIn(NameSpaceManager, NumberFormats); XmlNode numNode = _styleXml.SelectSingleNode(NumberFormatsPath, _nameSpaceManager); if (numNode != null) { foreach (XmlNode n in numNode) { ExcelNumberFormatXml nf = new ExcelNumberFormatXml(_nameSpaceManager, n); NumberFormats.Add(nf.Id, nf); if (nf.NumFmtId >= NumberFormats.NextId) { NumberFormats.NextId = nf.NumFmtId + 1; } } } //Fonts XmlNode fontNode = _styleXml.SelectSingleNode(FontsPath, _nameSpaceManager); foreach (XmlNode n in fontNode) { ExcelFontXml f = new ExcelFontXml(_nameSpaceManager, n); Fonts.Add(f.Id, f); } //Fills XmlNode fillNode = _styleXml.SelectSingleNode(FillsPath, _nameSpaceManager); foreach (XmlNode n in fillNode) { ExcelFillXml f; if (n.FirstChild != null && n.FirstChild.LocalName == "gradientFill") { f = new ExcelGradientFillXml(_nameSpaceManager, n); } else { f = new ExcelFillXml(_nameSpaceManager, n); } Fills.Add(f.Id, f); } //Borders XmlNode borderNode = _styleXml.SelectSingleNode(BordersPath, _nameSpaceManager); foreach (XmlNode n in borderNode) { ExcelBorderXml b = new ExcelBorderXml(_nameSpaceManager, n); Borders.Add(b.Id, b); } //cellStyleXfs XmlNode styleXfsNode = _styleXml.SelectSingleNode(CellStyleXfsPath, _nameSpaceManager); if (styleXfsNode != null) { foreach (XmlNode n in styleXfsNode) { ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this); CellStyleXfs.Add(item.Id, item); } } XmlNode styleNode = _styleXml.SelectSingleNode(CellXfsPath, _nameSpaceManager); for (int i = 0; i < styleNode.ChildNodes.Count; i++) { XmlNode n = styleNode.ChildNodes[i]; ExcelXfs item = new ExcelXfs(_nameSpaceManager, n, this); CellXfs.Add(item.Id, item); } //cellStyle XmlNode namedStyleNode = _styleXml.SelectSingleNode(CellStylesPath, _nameSpaceManager); if (namedStyleNode != null) { foreach (XmlNode n in namedStyleNode) { ExcelNamedStyleXml item = new ExcelNamedStyleXml(_nameSpaceManager, n, this); NamedStyles.Add(item.Name, item); } } //dxfsPath XmlNode dxfsNode = _styleXml.SelectSingleNode(dxfsPath, _nameSpaceManager); if (dxfsNode != null) { foreach (XmlNode x in dxfsNode) { ExcelDxfStyleConditionalFormatting item = new ExcelDxfStyleConditionalFormatting(_nameSpaceManager, x, this); Dxfs.Add(item.Id, item); } } }
/****************************************************************************************/ internal protected void SetStyle(ExcelDxfStyleConditionalFormatting style) { _style = Style; TopNode = null; }
/// <summary> /// Apply conditional formatting style to rule style property /// </summary> /// <param name="style"></param> /// <param name="ssStyle"></param> internal static void ApplyConditionalFormattingStyle(ExcelDxfStyleConditionalFormatting style, RCConditionalFormatStyleRecord ssStyle) { ExcelUnderLineType underline = (ExcelUnderLineType)ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssUnderline; ExcelBorderStyle bTop = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderTop.ssSTBorderStyle.ssStyle; ExcelBorderStyle bBottom = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderBottom.ssSTBorderStyle.ssStyle; ExcelBorderStyle bLeft = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderLeft.ssSTBorderStyle.ssStyle; ExcelBorderStyle bRight = (ExcelBorderStyle)ssStyle.ssSTConditionalFormatStyle.ssBorderRight.ssSTBorderStyle.ssStyle; ExcelFillStyle patternType = (ExcelFillStyle)ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssPatternType; style.Border.Bottom.Style = bBottom; if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderBottom.ssSTBorderStyle.ssColor)) { style.Border.Bottom.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderBottom.ssSTBorderStyle.ssColor); } style.Border.Left.Style = bLeft; if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderLeft.ssSTBorderStyle.ssColor)) { style.Border.Left.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderLeft.ssSTBorderStyle.ssColor); } style.Border.Right.Style = bRight; if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderRight.ssSTBorderStyle.ssColor)) { style.Border.Right.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderRight.ssSTBorderStyle.ssColor); } style.Border.Top.Style = bTop; if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssBorderTop.ssSTBorderStyle.ssColor)) { style.Border.Top.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssBorderTop.ssSTBorderStyle.ssColor); } if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssBackgroundColor)) { style.Fill.BackgroundColor.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssBackgroundColor); } if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssPatternColor)) { style.Fill.PatternColor.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssFill.ssSTFillStyle.ssPatternColor); } style.Fill.PatternType = patternType; style.Font.Bold = ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssBold; style.Font.Italic = ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssItalic; if (ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssStrike) { style.Font.Strike = ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssStrike; } else { style.Font.Strike = null; } style.Font.Underline = underline; if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssColor)) { style.Font.Color.Color = ConvertFromColorCode(ssStyle.ssSTConditionalFormatStyle.ssFont.ssSTFontStyle.ssColor); } if (!string.IsNullOrEmpty(ssStyle.ssSTConditionalFormatStyle.ssNumberFormat)) { style.NumberFormat.Format = ssStyle.ssSTConditionalFormatStyle.ssNumberFormat; } }
/// <summary> /// Initialize the <see cref="ExcelConditionalFormattingRule"/> /// </summary> /// <param name="type"></param> /// <param name="address"></param> /// <param name="priority">Used also as the cfRule unique key</param> /// <param name="worksheet"></param> /// <param name="itemElementNode"></param> /// <param name="namespaceManager"></param> internal ExcelConditionalFormattingRule( eExcelConditionalFormattingRuleType type, ExcelAddress address, int priority, ExcelWorksheet worksheet, XmlNode itemElementNode, XmlNamespaceManager namespaceManager) : base(namespaceManager, itemElementNode) { Require.Argument(address).IsNotNull("address"); Require.Argument(priority).IsInRange(1, int.MaxValue, "priority"); Require.Argument(worksheet).IsNotNull("worksheet"); _type = type; _worksheet = worksheet; SchemaNodeOrder = _worksheet.SchemaNodeOrder; if (itemElementNode == null) { // Create/Get the <cfRule> inside <conditionalFormatting> itemElementNode = CreateComplexNode( _worksheet.WorksheetXml.DocumentElement, string.Format( "{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'", //{0} ExcelConditionalFormattingConstants.Paths.ConditionalFormatting, // {1} ExcelConditionalFormattingConstants.Paths.SqrefAttribute, // {2} address.Address, // {3} ExcelConditionalFormattingConstants.Paths.CfRule, //{4} ExcelConditionalFormattingConstants.Paths.PriorityAttribute, //{5} priority)); } // Point to <cfRule> TopNode = itemElementNode; Address = address; Priority = priority; Type = type; if (DxfId >= 0) { worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true; //This Id is referenced by CF, so we can use it when we save. _style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone(); //Clone, so it can be altered without effecting other dxf styles } }