internal void FromColorScale(X14.ColorScale cs)
{
this.SetAllNull();
SLConditionalFormattingValueObject2010 cfvo;
SLColor clr;
using (OpenXmlReader oxr = OpenXmlReader.Create(cs))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(X14.ConditionalFormattingValueObject))
{
cfvo = new SLConditionalFormattingValueObject2010();
cfvo.FromConditionalFormattingValueObject((X14.ConditionalFormattingValueObject)oxr.LoadCurrentElement());
this.Cfvos.Add(cfvo);
}
else if (oxr.ElementType == typeof(X14.Color))
{
clr = new SLColor(new List<System.Drawing.Color>(), new List<System.Drawing.Color>());
clr.FromExcel2010Color((X14.Color)oxr.LoadCurrentElement());
this.Colors.Add(clr);
}
}
}
}
internal void FromConditionalFormatting(X14.ConditionalFormatting cf)
{
this.SetAllNull();
if (cf.Pivot != null) this.Pivot = cf.Pivot.Value;
using (OpenXmlReader oxr = OpenXmlReader.Create(cf))
{
while (oxr.Read())
{
SLConditionalFormattingRule2010 cfr;
if (oxr.ElementType == typeof(X14.ConditionalFormattingRule))
{
cfr = new SLConditionalFormattingRule2010();
cfr.FromConditionalFormattingRule((X14.ConditionalFormattingRule)oxr.LoadCurrentElement());
this.Rules.Add(cfr);
}
else if (oxr.ElementType == typeof(Excel.ReferenceSequence))
{
Excel.ReferenceSequence refseq = (Excel.ReferenceSequence)oxr.LoadCurrentElement();
this.ReferenceSequence = SLTool.TranslateRefSeqToCellPointRange(refseq);
}
}
}
}
internal void FromConditionalFormattingIcon(X14.ConditionalFormattingIcon cfi)
{
this.SetAllNull();
if (cfi.IconSet != null) this.IconSet = cfi.IconSet.Value;
if (cfi.IconId != null) this.IconId = cfi.IconId.Value;
}
internal void FromConditionalFormattingValueObject(X14.ConditionalFormattingValueObject cfvo)
{
this.SetAllNull();
if (cfvo.Formula != null) this.Formula = cfvo.Formula.Text;
this.Type = cfvo.Type.Value;
if (cfvo.GreaterThanOrEqual != null) this.GreaterThanOrEqual = cfvo.GreaterThanOrEqual.Value;
}
internal void FromSeriesColor(X14.SeriesColor clr)
{
this.SetAllNull();
if (clr.Auto != null) this.Auto = clr.Auto.Value;
if (clr.Indexed != null) this.Indexed = clr.Indexed.Value;
if (clr.Rgb != null) this.Rgb = clr.Rgb.Value;
if (clr.Theme != null) this.Theme = clr.Theme.Value;
if (clr.Tint != null) this.Tint = clr.Tint.Value;
this.SetDisplayColor();
}
internal void FromDataBar(X14.DataBar db)
{
this.SetAllNull();
using (OpenXmlReader oxr = OpenXmlReader.Create(db))
{
int i = 0;
while (oxr.Read())
{
if (oxr.ElementType == typeof(X14.ConditionalFormattingValueObject))
{
if (i == 0)
{
this.Cfvo1.FromConditionalFormattingValueObject((X14.ConditionalFormattingValueObject)oxr.LoadCurrentElement());
++i;
}
else if (i == 1)
{
this.Cfvo2.FromConditionalFormattingValueObject((X14.ConditionalFormattingValueObject)oxr.LoadCurrentElement());
++i;
}
}
else if (oxr.ElementType == typeof(X14.FillColor))
{
this.FillColor.FromFillColor((X14.FillColor)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(X14.BorderColor))
{
this.BorderColor.FromBorderColor((X14.BorderColor)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(X14.NegativeFillColor))
{
this.NegativeFillColor.FromNegativeFillColor((X14.NegativeFillColor)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(X14.NegativeBorderColor))
{
this.NegativeBorderColor.FromNegativeBorderColor((X14.NegativeBorderColor)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(X14.BarAxisColor))
{
this.AxisColor.FromBarAxisColor((X14.BarAxisColor)oxr.LoadCurrentElement());
}
}
}
if (db.MinLength != null) this.MinLength = db.MinLength.Value;
if (db.MaxLength != null) this.MaxLength = db.MaxLength.Value;
if (db.ShowValue != null) this.ShowValue = db.ShowValue.Value;
if (db.Border != null) this.Border = db.Border.Value;
if (db.Gradient != null) this.Gradient = db.Gradient.Value;
if (db.Direction != null) this.Direction = db.Direction.Value;
if (db.NegativeBarColorSameAsPositive != null) this.NegativeBarColorSameAsPositive = db.NegativeBarColorSameAsPositive.Value;
if (db.NegativeBarBorderColorSameAsPositive != null) this.NegativeBarBorderColorSameAsPositive = db.NegativeBarBorderColorSameAsPositive.Value;
if (db.AxisPosition != null) this.AxisPosition = db.AxisPosition.Value;
}
internal void FromSparklineGroup(X14.SparklineGroup spkgrp)
{
this.SetAllNull();
if (spkgrp.SeriesColor != null) this.SeriesColor.FromSeriesColor(spkgrp.SeriesColor);
if (spkgrp.NegativeColor != null) this.NegativeColor.FromNegativeColor(spkgrp.NegativeColor);
if (spkgrp.AxisColor != null) this.AxisColor.FromAxisColor(spkgrp.AxisColor);
if (spkgrp.MarkersColor != null) this.MarkersColor.FromMarkersColor(spkgrp.MarkersColor);
if (spkgrp.FirstMarkerColor != null) this.FirstMarkerColor.FromFirstMarkerColor(spkgrp.FirstMarkerColor);
if (spkgrp.LastMarkerColor != null) this.LastMarkerColor.FromLastMarkerColor(spkgrp.LastMarkerColor);
if (spkgrp.HighMarkerColor != null) this.HighMarkerColor.FromHighMarkerColor(spkgrp.HighMarkerColor);
if (spkgrp.LowMarkerColor != null) this.LowMarkerColor.FromLowMarkerColor(spkgrp.LowMarkerColor);
int index;
string sRef = string.Empty;
string sWorksheetName = string.Empty;
int iStartRowIndex = -1;
int iStartColumnIndex = -1;
int iEndRowIndex = -1;
int iEndColumnIndex = -1;
if (spkgrp.Formula != null)
{
sRef = spkgrp.Formula.Text;
index = sRef.IndexOf("!");
if (index >= 0)
{
this.DateWorksheetName = sRef.Substring(0, index);
sRef = sRef.Substring(index + 1);
}
index = sRef.LastIndexOf(":");
if (index >= 0)
{
if (!SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
{
iStartRowIndex = -1;
iStartColumnIndex = -1;
iEndRowIndex = -1;
iEndColumnIndex = -1;
}
if (iStartRowIndex > 0 && iStartColumnIndex > 0 && iEndRowIndex > 0 && iEndColumnIndex > 0)
{
this.DateStartRowIndex = iStartRowIndex;
this.DateStartColumnIndex = iStartColumnIndex;
this.DateEndRowIndex = iEndRowIndex;
this.DateEndColumnIndex = iEndColumnIndex;
this.DateAxis = true;
}
else
{
this.DateStartRowIndex = 1;
this.DateStartColumnIndex = 1;
this.DateEndRowIndex = 1;
this.DateEndColumnIndex = 1;
this.DateAxis = false;
}
}
else
{
if (!SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
iStartRowIndex = -1;
iStartColumnIndex = -1;
}
if (iStartRowIndex > 0 && iStartColumnIndex > 0)
{
this.DateStartRowIndex = iStartRowIndex;
this.DateStartColumnIndex = iStartColumnIndex;
this.DateEndRowIndex = iStartRowIndex;
this.DateEndColumnIndex = iStartColumnIndex;
this.DateAxis = true;
}
else
{
this.DateStartRowIndex = 1;
this.DateStartColumnIndex = 1;
this.DateEndRowIndex = 1;
this.DateEndColumnIndex = 1;
this.DateAxis = false;
}
}
}
if (spkgrp.Sparklines != null)
{
X14.Sparkline spkline;
SLSparkline spk;
foreach (var child in spkgrp.Sparklines.ChildElements)
{
if (child is X14.Sparkline)
{
spkline = (X14.Sparkline)child;
spk = new SLSparkline();
// the formula part contains the data source. Apparently, Excel is fine
// if it's empty. IF IT'S EMPTY THEN DELETE THE WHOLE SPARKLINE!
// Ok, I'm fine now... I'm gonna treat an empty Formula as "invalid".
if (spkline.Formula != null && spkline.ReferenceSequence != null)
{
sRef = spkline.Formula.Text;
index = sRef.IndexOf("!");
if (index >= 0)
{
spk.WorksheetName = sRef.Substring(0, index);
sRef = sRef.Substring(index + 1);
}
index = sRef.LastIndexOf(":");
if (index >= 0)
{
if (!SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex, out iEndRowIndex, out iEndColumnIndex))
{
iStartRowIndex = -1;
iStartColumnIndex = -1;
iEndRowIndex = -1;
iEndColumnIndex = -1;
}
if (iStartRowIndex > 0 && iStartColumnIndex > 0 && iEndRowIndex > 0 && iEndColumnIndex > 0)
{
spk.StartRowIndex = iStartRowIndex;
spk.StartColumnIndex = iStartColumnIndex;
spk.EndRowIndex = iEndRowIndex;
spk.EndColumnIndex = iEndColumnIndex;
}
else
{
spk.StartRowIndex = 1;
spk.StartColumnIndex = 1;
spk.EndRowIndex = 1;
spk.EndColumnIndex = 1;
}
}
else
{
if (!SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
iStartRowIndex = -1;
iStartColumnIndex = -1;
}
if (iStartRowIndex > 0 && iStartColumnIndex > 0)
{
spk.StartRowIndex = iStartRowIndex;
spk.StartColumnIndex = iStartColumnIndex;
spk.EndRowIndex = iStartRowIndex;
spk.EndColumnIndex = iStartColumnIndex;
}
else
{
spk.StartRowIndex = 1;
spk.StartColumnIndex = 1;
spk.EndRowIndex = 1;
spk.EndColumnIndex = 1;
}
}
if (!SLTool.FormatCellReferenceToRowColumnIndex(spkline.ReferenceSequence.Text, out iStartRowIndex, out iStartColumnIndex))
{
iStartRowIndex = -1;
iStartColumnIndex = -1;
}
if (iStartRowIndex > 0 && iStartColumnIndex > 0)
{
spk.LocationRowIndex = iStartRowIndex;
spk.LocationColumnIndex = iStartColumnIndex;
// there are so many things that could possibly go wrong
// that we'll just assume that if the location part is correct,
// we'll just take it...
this.Sparklines.Add(spk.Clone());
}
}
}
}
}
if (spkgrp.ManualMax != null) this.ManualMax = spkgrp.ManualMax.Value;
if (spkgrp.ManualMin != null) this.ManualMin = spkgrp.ManualMin.Value;
if (spkgrp.LineWeight != null) this.LineWeight = (decimal)spkgrp.LineWeight.Value;
if (spkgrp.Type != null) this.Type = spkgrp.Type.Value;
// we're gonna ignore dateAxis because if there's no formula, having it true is useless
if (spkgrp.DisplayEmptyCellsAs != null) this.ShowEmptyCellsAs = spkgrp.DisplayEmptyCellsAs.Value;
if (spkgrp.Markers != null) this.ShowMarkers = spkgrp.Markers.Value;
if (spkgrp.High != null) this.ShowHighPoint = spkgrp.High.Value;
if (spkgrp.Low != null) this.ShowLowPoint = spkgrp.Low.Value;
if (spkgrp.First != null) this.ShowFirstPoint = spkgrp.First.Value;
if (spkgrp.Last != null) this.ShowLastPoint = spkgrp.Last.Value;
if (spkgrp.Negative != null) this.ShowNegativePoints = spkgrp.Negative.Value;
if (spkgrp.DisplayXAxis != null) this.ShowAxis = spkgrp.DisplayXAxis.Value;
if (spkgrp.DisplayHidden != null) this.ShowHiddenData = spkgrp.DisplayHidden.Value;
if (spkgrp.MinAxisType != null) this.MinAxisType = spkgrp.MinAxisType.Value;
if (spkgrp.MaxAxisType != null) this.MaxAxisType = spkgrp.MaxAxisType.Value;
if (spkgrp.RightToLeft != null) this.RightToLeft = spkgrp.RightToLeft.Value;
}
internal void FromDifferentialType(X14.DifferentialType dt)
{
this.SetAllNull();
List<System.Drawing.Color> listempty = new List<System.Drawing.Color>();
if (dt.Font != null)
{
HasFont = true;
this.fontReal = new SLFont(SLConstants.OfficeThemeMajorLatinFont, SLConstants.OfficeThemeMinorLatinFont, listempty, listempty);
this.fontReal.FromFont(dt.Font);
}
if (dt.NumberingFormat != null)
{
HasNumberingFormat = true;
this.nfFormatCode = new SLNumberingFormat();
this.nfFormatCode.FromNumberingFormat(dt.NumberingFormat);
}
if (dt.Fill != null)
{
HasFill = true;
this.fillReal = new SLFill(listempty, listempty);
this.fillReal.FromFill(dt.Fill);
}
if (dt.Alignment != null)
{
HasAlignment = true;
this.alignReal = new SLAlignment();
this.alignReal.FromAlignment(dt.Alignment);
}
if (dt.Border != null)
{
HasBorder = true;
this.borderReal = new SLBorder(listempty, listempty);
this.borderReal.FromBorder(dt.Border);
}
if (dt.Protection != null)
{
HasProtection = true;
this.protectionReal = new SLProtection();
this.protectionReal.FromProtection(dt.Protection);
}
Sync();
}
internal void FromConditionalFormattingRule(X14.ConditionalFormattingRule cfr)
{
this.SetAllNull();
if (cfr.Type != null) this.Type = cfr.Type.Value;
if (cfr.Priority != null) 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.StandardDeviation != null) this.StandardDeviation = cfr.StandardDeviation.Value;
if (cfr.EqualAverage != null) this.EqualAverage = cfr.EqualAverage.Value;
if (cfr.ActivePresent != null) this.ActivePresent = cfr.ActivePresent.Value;
if (cfr.Id != null) this.Id = cfr.Id.Value;
using (OpenXmlReader oxr = OpenXmlReader.Create(cfr))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Excel.Formula))
{
this.Formulas.Add((Excel.Formula)oxr.LoadCurrentElement().CloneNode(true));
}
else if (oxr.ElementType == typeof(X14.ColorScale))
{
this.ColorScale = new SLColorScale2010();
this.ColorScale.FromColorScale((X14.ColorScale)oxr.LoadCurrentElement());
this.HasColorScale = true;
}
else if (oxr.ElementType == typeof(X14.DataBar))
{
this.DataBar = new SLDataBar2010();
this.DataBar.FromDataBar((X14.DataBar)oxr.LoadCurrentElement());
this.HasDataBar = true;
}
else if (oxr.ElementType == typeof(X14.IconSet))
{
this.IconSet = new SLIconSet2010();
this.IconSet.FromIconSet((X14.IconSet)oxr.LoadCurrentElement());
this.HasIconSet = true;
}
else if (oxr.ElementType == typeof(X14.DifferentialType))
{
this.DifferentialType = new SLDifferentialFormat();
this.DifferentialType.FromDifferentialType((X14.DifferentialType)oxr.LoadCurrentElement());
this.HasDifferentialType = true;
}
}
}
}
internal void FromIconSet(X14.IconSet ics)
{
this.SetAllNull();
if (ics.IconSetTypes != null) this.IconSetType = ics.IconSetTypes.Value;
if (ics.ShowValue != null) this.ShowValue = ics.ShowValue.Value;
if (ics.Percent != null) this.Percent = ics.Percent.Value;
if (ics.Reverse != null) this.Reverse = ics.Reverse.Value;
using (OpenXmlReader oxr = OpenXmlReader.Create(ics))
{
SLConditionalFormattingValueObject2010 cfvo;
SLConditionalFormattingIcon2010 cfi;
while (oxr.Read())
{
if (oxr.ElementType == typeof(X14.ConditionalFormattingValueObject))
{
cfvo = new SLConditionalFormattingValueObject2010();
cfvo.FromConditionalFormattingValueObject((X14.ConditionalFormattingValueObject)oxr.LoadCurrentElement());
this.Cfvos.Add(cfvo);
}
else if (oxr.ElementType == typeof(X14.ConditionalFormattingIcon))
{
cfi = new SLConditionalFormattingIcon2010();
cfi.FromConditionalFormattingIcon((X14.ConditionalFormattingIcon)oxr.LoadCurrentElement());
this.CustomIcons.Add(cfi);
}
}
}
}
