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)); }