static void Main(string[] args)
{
SpreadsheetGear.IWorkbook gear = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = gear.Worksheets.Add();
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
// Load some sample data.
SpreadsheetGear.IRange dataRange = worksheet.Cells["A1:B6"];
dataRange.Value = new string[, ]
{
{ "A", "$7,923" },
{ "B", "$5,954" },
{ "C", "$5,522" },
{ "D", "$3,701" },
{ "E", "$5,522" },
{ "F", "$3,701" }
};
SpreadsheetGear.Shapes.IShape shape = worksheet.Shapes.AddChart(0, 0, 100, 100);
SpreadsheetGear.Charts.IChart chart = shape.Chart;
chart.SetSourceData(dataRange, SpreadsheetGear.Charts.RowCol.Columns);
chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnStacked;
chart.ChartGroups[0].GapWidth = 50;
chart.HasTitle = false;
chart.HasLegend = false;
chart.PlotVisibleOnly = true;
chart.ChartArea.Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178);
chart.SeriesCollection[0].HasDataLabels = false;
chart.SeriesCollection[0].HasLeaderLines = false;
chart.SeriesCollection[0].MarkerStyle = SpreadsheetGear.Charts.MarkerStyle.Automatic;
shape = worksheet.Shapes.AddChart(500, 500, 600, 600);
chart = shape.Chart;
chart.SetSourceData(dataRange, SpreadsheetGear.Charts.RowCol.Columns);
chart.ChartType = SpreadsheetGear.Charts.ChartType.Pie;
SpreadsheetGear.Charts.ISeries series = chart.SeriesCollection[0];
series.XValues = dataRange;
// Add series data labels and change to show percentage only.
series.HasDataLabels = true;
series.DataLabels.ShowPercentage = true;
series.DataLabels.ShowValue = false;
series.DataLabels.ShowCategoryName = false;
worksheet.Cells["F3"].NumberFormat = @"_-* #,##0.00_-;-* #,##0.00_-;_-@_-";
worksheet.Cells["F3"].Value = 3553654566.641;
worksheet.Cells["F6"].Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178);
worksheet.Cells["F6"].Font.Name = "Webdings";
worksheet.Cells["F6"].Value = "a";
worksheet.Cells["F9"].Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178);
worksheet.Cells["F9"].Font.Name = "Webdings";
worksheet.Cells["F9"].Value = "r";
gear.SaveAs(@"D:\Excels.xls", SpreadsheetGear.FileFormat.OpenXMLWorkbook);
}
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;
}
