private void ValidateGroup(ExcelSparklineGroup group)
{
Assert.AreEqual(SparklineType.Stacked, group.Type);
Assert.AreEqual(1000.23, group.ManualMax);
Assert.AreEqual(15.5, group.ManualMin);
Assert.AreEqual(1.5, group.LineWeight);
Assert.IsTrue(group.DateAxis);
Assert.AreEqual(DispBlanksAs.Span, group.DisplayEmptyCellsAs);
Assert.IsTrue(group.Markers);
Assert.IsTrue(group.High);
Assert.IsTrue(group.Low);
Assert.IsTrue(group.First);
Assert.IsTrue(group.Last);
Assert.IsTrue(group.Negative);
Assert.IsTrue(group.DisplayXAxis);
Assert.IsTrue(group.DisplayHidden);
Assert.AreEqual(SparklineAxisMinMax.Custom, group.MinAxisType);
Assert.AreEqual(SparklineAxisMinMax.Group, group.MaxAxisType);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xAA375052)), group.ColorSeries);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xAB375052)), group.ColorNegative);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xAC375052)), group.ColorAxis);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xAD375052)), group.ColorMarkers);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xAE375052)), group.ColorFirst);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xAF375052)), group.ColorLast);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xBA375052)), group.ColorHigh);
Assert.AreEqual(Color.FromArgb(unchecked ((int)0xBB375052)), group.ColorLow);
Assert.IsTrue(group.RightToLeft);
Assert.AreEqual(2, group.Sparklines.Count);
Assert.AreEqual("A1:B2", group.Sparklines.Last().Formula.Address);
Assert.AreEqual("G9", group.Sparklines.Last().HostCell.Address);
}
public void SaveSparklines()
{
FileInfo newFile = new FileInfo(Path.GetTempFileName());
if (newFile.Exists)
{
newFile.Delete();
}
using (var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("Sheet1");
var group = new ExcelSparklineGroup(sheet, sheet.NameSpaceManager);
var sparkline = new ExcelSparkline(group, sheet.NameSpaceManager)
{
Formula = new ExcelAddress("G1:G20"), HostCell = new ExcelAddress("B2")
};
group.Sparklines.Add(sparkline);
sheet.SparklineGroups.SparklineGroups.Add(group);
package.SaveAs(newFile);
}
using (var package = new ExcelPackage(newFile))
{
var sparklineGroup = package.Workbook.Worksheets.First().SparklineGroups.SparklineGroups.First();
var sparkline = sparklineGroup.Sparklines.First();
Assert.AreEqual("B2", sparkline.HostCell.Address);
Assert.AreEqual("G1:G20", sparkline.Formula.Address);
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, XlsxMiniChart miniChart)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
ExcelWorksheet ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
// Create Minichart
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
ExcelAddressBase dataddress = miniChart.ChartRanges.Data.ToEppExcelAddress();
ExcelSparklineGroup sparkline = ws.SparklineGroups.Add(miniChart.ChartType.Active.ToEppeSparklineType(), ws.Cells[locationAddress.ToString()], ws.Cells[dataddress.ToString()]);
sparkline.DisplayHidden = miniChart.DisplayHidden == YesNo.Yes;
sparkline.ColorSeries.SetColor(miniChart.ChartType.GetMiniChartSerieColor());
sparkline.DisplayEmptyCellsAs = miniChart.EmptyValueAs.ToEppeDisplayBlanksAs();
// Minichart Size
int offsetY = miniChart.ChartSize.VerticalCells == 1 ? 0 : miniChart.ChartSize.VerticalCells - 1;
int offsetX = miniChart.ChartSize.HorizontalCells == 1 ? 0 : miniChart.ChartSize.HorizontalCells - 1;
ExcelCellBase miniChartSizeAddress = location.Expand(miniChart.ChartSize);
ExcelRange miniChartCell = ws.Cells[miniChartSizeAddress.ToString()];
if (offsetX >= 1 || offsetY >= 1)
{
miniChartCell.Merge = true;
}
// Axes
// Horizontal axis
sparkline.DateAxisRange = null;
if (miniChart.ChartAxes.Horizontal.Type == MiniChartHorizontalAxisType.Date)
{
ExcelAddressBase axisRangeType = miniChart.ChartRanges.Axis.ToEppExcelAddress();
sparkline.DateAxisRange = ws.Cells[axisRangeType.ToString()];
}
sparkline.RightToLeft = miniChart.ChartAxes.Horizontal.RightToLeft == YesNo.Yes;
if (miniChart.ChartAxes.Horizontal.Show == YesNo.Yes)
{
sparkline.DisplayXAxis = true;
sparkline.ColorAxis.SetColor(miniChart.ChartAxes.Horizontal.GetColor());
}
// Vertical axis
bool maxVerticalAxisIsAuto = string.IsNullOrEmpty(miniChart.ChartAxes.Vertical.Max) || miniChart.ChartAxes.Vertical.Max.Equals("Automatic", StringComparison.OrdinalIgnoreCase);
sparkline.MaxAxisType = maxVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
bool minVerticalAxisIsAuto = string.IsNullOrEmpty(miniChart.ChartAxes.Vertical.Min) || miniChart.ChartAxes.Vertical.Min.Equals("Automatic", StringComparison.OrdinalIgnoreCase);
sparkline.MinAxisType = minVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
if (!maxVerticalAxisIsAuto)
{
sparkline.ManualMax = double.Parse(miniChart.ChartAxes.Vertical.Max);
}
if (!minVerticalAxisIsAuto)
{
sparkline.ManualMin = double.Parse(miniChart.ChartAxes.Vertical.Min);
}
// Points
switch (miniChart.ChartType.Active)
{
case MiniChartType.Column:
if (!miniChart.ChartType.Column.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.ChartType.Column.Points.Low.GetColor());
}
if (!miniChart.ChartType.Column.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.ChartType.Column.Points.First.GetColor());
}
if (!miniChart.ChartType.Column.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.ChartType.Column.Points.High.GetColor());
}
if (!miniChart.ChartType.Column.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.ChartType.Column.Points.Last.GetColor());
}
if (!miniChart.ChartType.Column.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.ChartType.Column.Points.Negative.GetColor());
}
break;
case MiniChartType.Line:
sparkline.LineWidth = double.Parse(miniChart.ChartType.Line.Serie.Width);
if (!miniChart.ChartType.Line.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.ChartType.Line.Points.Low.GetColor());
}
if (!miniChart.ChartType.Line.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.ChartType.Line.Points.First.GetColor());
}
if (!miniChart.ChartType.Line.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.ChartType.Line.Points.High.GetColor());
}
if (!miniChart.ChartType.Line.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.ChartType.Line.Points.Last.GetColor());
}
if (!miniChart.ChartType.Line.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(
miniChart.ChartType.Line.Points.Negative.GetColor());
}
if (!miniChart.ChartType.Line.Points.Markers.IsDefault)
{
sparkline.Markers = true;
sparkline.ColorNegative.SetColor(miniChart.ChartType.Line.Points.Markers.GetColor());
}
break;
case MiniChartType.WinLoss:
if (!miniChart.ChartType.WinLoss.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.ChartType.WinLoss.Points.Low.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.ChartType.WinLoss.Points.First.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.ChartType.WinLoss.Points.High.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.ChartType.WinLoss.Points.Last.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.ChartType.WinLoss.Points.Negative.GetColor());
}
break;
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
