internal SLSparkline Clone()
{
SLSparkline spk = new SLSparkline();
spk.WorksheetName = this.WorksheetName;
spk.StartRowIndex = this.StartRowIndex;
spk.StartColumnIndex = this.StartColumnIndex;
spk.EndRowIndex = this.EndRowIndex;
spk.EndColumnIndex = this.EndColumnIndex;
spk.LocationRowIndex = this.LocationRowIndex;
spk.LocationColumnIndex = this.LocationColumnIndex;
return spk;
}
internal SLSparkline Clone()
{
SLSparkline spk = new SLSparkline();
spk.WorksheetName = this.WorksheetName;
spk.StartRowIndex = this.StartRowIndex;
spk.StartColumnIndex = this.StartColumnIndex;
spk.EndRowIndex = this.EndRowIndex;
spk.EndColumnIndex = this.EndColumnIndex;
spk.LocationRowIndex = this.LocationRowIndex;
spk.LocationColumnIndex = this.LocationColumnIndex;
return(spk);
}
/// <summary>
/// Set the location of the sparkline group given row and column indices of opposite cells in a cell range.
/// Note that the cell range has to be a 1-dimensional vector, meaning it's either a single row or single column.
/// Note also that the length of the vector must be equal to either the number of rows or number of columns in the data source range.
/// </summary>
/// <param name="StartRowIndex">The row index of the start row. This is typically the top row.</param>
/// <param name="StartColumnIndex">The column index of the start column. This is typically the left-most column.</param>
/// <param name="EndRowIndex">The row index of the end row. This is typically the bottom row.</param>
/// <param name="EndColumnIndex">The column index of the end column. This is typically the right-most column.</param>
/// <param name="RowsAsDataSeries">True if the data source has its series in rows. False if it's in columns. This only comes into play if the data source has the same number of rows as its columns.</param>
public void SetLocation(int StartRowIndex, int StartColumnIndex, int EndRowIndex, int EndColumnIndex, bool RowsAsDataSeries)
{
int iStartRowIndex = 1, iEndRowIndex = 1, iStartColumnIndex = 1, iEndColumnIndex = 1;
if (StartRowIndex < EndRowIndex)
{
iStartRowIndex = StartRowIndex;
iEndRowIndex = EndRowIndex;
}
else
{
iStartRowIndex = EndRowIndex;
iEndRowIndex = StartRowIndex;
}
if (StartColumnIndex < EndColumnIndex)
{
iStartColumnIndex = StartColumnIndex;
iEndColumnIndex = EndColumnIndex;
}
else
{
iStartColumnIndex = EndColumnIndex;
iEndColumnIndex = StartColumnIndex;
}
if (iStartRowIndex < 1) iStartRowIndex = 1;
if (iStartColumnIndex < 1) iStartColumnIndex = 1;
if (iEndRowIndex > SLConstants.RowLimit) iEndRowIndex = SLConstants.RowLimit;
if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;
int iLocationRowDimension = iEndRowIndex - iStartRowIndex + 1;
int iLocationColumnDimension = iEndColumnIndex - iStartColumnIndex + 1;
// either the location row or column dimension must be 1. One of them has to be 1
// for the location range to be valid. If there's an error, we'll just "shorten"
// the smaller of the 2 dimensions. The Excel user interface has error dialog boxes
// to warn the user. We don't have this luxury, so we'll make the best of things...
if (iLocationRowDimension != 1 && iLocationColumnDimension != 1)
{
if (iLocationRowDimension < iLocationColumnDimension)
{
iEndRowIndex = iStartRowIndex;
iLocationRowDimension = 1;
}
else
{
iEndColumnIndex = iStartColumnIndex;
iLocationColumnDimension = 1;
}
}
int iDataRowDimension = this.EndRowIndex - this.StartRowIndex + 1;
int iDataColumnDimension = this.EndColumnIndex - this.StartColumnIndex + 1;
bool bRowsAsDataSeries = true;
int iMaxLocationDimension = 1;
if (iLocationRowDimension >= iLocationColumnDimension)
{
iMaxLocationDimension = iLocationRowDimension;
bRowsAsDataSeries = true;
}
else
{
iMaxLocationDimension = iLocationColumnDimension;
bRowsAsDataSeries = false;
}
// If the data source has the same number of rows as its columns, the "default" is to use rows as data series,
// unless otherwise stated. This is the "otherwise stated" part.
if (iDataRowDimension == iDataColumnDimension)
{
bRowsAsDataSeries = RowsAsDataSeries;
}
// Furthermore, the "length" of the location range has to be either equal to
// the data source range's row dimension or column dimension.
// This is how Excel determines whether to use rows or columns as data series.
int index;
SLSparkline spk;
if (iMaxLocationDimension == iDataRowDimension)
{
// sparkline data in row
for (index = 0; index < iMaxLocationDimension; ++index)
{
spk = new SLSparkline();
spk.WorksheetName = this.WorksheetName;
spk.StartRowIndex = index + this.StartRowIndex;
spk.EndRowIndex = spk.StartRowIndex;
spk.StartColumnIndex = this.StartColumnIndex;
spk.EndColumnIndex = this.EndColumnIndex;
if (bRowsAsDataSeries)
{
spk.LocationRowIndex = index + iStartRowIndex;
spk.LocationColumnIndex = iStartColumnIndex;
}
else
{
spk.LocationRowIndex = iStartRowIndex;
spk.LocationColumnIndex = index + iStartColumnIndex;
}
this.Sparklines.Add(spk);
}
}
else if (iMaxLocationDimension == iDataColumnDimension)
{
// sparkline data in column
for (index = 0; index < iMaxLocationDimension; ++index)
{
spk = new SLSparkline();
spk.WorksheetName = this.WorksheetName;
spk.StartRowIndex = this.StartRowIndex;
spk.EndRowIndex = this.EndRowIndex;
spk.StartColumnIndex = index + this.StartColumnIndex;
spk.EndColumnIndex = spk.StartColumnIndex;
if (bRowsAsDataSeries)
{
spk.LocationRowIndex = index + iStartRowIndex;
spk.LocationColumnIndex = iStartColumnIndex;
}
else
{
spk.LocationRowIndex = iStartRowIndex;
spk.LocationColumnIndex = index + iStartColumnIndex;
}
this.Sparklines.Add(spk);
}
}
else
{
// don't do anything? The location range is invalid to the point
// where I don't know what to do. So just don't do anything...
}
}
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;
}
