private void CreateNewGraph(_Worksheet sheet, int row, dynamic[] dataArray, string[] labelArray, string name)
{
var charts = (ChartObjects)sheet.ChartObjects(Type.Missing);
var chartObject = (ChartObject)charts.Add(10, row * 15, 400, 250);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlLineMarkers;
chart.ChartWizard(Title: name, HasLegend: false);
//var dataArray = (dataList as object) as Array;
//var labelArray = (labelList as object) as Array;
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.NewSeries();
Axis xAxis = (Axis)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
var yAxis = (Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
//yAxis.HasTitle = true;
//yAxis.AxisTitle.Text = "Y-Axis Title text";
//yAxis.AxisTitle.Orientation = XlOrientation.xlUpward;
xAxis.CategoryNames = labelArray;
series.Values = dataArray;
series.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
}
public void generateChart(List <Variable> variablesX, List <Variable> variablesY, _Worksheet sheet, DataSet dataSet)
{
int offsetY = 0;
foreach (Variable variableX in variablesX)
{
int offsetX = 0;
foreach (Variable variableY in variablesY)
{
var charts = (ChartObjects)sheet.ChartObjects();
var chartObject = charts.Add(offsetX * 450, offsetY * 250, 450, 250);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
chart.ChartWizard(Title: "Time Series " + dataSet.getName() + " - " + variableX.name + " vs " + variableY.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add();
series.Values = variableY.getRange();
series.XValues = variableX.getRange();
series.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
offsetX++;
}
offsetY++;
}
}
public void createScatterPlot(List <Variable> variablesX, List <Variable> variablesY)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Scatterplot");
int offsetY = 0;
foreach (Variable variableX in variablesX)
{
int offsetX = 0;
foreach (Variable variableY in variablesY)
{
var charts = (ChartObjects)sheet.ChartObjects();
var chartObject = charts.Add(offsetX * 300, offsetY * 200, 300, 200);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlXYScatter;
chart.ChartWizard(Title: "Scatterplot " + variableX.name + " vs " + variableY.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add();
series.Values = variableX.getRange();
series.XValues = variableY.getRange();
series.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
offsetX++;
}
offsetY++;
}
}
private void PrintVariables(_Worksheet _sheet, int _row, int _counter, Variable variable)
{
var function = Globals.ExcelAddIn.Application.WorksheetFunction;
int numberOfBins = model.useBins && model.bins > 0 ? model.bins : (int)function.RoundUp(Math.Sqrt(function.Count(variable.getRange())), 0);
// Write the needed data to the sheet.
for (int bin = 0; bin < numberOfBins; bin++)
{
var column = 1;
var range = variable.getRange().Address(true, true, true);
_sheet.Cells[_row, column++] = "Bin #" + bin;
_sheet.WriteFunction(_row, column, bin == 0 ? "MIN(" + range + ")" : AddressConverter.CellAddress(_row - 1, column + 1)); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "+" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)"); column++;
_sheet.Cells[_row, column] = "=(" + AddressConverter.CellAddress(_row, column - 2) + "+" + AddressConverter.CellAddress(_row, column - 1) + ")/2"; column++;
_sheet.WriteFunction(_row, column, "COUNTIF(" + range + ",\"<=\"&" + AddressConverter.CellAddress(_row, column - 2) + ")-COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(_row, column - 3) + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "COUNT(" + range + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)");
_row++;
}
// Create the chart.
var charts = (ChartObjects)_sheet.ChartObjects();
var chartObject = charts.Add(400, 225 * _counter, 100 * numberOfBins, 200);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlColumnClustered;
chart.ChartWizard(Title: "Histogram - " + variable.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add(_sheet.Range[_sheet.Cells[_row - numberOfBins, 5], _sheet.Cells[_row - 1, 5]]);
series.ChartType = XlChartType.xlColumnClustered;
series.XValues = _sheet.Range[_sheet.Cells[_row - numberOfBins, 4], _sheet.Cells[_row - 1, 4]];
}
private static void BuildChart(_Worksheet worksheet, string title, uint size, string pngFile)
{
var xlCharts = (ChartObjects)worksheet.ChartObjects(Type.Missing);
var myChart = xlCharts.Add(10, 80, 400, 200);
var chartPage = myChart.Chart;
var chartRange = worksheet.Range["A1", "C" + size];
chartPage.SetSourceData(chartRange);
SetExcelChart(chartPage, title);
//export chart as picture file
chartPage.Export(pngFile);
}
private static void AddChart(_Worksheet excellWorkSheet, string startSel, string endSel)
{
ChartObjects xlCharts = (ChartObjects)excellWorkSheet.ChartObjects(Type.Missing);
ChartObject myChart = xlCharts.Add(10, 80, 600, 400);
Chart chartPage = myChart.Chart;
Range chartRange = excellWorkSheet.Range[startSel, endSel];
chartPage.SetSourceData(chartRange, Type.Missing);
chartPage.ChartType = XlChartType.xlXYScatter;
var seriesCollection = (SeriesCollection)chartPage.SeriesCollection();
seriesCollection.Item(1).Name = "PROPORTION";
}
private static void AddChart2(_Worksheet pivotWorkSheet, string myTitle, Range pivotData, XlChartType type)
{
var chartObjects = (ChartObjects)pivotWorkSheet.ChartObjects();
var pivotChart = chartObjects.Add(Left: 60, Top: 250, Width: 325, Height: 275);
pivotChart.Chart.SetSourceData(pivotData);
pivotChart.Chart.ChartType = type;
if (myTitle.Equals(string.Empty) == false)
{
pivotChart.Name = myTitle;
}
pivotChart.Chart.Location(XlChartLocation.xlLocationAsNewSheet);
}
public void drawGraphs(_Worksheet sheet, double[] xData, double[] yData, String name)
{
var Xcharts = (ChartObjects)sheet.ChartObjects();
var XchartObject = Xcharts.Add(20, (lowestDataRow + 2) * 15 + nrOfGraphs * 230, 350, 200);
var Xchart = XchartObject.Chart;
Xchart.ChartType = XlChartType.xlXYScatter;
Xchart.ChartWizard(Title: name, HasLegend: false);
var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection();
var graph = XseriesCollection.NewSeries();
graph.Values = yData;
graph.XValues = xData;
nrOfGraphs++;
}
private static int CreateNewFigure(Range rangeX, Range rangeY, int row, string name, _Worksheet sheet)
{
var charts = (ChartObjects)sheet.ChartObjects();
var chartObject = charts.Add(10, row * 15, 400, 250);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlXYScatter;
chart.ChartWizard(Title: name, HasLegend: false);
((SeriesCollection)chart.SeriesCollection()).Add(rangeY);
((Series)chart.SeriesCollection(1)).Values = rangeY;
((Series)chart.SeriesCollection(1)).XValues = rangeX;
((Series)chart.SeriesCollection(1)).MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
return(row + 20);
}
public static _Workbook CriarGraficoCotaTempo(_Workbook workbook, EstacaoData estacao)
{
GC.Collect();
_Worksheet worksheet = workbook.Worksheets[5];
// Add chart.
var charts = worksheet.ChartObjects() as
Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(10, 10, 800, 600) as
Microsoft.Office.Interop.Excel.ChartObject;
var chart = chartObject.Chart;
var dadosGrafico = new object[dadosBackup.GetUpperBound(0) + 1, 2];
for (int i = 0; i < dadosBackup.GetUpperBound(0); i++)
{
if (dadosBackup[i, 1] != null && !string.IsNullOrEmpty(dadosBackup[i, 1].ToString()))
{
dadosGrafico[i, 0] = dadosBackup[i, 0]; //Atribui vazão
dadosGrafico[i, 1] = dadosBackup[i, 1]; //Atribui cota
}
}
Range range = worksheet.Cells[3, 2];
range = range.Resize[dadosGrafico.GetUpperBound(0), 2];
range.Value = dadosGrafico;
chart.SetSourceData(range);
// Set chart properties.
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterSmooth;
chart.ChartWizard(Source: range,
Title: "Cota X Tempo",
CategoryTitle: "Tempo",
ValueTitle: "Cota");
return(workbook);
}
public void createBoxWhiskerPlot(List <Variable> variables)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Box-Whisker Plot");
var excel = Globals.ExcelAddIn.Application;
List <double> yValues = new List <double>();
// To center the mean and outliers, the secondary Y-Axis has to be divided in sections. Each Box-Whisker Plot has a section above and below so if 1 Box-Whisker Plot is needed, the dots will be at 50% of the max. secondary Y-Axis value. If 2 Box-Whisker Plots are needed, the dots will be at 25% for the 1st and 75% for the 2nd of the max. secondary Y-Axis value.
var section = 1.0 / (2 * variables.Count);
for (var i = 0; i < variables.Count; i++)
{
yValues.Add((2 * (i + 1) - 1) * section);
}
// Create the chart.
var charts = (ChartObjects)sheet.ChartObjects();
var chartObject = charts.Add(0, 0, 500, 125 * variables.Count);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlBarStacked;
chart.ChartWizard(Title: "Box-Whisker Plot", HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
// Add the Quartile 1 to the chart.
double[] values = new double[variables.Count];
for (int i = 0; i < variables.Count; i++)
{
values[i] = excel.Evaluate("=QUARTILE.INC(" + variables[i].getRange().Address(true, true, true) + ",1)");
}
var series = seriesCollection.NewSeries();
series.Values = values;
series.ChartType = XlChartType.xlBarStacked;
series.Format.Fill.Solid();
series.Format.Fill.Transparency = 1;
// Add the lower halve to the chart.
for (int i = 0; i < variables.Count; i++)
{
values[i] = excel.Evaluate("=MEDIAN(" + variables[i].getRange().Address(true, true, true) + ")-QUARTILE.INC(" + variables[i].getRange().Address(true, true, true) + ",1)");
}
series = seriesCollection.NewSeries();
series.Values = values;
series.ChartType = XlChartType.xlBarStacked;
series.Format.Fill.Solid();
series.Format.Fill.Transparency = 1;
series.Border.LineStyle = XlLineStyle.xlContinuous;
series.Format.Line.Weight = 1.5f;
// Add the upper halve to the chart.
for (int i = 0; i < variables.Count; i++)
{
values[i] = excel.Evaluate("=QUARTILE.INC(" + variables[i].getRange().Address(true, true, true) + ",3)-MEDIAN(" + variables[i].getRange().Address(true, true, true) + ")");
}
series = seriesCollection.NewSeries();
series.Values = values;
series.ChartType = XlChartType.xlBarStacked;
series.Format.Fill.Solid();
series.Format.Fill.Transparency = 1;
series.Border.LineStyle = XlLineStyle.xlContinuous;
series.Format.Line.Weight = 1.5f;
// Set the Categories Axis.
List <string> headers = new List <string>();
foreach (Variable variable in variables)
{
headers.Add(variable.name);
}
((Axis)chart.Axes(XlAxisType.xlCategory)).CategoryNames = headers.ToArray();
// Add both the minus and plus whiskers to the chart.
for (int i = 0; i < variables.Count; i++)
{
var range = variables[i].getRange().Address(true, true, true);
double quartile1 = excel.Evaluate("=QUARTILE.INC(" + range + ",1)");
double minimum = excel.Evaluate("=MIN(" + range + ")");
double quartile3 = excel.Evaluate("=QUARTILE.INC(" + range + ",3)");
double interquartileRange = quartile3 - quartile1;
values[i] = quartile1 - minimum <= 1.5 * interquartileRange ? quartile1 - minimum : 1.5 * interquartileRange;
}
((Series)chart.SeriesCollection(1)).ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludeMinusValues, XlErrorBarType.xlErrorBarTypeCustom, 0, values);
((Series)chart.SeriesCollection(1)).ErrorBars.Format.Line.Weight = 1.5f;
for (int i = 0; i < variables.Count; i++)
{
var range = variables[i].getRange().Address(true, true, true);
double quartile1 = excel.Evaluate("=QUARTILE.INC(" + range + ",1)");
double maximum = excel.Evaluate("=MAX(" + range + ")");
double quartile3 = excel.Evaluate("=QUARTILE.INC(" + range + ",3)");
double interquartileRange = quartile3 - quartile1;
values[i] = maximum - quartile3 <= 1.5 * interquartileRange ? maximum - quartile3 : 1.5 * interquartileRange;
}
((Series)chart.SeriesCollection(3)).ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludePlusValues, XlErrorBarType.xlErrorBarTypeCustom, values, 0);
((Series)chart.SeriesCollection(3)).ErrorBars.Format.Line.Weight = 1.5f;
// Add the means to the chart as a Scatterplot and change the layout.
series = seriesCollection.Add();
series.ChartType = XlChartType.xlXYScatter;
series.Name = "Mean";
series.Values = yValues.ToArray();
for (int i = 0; i < variables.Count; i++)
{
values[i] = excel.Evaluate("=AVERAGE(" + variables[i].getRange().Address(true, true, true) + ")");
}
series.XValues = values;
series.MarkerStyle = XlMarkerStyle.xlMarkerStyleX;
series.MarkerForegroundColor = (int)XlRgbColor.rgbDarkBlue;
// Add all the outliers to the chart as a Scatterplot and change the layout.
List <List <double> > listOutliers = new List <List <double> >();
List <List <double> > listY = new List <List <double> >();
foreach (Variable variable in variables)
{
List <double> outliers = new List <double>();
for (var i = 1; i <= variable.getRange().Rows.Count; i++)
{
try
{
var range = variable.getRange().Address(true, true, true);
double quartile1 = excel.Evaluate("=QUARTILE.INC(" + range + ",1)");
double quartile3 = excel.Evaluate("=QUARTILE.INC(" + range + ",3)");
double interquartileRange = quartile3 - quartile1;
double value = (double)((Range)excel.Evaluate("=INDEX(" + range + "," + i + ")")).Value2;
if (value < quartile1 - 1.5 * interquartileRange)
{
outliers.Add(value);
}
if (value > quartile3 + 1.5 * interquartileRange)
{
outliers.Add(value);
}
}
catch
{
}
}
listOutliers.Add(outliers);
}
var sect = 1.0 / (2 * variables.Count);
int count = 0;
foreach (List <double> lst in listOutliers)
{
List <double> yOutliers = new List <double>();
double val = (2 * (count + 1) - 1) * sect;
for (var i = 0; i < lst.Count; i++)
{
yOutliers.Add(val);
}
count = count + 1;
listY.Add(yOutliers);
}
count = 0;
foreach (List <double> outl in listOutliers)
{
if (outl.Count > 0)
{
series = seriesCollection.Add();
series.ChartType = XlChartType.xlXYScatter;
series.Name = "Outliers";
series.Values = listY[count].ToArray();
series.XValues = outl.ToArray();
series.MarkerStyle = XlMarkerStyle.xlMarkerStyleSquare;
series.MarkerBackgroundColor = (int)XlRgbColor.rgbDarkRed;
series.MarkerForegroundColor = (int)XlRgbColor.rgbDarkRed;
}
count = count + 1;
}
// Hide the secondary axis and set max value.
((Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary)).MaximumScale = 1;
chart.HasAxis[XlAxisType.xlValue, XlAxisGroup.xlSecondary] = false;
}
private void generatePChart(int startindex, int stopindex, DataSet dataSet, _Worksheet sheet, int plotstartindex, int plotstopindex)
{
int index = 0;
int row = 1;
int column = 1;
double[] pValues = new double[dataSet.amountOfVariables() - 1];
double[] averageOfPValues = new double[dataSet.amountOfVariables() - 1];
double[] pValuesInRange = new double[stopindex - startindex + 1];
double[] pChartUpperControlLimit = new double[dataSet.amountOfVariables()];
double[] pChartLowerControlLimit = new double[dataSet.amountOfVariables()];
sheet.Cells[row, column] = "Index";
sheet.Cells[row, column + 1] = "Observation";
sheet.Cells[row, column + 2] = "Average";
for (index = 1; index < dataSet.amountOfVariables(); index++)
{
row++;
sheet.Cells[row, column] = index - 1;
sheet.Cells[row, column + 1] = dataSet.getVariables()[index].name;
sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")";
var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value;
if (cellValue < -214682680)
{
cellValue = 0; // if cellValue is the result of a division by 0, set value to 0
}
pValues[index - 1] = cellValue;
if (cellValue > 1)
{
MessageBox.Show("Cannot generate P-Chart; Data in " + dataSet.Name + " contains samples greater than 1");
return;
}
}
for (index = startindex; index <= stopindex; index++)
{
pValuesInRange[index - startindex] = pValues[index];
}
double pChartCorrection = Math.Pow(((pValuesInRange.Average() * (1 - pValuesInRange.Average())) / dataSet.rangeSize()), (0.3333333));
for (index = 0; index < dataSet.amountOfVariables() - 1; index++)
{
averageOfPValues[index] = pValuesInRange.Average();
pChartUpperControlLimit[index] = pValues.Average() + pChartCorrection;
pChartLowerControlLimit[index] = pValues.Average() - pChartCorrection;
}
// new arrays for subsets of existing arrays but within limits
double[] plotpValues = new double[plotstopindex - plotstartindex + 1];
double[] plotaverageOfPValues = new double[plotstopindex - plotstartindex + 1];
double[] plotpChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotpChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1];
int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1];
for (int i = plotstartindex; i <= plotstopindex; i++)
{
plotpValues[i - plotstartindex] = pValues[i];
plotaverageOfPValues[i - plotstartindex] = averageOfPValues[i];
plotpChartLowerControlLimit[i - plotstartindex] = pChartLowerControlLimit[i];
plotpChartUpperControlLimit[i - plotstartindex] = pChartUpperControlLimit[i];
ArrayIndex[i - plotstartindex] = i;
}
var Xcharts = (ChartObjects)sheet.ChartObjects();
var XchartObject = Xcharts.Add(340, 20, 550, 300);
var Xchart = XchartObject.Chart;
Xchart.ChartType = XlChartType.xlXYScatterLines;
Xchart.ChartWizard(Title: "P-Chart " + dataSet.Name, HasLegend: true);
var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection();
var pseries = XseriesCollection.NewSeries();
var CLseries = XseriesCollection.NewSeries();
var UCLseries = XseriesCollection.NewSeries();
var LCLseries = XseriesCollection.NewSeries();
pseries.Name = ("Proportion");
CLseries.Name = ("Center line");
UCLseries.Name = ("UCL");
LCLseries.Name = ("LCL");
pseries.Values = plotpValues;
CLseries.Values = plotaverageOfPValues;
UCLseries.Values = plotpChartUpperControlLimit;
LCLseries.Values = plotpChartLowerControlLimit;
pseries.XValues = ArrayIndex;
CLseries.XValues = ArrayIndex;
UCLseries.XValues = ArrayIndex;
LCLseries.XValues = ArrayIndex;
}
public void generateXRChart(List <Variable> variables, int startindex, int stopindex, int plotstartindex, int plotstopindex, DataSet dataSet, int offset, _Worksheet sheet)
{
int counter = 0;
int index = 0;
int row = 1;
int column = 1;
double xControlLimitFactor, rControlLimitFactor1, rControlLimitFactor2;
double[] averages = new double[dataSet.rangeSize()];
double[] Rvalues = new double[dataSet.rangeSize()];
double[] averageOfAverages = new double[dataSet.rangeSize()];
double[] xChartUpperControlLimit = new double[dataSet.rangeSize()]; //amountOfVariables()
double[] xChartLowerControlLimit = new double[dataSet.rangeSize()];
double[] rChartUpperControlLimit = new double[dataSet.rangeSize()];
double[] rChartLowerControlLimit = new double[dataSet.rangeSize()];
double[] averageOfRvalues = new double[dataSet.rangeSize()];
double[] RvaluesInRange = new double[stopindex - startindex]; // +1
double[] averageOfAveragesInRange = new double[stopindex - startindex]; // +1
int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1]; // +1
double[] xChartConstants = new double[25] {
0.0, 1.128, 1.693, 2.059, 2.326, 2.534, 2.704, 2.847, 2.970, 3.078, 3.173, 3.258, 3.336, 3.407, 3.472, 3.532, 3.588, 3.640, 3.689, 3.735, 3.778, 3.819, 3.858, 3.895, 3.931
};
double[] rChartConstants1 = new double[25] {
0, 0, 0, 0, 0, 0, 0.076, 0.136, 0.184, 0.223, 0.256, 0.283, 0.307, 0.328, 0.347, 0.363, 0.378, 0.391, 0.403, 0.415, 0.425, 0.434, 0.443, 0.451, 0.459
};
double[] rChartConstants2 = new double[25] {
0, 3.267, 2.574, 2.282, 2.114, 2.004, 1.924, 1.864, 1.816, 1.777, 1.744, 1.717, 1.693, 1.672, 1.653, 1.637, 1.662, 1.607, 1.597, 1.585, 1.575, 1.566, 1.557, 1.548, 1.541
};
sheet.Cells[row, column] = "Index";
sheet.Cells[row, column + 1] = "Observation";
sheet.Cells[row, column + 2] = "Average";
sheet.Cells[row, column + 3] = "Max";
sheet.Cells[row, column + 4] = "Min";
sheet.Cells[row, column + 5] = "R";
string colLetter = variables[0].Range[1].ToString();
int columnIndex = ColumnLetterToColumnIndex(colLetter);
int columnIndex2 = columnIndex + variables.Count - 1;
string colLetter2 = ColumnIndexToColumnLetter(columnIndex2);
char chstart = variables[0].Range[3];
int start = (int)Char.GetNumericValue(chstart);
int final = 0;
for (counter = 1; counter <= dataSet.rangeSize(); counter++) //amountOfVariables()
{
row++;
for (index = 0; index < variables.Count; index++)
{
sheet.Cells[row, column] = counter;
if (variables.Count > 1)
{
sheet.Cells[row, column + 1] = variables[0].name + "-" + variables[variables.Count - 1].name;
}
else
{
sheet.Cells[row, column + 1] = variables[index].name;
}
}
int temp = counter;
if (dataSet.getVariableNamesInFirstRowOrColumn())
{
temp = counter + 1;
}
sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!$" + colLetter + "$" + start + ":$" + colLetter2 + "$" + temp + ")";
sheet.Cells[row, column + 3] = "=MAX(" + dataSet.getWorksheet().Name + "!$" + colLetter + "$" + temp + ":$" + colLetter2 + "$" + temp + ")";
sheet.Cells[row, column + 4] = "=MIN(" + dataSet.getWorksheet().Name + "!$" + colLetter + "$" + temp + ":$" + colLetter2 + "$" + temp + ")";
sheet.Cells[row, column + 5] = (double)(sheet.Cells[row, column + 3] as Range).Value - (double)(sheet.Cells[row, column + 4] as Range).Value;
var cellValue = (double)sheet.Cells[row, column + 2].Value;
if (cellValue < -214682680)
{
cellValue = 0; // if cellValue is the result of a division by 0, set value to 0
}
averages[counter - 1] = cellValue;
cellValue = (double)(sheet.Cells[row, column + 5] as Range).Value;
Rvalues[counter - 1] = cellValue;
start = start + 1;
final = temp;
}
for (counter = startindex; counter < stopindex; counter++)
{
RvaluesInRange[counter - startindex] = Rvalues[counter];
averageOfAveragesInRange[counter - startindex] = averages[counter];
}
if (dataSet.getVariableNamesInFirstRowOrColumn())
{
xControlLimitFactor = xChartConstants[variables.Count - 1];
rControlLimitFactor1 = rChartConstants1[variables.Count - 1];
rControlLimitFactor2 = rChartConstants2[variables.Count - 1];
}
else
{
xControlLimitFactor = xChartConstants[variables.Count];
rControlLimitFactor1 = rChartConstants1[variables.Count];
rControlLimitFactor2 = rChartConstants2[variables.Count];
}
for (counter = 0; counter <= final - 2; counter++)
{
averageOfAverages[counter] = averageOfAveragesInRange.Average();
xChartUpperControlLimit[counter] = averageOfAveragesInRange.Average() + 3.0 * (Rvalues.Average() / (xControlLimitFactor * Math.Sqrt(variables.Count)));
xChartLowerControlLimit[counter] = averageOfAveragesInRange.Average() - 3.0 * (Rvalues.Average() / (xControlLimitFactor * Math.Sqrt(variables.Count)));
averageOfRvalues[counter] = RvaluesInRange.Average();
rChartUpperControlLimit[counter] = RvaluesInRange.Average() * rControlLimitFactor2;
rChartLowerControlLimit[counter] = RvaluesInRange.Average() * rControlLimitFactor1;
}
// new subsets of arrays for plotting data
double[] plotaverages = new double[plotstopindex - plotstartindex + 1];
double[] plotRvalues = new double[plotstopindex - plotstartindex + 1];
double[] plotaverageOfAverages = new double[plotstopindex - plotstartindex + 1];
double[] plotxChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotxChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotrChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotrChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotaverageOfRvalues = new double[plotstopindex - plotstartindex + 1];
for (int i = plotstartindex; i <= plotstopindex; i++)
{
ArrayIndex[i - plotstartindex] = i;
plotaverages[i - plotstartindex] = averages[i];
plotRvalues[i - plotstartindex] = Rvalues[i];
plotaverageOfAverages[i - plotstartindex] = averageOfAverages[i];
plotxChartUpperControlLimit[i - plotstartindex] = xChartUpperControlLimit[i];
plotxChartLowerControlLimit[i - plotstartindex] = xChartLowerControlLimit[i];
plotrChartUpperControlLimit[i - plotstartindex] = rChartUpperControlLimit[i];
plotrChartLowerControlLimit[i - plotstartindex] = rChartLowerControlLimit[i];
plotaverageOfRvalues[i - plotstartindex] = averageOfRvalues[i];
}
var Xcharts = (ChartObjects)sheet.ChartObjects();
var XchartObject = Xcharts.Add(340, 20, 550, 300);
var Xchart = XchartObject.Chart;
Xchart.ChartType = XlChartType.xlXYScatterLines;
Xchart.ChartWizard(Title: "X-Chart " + dataSet.Name, HasLegend: true);
var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection();
var avgseries = XseriesCollection.NewSeries();
var avgAvgseries = XseriesCollection.NewSeries();
var UCLseries = XseriesCollection.NewSeries();
var LCLseries = XseriesCollection.NewSeries();
avgseries.Name = ("Observation Averages");
avgAvgseries.Name = ("Center Line");
UCLseries.Name = ("UCL");
LCLseries.Name = ("LCL");
avgseries.Values = plotaverages;
avgAvgseries.Values = plotaverageOfAverages;
UCLseries.Values = plotxChartUpperControlLimit;
LCLseries.Values = plotxChartLowerControlLimit;
avgseries.XValues = ArrayIndex;
UCLseries.XValues = ArrayIndex;
LCLseries.XValues = ArrayIndex;
avgAvgseries.XValues = ArrayIndex;
var Rcharts = (ChartObjects)sheet.ChartObjects();
var RchartObject = Rcharts.Add(340, 20 + offset, 550, 300);
var Rchart = RchartObject.Chart;
Rchart.ChartType = XlChartType.xlXYScatterLines;
Rchart.ChartWizard(Title: "R-Chart " + dataSet.Name, HasLegend: true);
var RseriesCollection = (SeriesCollection)Rchart.SeriesCollection();
var rSeries = RseriesCollection.NewSeries();
var averageRSeries = RseriesCollection.NewSeries();
var UCLSeries = RseriesCollection.NewSeries();
var LCLSeries = RseriesCollection.NewSeries();
rSeries.Name = ("Observation R");
averageRSeries.Name = ("Center Line");
UCLSeries.Name = ("UCL");
LCLSeries.Name = ("LCL");
rSeries.Values = plotRvalues;
averageRSeries.Values = plotaverageOfRvalues;
UCLSeries.Values = plotrChartUpperControlLimit;
LCLSeries.Values = plotrChartLowerControlLimit;
rSeries.XValues = ArrayIndex;
averageRSeries.XValues = ArrayIndex;
UCLSeries.XValues = ArrayIndex;
LCLSeries.XValues = ArrayIndex;
}
public void generateXRChart(int startindex, int stopindex, int plotstartindex, int plotstopindex, DataSet dataSet, int offset, _Worksheet sheet)
{
// declaration of variables
int index = 0;
int row = 1;
int column = 1;
double xControlLimitFactor, rControlLimitFactor1, rControlLimitFactor2;
double[] averages = new double[dataSet.amountOfVariables()];
double[] Rvalues = new double[dataSet.amountOfVariables()];
double[] averageOfAverages = new double[dataSet.amountOfVariables()];
double[] xChartUpperControlLimit = new double[dataSet.amountOfVariables()];
double[] xChartLowerControlLimit = new double[dataSet.amountOfVariables()];
double[] rChartUpperControlLimit = new double[dataSet.amountOfVariables()];
double[] rChartLowerControlLimit = new double[dataSet.amountOfVariables()];
double[] averageOfRvalues = new double[dataSet.amountOfVariables()];
double[] RvaluesInRange = new double[stopindex - startindex + 1];
double[] averageOfAveragesInRange = new double[stopindex - startindex + 1];
int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1];
double[] xChartConstants = new double[25] {
0.0, 1.880, 1.023, 0.729, 0.577, 0.483, 0.419, 0.373, 0.337, 0.308, 0.285, 0.266, 0.249, 0.235, 0.223, 0.212, 0.203, 0.194, 0.187, 0.180, 0.173, 0.167, 0.162, 0.157, 0.153
};
double[] rChartConstants1 = new double[25] {
0, 0, 0, 0, 0, 0, 0.076, 0.136, 0.184, 0.223, 0.256, 0.283, 0.307, 0.328, 0.347, 0.363, 0.378, 0.391, 0.403, 0.415, 0.425, 0.434, 0.443, 0.451, 0.459
};
double[] rChartConstants2 = new double[25] {
0, 3.267, 2.574, 2.282, 2.114, 2.004, 1.924, 1.864, 1.816, 1.777, 1.744, 1.717, 1.693, 1.672, 1.653, 1.637, 1.662, 1.607, 1.597, 1.585, 1.575, 1.566, 1.557, 1.548, 1.541
};
// write labels to sheet
sheet.Cells[row, column] = "Index";
sheet.Cells[row, column + 1] = "Observation";
sheet.Cells[row, column + 2] = "Average";
sheet.Cells[row, column + 3] = "Max";
sheet.Cells[row, column + 4] = "Min";
sheet.Cells[row, column + 5] = "R";
// write values to sheet
for (index = 1; index < dataSet.amountOfVariables(); index++)
{
row++;
sheet.Cells[row, column] = index - 1;
sheet.Cells[row, column + 1] = dataSet.getVariables()[index].name;
sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")";
sheet.Cells[row, column + 3] = "=MAX(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")";
sheet.Cells[row, column + 4] = "=MIN(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")";
sheet.Cells[row, column + 5] = (double)(sheet.Cells[row, column + 3] as Range).Value - (double)(sheet.Cells[row, column + 4] as Range).Value;
var cellValue = (double)sheet.Cells[row, column + 2].Value;
if (cellValue < -214682680)
{
cellValue = 0; // if cellValue is the result of a division by 0, set value to 0
}
averages[index - 1] = cellValue;
cellValue = (double)(sheet.Cells[row, column + 5] as Range).Value;
Rvalues[index - 1] = cellValue;
}
// make arrays for calculation within limits
for (index = startindex; index <= stopindex; index++)
{
RvaluesInRange[index - startindex] = Rvalues[index];
averageOfAveragesInRange[index - startindex] = averages[index];
}
// create variable to limit amount of subsamples in 1 measurement to 25
int safe;
if (dataSet.rangeSize() > 24)
{
safe = 24;
}
else
{
safe = dataSet.rangeSize();
}
if (dataSet.getVariableNamesInFirstRowOrColumn())
{
xControlLimitFactor = xChartConstants[safe - 1];
rControlLimitFactor1 = rChartConstants1[safe - 1];
rControlLimitFactor2 = rChartConstants2[safe - 1];
}
else
{
xControlLimitFactor = xChartConstants[safe];
}
rControlLimitFactor1 = rChartConstants1[safe];
rControlLimitFactor2 = rChartConstants2[safe];
for (index = 0; index < dataSet.amountOfVariables(); index++)
{
averageOfAverages[index] = averageOfAveragesInRange.Average();
xChartUpperControlLimit[index] = averageOfAveragesInRange.Average() + (xControlLimitFactor * Rvalues.Average());
xChartLowerControlLimit[index] = averageOfAveragesInRange.Average() - (xControlLimitFactor * Rvalues.Average());
averageOfRvalues[index] = RvaluesInRange.Average();
rChartUpperControlLimit[index] = RvaluesInRange.Average() * rControlLimitFactor2;
rChartLowerControlLimit[index] = RvaluesInRange.Average() * rControlLimitFactor1;
}
// new subsets of arrays for plotting data
double[] plotaverages = new double[plotstopindex - plotstartindex + 1];
double[] plotRvalues = new double[plotstopindex - plotstartindex + 1];
double[] plotaverageOfAverages = new double[plotstopindex - plotstartindex + 1];
double[] plotxChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotxChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotrChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotrChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotaverageOfRvalues = new double[plotstopindex - plotstartindex + 1];
for (int i = plotstartindex; i <= plotstopindex; i++)
{
ArrayIndex[i - plotstartindex] = i;
plotaverages[i - plotstartindex] = averages[i];
plotRvalues[i - plotstartindex] = Rvalues[i];
plotaverageOfAverages[i - plotstartindex] = averageOfAverages[i];
plotxChartUpperControlLimit[i - plotstartindex] = xChartUpperControlLimit[i];
plotxChartLowerControlLimit[i - plotstartindex] = xChartLowerControlLimit[i];
plotrChartUpperControlLimit[i - plotstartindex] = rChartUpperControlLimit[i];
plotrChartLowerControlLimit[i - plotstartindex] = rChartLowerControlLimit[i];
plotaverageOfRvalues[i - plotstartindex] = averageOfRvalues[i];
}
var Xcharts = (ChartObjects)sheet.ChartObjects();
var XchartObject = Xcharts.Add(340, 20, 550, 300);
var Xchart = XchartObject.Chart;
Xchart.ChartType = XlChartType.xlXYScatterLines;
Xchart.ChartWizard(Title: "X-Chart " + dataSet.Name, HasLegend: true);
var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection();
var avgseries = XseriesCollection.NewSeries();
var avgAvgseries = XseriesCollection.NewSeries();
var UCLseries = XseriesCollection.NewSeries();
var LCLseries = XseriesCollection.NewSeries();
avgseries.Name = ("Observation Averages");
avgAvgseries.Name = ("Center Line");
UCLseries.Name = ("UCL");
LCLseries.Name = ("LCL");
avgseries.Values = plotaverages;
avgAvgseries.Values = plotaverageOfAverages;
UCLseries.Values = plotxChartUpperControlLimit;
LCLseries.Values = plotxChartLowerControlLimit;
avgseries.XValues = ArrayIndex;
UCLseries.XValues = ArrayIndex;
LCLseries.XValues = ArrayIndex;
avgAvgseries.XValues = ArrayIndex;
var Rcharts = (ChartObjects)sheet.ChartObjects();
var RchartObject = Rcharts.Add(340, 20 + offset, 550, 300);
var Rchart = RchartObject.Chart;
Rchart.ChartType = XlChartType.xlXYScatterLines;
Rchart.ChartWizard(Title: "R-Chart " + dataSet.Name, HasLegend: true);
var RseriesCollection = (SeriesCollection)Rchart.SeriesCollection();
var rSeries = RseriesCollection.NewSeries();
var averageRSeries = RseriesCollection.NewSeries();
var UCLSeries = RseriesCollection.NewSeries();
var LCLSeries = RseriesCollection.NewSeries();
rSeries.Name = ("Observation R");
averageRSeries.Name = ("Center Line");
UCLSeries.Name = ("UCL");
LCLSeries.Name = ("LCL");
rSeries.Values = plotRvalues;
averageRSeries.Values = plotaverageOfRvalues;
UCLSeries.Values = plotrChartUpperControlLimit;
LCLSeries.Values = plotrChartLowerControlLimit;
rSeries.XValues = ArrayIndex;
averageRSeries.XValues = ArrayIndex;
UCLSeries.XValues = ArrayIndex;
LCLSeries.XValues = ArrayIndex;
}
private void generatePChart(List <Variable> variableX, List <Variable> variableY, bool radioButton2, int startindex, int stopindex, DataSet dataSet, _Worksheet sheet, int plotstartindex, int plotstopindex)
{
int index = 0;
int row = 1;
int column = 1;
double[] pValues = new double[dataSet.rangeSize()];
double[] sample = new double[dataSet.rangeSize()];
double[] averageOfPValues = new double[dataSet.rangeSize()];
double[] pValuesInRange = new double[stopindex - startindex + 1];
double[] pChartUpperControlLimit = new double[dataSet.rangeSize()];
double[] pChartLowerControlLimit = new double[dataSet.rangeSize()];
double[] pChartCorrection = new double[dataSet.rangeSize()];
sheet.Cells[row, column] = "Index";
sheet.Cells[row, column + 1] = "Sample Size";
sheet.Cells[row, column + 2] = "Proportion";
for (index = 1; index <= dataSet.rangeSize(); index++)
{
int temp = index + 1;
String N = "(" + dataSet.getWorksheet().Name + "!$" + variableY[0].Range[1] + "$" + temp + ")";
String K = "(" + dataSet.getWorksheet().Name + "!$" + variableX[0].Range[1] + "$" + temp + ")";
row++;
sheet.Cells[row, column] = index;
sheet.Cells[row, column + 1] = "=(" + dataSet.getWorksheet().Name + "!$" + variableX[0].Range[1] + "$" + temp + ")";
if (radioButton2)
{
sheet.Cells[row, column + 2] = "=" + N + "/" + K;
}
else
{
sheet.Cells[row, column + 2] = "=(" + dataSet.getWorksheet().Name + "!$" + variableY[0].Range[1] + "$" + temp + ")";
}
var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value;
if (cellValue < -214682680)
{
cellValue = 0; // if cellValue is the result of a division by 0, set value to 0
}
pValues[index - 1] = cellValue;
if (cellValue > 1)
{
MessageBox.Show("Cannot generate P-Chart; Data in " + dataSet.Name + " contains samples greater than 1");
return;
}
cellValue = (double)(sheet.Cells[row, column + 1] as Range).Value;
if (cellValue < -214682680)
{
cellValue = 0; // if cellValue is the result of a division by 0, set value to 0
}
sample[index - 1] = cellValue;
}
for (index = startindex; index <= stopindex; index++)
{
pValuesInRange[index - startindex] = pValues[index];
}
for (index = 0; index <= dataSet.rangeSize() - 1; index++)
{
pChartCorrection[index] = Math.Sqrt((pValuesInRange.Average() * (1 - pValuesInRange.Average())) / sample[index]);
averageOfPValues[index] = pValuesInRange.Average();
pChartUpperControlLimit[index] = pValues.Average() + 3.0 * pChartCorrection[index];
pChartLowerControlLimit[index] = pValues.Average() - 3.0 * pChartCorrection[index];
}
// new arrays for subsets of existing arrays but within limits
double[] plotpValues = new double[plotstopindex - plotstartindex + 1];
double[] plotaverageOfPValues = new double[plotstopindex - plotstartindex + 1];
double[] plotpChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1];
double[] plotpChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1];
int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1];
for (int i = plotstartindex; i <= plotstopindex; i++)
{
plotpValues[i - plotstartindex] = pValues[i];
plotaverageOfPValues[i - plotstartindex] = averageOfPValues[i];
plotpChartLowerControlLimit[i - plotstartindex] = pChartLowerControlLimit[i];
plotpChartUpperControlLimit[i - plotstartindex] = pChartUpperControlLimit[i];
ArrayIndex[i - plotstartindex] = i;
}
var Xcharts = (ChartObjects)sheet.ChartObjects();
var XchartObject = Xcharts.Add(340, 20, 550, 300);
var Xchart = XchartObject.Chart;
Xchart.ChartType = XlChartType.xlXYScatterLines;
Xchart.ChartWizard(Title: "P-Chart " + dataSet.Name, HasLegend: true);
var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection();
var pseries = XseriesCollection.NewSeries();
var CLseries = XseriesCollection.NewSeries();
var UCLseries = XseriesCollection.NewSeries();
var LCLseries = XseriesCollection.NewSeries();
pseries.Name = ("Proportion");
CLseries.Name = ("Center line");
UCLseries.Name = ("UCL");
LCLseries.Name = ("LCL");
pseries.Values = plotpValues;
CLseries.Values = plotaverageOfPValues;
UCLseries.Values = plotpChartUpperControlLimit;
LCLseries.Values = plotpChartLowerControlLimit;
pseries.XValues = ArrayIndex;
CLseries.XValues = ArrayIndex;
UCLseries.XValues = ArrayIndex;
LCLseries.XValues = ArrayIndex;
}
public static int Main()
{
Console.WriteLine("Creating new Excel.Application");
Application app = new Application();
if (app == null)
{
Console.WriteLine("ERROR: EXCEL couldn't be started!");
return(0);
}
Console.WriteLine("Making application visible");
app.Visible = true;
Console.WriteLine("Getting the workbooks collection");
Workbooks workbooks = app.Workbooks;
Console.WriteLine("Adding a new workbook");
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Console.WriteLine("Getting the worksheets collection");
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
Console.WriteLine("ERROR: worksheet == null");
}
Console.WriteLine("Setting the value for cell");
// This paragraph puts the value 5 to the cell G1
Range range1 = worksheet.get_Range("G1", Missing.Value);
if (range1 == null)
{
Console.WriteLine("ERROR: range == null");
}
const int nCells = 5;
range1.Value2 = nCells;
// This paragraph sends single dimension array to Excel
Range range2 = worksheet.get_Range("A1", "E1");
int[] array2 = new int [nCells];
for (int i = 0; i < array2.GetLength(0); i++)
{
array2[i] = i + 1;
}
range2.Value2 = array2;
// This paragraph sends two dimension array to Excel
Range range3 = worksheet.get_Range("A2", "E3");
int[,] array3 = new int [2, nCells];
for (int i = 0; i < array3.GetLength(0); i++)
{
for (int j = 0; j < array3.GetLength(1); j++)
{
array3[i, j] = i * 10 + j;
}
}
range3.Value2 = array3;
// This paragraph reads two dimension array from Excel
Range range4 = worksheet.get_Range("A2", "E3");
Object[,] array4;
array4 = (Object[, ])range4.Value2;
for (int i = array4.GetLowerBound(0); i <= array4.GetUpperBound(0); i++)
{
for (int j = array4.GetLowerBound(1); j <= array4.GetUpperBound(1); j++)
{
if ((double)array4[i, j] != array3[i - 1, j - 1])
{
Console.WriteLine("ERROR: Comparison FAILED!");
return(0);
}
}
}
// This paragraph fills two dimension array with points for two curves and sends it to Excel
Range range5 = worksheet.get_Range("A5", "J6");
double[,] array5 = new double[2, 10];
for (int j = 0; j < array5.GetLength(1); j++)
{
double arg = Math.PI / array5.GetLength(1) * j;
array5[0, j] = Math.Sin(arg);
array5[1, j] = Math.Cos(arg);
}
range5.Value2 = array5;
// The following code draws the chart
range5.Select();
ChartObjects chartobjects = (ChartObjects)worksheet.ChartObjects(Missing.Value);
ChartObject chartobject = (ChartObject)chartobjects.Add(10 /*Left*/, 100 /*Top*/, 450 /*Width*/, 250 /*Height*/);
_Chart chart = (_Chart)chartobject.Chart;
// Call to chart.ChartWizard() is shown using late binding technique solely for the demonstration purposes
Object[] args7 = new Object[11];
args7[0] = range5; // Source
args7[1] = XlChartType.xl3DColumn; // Gallery
args7[2] = Missing.Value; // Format
args7[3] = XlRowCol.xlRows; // PlotBy
args7[4] = 0; // CategoryLabels
args7[5] = 0; // SeriesLabels
args7[6] = true; // HasLegend
args7[7] = "Sample Chart"; // Title
args7[8] = "Sample Category Type"; // CategoryTitle
args7[9] = "Sample Value Type"; // ValueTitle
args7[10] = Missing.Value; // ExtraTitle
chart.GetType().InvokeMember("ChartWizard", BindingFlags.InvokeMethod, null, chart, args7);
Console.WriteLine("Press ENTER to finish the sample:");
Console.ReadLine();
try {
// If user interacted with Excel it will not close when the app object is destroyed, so we close it explicitely
workbook.Saved = true;
app.UserControl = false;
app.Quit();
} catch (COMException) {
Console.WriteLine("User closed Excel manually, so we don't have to do that");
}
Console.WriteLine("Sample successfully finished!");
return(100);
}
public static void CreateColClusterBarGraph(
int left, int top, int width, int height,
_Worksheet oSheet, Range dataRange,
string strXAxis, string strChartTitle, double titleFontSize,
string strXTitle, double xFontSize, List <string> listLegend,
string strYTitle, double yFontSize, XlRowCol rowCol)
{
ChartObjects chartObjs = (ChartObjects)oSheet.ChartObjects(Type.Missing);
ChartObject chartObj = chartObjs.Add(left + 100, top, 425, 315);
Chart xlChart = chartObj.Chart;
xlChart.SetSourceData(dataRange, Type.Missing);
xlChart.ChartType = XlChartType.xlColumnClustered;
xlChart.PlotBy = rowCol;
// Xaxis data labels
Series oS = (Series)xlChart.SeriesCollection(1);
string strVersion = m_oXL.Application.Version;
if (strVersion == "11.0") // Office 2003 Excel
{
string strStartRow, strEndRow, strStartCol, strEndCol;
int nPos = -1, nEnd = -1, nStartCol, nEndCol, nStartRow, nEndRow;
// Sample incoming: "='Total Lane-Miles Per
// Condition'!$B$5:$F$5"; or "='Total Lane-Miles Per
// Condition'!$B$5:$B$10";
nPos = strXAxis.IndexOf('$');
if (nPos > -1)
{
nEnd = strXAxis.IndexOf('$', nPos + 1);
strStartCol = strXAxis.Substring(nPos + 1, nEnd - nPos - 1);
nPos = strXAxis.IndexOf(':');
strStartRow = strXAxis.Substring(nEnd + 1, nPos - nEnd - 1);
nPos = strXAxis.IndexOf('$', nPos);
nEnd = strXAxis.IndexOf('$', nPos + 1);
strEndCol = strXAxis.Substring(nPos + 1, nEnd - nPos - 1);
strEndRow = strXAxis.Substring(nEnd + 1);
nStartRow = int.Parse(strStartRow);
nEndRow = int.Parse(strEndRow);
nStartCol = GetColumnNumber(strStartCol + strStartRow);
nEndCol = GetColumnNumber(strEndCol + strStartRow);
nPos = strXAxis.IndexOf('!');
strXAxis = strXAxis.Substring(0, nPos + 1);
strXAxis += "R" + nStartRow.ToString() + "C" + nStartCol.ToString() + ":R" + nEndRow.ToString() +
"C" + nEndCol.ToString();
}
}
oS.XValues = strXAxis;
// Add title:
if (strChartTitle != "")
{
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = strChartTitle;
}
// Xaxis title
if (strXTitle != "")
{
Axis xAxis = (Axis)xlChart.Axes(XlAxisType.xlCategory,
XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = strXTitle;
xAxis.AxisTitle.Font.Size = xFontSize;
}
// legend:
xlChart.HasLegend = false;
if (listLegend != null)
{
if (listLegend.Count > 0)
{
xlChart.HasLegend = true;
int seriesNum = 1;
foreach (string str in listLegend)
{
oS = (Series)xlChart.SeriesCollection(seriesNum);
oS.Name = str;
seriesNum++;
}
}
}
if (strYTitle != "")
{
Axis yAxis = (Axis)xlChart.Axes(XlAxisType.xlValue,
XlAxisGroup.xlPrimary);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = strYTitle;
yAxis.AxisTitle.Font.Size = yFontSize;
}
}
private void PrintVariables(_Worksheet _sheet, int _row, int _counter, Variable variable, DataSet dataSet)
{
var function = Globals.ExcelAddIn.Application.WorksheetFunction;
int numberOfBins = model.useBins && model.bins > 0 ? model.bins : (int)function.RoundUp(Math.Sqrt(function.Count(variable.getRange())), 0);
_sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
int length = Convert.ToInt32((_sheet.Cells[100, 100] as Range).Value);
_sheet.Cells[100, 100] = "";
int count = 0;
int i = 0;
int j = 0;
double temp;
double[] yData = new double[length];
string ran = variable.Range.ToString();
Array arr = dataSet.getWorksheet().Range[ran].Value;
double[] vals = new double[length];
foreach (var item in arr)
{
temp = Convert.ToDouble(item);
yData[count] = temp;
count++;
}
List <double> Y = new List <double>(yData);
Y.Sort();
yData = Y.ToArray();
double start = yData[0];
double end = yData[length - 1];
double step = (end - start) / numberOfBins;
List <List <double> > finalList = new List <List <double> >();
i = 0;
double startVal = start;
double endVal = startVal + step;
while (i < numberOfBins)
{
List <double> tempList = calcVal(finalList, yData, startVal, endVal);
finalList.Add(tempList);
startVal = endVal;
endVal = endVal + step;
i++;
}
startVal = start;
endVal = startVal + step;
for (int bin = 0; bin < numberOfBins; bin++)
{
var column = 1;
var range = variable.getRange().Address(true, true, true);
_sheet.Cells[_row, column++] = "Bin #" + bin;
_sheet.Cells[_row, column++] = startVal;
_sheet.Cells[_row, column++] = endVal;
_sheet.Cells[_row, column++] = startVal + (endVal - startVal) / 2.0;
//_sheet.WriteFunction(_row, column, bin == 0 ? "MIN(" + range + ")" : AddressConverter.CellAddress(_row - 1, column + 1)); column++;
//_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "+" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)"); column++;
//_sheet.Cells[_row, column] = "=(" + AddressConverter.CellAddress(_row, column - 2) + "+" + AddressConverter.CellAddress(_row, column - 1) + ")/2"; column++;
_sheet.WriteFunction(_row, column, "COUNTIF(" + range + ",\"<=\"&" + AddressConverter.CellAddress(_row, column - 2) + ")-COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(_row, column - 3) + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "COUNT(" + range + ")");
double mean = (_sheet.Cells[_row, column] as Range).Value;
column++;
_sheet.Cells[_row, column] = mean / (endVal - startVal);
startVal = endVal;
endVal = endVal + step;
//_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)");
_row++;
}
// Create the chart.
var charts = (ChartObjects)_sheet.ChartObjects();
var chartObject = charts.Add(400, 225 * _counter, 100 * numberOfBins, 200);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlColumnClustered;
chart.ChartWizard(Title: "Histogram - " + variable.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add(_sheet.Range[_sheet.Cells[_row - numberOfBins, 5], _sheet.Cells[_row - 1, 5]]);
series.ChartType = XlChartType.xlColumnClustered;
series.XValues = _sheet.Range[_sheet.Cells[_row - numberOfBins, 4], _sheet.Cells[_row - 1, 4]];
}
