public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var colorScale = new ColorScale();
for (Int32 i = 1; i <= cf.Values.Count; i++)
{
var conditionalFormatValueObject = new ConditionalFormatValueObject {
Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value
};
colorScale.Append(conditionalFormatValueObject);
}
for (Int32 i = 1; i <= cf.Values.Count; i++)
{
Color color = new Color {
Rgb = cf.Colors[i].Color.ToHex()
};
colorScale.Append(color);
}
conditionalFormattingRule.Append(colorScale);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
String val = GetQuoted(cf.Values[1]);
var conditionalFormattingRule = new ConditionalFormattingRule {
FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var formula = new Formula();
if (cf.Operator == XLCFOperator.Equal || cf.Operator == XLCFOperator.NotEqual)
{
formula.Text = val;
}
else
{
formula.Text = val;
}
conditionalFormattingRule.Append(formula);
if (cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween)
{
var formula2 = new Formula {
Text = GetQuoted(cf.Values[2])
};
conditionalFormattingRule.Append(formula2);
}
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
return(conditionalFormattingRule);
}
public static void AddConditionalFormatting(this Worksheet worksheet, int rowStart, int rowCount, int columnStart, int columnCount)
{
string range = ComputeRange(rowStart, rowCount, columnStart, columnCount);
ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = range
}
};
ConditionalFormattingRule conditionalFormattingRule1 =
new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.ColorScale, Priority = 1
};
ColorScale colorScale1 = new ColorScale();
ConditionalFormatValueObject conditionalFormatValueObject1 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Number, Val = "0"
};
ConditionalFormatValueObject conditionalFormatValueObject2 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Percentile, Val = "50"
};
ConditionalFormatValueObject conditionalFormatValueObject3 = new ConditionalFormatValueObject()
{
Type = ConditionalFormatValueObjectValues.Number, Val = "100"
};
Color color1 = new Color()
{
Rgb = "FFF8696B"
};
Color color2 = new Color()
{
Rgb = "FFFFEB84"
};
Color color3 = new Color()
{
Rgb = "FF63BE7B"
};
colorScale1.Append(conditionalFormatValueObject1);
colorScale1.Append(conditionalFormatValueObject2);
colorScale1.Append(conditionalFormatValueObject3);
colorScale1.Append(color1);
colorScale1.Append(color2);
colorScale1.Append(color3);
conditionalFormattingRule1.Append(colorScale1);
conditionalFormatting1.Append(conditionalFormattingRule1);
// If we don't have this after SheetData, it corrupts the file if we have added hyperlinks before
worksheet.InsertAfter(conditionalFormatting1, worksheet.Descendants <SheetData>().First());
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
UInt32 val = UInt32.Parse(cf.Values[1].Value);
var conditionalFormattingRule = new ConditionalFormattingRule {
FormatId = (UInt32)context.DifferentialFormats[cf.Style], Percent = cf.Percent, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, Rank = val, Bottom = cf.Bottom
};
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula { Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" };
conditionalFormattingRule.Append(formula);
return conditionalFormattingRule;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
String val = cf.Values[1].Value;
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" };
conditionalFormattingRule.Append(formula);
return conditionalFormattingRule;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var dataBar = new DataBar {
ShowValue = !cf.ShowBarOnly
};
var conditionalFormatValueObject1 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[1].ToOpenXml()
};
if (cf.Values.Any() && cf.Values[1]?.Value != null)
{
conditionalFormatValueObject1.Val = cf.Values[1].Value;
}
var conditionalFormatValueObject2 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[2].ToOpenXml()
};
if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null)
{
conditionalFormatValueObject2.Val = cf.Values[2].Value;
}
var color = new Color();
switch (cf.Colors[1].ColorType)
{
case XLColorType.Color:
color.Rgb = cf.Colors[1].Color.ToHex();
break;
case XLColorType.Theme:
color.Theme = System.Convert.ToUInt32(cf.Colors[1].ThemeColor);
break;
case XLColorType.Indexed:
color.Indexed = System.Convert.ToUInt32(cf.Colors[1].Indexed);
break;
}
dataBar.Append(conditionalFormatValueObject1);
dataBar.Append(conditionalFormatValueObject2);
dataBar.Append(color);
conditionalFormattingRule.Append(dataBar);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var formula = new Formula {
Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))"
};
conditionalFormattingRule.Append(formula);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
String val = cf.Values[1].Value;
var conditionalFormattingRule = new ConditionalFormattingRule {
FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = ConditionalFormattingOperatorValues.EndsWith, Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var formula = new Formula {
Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\""
};
conditionalFormattingRule.Append(formula);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var iconSet = new IconSet {ShowValue = !cf.ShowIconOnly, Reverse = cf.ReverseIconOrder, IconSetValue = cf.IconSetStyle.ToOpenXml()};
Int32 count = cf.Values.Count;
for(Int32 i=1;i<= count; i++ )
{
var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value, GreaterThanOrEqual = cf.IconSetOperators[i] == XLCFIconSetOperator.EqualOrGreaterThan};
iconSet.Append(conditionalFormatValueObject);
}
conditionalFormattingRule.Append(iconSet);
return conditionalFormattingRule;
}
public void SetCondition(string range, string opr, string formula, int priority, int style)
{
ConditionalFormatting conditionalFormatting = this.part.Worksheet.Elements <ConditionalFormatting>().FirstOrDefault();
if (conditionalFormatting == null)
{
conditionalFormatting = new ConditionalFormatting();
conditionalFormatting.SequenceOfReferences = new ListValue <StringValue>();
this.part.Worksheet.Append(conditionalFormatting);
}
conditionalFormatting.SequenceOfReferences.Items.Add(new StringValue()
{
InnerText = range
});
ConditionalFormattingOperatorValues mopr = ConditionalFormattingOperatorValues.Equal;
if (opr == "<")
{
mopr = ConditionalFormattingOperatorValues.LessThan;
}
if (opr == "<=")
{
mopr = ConditionalFormattingOperatorValues.LessThanOrEqual;
}
if (opr == ">")
{
mopr = ConditionalFormattingOperatorValues.GreaterThan;
}
if (opr == ">=")
{
mopr = ConditionalFormattingOperatorValues.GreaterThanOrEqual;
}
ConditionalFormattingRule rule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32)style,
Priority = priority,
Operator = mopr
};
rule.Append(new Formula(formula));
conditionalFormatting.Append(rule);
}
internal void FromConditionalFormattingRule(ConditionalFormattingRule cfr)
{
this.SetAllNull();
if (cfr.Type != null) this.Type = cfr.Type.Value;
if (cfr.FormatId != null) this.FormatId = cfr.FormatId.Value;
this.Priority = cfr.Priority.Value;
if (cfr.StopIfTrue != null) this.StopIfTrue = cfr.StopIfTrue.Value;
if (cfr.AboveAverage != null) this.AboveAverage = cfr.AboveAverage.Value;
if (cfr.Percent != null) this.Percent = cfr.Percent.Value;
if (cfr.Bottom != null) this.Bottom = cfr.Bottom.Value;
if (cfr.Operator != null)
{
this.Operator = cfr.Operator.Value;
this.HasOperator = true;
}
if (cfr.Text != null) this.Text = cfr.Text.Value;
if (cfr.TimePeriod != null)
{
this.TimePeriod = cfr.TimePeriod.Value;
this.HasTimePeriod = true;
}
if (cfr.Rank != null) this.Rank = cfr.Rank.Value;
if (cfr.StdDev != null) this.StdDev = cfr.StdDev.Value;
if (cfr.EqualAverage != null) this.EqualAverage = cfr.EqualAverage.Value;
foreach (var c in cfr)
{
if (c is Formula)
this.Formulas.Add((Formula)c.CloneNode(true));
if (c is ColorScale)
{
this.ColorScale = new SLColorScale();
this.ColorScale.FromColorScale((ColorScale)c);
}
if (c is DataBar)
{
this.DataBar = new SLDataBar();
this.DataBar.FromDataBar((DataBar)c);
}
if (c is IconSet)
{
this.IconSet = new SLIconSet();
this.IconSet.FromIconSet((IconSet)c);
}
}
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var iconSet = new IconSet {
ShowValue = !cf.ShowIconOnly, Reverse = cf.ReverseIconOrder, IconSetValue = cf.IconSetStyle.ToOpenXml()
};
Int32 count = cf.Values.Count;
for (Int32 i = 1; i <= count; i++)
{
var conditionalFormatValueObject = new ConditionalFormatValueObject {
Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value, GreaterThanOrEqual = cf.IconSetOperators[i] == XLCFIconSetOperator.EqualOrGreaterThan
};
iconSet.Append(conditionalFormatValueObject);
}
conditionalFormattingRule.Append(iconSet);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var dataBar = new DataBar {ShowValue = !cf.ShowBarOnly};
var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml()};
if (cf.Values.Count >= 1) conditionalFormatValueObject1.Val = cf.Values[1].Value;
var conditionalFormatValueObject2 = new ConditionalFormatValueObject { Type = cf.ContentTypes[2].ToOpenXml()};
if (cf.Values.Count >= 2) conditionalFormatValueObject2.Val = cf.Values[2].Value;
var color = new Color { Rgb = cf.Colors[1].Color.ToHex() };
dataBar.Append(conditionalFormatValueObject1);
dataBar.Append(conditionalFormatValueObject2);
dataBar.Append(color);
conditionalFormattingRule.Append(dataBar);
return conditionalFormattingRule;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var colorScale = new ColorScale();
for(Int32 i = 1; i <= cf.Values.Count; i++)
{
var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value };
colorScale.Append(conditionalFormatValueObject);
}
for (Int32 i = 1; i <= cf.Values.Count; i++)
{
Color color = new Color { Rgb = cf.Colors[i].Color.ToHex() };
colorScale.Append(color);
}
conditionalFormattingRule.Append(colorScale);
return conditionalFormattingRule;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
String val = GetQuoted(cf.Values[1]);
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
var formula = new Formula();
if (cf.Operator == XLCFOperator.Equal || cf.Operator == XLCFOperator.NotEqual)
formula.Text = val;
else
formula.Text = val;
conditionalFormattingRule.Append(formula);
if(cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween)
{
var formula2 = new Formula { Text = GetQuoted(cf.Values[2]) };
conditionalFormattingRule.Append(formula2);
}
return conditionalFormattingRule;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var dataBar = new DataBar {
ShowValue = !cf.ShowBarOnly
};
var conditionalFormatValueObject1 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[1].ToOpenXml()
};
if (cf.Values.Count >= 1)
{
conditionalFormatValueObject1.Val = cf.Values[1].Value;
}
var conditionalFormatValueObject2 = new ConditionalFormatValueObject {
Type = cf.ContentTypes[2].ToOpenXml()
};
if (cf.Values.Count >= 2)
{
conditionalFormatValueObject2.Val = cf.Values[2].Value;
}
var color = new Color {
Rgb = cf.Colors[1].Color.ToHex()
};
dataBar.Append(conditionalFormatValueObject1);
dataBar.Append(conditionalFormatValueObject2);
dataBar.Append(color);
conditionalFormattingRule.Append(dataBar);
return(conditionalFormattingRule);
}
public void AppendConditionalFormatting(String[] sequenceOfReferences, UInt32 formatId, String formula)
{
ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = sequenceOfReferences[0] // пока только первая стока
}
};
ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.Expression,
FormatId = formatId,
Priority = 1
};
conditionalFormattingRule.Append(new Formula()
{
Text = formula
});
conditionalFormatting.Append(conditionalFormattingRule);
worksheet.Append(conditionalFormatting);
}
private void AddConditionalFormatting(Worksheet ws, int index)
{
var conditionalFormatting1 =
new ConditionalFormatting {
SequenceOfReferences = new ListValue <StringValue> {
InnerText = formatReference
}
};
var conditionalFormattingRule1 =
new ConditionalFormattingRule {
Type = ConditionalFormatValues.Expression, FormatId = (uint)index, Priority = 1
};
var formula1 = new Formula {
Text = formatFormula
};
conditionalFormattingRule1.Append(formula1);
conditionalFormatting1.Append(conditionalFormattingRule1);
var pp = ws.Descendants <PhoneticProperties>()
.FirstOrDefault();
ws.InsertAfter(conditionalFormatting1, pp);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule {
Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority
};
var colorScale = new ColorScale();
for (Int32 i = 1; i <= cf.ContentTypes.Count; i++)
{
var type = cf.ContentTypes[i].ToOpenXml();
var val = (cf.Values.ContainsKey(i) && cf.Values[i] != null) ? cf.Values[i].Value : null;
var conditionalFormatValueObject = new ConditionalFormatValueObject {
Type = type
};
if (val != null)
{
conditionalFormatValueObject.Val = val;
}
colorScale.Append(conditionalFormatValueObject);
}
for (Int32 i = 1; i <= cf.Colors.Count; i++)
{
Color color = new Color {
Rgb = cf.Colors[i].Color.ToHex()
};
colorScale.Append(color);
}
conditionalFormattingRule.Append(colorScale);
return(conditionalFormattingRule);
}
internal void FromConditionalFormattingRule(ConditionalFormattingRule cfr)
{
this.SetAllNull();
if (cfr.Type != null)
{
this.Type = cfr.Type.Value;
}
if (cfr.FormatId != null)
{
this.FormatId = cfr.FormatId.Value;
}
this.Priority = cfr.Priority.Value;
if (cfr.StopIfTrue != null)
{
this.StopIfTrue = cfr.StopIfTrue.Value;
}
if (cfr.AboveAverage != null)
{
this.AboveAverage = cfr.AboveAverage.Value;
}
if (cfr.Percent != null)
{
this.Percent = cfr.Percent.Value;
}
if (cfr.Bottom != null)
{
this.Bottom = cfr.Bottom.Value;
}
if (cfr.Operator != null)
{
this.Operator = cfr.Operator.Value;
this.HasOperator = true;
}
if (cfr.Text != null)
{
this.Text = cfr.Text.Value;
}
if (cfr.TimePeriod != null)
{
this.TimePeriod = cfr.TimePeriod.Value;
this.HasTimePeriod = true;
}
if (cfr.Rank != null)
{
this.Rank = cfr.Rank.Value;
}
if (cfr.StdDev != null)
{
this.StdDev = cfr.StdDev.Value;
}
if (cfr.EqualAverage != null)
{
this.EqualAverage = cfr.EqualAverage.Value;
}
using (OpenXmlReader oxr = OpenXmlReader.Create(cfr))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Formula))
{
this.Formulas.Add((Formula)oxr.LoadCurrentElement().CloneNode(true));
}
else if (oxr.ElementType == typeof(ColorScale))
{
this.ColorScale = new SLColorScale();
this.ColorScale.FromColorScale((ColorScale)oxr.LoadCurrentElement());
this.HasColorScale = true;
}
else if (oxr.ElementType == typeof(DataBar))
{
this.DataBar = new SLDataBar();
this.DataBar.FromDataBar((DataBar)oxr.LoadCurrentElement());
this.HasDataBar = true;
}
else if (oxr.ElementType == typeof(IconSet))
{
this.IconSet = new SLIconSet();
this.IconSet.FromIconSet((IconSet)oxr.LoadCurrentElement());
this.HasIconSet = true;
}
else if (oxr.ElementType == typeof(ConditionalFormattingRuleExtension))
{
this.Extensions.Add((ConditionalFormattingRuleExtension)oxr.LoadCurrentElement().CloneNode(true));
}
}
}
}
internal ConditionalFormattingRule ToConditionalFormattingRule()
{
var cfr = new ConditionalFormattingRule();
cfr.Type = Type;
if (FormatId != null)
{
cfr.FormatId = FormatId.Value;
}
cfr.Priority = Priority;
if (StopIfTrue)
{
cfr.StopIfTrue = StopIfTrue;
}
if (!AboveAverage)
{
cfr.AboveAverage = AboveAverage;
}
if (Percent)
{
cfr.Percent = Percent;
}
if (Bottom)
{
cfr.Bottom = Bottom;
}
if (HasOperator)
{
cfr.Operator = Operator;
}
if ((Text != null) && (Text.Length > 0))
{
cfr.Text = Text;
}
if (HasTimePeriod)
{
cfr.TimePeriod = TimePeriod;
}
if (Rank != null)
{
cfr.Rank = Rank.Value;
}
if (StdDev != null)
{
cfr.StdDev = StdDev.Value;
}
if (EqualAverage)
{
cfr.EqualAverage = EqualAverage;
}
foreach (var f in Formulas)
{
cfr.Append((Formula)f.CloneNode(true));
}
if (HasColorScale)
{
cfr.Append(ColorScale.ToColorScale());
}
if (HasDataBar)
{
cfr.Append(DataBar.ToDataBar());
}
if (HasIconSet)
{
cfr.Append(IconSet.ToIconSet());
}
if (Extensions.Count > 0)
{
var extlist = new ConditionalFormattingRuleExtensionList();
foreach (var ext in Extensions)
{
extlist.Append((ConditionalFormattingRuleExtension)ext.CloneNode(true));
}
cfr.Append(extlist);
}
return(cfr);
}
static void Main(string[] args)
{
var workbookClosed = new XLWorkbook();
var sheet = workbookClosed.Worksheets.Add("Sheet1");
sheet.Cell(1, 1).Value = 1;
sheet.Cell(2, 1).Value = -1;
workbookClosed.SaveAs("closed.xlsx");
using (SpreadsheetDocument report = SpreadsheetDocument.Open("closed.xlsx", true))
{
Workbook workbook = report.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();
DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };
DifferentialFormat lessThanFormat = new DifferentialFormat();
Font lessThanFont = new Font();
lessThanFont.Append(new Condense() { Val = false });
lessThanFont.Append(new Extend() { Val = false });
lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
Fill lessThanFill = new Fill();
PatternFill lessThanPatternFill = new PatternFill();
lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
lessThanFill.Append(lessThanPatternFill);
lessThanFormat.Append(lessThanFont);
lessThanFormat.Append(lessThanFill);
DifferentialFormat greaterThanFormat = new DifferentialFormat();
Font greaterThanFont = new Font();
greaterThanFont.Append(new Condense() { Val = false });
greaterThanFont.Append(new Extend() { Val = false });
greaterThanFont.Append(new Color() { Rgb = "FF006100" });
Fill greatherThanFill = new Fill();
PatternFill greaterThanPatternFill = new PatternFill();
greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
greatherThanFill.Append(greaterThanPatternFill);
greaterThanFormat.Append(greaterThanFont);
greaterThanFormat.Append(greatherThanFill);
differentialFormats.Append(lessThanFormat);
differentialFormats.Append(greaterThanFormat);
workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);
ConditionalFormatting conditionalFormatting = new ConditionalFormatting()
{
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
};
ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)1U,
Priority = 2,
Operator = ConditionalFormattingOperatorValues.GreaterThan
};
Formula greaterThanFormula = new Formula();
greaterThanFormula.Text = "0";
greaterThanRule.Append(greaterThanFormula);
ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)0U,
Priority = 1,
Operator = ConditionalFormattingOperatorValues.LessThan
};
Formula lessThanFormula = new Formula();
lessThanFormula.Text = "0";
lessThanRule.Append(lessThanFormula);
conditionalFormatting.Append(greaterThanRule);
conditionalFormatting.Append(lessThanRule);
worksheetPart.Worksheet.PrependChild<ConditionalFormatting>(conditionalFormatting);
report.WorkbookPart.Workbook.Save();
report.Close();
}
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority };
return conditionalFormattingRule;
}
// Generates content of worksheetPart1.
private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
Worksheet worksheet1 = new Worksheet();
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1:A2" };
SheetViews sheetViews1 = new SheetViews();
SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
Selection selection1 = new Selection() { ActiveCell = "A2", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };
sheetView1.Append(selection1);
sheetViews1.Append(sheetView1);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D };
SheetData sheetData1 = new SheetData();
Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };
Cell cell1 = new Cell() { CellReference = "A1" };
CellValue cellValue1 = new CellValue();
cellValue1.Text = "1";
cell1.Append(cellValue1);
row1.Append(cell1);
Row row2 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:1" } };
Cell cell2 = new Cell() { CellReference = "A2" };
CellValue cellValue2 = new CellValue();
cellValue2.Text = "-1";
cell2.Append(cellValue2);
row2.Append(cell2);
sheetData1.Append(row1);
sheetData1.Append(row2);
ConditionalFormatting conditionalFormatting1 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1" } };
ConditionalFormattingRule conditionalFormattingRule1 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)2U, Priority = 2, Operator = ConditionalFormattingOperatorValues.GreaterThan };
Formula formula1 = new Formula();
formula1.Text = "0";
conditionalFormattingRule1.Append(formula1);
conditionalFormatting1.Append(conditionalFormattingRule1);
ConditionalFormatting conditionalFormatting2 = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A2" } };
ConditionalFormattingRule conditionalFormattingRule2 = new ConditionalFormattingRule() { Type = ConditionalFormatValues.CellIs, FormatId = (UInt32Value)0U, Priority = 1, Operator = ConditionalFormattingOperatorValues.LessThan };
Formula formula2 = new Formula();
formula2.Text = "0";
conditionalFormattingRule2.Append(formula2);
conditionalFormatting2.Append(conditionalFormattingRule2);
PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
worksheet1.Append(sheetDimension1);
worksheet1.Append(sheetViews1);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(sheetData1);
worksheet1.Append(conditionalFormatting1);
worksheet1.Append(conditionalFormatting2);
worksheet1.Append(pageMargins1);
worksheetPart1.Worksheet = worksheet1;
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, XLWorkbook.SaveContext context)
{
ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
{
Type = DocumentFormat.OpenXml.Spreadsheet.ConditionalFormatValues.DataBar,
Id = (cf as XLConditionalFormat).Id.WrapInBraces()
};
DataBar dataBar = new DataBar()
{
MinLength = 0,
MaxLength = 100,
Gradient = true,
ShowValue = !cf.ShowBarOnly
};
var cfMinType = cf.ContentTypes.TryGetValue(1, out var contentType1)
? Convert(contentType1.ToOpenXml())
: ConditionalFormattingValueObjectTypeValues.AutoMin;
var cfMin = new ConditionalFormattingValueObject {
Type = cfMinType
};
if (cf.Values.Any() && cf.Values[1]?.Value != null)
{
cfMin.Type = ConditionalFormattingValueObjectTypeValues.Numeric;
cfMin.Append(new Formula()
{
Text = cf.Values[1].Value
});
}
var cfMaxType = cf.ContentTypes.TryGetValue(2, out var contentType2)
? Convert(contentType2.ToOpenXml())
: ConditionalFormattingValueObjectTypeValues.AutoMax;
var cfMax = new ConditionalFormattingValueObject {
Type = cfMaxType
};
if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null)
{
cfMax.Type = ConditionalFormattingValueObjectTypeValues.Numeric;
cfMax.Append(new Formula()
{
Text = cf.Values[2].Value
});
}
var barAxisColor = new BarAxisColor {
Rgb = XLColor.Black.Color.ToHex()
};
var negativeFillColor = new NegativeFillColor {
Rgb = cf.Colors[1].Color.ToHex()
};
if (cf.Colors.Count == 2)
{
negativeFillColor = new NegativeFillColor {
Rgb = cf.Colors[2].Color.ToHex()
};
}
dataBar.Append(cfMin);
dataBar.Append(cfMax);
dataBar.Append(negativeFillColor);
dataBar.Append(barAxisColor);
conditionalFormattingRule.Append(dataBar);
return(conditionalFormattingRule);
}
public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context)
{
UInt32 val = UInt32.Parse(cf.Values[1].Value);
var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Percent = cf.Percent, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, Rank = val, Bottom = cf.Bottom};
return conditionalFormattingRule;
}
internal void FromConditionalFormattingRule(ConditionalFormattingRule cfr)
{
this.SetAllNull();
if (cfr.Type != null) this.Type = cfr.Type.Value;
if (cfr.FormatId != null) this.FormatId = cfr.FormatId.Value;
this.Priority = cfr.Priority.Value;
if (cfr.StopIfTrue != null) this.StopIfTrue = cfr.StopIfTrue.Value;
if (cfr.AboveAverage != null) this.AboveAverage = cfr.AboveAverage.Value;
if (cfr.Percent != null) this.Percent = cfr.Percent.Value;
if (cfr.Bottom != null) this.Bottom = cfr.Bottom.Value;
if (cfr.Operator != null)
{
this.Operator = cfr.Operator.Value;
this.HasOperator = true;
}
if (cfr.Text != null) this.Text = cfr.Text.Value;
if (cfr.TimePeriod != null)
{
this.TimePeriod = cfr.TimePeriod.Value;
this.HasTimePeriod = true;
}
if (cfr.Rank != null) this.Rank = cfr.Rank.Value;
if (cfr.StdDev != null) this.StdDev = cfr.StdDev.Value;
if (cfr.EqualAverage != null) this.EqualAverage = cfr.EqualAverage.Value;
using (OpenXmlReader oxr = OpenXmlReader.Create(cfr))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Formula))
{
this.Formulas.Add((Formula)oxr.LoadCurrentElement().CloneNode(true));
}
else if (oxr.ElementType == typeof(ColorScale))
{
this.ColorScale = new SLColorScale();
this.ColorScale.FromColorScale((ColorScale)oxr.LoadCurrentElement());
this.HasColorScale = true;
}
else if (oxr.ElementType == typeof(DataBar))
{
this.DataBar = new SLDataBar();
this.DataBar.FromDataBar((DataBar)oxr.LoadCurrentElement());
this.HasDataBar = true;
}
else if (oxr.ElementType == typeof(IconSet))
{
this.IconSet = new SLIconSet();
this.IconSet.FromIconSet((IconSet)oxr.LoadCurrentElement());
this.HasIconSet = true;
}
else if (oxr.ElementType == typeof(ConditionalFormattingRuleExtension))
{
this.Extensions.Add((ConditionalFormattingRuleExtension)oxr.LoadCurrentElement().CloneNode(true));
}
}
}
}
internal ConditionalFormattingRule ToConditionalFormattingRule()
{
ConditionalFormattingRule cfr = new ConditionalFormattingRule();
cfr.Type = this.Type;
if (this.FormatId != null)
{
cfr.FormatId = this.FormatId.Value;
}
cfr.Priority = this.Priority;
if (this.StopIfTrue)
{
cfr.StopIfTrue = this.StopIfTrue;
}
if (!this.AboveAverage)
{
cfr.AboveAverage = this.AboveAverage;
}
if (this.Percent)
{
cfr.Percent = this.Percent;
}
if (this.Bottom)
{
cfr.Bottom = this.Bottom;
}
if (HasOperator)
{
cfr.Operator = this.Operator;
}
if (this.Text != null && this.Text.Length > 0)
{
cfr.Text = this.Text;
}
if (HasTimePeriod)
{
cfr.TimePeriod = this.TimePeriod;
}
if (this.Rank != null)
{
cfr.Rank = this.Rank.Value;
}
if (this.StdDev != null)
{
cfr.StdDev = this.StdDev.Value;
}
if (this.EqualAverage)
{
cfr.EqualAverage = this.EqualAverage;
}
foreach (Formula f in this.Formulas)
{
cfr.Append((Formula)f.CloneNode(true));
}
if (HasColorScale)
{
cfr.Append(this.ColorScale.ToColorScale());
}
if (HasDataBar)
{
cfr.Append(this.DataBar.ToDataBar());
}
if (HasIconSet)
{
cfr.Append(this.IconSet.ToIconSet());
}
if (this.Extensions.Count > 0)
{
ConditionalFormattingRuleExtensionList extlist = new ConditionalFormattingRuleExtensionList();
foreach (ConditionalFormattingRuleExtension ext in this.Extensions)
{
extlist.Append((ConditionalFormattingRuleExtension)ext.CloneNode(true));
}
cfr.Append(extlist);
}
return(cfr);
}
private void AddConditionalFormatting()
{
//use openxml directly to apply conditional formatting.
using (SpreadsheetDocument report = SpreadsheetDocument.Open(fileName, true))
{
Workbook workbook = report.WorkbookPart.Workbook;
WorksheetPart worksheetPart = workbook.WorkbookPart.WorksheetParts.First();
DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)2U };
DifferentialFormat lessThanFormat = new DifferentialFormat();
Font lessThanFont = new Font();
lessThanFont.Append(new Condense() { Val = false });
lessThanFont.Append(new Extend() { Val = false });
lessThanFont.Append(new Color() { Rgb = "FF9C0006" });
Fill lessThanFill = new Fill();
PatternFill lessThanPatternFill = new PatternFill();
lessThanPatternFill.Append(new BackgroundColor() { Rgb = "FFFFC7CE" });
lessThanFill.Append(lessThanPatternFill);
lessThanFormat.Append(lessThanFont);
lessThanFormat.Append(lessThanFill);
DifferentialFormat greaterThanFormat = new DifferentialFormat();
Font greaterThanFont = new Font();
greaterThanFont.Append(new Condense() { Val = false });
greaterThanFont.Append(new Extend() { Val = false });
greaterThanFont.Append(new Color() { Rgb = "FF006100" });
Fill greatherThanFill = new Fill();
PatternFill greaterThanPatternFill = new PatternFill();
greaterThanPatternFill.Append(new BackgroundColor() { Rgb = "FFC6EFCE" });
greatherThanFill.Append(greaterThanPatternFill);
greaterThanFormat.Append(greaterThanFont);
greaterThanFormat.Append(greatherThanFill);
differentialFormats.Append(lessThanFormat);
differentialFormats.Append(greaterThanFormat);
workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Append(differentialFormats);
ConditionalFormatting conditionalFormatting = new ConditionalFormatting() {
SequenceOfReferences = new ListValue<StringValue>() { InnerText = "D2:D10" }
};
ConditionalFormattingRule greaterThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)1U,
Priority = 2,
Operator = ConditionalFormattingOperatorValues.GreaterThan
};
Formula greaterThanFormula = new Formula();
greaterThanFormula.Text = "0";
greaterThanRule.Append(greaterThanFormula);
ConditionalFormattingRule lessThanRule = new ConditionalFormattingRule()
{
Type = ConditionalFormatValues.CellIs,
FormatId = (UInt32Value)0U,
Priority = 1,
Operator = ConditionalFormattingOperatorValues.LessThan
};
Formula lessThanFormula = new Formula();
lessThanFormula.Text = "0";
lessThanRule.Append(lessThanFormula);
conditionalFormatting.Append(greaterThanRule);
conditionalFormatting.Append(lessThanRule);
worksheetPart.Worksheet.Append(conditionalFormatting);
report.WorkbookPart.Workbook.Save();
report.Close();
}
}
internal ConditionalFormattingRule ToConditionalFormattingRule()
{
ConditionalFormattingRule cfr = new ConditionalFormattingRule();
cfr.Type = this.Type;
if (this.FormatId != null) cfr.FormatId = this.FormatId.Value;
cfr.Priority = this.Priority;
if (this.StopIfTrue) cfr.StopIfTrue = this.StopIfTrue;
if (!this.AboveAverage) cfr.AboveAverage = this.AboveAverage;
if (this.Percent) cfr.Percent = this.Percent;
if (this.Bottom) cfr.Bottom = this.Bottom;
if (HasOperator) cfr.Operator = this.Operator;
if (this.Text != null && this.Text.Length > 0) cfr.Text = this.Text;
if (HasTimePeriod) cfr.TimePeriod = this.TimePeriod;
if (this.Rank != null) cfr.Rank = this.Rank.Value;
if (this.StdDev != null) cfr.StdDev = this.StdDev.Value;
if (this.EqualAverage) cfr.EqualAverage = this.EqualAverage;
foreach (Formula f in this.Formulas)
{
cfr.Append((Formula)f.CloneNode(true));
}
if (HasColorScale) cfr.Append(this.ColorScale.ToColorScale());
if (HasDataBar) cfr.Append(this.DataBar.ToDataBar());
if (HasIconSet) cfr.Append(this.IconSet.ToIconSet());
if (this.Extensions.Count > 0)
{
ConditionalFormattingRuleExtensionList extlist = new ConditionalFormattingRuleExtensionList();
foreach (ConditionalFormattingRuleExtension ext in this.Extensions)
{
extlist.Append((ConditionalFormattingRuleExtension)ext.CloneNode(true));
}
cfr.Append(extlist);
}
return cfr;
}
