public void AddLineOrColumnChart()
{
var excelManager = new JlgCommon.ExcelManager.ExcelManager();
var chartName = "Custom line chart";
var lineChart = new LineOrColumnChartForExcel();
lineChart.ChartName = chartName;
lineChart.Series.Add(new SerieForExcel()
{
Name = "Delevopment",
Values = new List <StringDoublePair>()
{
new StringDoublePair("x1", 3.2),
new StringDoublePair("x2", 1.1),
new StringDoublePair("x3", 3.9),
new StringDoublePair("x4", 8.2),
new StringDoublePair("x5", 4.6),
}
});
lineChart.Series.Add(new SerieForExcel()
{
Name = "Maintenance",
Values = new List <StringDoublePair>()
{
new StringDoublePair("x1", 12.2),
new StringDoublePair("x2", 9.1),
new StringDoublePair("x3", 1.9),
new StringDoublePair("x4", 8.2),
new StringDoublePair("x5", 3.6),
}
});
lineChart.OtherInfo.Add(new StringDoublePair("Hours", 234));
lineChart.OtherInfo.Add(new StringDoublePair("Other", 5.4));
excelManager.Writer.AddLineOrColumnChart(lineChart, 1, 100, 100);
Assert.AreEqual(chartName, excelManager.Reader.GetCellValueAsString(1, 1));
Assert.AreEqual("x1", excelManager.Reader.GetCellValueAsString(2, 2));
Assert.AreEqual("x2", excelManager.Reader.GetCellValueAsString(2, 3));
Assert.AreEqual("x3", excelManager.Reader.GetCellValueAsString(2, 4));
Assert.AreEqual("x4", excelManager.Reader.GetCellValueAsString(2, 5));
Assert.AreEqual("x5", excelManager.Reader.GetCellValueAsString(2, 6));
Assert.AreEqual("Delevopment", excelManager.Reader.GetCellValueAsString(3, 1));
Assert.AreEqual("Maintenance", excelManager.Reader.GetCellValueAsString(4, 1));
Assert.AreEqual(3.2, excelManager.Reader.GetCellValueAsDouble(3, 2));
Assert.AreEqual(1.1, excelManager.Reader.GetCellValueAsDouble(3, 3));
Assert.AreEqual(3.9, excelManager.Reader.GetCellValueAsDouble(3, 4));
Assert.AreEqual(8.2, excelManager.Reader.GetCellValueAsDouble(3, 5));
Assert.AreEqual(4.6, excelManager.Reader.GetCellValueAsDouble(3, 6));
Assert.AreEqual(12.2, excelManager.Reader.GetCellValueAsDouble(4, 2));
Assert.AreEqual(9.1, excelManager.Reader.GetCellValueAsDouble(4, 3));
Assert.AreEqual(1.9, excelManager.Reader.GetCellValueAsDouble(4, 4));
Assert.AreEqual(8.2, excelManager.Reader.GetCellValueAsDouble(4, 5));
Assert.AreEqual(3.6, excelManager.Reader.GetCellValueAsDouble(4, 6));
Assert.AreEqual("Hours", excelManager.Reader.GetCellValueAsString(5, 1));
Assert.AreEqual(234, excelManager.Reader.GetCellValueAsDouble(5, 2));
Assert.AreEqual("Other", excelManager.Reader.GetCellValueAsString(6, 1));
Assert.AreEqual(5.4, excelManager.Reader.GetCellValueAsDouble(6, 2));
}
public int AddLineOrColumnChart(LineOrColumnChartForExcel lineChart, int startingRow, double width, double height, bool isMixt = false, int startingColumn = 1)
{
if (lineChart.Series == null ||
lineChart.Series.Count == 0)
{
return(startingRow + 1);
}
if (!string.IsNullOrEmpty(lineChart.ChartName))
{
_excelDocument.SetCellValue(startingRow, startingColumn, lineChart.ChartName);
_excelDocument.SetCellBold(startingRow, startingColumn);
_excelDocument.SetCellItalic(startingRow, startingColumn);
startingRow++;
}
var xAxis = lineChart.Series[0].Values;
for (int i = 0; i < xAxis.Count; i++)
{
var column = i + startingColumn + 1;
_excelDocument.SetCellValue(startingRow, column, xAxis[i].Name);
_excelDocument.SetCellBold(startingRow, column);
}
for (int i = 0; i < lineChart.Series.Count; i++)
{
var row = startingRow + 1 + i;
var serie = lineChart.Series[i];
if (serie.HideFromTable)
{
_excelDocument.HideRowButLetChartsSeeIt(row);
}
_excelDocument.SetCellValue(row, startingColumn, serie.Name);
_excelDocument.SetCellBold(row, startingColumn);
for (int j = 0; j < serie.Values.Count; j++)
{
_excelDocument.SetCellValue(row, j + startingColumn + 1, serie.Values[j].Value);
if (!string.IsNullOrEmpty(serie.CellsFormatCode))
{
_excelDocument.SetCellFormatCode(row, j + startingColumn + 1, serie.CellsFormatCode);
}
else if (!string.IsNullOrEmpty(lineChart.CellsFormatCode))
{
_excelDocument.SetCellFormatCode(row, j + startingColumn + 1, lineChart.CellsFormatCode);
}
_excelDocument.SetCellHorizoltalAllign(row, j + startingColumn + 1, HorizontalAlignmentType.Left);
}
}
for (int i = 0; i < lineChart.OtherInfo.Count; i++)
{
var othInfo = lineChart.OtherInfo[i];
_excelDocument.SetCellValue(startingRow + lineChart.Series.Count + i + 1, startingColumn, othInfo.Name);
_excelDocument.SetCellItalic(startingRow + lineChart.Series.Count + i + 1, startingColumn);
_excelDocument.SetCellValue(startingRow + lineChart.Series.Count + i + 1, startingColumn + 1, othInfo.Value);
_excelDocument.SetCellItalic(startingRow + lineChart.Series.Count + i + 1, startingColumn + 1);
}
var chartCreateOptions = new SLCreateChartOptions();
chartCreateOptions.RowsAsDataSeries = true;
var chart = _excelDocument.CreateChart(startingRow, startingColumn, startingRow + lineChart.Series.Count, xAxis.Count + startingColumn, chartCreateOptions);
switch (lineChart.ChartType)
{
case ChartType.ClusteredColumn:
chart.SetChartType(SLColumnChartType.ClusteredColumn);
break;
case ChartType.LineWithMarkers:
chart.SetChartType(SLLineChartType.LineWithMarkers);
break;
}
if (isMixt)
{
chart.SecondaryValueAxis.ShowMajorGridlines = false;
chart.SecondaryValueAxis.TickLabelPosition = TickLabelPositionValues.None;
}
for (int i = 0; i < lineChart.Series.Count; i++)
{
var serie = lineChart.Series[i];
if (serie.Color.HasValue)
{
chart.SetSerieColor(i + 1, serie.Color.Value, serie.ColorTransparencyPercent);
}
if (serie.MarkerStyle.HasValue)
{
chart.SetMarkerForChartSerie(i + 1, serie.MarkerStyle.Value);
}
if (isMixt)
{
if (!serie.IsColumn)
{
chart.PlotDataSeriesAsSecondaryLineChart(i + 1, SLChartDataDisplayType.Normal, true);
chart.SetMarkerForChartSerie(i + 1, MarkerStyleType.Circle);
}
}
}
chart.SetChartPosition(startingRow + 1 + lineChart.Series.Count + lineChart.OtherInfo.Count, startingColumn - 1, startingRow + 1 + lineChart.Series.Count + height, startingColumn + width - 1);
foreach (var lineKvp in lineChart.LabelsForSeriesAndDataPoints)
{
foreach (var pointKvp in lineKvp.Value)
{
var dloptions = chart.CreateDataLabelOptions();
dloptions.FormatCode = pointKvp.Value;
dloptions.ShowValue = true;
dloptions.SourceLinked = false;
dloptions.SetLabelPosition(DataLabelPositionValues.Top);
chart.SetDataLabelOptions(lineKvp.Key, pointKvp.Key, dloptions);
}
}
if (!lineChart.ShowMajorGridlines)
{
chart.PrimaryValueAxis.ShowMajorGridlines = false;
chart.PrimaryValueAxis.TickLabelPosition = TickLabelPositionValues.None;
}
else
{
chart.PrimaryValueAxis.Maximum = lineChart.MaxValueAxis;
if (!string.IsNullOrEmpty(lineChart.PrimaryValueAxisFormatCode))
{
chart.PrimaryValueAxis.FormatCode = lineChart.PrimaryValueAxisFormatCode;
}
}
_excelDocument.InsertChart(chart);
return(startingRow + 1 + lineChart.Series.Count + Convert.ToInt32(height));
}
