public SpreadsheetGear.IWorkbook DrawChart(List <ChartModel> columns, List <DataTable> allitem, string sheetname, bool OpenSheet = false, bool cellText = false, int cellInt = 0) { //创建新的workbook SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook(); SpreadsheetGear.IWorksheets worksheets = workbook.Worksheets; IWorksheet worksheet = worksheets[0]; worksheet.Name = sheetname + 1; IWorksheetWindowInfo windowInfo = worksheet.WindowInfo; IShapes shapes = worksheet.Shapes; int high = 1; int Rowhigh = 17; double top = 0.5; double bottom = 15.5; for (int a = 0; a < columns.Count; a++) { int row = 0; int column = 0; int piecol = 0; string site = null; if (columns[a].series.Count > 0) { row = columns[a].xAxis.Count; column = columns[a].series.Count; } if (columns[a].pieseries.Count > 0) { piecol = columns[a].pieseries.Count; } //+((17 + row) * j); string[,] arrayChart = null; if ((columns[a].charttype.ToString()) == "Pie") { site = "A" + Rowhigh + ":B" + (piecol + Rowhigh); arrayChart = new string[piecol + 1, 2]; for (int j = 0; j < (piecol + 1); j++) { if (j == 0) { if (!string.IsNullOrEmpty(columns[a].title)) { arrayChart[0, 0] = ""; arrayChart[0, 1] = columns[a].title; } else { arrayChart[0, 0] = ""; arrayChart[0, 1] = ""; } } else { for (int i = 0; i < 2; i++) { if (i == 0) { arrayChart[j, i] = columns[a].pieseries[j - 1].name; } else { arrayChart[j, i] = columns[a].pieseries[j - 1].y.ToString(); } } } } } else if ((columns[a].charttype.ToString()) == "Radar") { if (row < 3) { row = 3; } string aaa = column / 26 == 0 ? ((char)((65 + column))).ToString() : "" + (char)((64 + column / 26)) + (char)((65 + column % 26)); site = "A" + Rowhigh + ":" + aaa + (row + Rowhigh); if (column > 25) { arrayChart = new string[(row + 1), (column + 1)]; } for (int j = 0; j < row + 1; j++) { if (j == 0) { for (int i = 0; i < column + 1; i++) { if (i == 0) { arrayChart[j, i] = ""; } else { arrayChart[j, i] = columns[a].series[i - 1].name; } } } else { for (int k = 0; k < column + 1; k++) { if (k == 0) { if (j > columns[a].xAxis.Count) { arrayChart[j, k] = ""; } else { arrayChart[j, k] = columns[a].xAxis[j - 1]; } } else { if (j > columns[a].xAxis.Count) { arrayChart[j, k] = ""; } else { List <double> adata = columns[a].series[k - 1].data; arrayChart[j, k] = adata[j - 1].ToString(); } } } } } } else { string aaa = column / 26 == 0 ? ((char)((65 + column))).ToString() : "" + (char)((64 + column / 26)) + (char)((65 + column % 26)); site = "A" + Rowhigh + ":" + aaa + (row + Rowhigh); arrayChart = new string[(row + 1), (column + 1)]; for (int j = 0; j < row + 1; j++) { if (j == 0) { for (int i = 0; i < column + 1; i++) { if (i == 0) { arrayChart[j, i] = ""; } else { arrayChart[j, i] = columns[a].series[i - 1].name; } } } else { for (int k = 0; k < column + 1; k++) { if (k == 0) { arrayChart[j, k] = columns[a].xAxis[j - 1]; } else { List <double> adata = columns[a].series[k - 1].data; arrayChart[j, k] = adata[j - 1].ToString(); } } } } } double chartleft = windowInfo.ColumnToPoints(0.15); double charttop = windowInfo.RowToPoints(top); double chartright = windowInfo.ColumnToPoints(7.85); double chartbottom = windowInfo.RowToPoints(bottom); SpreadsheetGear.Charts.IChart chart = shapes.AddChart(chartleft, charttop, chartright - chartleft, chartbottom - charttop).Chart; IRange source = worksheet.Cells[site]; source.Value = arrayChart; chart.SetSourceData(source, RowCol.Columns); if ((columns[a].charttype.ToString()) == "Column") { chart.ChartType = ChartType.ColumnClustered; } else if ((columns[a].charttype.ToString()) == "Line") { chart.ChartType = ChartType.Line; } else if ((columns[a].charttype.ToString()) == "Pie") { chart.ChartType = ChartType.Pie; ISeries seriesTotal = chart.SeriesCollection[0]; seriesTotal.HasDataLabels = true; seriesTotal.DataLabels.ShowPercentage = true; seriesTotal.DataLabels.ShowValue = false; } else if ((columns[a].charttype.ToString()) == "Radar") { chart.ChartType = ChartType.Radar; } else if ((columns[a].charttype.ToString()) == "Bar") { chart.ChartType = ChartType.BarClustered; } else if ((columns[a].charttype.ToString()) == "BarStacked") { chart.ChartType = ChartType.BarStacked; } if (!string.IsNullOrEmpty(columns[a].title)) { chart.HasTitle = true; chart.ChartTitle.Text = columns[a].title; chart.ChartTitle.Font.Size = 12; } if ((columns[a].charttype.ToString()) == "Pie") { Rowhigh += 17 + piecol; top += 17 + piecol; bottom += 17 + piecol; high += 17 + piecol; } else { Rowhigh += 17 + row; top += 17 + row; bottom += 17 + row; high += 17 + row; } } //数据列表处理 if (allitem.Count > 0) { if (OpenSheet) { for (int c = 0; c < allitem.Count; c++) { if (columns.Count > 0) { worksheets.Add(); worksheet = worksheets[worksheets.Count - 1]; worksheet.Name = sheetname + worksheets.Count; } else { if (c > 0) { worksheets.Add(); worksheet = workbook.Worksheets[worksheets.Count - 1]; worksheet.Name = sheetname + worksheets.Count; } } string allsite = "A1"; IRange cell = worksheet.Cells[allsite]; //cell.CopyFromDataTable(allitem[c], SetDataFlags.None); cell.CopyFromDataTable(allitem[c], SetDataFlags.AllText); worksheet.UsedRange.Columns.AutoFit(); //high += allitem[c].Rows.Count; } } else { if (columns.Count > 0) { high += 2; } for (int b = 0; b < allitem.Count; b++) { if (b > 0) { high += 1; } string allsite = "A" + high; IRange cell = worksheet.Cells[allsite]; //cell.CopyFromDataTable(allitem[b], SetDataFlags.None); cell.CopyFromDataTable(allitem[b], SetDataFlags.AllText); if (cellText) { for (int i = 1; i <= allitem[0].Rows.Count; i++) { // cell[i, cellInt].NumberFormat = "@"; cell[i, cellInt].NumberFormat = "@"; } } worksheet.UsedRange.Columns.AutoFit(); high += allitem[b].Rows.Count; } } } return(workbook); }
public SpreadsheetGear.IWorkbook BuildSheet(List <SheetModel> sheets) { //创建新的workbook SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook(); SpreadsheetGear.IWorksheets worksheets = workbook.Worksheets; for (int i = 0; i < sheets.Count; i++) { int high = 1; if (i > 0) { worksheets.Add(); } IWorksheet worksheet = worksheets[i]; worksheet.Name = sheets[i].SheetName == null ? "sheet" + (i + 1) : sheets[i].SheetName; IWorksheetWindowInfo windowInfo = worksheet.WindowInfo; IShapes shapes = worksheet.Shapes; List <DataModel> dataList = sheets[i].DataModels; for (int j = 0; j < dataList.Count; j++) { if (dataList[j].datatype.ToString() != "DataTable") { high += 16; string allsite = "A" + high; IRange cell = worksheet.Cells[allsite]; cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.None); worksheet.UsedRange.Columns.AutoFit(); double top = high - 16.5; double bottom = high - 1.5; double chartleft = windowInfo.ColumnToPoints(0.15); double charttop = windowInfo.RowToPoints(top); double chartright = windowInfo.ColumnToPoints(7.85); double chartbottom = windowInfo.RowToPoints(bottom); SpreadsheetGear.Charts.IChart chart = shapes.AddChart(chartleft, charttop, chartright - chartleft, chartbottom - charttop).Chart; string site = ((char)(65 + dataList[j].StartPoint.x)).ToString() + (high + dataList[j].StartPoint.y) + ":" + ((char)(64 + dataList[j].ColumnCount + dataList[j].StartPoint.x)).ToString() + (high + dataList[j].StartPoint.y + dataList[j].RowCount); IRange source = worksheet.Cells[site]; chart.SetSourceData(source, RowCol.Columns); switch (dataList[j].datatype.ToString()) { case "Column": chart.ChartType = ChartType.ColumnClustered; break; case "Line": chart.ChartType = ChartType.Line; break; case "Radar": chart.ChartType = ChartType.Radar; break; case "Bar": chart.ChartType = ChartType.BarClustered; break; case "BarStacked": chart.ChartType = ChartType.BarStacked; break; case "Pie": chart.ChartType = ChartType.Pie; ISeries seriesTotal = chart.SeriesCollection[0]; seriesTotal.HasDataLabels = true; seriesTotal.DataLabels.ShowPercentage = true; seriesTotal.DataLabels.ShowValue = false; break; } high += dataList[j].Dataseries.Rows.Count + 1 + sheets[i].space; } else { string allsite = "A" + high; IRange cell = worksheet.Cells[allsite]; //cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.None); cell.CopyFromDataTable(dataList[j].Dataseries, SetDataFlags.AllText); worksheet.UsedRange.Columns.AutoFit(); high += dataList[j].Dataseries.Rows.Count + 1 + sheets[i].space; } } } return(workbook); }
private static void BuildBarGraph(IWorksheet dataWorksheet, BarGraphBE barGraphConfig, Dictionary <string, int> columnNameIndex, string pathNameColumnName) { SpreadsheetGear.IWorkbook workbook = dataWorksheet.Workbook; int columnIdx = -1; int xAxisTargetColumnIdx = -1; string xAxisColumnName = barGraphConfig.XAxis.FromColumnName; List <string> missingColumnNames = new List <string>(); // step 1: find the column we want to target for the XAxis if (!columnNameIndex.TryGetValue(xAxisColumnName, out xAxisTargetColumnIdx)) { missingColumnNames.Add(xAxisColumnName); } // step 2.1: find the columns we want to target for the YAxis Dictionary <int, string> yAxisTargetColIdxs = new Dictionary <int, string>(); foreach (string yAxisColumnName in barGraphConfig.YAxis.FromColumnNames) { if (columnNameIndex.TryGetValue(yAxisColumnName, out columnIdx)) { yAxisTargetColIdxs.Add(columnIdx, yAxisColumnName); } else { missingColumnNames.Add(yAxisColumnName); } } // step 3: find the columns we want to reference for the Gains string pidGainsColumnName = barGraphConfig.Gains?.PIDGains; string followerGainsColumnName = barGraphConfig.Gains?.FollowerGains; string controlModeColumnName = barGraphConfig.Gains?.ControlMode; int pidGainsColumnIdx = -1; int followerGainsColumnIdx = -1; int controlModeColumnIdx = -1; int elapsedDeltaColumnIdx = -1; int targetColumnIdx = -1; int actualColumnIdx = -1; int pathNameColumnIdx = -1; if (!string.IsNullOrEmpty(pidGainsColumnName)) { if (!columnNameIndex.TryGetValue(pidGainsColumnName, out pidGainsColumnIdx)) { //missingColumnNames.Add(pidGainsColumnName); } } if (!string.IsNullOrEmpty(followerGainsColumnName)) { if (!columnNameIndex.TryGetValue(followerGainsColumnName, out followerGainsColumnIdx)) { missingColumnNames.Add(followerGainsColumnName); } } if (!string.IsNullOrEmpty(controlModeColumnName)) { if (!columnNameIndex.TryGetValue(controlModeColumnName, out controlModeColumnIdx)) { //missingColumnNames.Add(controlModeColumnName); } } if (!string.IsNullOrEmpty(barGraphConfig.XAxis.FromColumnName)) { if (!columnNameIndex.TryGetValue(barGraphConfig.XAxis.FromColumnName, out elapsedDeltaColumnIdx)) { missingColumnNames.Add(barGraphConfig.XAxis.FromColumnName); } } if (!string.IsNullOrEmpty(pathNameColumnName)) { if (!columnNameIndex.TryGetValue(pathNameColumnName, out pathNameColumnIdx)) { missingColumnNames.Add(pathNameColumnName); } } // // stop if any were missing if (missingColumnNames.Count > 0) { string errList = String.Join(",", missingColumnNames); throw new ApplicationException($"... Error building graph: [{barGraphConfig.Name}], Expected cols: [{errList}] cannot be found!"); } // Step 4: add a new worksheet to hold the chart IWorksheet chartSheet = workbook.Worksheets.Add(); chartSheet.Name = barGraphConfig.Name; // Step 5.1: time to build the chart SpreadsheetGear.Shapes.IShape chartShape = chartSheet.Shapes.AddChart(1, 1, 500, 500); SpreadsheetGear.Charts.IChart chart = chartShape.Chart; // working variables int lastRowIdx = dataWorksheet.UsedRange.RowCount; IRange xAxisColumn = dataWorksheet.Cells[1, 0, lastRowIdx - 1, 0]; IRange yAxisColumn = null; ISeries chartSeries = null; string seriesName = string.Empty; // Step 5.2: add a chart series for each Y axis column in the config foreach (var kvp in yAxisTargetColIdxs) { seriesName = dataWorksheet.Cells[0, kvp.Key].Text; yAxisColumn = dataWorksheet.Cells[1, kvp.Key, lastRowIdx - 1, kvp.Key]; chartSeries = chart.SeriesCollection.Add(); chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200"; chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200"; switch (barGraphConfig.ChartTypeOverride) { case @"StackedBar": chartSeries.ChartType = ChartType.ColumnStacked; break; default: chartSeries.ChartType = ChartType.ColumnClustered; break; } chartSeries.Name = seriesName; } // Step 5.3: format the chart title chart.HasTitle = true; StringBuilder chartTitle = new StringBuilder(); string pathName = dataWorksheet.Cells[1, pathNameColumnIdx].Text; chartTitle.AppendLine($"{barGraphConfig.Name} | Path: [{pathName}]"); // optional add follower gains only if available if (pidGainsColumnIdx >= 0) { chartTitle.AppendLine($"PID Gains: {GetPIDGains(dataWorksheet, pidGainsColumnIdx, controlModeColumnIdx)}"); } // optional add follower gains only if available if (followerGainsColumnIdx >= 0) { chartTitle.AppendLine($"Follower Gains: {dataWorksheet.Cells[1, followerGainsColumnIdx].Text}"); } chart.ChartTitle.Text = chartTitle.ToString(); chart.ChartTitle.Font.Size = 12; // Step 5.4: format the chart legend chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom; chart.Legend.Font.Bold = true; // Step 5.5: format X & Y Axes IAxis xAxis = chart.Axes[AxisType.Category]; xAxis.HasMinorGridlines = true; xAxis.HasTitle = true; if (chart.ChartType == ChartType.Line) { // this option not valid on xy graphs xAxis.TickMarkSpacing = 100; // 10Msec per step * 100 = gidline every second } IAxisTitle xAxisTitle = xAxis.AxisTitle; xAxisTitle.Text = barGraphConfig.XAxis.AxisTitle; IAxis yAxis = chart.Axes[AxisType.Value, AxisGroup.Primary]; yAxis.HasTitle = true; yAxis.TickLabels.NumberFormat = "General"; yAxis.ReversePlotOrder = barGraphConfig.YAxis.IsYAxisValuesInReverseOrder; if (barGraphConfig.YAxis.MajorUnitOverride.HasValue) { yAxis.MajorUnit = (double)barGraphConfig.YAxis.MajorUnitOverride.Value; } IAxisTitle yAxisTitle = yAxis.AxisTitle; yAxisTitle.Text = barGraphConfig.YAxis.AxisTitle; }
private static void BuildHistogram(IWorksheet dataWorksheet, HistogramBE histogramConfig, Dictionary <string, int> columnNameXref, string pathNameColumnName) { // find sheet we are supposed to insert this one after IWorksheet afterWorkSheet = !(string.IsNullOrEmpty(histogramConfig.InsertAfterSheetName)) ? dataWorksheet.Workbook.Worksheets[histogramConfig.InsertAfterSheetName] : dataWorksheet; // add a new empty worksheet IWorksheet chartSheet = dataWorksheet.Workbook.Worksheets.AddAfter(afterWorkSheet); chartSheet.Name = histogramConfig.NewSheetName; // working fields int maxRows = dataWorksheet.UsedRange.RowCount; int sourceColumnIndex = -1; columnNameXref.TryGetValue(histogramConfig.DataColumnName, out sourceColumnIndex); List <decimal> dataValues = new List <decimal>(); // loop thru all the rows on the source worksheet and build a collection for (int rowIndex = 1; rowIndex < maxRows; rowIndex++) { dataValues.Add(Decimal.Parse(dataWorksheet.Cells[rowIndex, sourceColumnIndex].Text)); } // build the bin data var groupings = dataValues.GroupBy(item => histogramConfig.Bins.First(bin => bin >= item)).OrderBy(k => k.Key); // write out the bin data table column headers chartSheet.Cells[0, 0].Value = @"Bin (secs)"; chartSheet.Cells[0, 1].Value = @"Count"; chartSheet.Cells[0, 2].Value = @"%"; // write out the bin data table data int rowCtr = 1; foreach (var kvp in groupings) { chartSheet.Cells[rowCtr, 0].Value = (kvp.Key != 1000) ? kvp.Key.ToString() : @"Overflow"; chartSheet.Cells[rowCtr, 1].Value = kvp.Count(); chartSheet.Cells[rowCtr, 2].Value = kvp.Count() / (maxRows - 1.0M); // force decimal divison chartSheet.Cells[rowCtr, 2].NumberFormat = @"0.0%"; rowCtr++; } // build the bar chart SpreadsheetGear.Shapes.IShape chartShape = chartSheet.Shapes.AddChart(200, 1, 500, 500); SpreadsheetGear.Charts.IChart chart = chartShape.Chart; // working variables "[20190720_114539_375_Auton.tsv]Scan Times Histrogram!$B$2:$B$21" IRange xAxisColumn = chartSheet.Cells[1, 0, rowCtr - 1, 0]; IRange yAxisColumn = chartSheet.Cells[1, 1, rowCtr - 1, 1];; ISeries chartSeries = chart.SeriesCollection.Add(); chartSeries.XValues = xAxisColumn.ToString().Split("!")[1]; chartSeries.Values = yAxisColumn.ToString().Split("!")[1]; chartSeries.ChartType = ChartType.ColumnClustered; // format the chart title chart.HasTitle = true; StringBuilder chartTitle = new StringBuilder(); string pathName = GetCellValue <string>(dataWorksheet, pathNameColumnName, 1, columnNameXref); chartTitle.AppendLine($"{histogramConfig.Name} | Path: [{pathName}]"); chart.ChartTitle.Text = chartTitle.ToString(); chart.ChartTitle.Font.Size = 12; // format the chart legend chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom; chart.Legend.Font.Bold = true; // format X & Y Axes IAxis xAxis = chart.Axes[AxisType.Category]; xAxis.HasTitle = true; IAxisTitle xAxisTitle = xAxis.AxisTitle; xAxisTitle.Text = histogramConfig.XAxisTitle; }
/// <summary> /// Builds a xy graph /// </summary> /// <param name="dataWorksheet"></param> /// <param name="xyGraph"></param> /// <param name="columnNameIndex"></param> private static void BuildXYGraph(IWorksheet dataWorksheet, XYGraphBE xyGraphConfig, Dictionary <string, int> columnNameIndex, string pathNameColumnName) { SpreadsheetGear.IWorkbook workbook = dataWorksheet.Workbook; List <string> missingColumnNames = new List <string>(); //// step 3: find the columns we want to reference for the Gains string pidGainsColumnName = xyGraphConfig.Gains?.PIDGains; string followerGainsColumnName = xyGraphConfig.Gains?.FollowerGains; string controlModeColumnName = xyGraphConfig.Gains?.ControlMode; int pidGainsColumnIdx = -1; int followerGainsColumnIdx = -1; int controlModeColumnIdx = -1; int elapsedDeltaColumnIdx = -1; int targetColumnIdx = -1; int actualColumnIdx = -1; int pathNameColumnIdx = -1; if (!string.IsNullOrEmpty(pidGainsColumnName)) { if (!columnNameIndex.TryGetValue(pidGainsColumnName, out pidGainsColumnIdx)) { //missingColumnNames.Add(pidGainsColumnName); } } if (!string.IsNullOrEmpty(followerGainsColumnName)) { if (!columnNameIndex.TryGetValue(followerGainsColumnName, out followerGainsColumnIdx)) { missingColumnNames.Add(followerGainsColumnName); } } if (!string.IsNullOrEmpty(controlModeColumnName)) { if (!columnNameIndex.TryGetValue(controlModeColumnName, out controlModeColumnIdx)) { //missingColumnNames.Add(controlModeColumnName); } } //if (!string.IsNullOrEmpty(lineGraphConfig.XAxis.FromColumnName)) //{ // if (!columnNameIndex.TryGetValue(lineGraphConfig.XAxis.FromColumnName, out elapsedDeltaColumnIdx)) // { // missingColumnNames.Add(lineGraphConfig.XAxis.FromColumnName); // } //} //if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.TargetColumnName)) //{ // if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.TargetColumnName, out targetColumnIdx)) // { // missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.TargetColumnName); // } //} //if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.ActualColumnName)) //{ // if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.ActualColumnName, out actualColumnIdx)) // { // missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.ActualColumnName); // } //} if (!string.IsNullOrEmpty(pathNameColumnName)) { if (!columnNameIndex.TryGetValue(pathNameColumnName, out pathNameColumnIdx)) { missingColumnNames.Add(pathNameColumnName); } } // stop if any were missing if (missingColumnNames.Count > 0) { string errList = String.Join(",", missingColumnNames); throw new ApplicationException($"... Error building graph: [{xyGraphConfig.Name}], Expected cols: [{errList}] cannot be found!"); } string pathName = dataWorksheet.Cells[1, pathNameColumnIdx].Text; // Step 4: add a new worksheet to hold the chart IWorksheet chartSheet = workbook.Worksheets.Add(); chartSheet.Name = xyGraphConfig.Name; // Step 5.1: time to build the chart SpreadsheetGear.Shapes.IShape chartShape = chartSheet.Shapes.AddChart(1, 1, 500, 500); SpreadsheetGear.Charts.IChart chart = chartShape.Chart; // working variables int lastRowIdx = dataWorksheet.UsedRange.RowCount; IRange xAxisColumn = dataWorksheet.Cells[1, 0, lastRowIdx - 1, 0]; IRange yAxisColumn = null; ISeries chartSeries = null; string seriesName = string.Empty; // Step 5.2: add a chart series for each Y axis column in the config int xAxisColumnIndex = -1; int yAxisColumnIndex = -1; foreach (var series in xyGraphConfig.series) { columnNameIndex.TryGetValue(series.XAxisCoumnName, out xAxisColumnIndex); columnNameIndex.TryGetValue(series.YAxisColumnName, out yAxisColumnIndex); xAxisColumn = dataWorksheet.Cells[1, xAxisColumnIndex, lastRowIdx - 1, xAxisColumnIndex]; yAxisColumn = dataWorksheet.Cells[1, yAxisColumnIndex, lastRowIdx - 1, yAxisColumnIndex]; chartSeries = chart.SeriesCollection.Add(); chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200"; chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200"; chartSeries.ChartType = ChartType.XYScatter; chartSeries.Name = series.Name; } // Step 5.3: format the chart title chart.HasTitle = true; StringBuilder chartTitle = new StringBuilder(); chartTitle.AppendLine($"{xyGraphConfig.Name} | Path: [{pathName}]"); // optional add follower gains only if available if (pidGainsColumnIdx >= 0) { chartTitle.AppendLine($"PID Gains: {GetPIDGains(dataWorksheet, pidGainsColumnIdx, controlModeColumnIdx)}"); } // optional add follower gains only if available if (followerGainsColumnIdx >= 0) { chartTitle.AppendLine($"Follower Gains: {dataWorksheet.Cells[1, followerGainsColumnIdx].Text}"); } if (xyGraphConfig.CalcFinalErrorDelta != null) { (decimal posErr, decimal negErr) = CalcAreaDelta(dataWorksheet, elapsedDeltaColumnIdx, targetColumnIdx, actualColumnIdx, xyGraphConfig.Name); chartTitle.AppendLine($"Error Area (tot): {posErr:N0} | {negErr:N0}"); } chart.ChartTitle.Text = chartTitle.ToString(); chart.ChartTitle.Font.Size = 12; // Step 5.4: format the chart legend chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom; chart.Legend.Font.Bold = true; // Step 5.5: format X & Y Axes IAxis xAxis = chart.Axes[AxisType.Category]; xAxis.HasMinorGridlines = true; xAxis.HasTitle = true; if (chart.ChartType == ChartType.Line) { // this option not valid on xy graphs xAxis.TickMarkSpacing = 100; // 10Msec per step * 100 = gidline every second } IAxisTitle xAxisTitle = xAxis.AxisTitle; xAxisTitle.Text = xyGraphConfig.XAxisTitle; IAxis yAxis = chart.Axes[AxisType.Value, AxisGroup.Primary]; yAxis.HasTitle = true; yAxis.TickLabels.NumberFormat = "General"; yAxis.ReversePlotOrder = xyGraphConfig.IsYAxisValuesInReverseOrder; IAxisTitle yAxisTitle = yAxis.AxisTitle; yAxisTitle.Text = xyGraphConfig.YAxisTitle; }