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