public List <double[]> fillTable1(List <Variable> variablesD, List <Variable> variablesI, DataSet dataSet, int col, _Worksheet sheet)
{
List <double[]> final = new List <double[]>();
List <int> posN = new List <int>();
List <String> no = new List <String>();
List <int> posY = new List <int>();
string ran2 = variablesD[0].Range.ToString();
Array distCat = dataSet.getWorksheet().Range[ran2].Value;
no = distCat.OfType <String>().ToList();
int i = 0;
foreach (String str in no)
{
if (str.Equals("No"))
{
posN.Add(i);
}
else
{
posY.Add(i);
}
i++;
}
sheet.Cells[3, 2] = posN.Count;
sheet.Cells[4, 2] = posY.Count;
i = 0;
foreach (Variable var in variablesI)
{
double[] meanN = new double[posN.Count];
double[] meanY = new double[posY.Count];
ran2 = variablesI[i].Range.ToString();
distCat = dataSet.getWorksheet().Range[ran2].Value;
List <double> all = distCat.OfType <double>().ToList();
int j = 0;
foreach (int val in posN)
{
meanN[j] = all[val];
j++;
}
j = 0;
foreach (int val in posY)
{
meanY[j] = all[val];
j++;
}
final.Add(meanN);
final.Add(meanY);
sheet.Cells[3, 3 + i] = meanN.Average();
sheet.Cells[4, 3 + i] = meanY.Average();
i++;
}
return(final);
}
public void generateChart(Array arr, List <Variable> variables, _Worksheet sheet, DataSet dataSet)
{
sheet.Cells[1, 1] = "Autocorrelation Table";
sheet.Cells[1, 2] = variables[0].name;
sheet.Cells[2, 1] = "Number of Values";
sheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
int length = Convert.ToInt32((sheet.Cells[2, 2] as Range).Value);
sheet.Cells[3, 1] = "Standard Error";
sheet.Cells[3, 2] = 1.0 / Math.Sqrt(length);
int rows = 4;
int i = 1;
double[] vals = new double[length];
int count = 0;
foreach (var item in arr)
{
double temp = Convert.ToDouble(item);
vals[count] = temp;
count = count + 1;
}
double var = sheet.Application.WorksheetFunction.Var(vals);
double average = vals.Average();
while (i <= 12)
{
double solution = 0;
double[] temp1 = new double[length - i];
double[] temp2 = new double[length - i];
List <double> valsL = new List <double>(vals);
valsL.RemoveRange(0, i);
valsL.CopyTo(temp1);
List <double> valsL2 = new List <double>(vals);
//valsL2.Reverse();
valsL2.RemoveRange(length - i, i);
//valsL2.Reverse();
valsL2.CopyTo(temp2);
int j = 0;
while (j < valsL2.Count)
{
solution = solution + ((temp2[j] - average) * (temp1[j] - average));
j++;
}
solution = solution / var;
//double covar = sheet.Application.WorksheetFunction.Covar(temp1, temp2);
sheet.Cells[rows, 1] = "Lag #" + i;
sheet.Cells[rows++, 2] = solution / (length - 1);
i = i + 1;
}
}
public double[,] calcXdata(int length, DataSet dataSet, List <Variable> variablesI)
{
System.Diagnostics.Debug.WriteLine("lengte xdata is = {0}", length);
int count = 0;
double[,] xData = new double[length, variablesI.Count + 1];
//bereken (Xi-Xgem)
while (count < length)
{
xData[count, 0] = 1;
count++;
}
count = 0;
foreach (Variable var in variablesI)
{
int i = 0;
string ran = variablesI[count].Range.ToString();
Array arr = dataSet.getWorksheet().Range[ran].Value;
double[] vals = new double[length];
foreach (var item in arr)
{
if (item.GetType() == typeof(string))
{
MessageBox.Show("Please use numeric data", "Invalid data type error");
break;
}
double temp = Convert.ToDouble(item);
xData[i, count + 1] = temp;
//xData[count,i] = (temp - meansI[count]);
i++;
}
count++;
}
return(xData);
}
public static DataSet modify(DataSet dataSet, string newRange)
{
string tmp = dataSet.Range;
int temp = tmp.IndexOf(':');
int start = tmp.IndexOf('$', temp + 1) + 1;
char[] ch = tmp.ToCharArray();
ch[start] = newRange.ToCharArray()[0];
string newtmp = new string(ch);
System.Diagnostics.Debug.WriteLine(tmp);
System.Diagnostics.Debug.WriteLine(newtmp);
Range range = dataSet.getWorksheet().Range[newtmp];
range.Select();
List <Variable> variables = createVariables(dataSet.getWorksheet(), range, dataSet.getRangeLayout(), dataSet.getVariableNamesInFirstRowOrColumn());
DataSet test = new DataSet(dataSet.getWorksheet(), range, dataSet.getName(), dataSet.getRangeLayout(), dataSet.getVariableNamesInFirstRowOrColumn(), variables);
return(test);
}
public void setVariableNamesInFirstRowOrColumn(DataSet dataSet, bool variableNamesInFirstRowOrColumn)
{
if (dataSet == null)
{
return;
}
DataSet newDataSet = DataSetFactory.create(dataSet.getWorksheet(), dataSet.getRange(), dataSet.getName(), dataSet.getRangeLayout(),
variableNamesInFirstRowOrColumn);
model.swapDataSets(dataSet, newDataSet);
}
public void createConfidence(List <Variable> variables, DataSet dataSet)
{
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Confidence");
if (variables.Count == 1)
{
foreach (Variable variable in variables)
{
worksheet.Cells[1, 1] = "Conf. intervals (Proportion)";
worksheet.Cells[1, 2] = variable.name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
worksheet.Cells[3, 1] = "Sample proportion";
worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variable.Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value;
PrintCategories(worksheet, 4, dataSet);
}
}
else if (variables.Count == 2)
{
worksheet.Cells[1, 1] = "Conf. intervals (Proportion)";
worksheet.Cells[1, 2] = variables[0].name;
worksheet.Cells[1, 3] = variables[1].name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")";
worksheet.Cells[3, 1] = "Sample proportion";
worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value;
worksheet.Cells[3, 3] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + "," + 0 + ") / " + (worksheet.Cells[2, 3] as Range).Value;
PrintCategories2(worksheet, 4, dataSet);
}
}
public bool checkInput(List <Variable> variablesX, DataSet dataSet)
{
string ran = variablesX[0].Range.ToString();
Array arr = dataSet.getWorksheet().Range[ran].Value;
if (dataSet != null)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Autocorrelation");
generateChart(arr, variablesX, sheet, dataSet);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to generate Autocorrelation", "Autocorrelation error");
}
return(false);
}
public double[] calcYdata(List <Variable> variablesD, DataSet dataSet, int length)
{
//bereken (Yi-Ygem)
int count = 0;
double[] yData = new double[length];
foreach (Variable var in variablesD)
{
string ran = variablesD[0].Range.ToString();
Array arr = dataSet.getWorksheet().Range[ran].Value;
double[] vals = new double[length];
foreach (var item in arr)
{
double temp = Convert.ToDouble(item);
yData[count] = temp;
//temp2[count] = (temp - meansD);
count++;
}
}
return(yData);
}
public void createHypothese(List <Variable> variables, DataSet dataSet)
{
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Hypothesis test");
if (variables.Count == 1)
{
foreach (Variable variable in variables)
{
worksheet.Cells[1, 1] = "Hypothesis test";
worksheet.Cells[1, 2] = variable.name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
worksheet.Cells[3, 1] = "Sample mean";
worksheet.Cells[3, 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
worksheet.Cells[4, 1] = "Sample Std. Dev";
worksheet.Cells[4, 2] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
if (model.equal)
{
PrintCategories(worksheet, 5, "equal", dataSet);
}
else if (model.greater)
{
PrintCategories(worksheet, 5, "greater", dataSet);
}
}
}
else if (variables.Count == 2)
{
worksheet.Cells[1, 1] = "Hypothesis test";
worksheet.Cells[1, 2] = variables[0].name + " - " + variables[1].name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
worksheet.Cells[3, 1] = "Sample mean";
worksheet.Cells[3, 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ") - AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")";
worksheet.Cells[4, 1] = "Sample Std. Dev";
int length = Convert.ToInt32((worksheet.Cells[2, 2] as Range).Value);
double[] vals1 = new double[length];
string ran1 = variables[0].Range.ToString();
Array arr1 = dataSet.getWorksheet().Range[ran1].Value;
double[] vals2 = new double[length];
string ran2 = variables[1].Range.ToString();
Array arr2 = dataSet.getWorksheet().Range[ran2].Value;
int count = 0;
foreach (var item in arr1)
{
double temp = Convert.ToDouble(item);
vals1[count] = temp;
count = count + 1;
}
count = 0;
foreach (var item in arr2)
{
double temp = Convert.ToDouble(item);
vals2[count] = temp;
count = count + 1;
}
count = 0;
while (count < vals2.Length)
{
vals2[count] = vals2[count] - vals1[count];
count++;
}
double std = worksheet.Application.WorksheetFunction.StDev(vals2);
worksheet.Cells[4, 2] = std;
//worksheet.Cells[4, 2] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ") + STDEV(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")";
if (model.equal)
{
PrintCategories(worksheet, 5, "equal", dataSet);
}
else if (model.greater)
{
PrintCategories(worksheet, 5, "greater", dataSet);
}
}
}
public void createCorrelationCovariance(List <Variable> variablesX, DataSet dataSet)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Correlation Covariance");
int col = 1;
int row = 1;
int countX = variablesX.Count;
int i = 0;
sheet.Cells[row, col] = "Correlation";
sheet.Cells[row + countX + 2, col] = "Covariance";
while (i < variablesX.Count)
{
col = col + 1;
sheet.Cells[row, col] = variablesX[i].name.ToString();
i++;
}
i = 0;
col = 1;
row = 1;
while (i < variablesX.Count)
{
row = row + 1;
sheet.Cells[row, col] = variablesX[i].name.ToString();
i++;
}
row = countX + 3;
col = 1;
i = 0;
while (i < variablesX.Count)
{
col = col + 1;
sheet.Cells[row, col] = variablesX[i].name.ToString();
i++;
}
i = 0;
col = 1;
row = countX + 3;
while (i < variablesX.Count)
{
row = row + 1;
sheet.Cells[row, col] = variablesX[i].name.ToString();
i++;
}
// CORRELATION
i = 0;
row = 2;
col = 2;
while (i < variablesX.Count)
{
String range1 = variablesX[i].Range;
int j = 0;
while (j < variablesX.Count)
{
String range2 = variablesX[j].Range;
sheet.Cells[row + i, col + j] = "=CORREL(" + dataSet.getWorksheet().Name + "!" + range1 + "," + dataSet.getWorksheet().Name + "!" + range2 + ")";
j++;
}
i++;
}
// COVARIANCE
i = 0;
row = countX + 4;
col = 2;
while (i < variablesX.Count)
{
String range1 = variablesX[i].Range;
int j = 0;
while (j < variablesX.Count)
{
String range2 = variablesX[j].Range;
sheet.Cells[row + i, col + j] = "=COVARIANCE.S(" + dataSet.getWorksheet().Name + "!" + range1 + "," + dataSet.getWorksheet().Name + "!" + range2 + ")";
j++;
}
i++;
}
}
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 Interaction(Variable variable, Variable variable2, DataSet dataSet) //cat = gender var = salary
{
String colLetter = dataSet.getVariables()[dataSet.getVariables().Count - 1].Range[1].ToString();
int columnIndex = ColumnLetterToColumnIndex(colLetter) + 1;
string ran2 = variable.Range.ToString();
String colLetterCat = variable.Range[1].ToString();
int columnIndexCat = ColumnLetterToColumnIndex(colLetterCat) - 1;
Array distCat = dataSet.getWorksheet().Range[ran2].Value;
Array cat = dataSet.getWorksheet().Range[ran2].Value;
string ran = variable2.Range.ToString();
String colLetterVar = variable2.Range[1].ToString();
int columnIndexVar = ColumnLetterToColumnIndex(colLetterVar) - 1;
Array dist = dataSet.getWorksheet().Range[ran].Value;
int count = 0;
foreach (var item in distCat)
{
if (item == null)
{
count = 2;
}
else if (item.GetType().ToString() == "System.String")
{
count = 1;
break;
}
else
{
count = 2;
break;
}
}
foreach (var item in dist)
{
if (item == null)
{
count = 2;
}
else if (item.GetType().ToString() == "System.String")
{
count = 1;
break;
}
else
{
count = 2;
break;
}
}
if (count == 1)
{
MessageBox.Show("Interaction can only be calculated for numbers.");
return;
}
double[] var1 = distCat.OfType <double>().ToArray();
double[] var2 = dist.OfType <double>().ToArray();
System.Diagnostics.Debug.WriteLine(var1.Length);
System.Diagnostics.Debug.WriteLine(var2.Length);
int row = 1;
int column = columnIndex;
worksheet.Cells[row, column] = dataSet.getVariables()[columnIndexCat].name + " - " + dataSet.getVariables()[columnIndexVar].name;
row = 0;
int len = var1.Length;
if (var2.Length < var1.Length)
{
len = var2.Length;
}
while (row < len)
{
if (var1.Length < var2.Length)
{
column = columnIndex;
int temp = var2.Length - var1.Length;
worksheet.Cells[row + 2 + temp, column] = var1[row] * var2[row + temp];
row = row + 1;
}
else
{
column = columnIndex;
int temp = var1.Length - var2.Length;
worksheet.Cells[row + 2 + temp, column] = var1[row + temp] * var2[row];
row = row + 1;
}
}
DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column));
dataSet.setRange(nwRange.getRange());
dataSet.setVariables(nwRange.getVariables());
}
public void addUnstacked(Variable category, Variable variable, DataSet dataSet) //cat = gender var = salary
{
string ran2 = category.Range.ToString();
String colLetterCat = category.Range[1].ToString();
int columnIndexCat = ColumnLetterToColumnIndex(colLetterCat) - 1;
Array distCat = dataSet.getWorksheet().Range[ran2].Value;
Array cat = dataSet.getWorksheet().Range[ran2].Value;
string ran = variable.Range.ToString();
String colLetterVar = variable.Range[1].ToString();
int columnIndexVar = ColumnLetterToColumnIndex(colLetterVar) - 1;
Array dist = dataSet.getWorksheet().Range[ran].Value;
int count = 0;
Worksheet newworksheet;
try
{
newworksheet = dataSet.getWorksheet().Application.Worksheets.Add();
newworksheet.Name = "Unstack" + dataSet.getVariables()[columnIndexVar].name + "-" + dataSet.getVariables()[columnIndexCat].name;
}
catch (SystemException e)
{
MessageBox.Show("Unstacked data already exists, change name of existing worksheet and try again.");
return;
}
foreach (var item in distCat)
{
if (item == null)
{
count = 2;
}
else if (item.GetType().ToString() == "System.String")
{
count = 1;
}
else
{
count = 2;
}
break;
}
if (count == 1)
{
List <String> valuesCat = distCat.OfType <String>().ToList();
List <Double> valuesVar = dist.OfType <Double>().ToList();
distCat = valuesCat.Distinct <String>().ToArray();
int row = 1;
int column = 1;
foreach (var item in distCat)
{
newworksheet.Cells[row, column] = dataSet.getVariables()[columnIndexVar].name + "(" + item.ToString() + ")";
column = column + 1;
}
column = 1;
foreach (var item in distCat)
{
row = 0;
int counter = row;
while (row < valuesVar.Count)
{
String temp = valuesCat[row];
if (temp.Equals(item.ToString()))
{
newworksheet.Cells[counter + 2, column] = valuesVar[row];
counter = counter + 1;
}
row = row + 1;
}
column = column + 1;
}
}
else
{
List <Double> valueSortCat = distCat.OfType <Double>().ToList(); // één lijst gaat gesorteerd worden en de andere niet
List <Double> valueCat = distCat.OfType <Double>().ToList();
List <Double> valuesVar = dist.OfType <Double>().ToList();
valueSortCat.Sort();
distCat = valueSortCat.Distinct <Double>().ToArray();
int row = 1;
int column = 1;
foreach (var item in distCat)
{
newworksheet.Cells[row, column] = dataSet.getVariables()[columnIndexVar].name + "(" + item.ToString() + ")";
column = column + 1;
}
column = 1;
foreach (var item in distCat)
{
row = 0;
int counter = row;
while (row < valuesVar.Count)
{
double temp = valueCat[row];
if (temp == Convert.ToDouble(item.ToString()))
{
newworksheet.Cells[counter + 2, column] = valuesVar[row];
counter = counter + 1;
}
row = row + 1;
}
column = column + 1;
}
}
}
public void addDummy(Variable variable, DataSet dataSet)
{
string ran = variable.Range.ToString();
String colLetter2 = variable.Range[1].ToString();
int columnIndex2 = ColumnLetterToColumnIndex(colLetter2) - 1;
String colLetter = dataSet.getVariables()[dataSet.getVariables().Count - 1].Range[1].ToString();
int columnIndex = ColumnLetterToColumnIndex(colLetter) + 1;
Array dist = dataSet.getWorksheet().Range[ran].Value;
int count = 0;
foreach (var item in dist)
{
if (item.GetType().ToString() == "System.String")
{
count = 1;
}
else
{
count = 2;
}
break;
}
if (count == 1)
{
List <String> values = dist.OfType <String>().ToList();
dist = values.Distinct <String>().ToArray();
int row = 1;
int column = columnIndex;
foreach (var item in dist)
{
worksheet.Cells[row, column] = dataSet.getVariables()[columnIndex2].name + "=" + item.ToString();
column = column + 1;
}
row = 0;
while (row < values.Count)
{
String temp = values[row];
column = columnIndex;
foreach (var item in dist)
{
if (temp.Equals(item.ToString()))
{
worksheet.Cells[row + 2, column] = "1";
}
else
{
worksheet.Cells[row + 2, column] = "0";
}
column = column + 1;
}
row = row + 1;
}
DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column - 1));
dataSet.setRange(nwRange.getRange());
dataSet.setVariables(nwRange.getVariables());
}
else
{
List <Double> values = dist.OfType <Double>().ToList();
List <Double> value = dist.OfType <Double>().ToList();
values.Sort();
dist = values.Distinct <Double>().ToArray();
int row = 1;
int column = columnIndex;
foreach (var item in dist)
{
worksheet.Cells[row, column] = dataSet.getVariables()[columnIndex2].name + "=" + item.ToString();
column = column + 1;
}
row = 0;
while (row < values.Count)
{
double temp = value[row];
column = columnIndex;
foreach (var item in dist)
{
if (temp == Convert.ToInt16(item))
{
worksheet.Cells[row + 2, column] = "1";
}
else
{
worksheet.Cells[row + 2, column] = "0";
}
column = column + 1;
}
row = row + 1;
}
DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column - 1));
dataSet.setRange(nwRange.getRange());
dataSet.setVariables(nwRange.getVariables());
}
}
public void generateProcessCapability(int LSL, int USL, DataSet dataSet, _Worksheet sheet)
{
int index = 0;
int row = 1;
int column = 1;
double correctionFactor1, correctionFactor2;
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()];
int[] ArrayIndex = new int[dataSet.amountOfVariables()];
double[] constantC4 = new double[25] {
0.0, 0.7979, 0.8862, 0.9213, 0.9400, 0.9515, 0.9594, 0.9650, 0.9693, 0.9727, 0.9754, 0.9776, 0.9794, 0.9810, 0.9823, 0.9835, 0.9845, 0.9854, 0.9862, 0.9869, 0.9876, 0.9882, 0.9887, 0.9892, 0.9896
};
double[] constantD2 = 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
};
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";
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;
ArrayIndex[index - 1] = index;
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
}
averages[index - 1] = cellValue;
cellValue = (double)(sheet.Cells[row, column + 5] as Range).Value;
Rvalues[index - 1] = cellValue;
}
// 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())
{
correctionFactor1 = constantC4[safe - 1];
correctionFactor2 = constantD2[safe - 1];
}
else
{
correctionFactor1 = constantC4[safe];
}
correctionFactor2 = constantD2[safe];
double sigma = Rvalues.Average() / correctionFactor2;
double Cp = (USL - LSL) / (6 * sigma);
double Cpk = Math.Min((USL - averages.Average()) / (3 * sigma), (averages.Average() - LSL) / (3 * sigma));
row = 1;
column = 1;
sheet.Cells[row, column + 7] = "LSL";
sheet.Cells[row, column + 8] = "USL";
sheet.Cells[row, column + 9] = "Cp";
sheet.Cells[row, column + 10] = "Cpk";
row++;
sheet.Cells[row, column + 7] = LSL;
sheet.Cells[row, column + 8] = USL;
sheet.Cells[row, column + 9] = Cp;
sheet.Cells[row, column + 10] = Cpk;
}
public void createChiKwadraatPlot(DataSet dataSet)
{
string range = model.range.Replace("$", null);
//string range = model.range;
//string[] splitted = range.Split(':');
string first = range.Split(':')[0];
string second = range.Split(':')[1];
/*if (splitted[0].Length > 4)
* {
* int i = splitted[0].Length;
* first = splitted[0][1] + Convert.ToString(splitted[0][3]);
* int j = 4;
* while (j < i)
* {
* first = first + Convert.ToString(splitted[0][j]);
* j++;
* }
* }
* else
* {
* first = splitted[0][1] + Convert.ToString(splitted[0][3]);
* }
* if (splitted[1].Length > 4)
* {
* int i = splitted[1].Length;
* second = splitted[1][1] + Convert.ToString(splitted[1][3]);
* int j = 4;
* while (j < i)
* {
* second = second + Convert.ToString(splitted[1][j]);
* j++;
* }
* }
* else
* {
* second = splitted[1][1] + Convert.ToString(splitted[1][3]);
* }*/
_Worksheet sheet = WorksheetHelper.NewWorksheet("Chi Square");
Range from = dataSet.getWorksheet().Range[first, second];
int rangeWidth = from.Columns.Count;
int rangeHeight = from.Rows.Count;
//original Counts
sheet.Cells[2, 1] = "Original Counts";
//sheet.get_Range("A2").Cells.HorizontalAlignment = XlHAlign.xlHAlignFill;
sheet.get_Range("A2", AddressConverter.CellAddress(2, rangeWidth + 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
Range to = sheet.Range[sheet.Cells[3, 2], sheet.Cells[3, 2].offset(rangeHeight, rangeWidth)];
from.Copy(to);
int col = 2;
while (col < rangeWidth + 2)
{
int row = 3;
double sum = 0;
while (row < rangeHeight + 3)
{
sum += Convert.ToDouble((sheet.Cells[row, col] as Range).Value);
row++;
}
sheet.Cells[3 + rangeHeight, col] = sum;
col++;
}
sheet.Cells[2, col] = "Total";
int roww = 3;
while (roww < rangeHeight + 4)
{
int coll = 2;
double summ = 0;
while (coll < rangeWidth + 2)
{
summ += Convert.ToDouble((sheet.Cells[roww, coll] as Range).Value);
coll++;
}
sheet.Cells[roww, 2 + rangeWidth] = summ;
roww++;
}
sheet.Cells[roww - 1, 1] = "Total";
// percentage of Rows
sheet.Cells[rangeHeight + 6, 1] = "Percentage of Rows";
sheet.get_Range("A2", AddressConverter.CellAddress(rangeHeight + 6, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowb = rangeHeight + 7;
while (rowb < rangeHeight + rangeHeight + 7)
{
int cola = 2;
while (cola < rangeWidth + 2)
{
double proda = 0;
proda = Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, cola] as Range).Value) / Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, rangeWidth + 2] as Range).Value);
sheet.Cells[rowb, cola] = Math.Round(proda * 100, 2);
//sheet.Range[rowb, cola].NumberFormat = "0.00%";
cola++;
}
rowb++;
}
//Percentage of Colums
sheet.Cells[rangeHeight + rangeHeight + 9, 1] = "Percentage of Colums";
sheet.get_Range("A2", AddressConverter.CellAddress(2 * rangeHeight + 9, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowd = rangeHeight + rangeHeight + 10;
while (rowd < rangeHeight + rangeHeight + rangeHeight + 10)
{
int colc = 2;
while (colc < rangeWidth + 2)
{
double prodc = 0;
prodc = Convert.ToDouble((sheet.Cells[rowd - rangeHeight - rangeHeight - 7, colc] as Range).Value) / Convert.ToDouble((sheet.Cells[3 + rangeHeight, colc] as Range).Value);
sheet.Cells[rowd, colc] = Math.Round(prodc * 100, 2);
//sheet.Range[rowd, colc].NumberFormat = "0.00%";
colc++;
}
rowd++;
}
// Expected Counts
sheet.Cells[3 * (rangeHeight) + 12, 1] = "Expected Counts";
sheet.get_Range("A2", AddressConverter.CellAddress(3 * rangeHeight + 12, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowe = 3 * rangeHeight + 13;
while (rowe < 4 * (rangeHeight) + 13)
{
int cole = 2;
while (cole < rangeWidth + 2)
{
double prode = 0;
prode = Convert.ToDouble((sheet.Cells[rangeHeight + 3, cole] as Range).Value)
* Convert.ToDouble((sheet.Cells[rowe - 3 * (rangeHeight) - 10, rangeWidth + 2] as Range).Value)
/ Convert.ToDouble((sheet.Cells[rangeHeight + 3, rangeWidth + 2] as Range).Value);
sheet.Cells[rowe, cole] = prode;
cole++;
}
rowe++;
}
//distance ftom expected
sheet.Cells[4 * rangeHeight + 15, 1] = "Distance from Expected";
sheet.get_Range("A2", AddressConverter.CellAddress(4 * rangeHeight + 15, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowf = 4 * rangeHeight + 16;
while (rowf < 5 * rangeHeight + 16)
{
int colf = 2;
while (colf < rangeWidth + 2)
{
double prodf = 0;
double tussenprod = Convert.ToDouble((sheet.Cells[rowf - 4 * rangeHeight - 13, colf] as Range).Value)
- Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value);
prodf = Math.Pow(tussenprod, 2)
/ Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value);
sheet.Cells[rowf, colf] = Math.Round(prodf, 4);
colf++;
}
rowf++;
}
//chi square dings
sheet.Cells[5 * rangeHeight + 18, 1] = "Chi-Square Statistic";
sheet.Cells[5 * rangeHeight + 19, 1] = "CHi-Square";
sheet.Cells[5 * rangeHeight + 20, 1] = "p-value";
//=1-CHIKW.VERD(B39;3*3;1)
sheet.get_Range("A2", AddressConverter.CellAddress(5 * rangeHeight + 18, 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowg = 4 * rangeHeight + 16;
double som = 0;
while (rowg < 5 * rangeHeight + 16)
{
int colg = 2;
while (colg < rangeWidth + 2)
{
som += Convert.ToDouble((sheet.Cells[rowg, colg] as Range).Value);
colg++;
}
rowg++;
}
sheet.Cells[5 * rangeHeight + 19, 2] = som;
sheet.Cells[5 * rangeHeight + 20, 2] = sheet.Application.WorksheetFunction.ChiSq_Dist(som, (rangeHeight - 1) * (rangeWidth - 1), false);
//autofit
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 7]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 8]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 9]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 10]).EntireColumn.AutoFit();
// Merge Headers
sheet.Range[sheet.Cells[1, 2], sheet.Cells[1, rangeWidth + 2]].Merge();
sheet.Range[sheet.Cells[rangeHeight + 5, 2], sheet.Cells[rangeHeight + 5, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[2 * rangeHeight + 8, 2], sheet.Cells[2 * rangeHeight + 8, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[3 * rangeHeight + 11, 2], sheet.Cells[3 * rangeHeight + 11, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[4 * rangeHeight + 14, 2], sheet.Cells[4 * rangeHeight + 14, rangeWidth + 1]].Merge();
// Build Headers
var header = "";
if (model.hasRowTitle)
{
header += "Rows: {0}";
}
if (model.hasRowTitle && model.hasColTitle)
{
header += " / ";
}
if (model.hasColTitle)
{
header += "Columns: {1}";
}
header = String.Format(header, model.rowTitle, model.colTitle);
// Fill in Headers
sheet.Cells[1, 2] = header;
sheet.Cells[rangeHeight + 5, 2] = header;
sheet.Cells[2 * rangeHeight + 8, 2] = header;
sheet.Cells[3 * rangeHeight + 11, 2] = header;
sheet.Cells[4 * rangeHeight + 14, 2] = header;
// Center Headers
sheet.Cells[1, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[rangeHeight + 5, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[2 * rangeHeight + 8, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[3 * rangeHeight + 11, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[4 * rangeHeight + 14, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
// Row and column titles
if (model.hasRowColHeaders)
{
String[] rowTitles = new String[rangeHeight];
String[] colTitles = new String[rangeWidth];
for (int i = 0; i < rangeHeight; i++)
{
rowTitles[i] = dataSet.getWorksheet().Range[first].Offset[i, -1].Value;
}
for (int i = 0; i < rangeWidth; i++)
{
colTitles[i] = dataSet.getWorksheet().Range[first].Offset[-1, i].Value;
}
for (int i = 0; i < rangeHeight; i++)
{
sheet.Cells[3 + i, 1] = rowTitles[i];
sheet.Cells[7 + rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[10 + 2 * rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[13 + 3 * rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[16 + 4 * rangeHeight + i, 1] = rowTitles[i];
}
for (int i = 0; i < rangeWidth; i++)
{
sheet.Cells[2, 2 + i] = colTitles[i];
sheet.Cells[6 + rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[9 + 2 * rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[12 + 3 * rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[15 + 4 * rangeHeight, 2 + i] = colTitles[i];
}
}
}
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 void createHypothese(List <Variable> variables, DataSet dataSet)
{
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Hypothesis test");
if (variables.Count == 1)
{
foreach (Variable variable in variables)
{
worksheet.Cells[1, 1] = "Hypothesis test";
worksheet.Cells[1, 2] = variable.name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
worksheet.Cells[3, 1] = "Sample proportion";
string ran = variable.Range.ToString();
Array dist = dataSet.getWorksheet().Range[ran].Value;
double count = 0;
foreach (var item in dist)
{
if (Convert.ToInt32(item) == 0)
{
System.Diagnostics.Debug.WriteLine(item);
count = count + 1;
}
}
double n = (worksheet.Cells[2, 2] as Range).Value;
double prop = count / n;
worksheet.Cells[3, 2] = prop;
if (model.equal)
{
PrintCategories(worksheet, 5, "equal", dataSet);
}
else if (model.greater)
{
PrintCategories(worksheet, 5, "greater", dataSet);
}
}
}
else if (variables.Count == 2)
{
worksheet.Cells[1, 1] = "Hypothesis test";
worksheet.Cells[1, 2] = variables[0].name;
worksheet.Cells[1, 3] = variables[1].name;
worksheet.Cells[2, 1] = "Sample size";
worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")";
worksheet.Cells[3, 1] = "Sample proportion";
string ran = variables[0].Range.ToString();
string ran1 = variables[1].Range.ToString();
Array dist = dataSet.getWorksheet().Range[ran].Value;
Array dist1 = dataSet.getWorksheet().Range[ran1].Value;
double count = 0;
double count1 = 0;
foreach (var item in dist)
{
if (Convert.ToInt32(item) == 0)
{
System.Diagnostics.Debug.WriteLine(item);
count = count + 1;
}
}
foreach (var item in dist1)
{
if (Convert.ToInt32(item) == 0)
{
System.Diagnostics.Debug.WriteLine(item);
count1 = count1 + 1;
}
}
double n = (worksheet.Cells[2, 2] as Range).Value;
double n1 = (worksheet.Cells[2, 3] as Range).Value;
double prop = count / n;
double prop1 = count1 / n1;
worksheet.Cells[3, 2] = prop;
worksheet.Cells[3, 3] = prop1;
if (model.equal)
{
PrintCategories2(worksheet, 5, "equal", dataSet);
}
else if (model.greater)
{
PrintCategories2(worksheet, 5, "greater", dataSet);
}
}
}
public void generateProcessCapability(List <Variable> variables, double LSL, double USL, DataSet dataSet, _Worksheet sheet)
{
int index = 0;
int row = 1;
int column = 1;
double correctionFactor1, correctionFactor2;
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()];
int[] ArrayIndex = new int[dataSet.amountOfVariables()];
double[] constantC4 = new double[25] {
0.0, 0.7979, 0.8862, 0.9213, 0.9400, 0.9515, 0.9594, 0.9650, 0.9693, 0.9727, 0.9754, 0.9776, 0.9794, 0.9810, 0.9823, 0.9835, 0.9845, 0.9854, 0.9862, 0.9869, 0.9876, 0.9882, 0.9887, 0.9892, 0.9896
};
double[] constantD2 = 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
};
sheet.Cells[row, column] = "Index";
sheet.Cells[row, column + 1] = "Observation";
sheet.Cells[row, column + 2] = "Average";
sheet.Cells[row, column + 3] = "STD DEV";
sheet.Cells[row, column + 4] = "Max";
sheet.Cells[row, column + 5] = "Min";
sheet.Cells[row, column + 6] = "R";
sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value);
sheet.Cells[100, 100] = "";
double temp = 0;
List <double> allVals = new List <double>();
double[] all = new double[length * variables.Count];
foreach (Variable var in variables)
{
string ran = var.Range.ToString();
Array arr = dataSet.getWorksheet().Range[ran].Value;
double[] vals = new double[length];
int count = 0;
foreach (var item in arr)
{
temp = Convert.ToDouble(item);
vals[count] = temp;
count++;
}
allVals.AddRange(vals);
}
all = allVals.ToArray();
double totMean = 0;
double totStd = 0;
for (index = 0; index < variables.Count; index++)
{
row++;
sheet.Cells[row, column] = index;
sheet.Cells[row, column + 1] = variables[index].name;
sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
sheet.Cells[row, column + 3] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
sheet.Cells[row, column + 4] = "=MAX(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
sheet.Cells[row, column + 5] = "=MIN(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
sheet.Cells[row, column + 6] = (double)(sheet.Cells[row, column + 4] as Range).Value - (double)(sheet.Cells[row, column + 5] as Range).Value;
totMean = totMean + Convert.ToDouble((sheet.Cells[row, column + 2] as Range).Value);
totStd = totStd + (Convert.ToDouble((sheet.Cells[row, column + 3] as Range).Value));
ArrayIndex[index] = index;
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
}
averages[index] = cellValue;
cellValue = (double)(sheet.Cells[row, column + 6] as Range).Value;
Rvalues[index] = cellValue;
}
totMean = all.Average();
totStd = sheet.Application.WorksheetFunction.StDev(all);
//totMean = totMean / variables.Count;
//totStd = totStd / variables.Count;
if (dataSet.getVariableNamesInFirstRowOrColumn())
{
correctionFactor1 = constantC4[variables.Count - 1];
correctionFactor2 = constantD2[variables.Count - 1];
}
else
{
correctionFactor1 = constantC4[variables.Count];
}
correctionFactor2 = constantD2[variables.Count];
System.Diagnostics.Debug.WriteLine(totStd);
double sigma = totStd;
double Cp = (USL - LSL) / (6 * sigma);
double Cpk = Math.Min((USL - totMean) / (3 * sigma), (totMean - LSL) / (3 * sigma));
double Pbelow = sheet.Application.WorksheetFunction.NormDist(LSL, totMean, totStd, true);
double Pabove = 1.0 - sheet.Application.WorksheetFunction.NormDist(USL, totMean, totStd, true);
row = 1;
column = 1;
sheet.Cells[row, column + 8] = "LSL";
sheet.Cells[row, column + 9] = "USL";
sheet.Cells[row, column + 10] = "Cp";
sheet.Cells[row, column + 11] = "Cpk";
sheet.Cells[row, column + 12] = "P(below LSL)";
sheet.Cells[row, column + 13] = " Per million";
sheet.Cells[row, column + 14] = "P(above USL)";
sheet.Cells[row, column + 15] = " Per million";
row++;
sheet.Cells[row, column + 8] = LSL;
sheet.Cells[row, column + 9] = USL;
sheet.Cells[row, column + 10] = Cp;
sheet.Cells[row, column + 11] = Cpk;
sheet.Cells[row, column + 12] = Pbelow;
sheet.Cells[row, column + 13] = Pbelow * 1000000;
sheet.Cells[row, column + 14] = Pabove;
sheet.Cells[row, column + 15] = Pabove * 1000000;
}
public void createRegression(List <Variable> variablesD, List <Variable> variablesI, DataSet dataSet, DataSet dataSet2, bool[] graphs, List <Variable> variablesPrediction)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Regression");
double confLevel = model.confidenceLevel;
sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variablesI[0].Range + ")";
int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value);
sheet.Cells[100, 100] = "";
int row = 1;
sheet.Cells[1, 1] = "Regression Summary";
sheet.Cells[2, 1] = "R";
sheet.Cells[3, 1] = "R-square";
sheet.Cells[4, 1] = "adjusted R-square";
sheet.Cells[5, 1] = "stErr of Estimate";
sheet.Cells[9, 1] = "ANOVA Table";
sheet.Cells[10, 1] = "Explained";
sheet.Cells[11, 1] = "Unexplained";
sheet.Cells[8, 3] = "Sum of";
sheet.Cells[9, 3] = "Squares";
sheet.Cells[8, 2] = "Degrees of";
sheet.Cells[9, 2] = "Freedom";
sheet.Cells[8, 4] = "Mean";
sheet.Cells[9, 4] = "Squares";
sheet.Cells[9, 5] = "F-Ratio";
sheet.Cells[9, 6] = "p-Value";
sheet.Cells[15, 1] = "Regression Table";
sheet.Cells[16, 1] = "Constant";
row = 17;
foreach (Variable var in variablesI)
{
sheet.Cells[row, 1] = var.name;
row++;
}
lowestDataRow = row;
sheet.Cells[15, 2] = "Coefficient";
sheet.Cells[14, 3] = "Standard";
sheet.Cells[15, 3] = "Error";
sheet.Cells[15, 4] = "t-value";
sheet.Cells[15, 5] = "p-value";
sheet.Cells[14, 6] = "Confidence Interval " + confLevel.ToString() + "%";
sheet.Range[sheet.Cells[14, 6], sheet.Cells[14, 7]].Merge();
sheet.Cells[15, 6] = "Lower";
sheet.Cells[15, 7] = "Upper";
sheet.get_Range("B1", "J200").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.get_Range("A1", "B1").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range("A9", "F9").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range("A15", "G15").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.get_Range("B3", "B5").NumberFormat = "0.0000";
sheet.get_Range("B18", "B20").NumberFormat = "0.0000";
sheet.get_Range("D18", "E19").NumberFormat = "0.0000";
Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false;
//
//calculate values of regression
//
double[] b = new double[variablesI.Count];
double[] yData = calcYdata(variablesD, dataSet, length);
double[,] xData = calcXdata(length, dataSet, variablesI);
b = calculateCoefB(yData, xData, b);
double a = b[0];
var X = DenseMatrix.OfArray(xData);
var B = new DenseVector(b);
var Y = new DenseVector(yData);
var yh = X.Multiply(B);
var er = Y.Subtract(yh);
double[] error = er.ToArray();
double[] yhat = yh.ToArray();
double R2 = sheet.Application.WorksheetFunction.Correl(yData, yhat);
double R = Math.Pow(R2, 2);
double Radj = 1.0 - ((1 - R) * (length - 1) / (length - variablesI.Count - 1));
double err = 0;
int i = 0;
while (i < error.Length)
{
err = err + Math.Pow(error[i], 2);
i++;
}
err = Math.Sqrt(err / (length - 4));
double[] anovaResults = calculateAnova(yData, yhat, variablesI.Count(), sheet);
multicollinearity(sheet, xData, variablesI.Count);
if (model.doDurbinWatson == true)
{
DurbinWatson(sheet, error);
}
//
//Calculate regressionTable
//
double MSE = anovaResults[6];
var Xt = X.Transpose();
var tempM = (Xt.Multiply(X)).Inverse();
var variancesM = tempM.Multiply(MSE);
double[] variances = variancesM.Diagonal().ToArray();
double[] std = new double[variances.Length];
int index = 0;
foreach (double elem in variances)
{
std[index] = Math.Sqrt(elem);
//System.Diagnostics.Debug.WriteLine(std[index]);
index++;
}
double[] tValue = new double[b.Length];
double[] pValue = new double[b.Length];
double[] lowerInt = new double[b.Length];
double[] higherInt = new double[b.Length];
for (index = 0; index < b.Length; index++)
{
tValue[index] = b[index] / std[index];
pValue[index] = sheet.Application.WorksheetFunction.TDist(Math.Abs(tValue[index]), length - variablesI.Count - 1, 2);
lowerInt[index] = b[index] - sheet.Application.WorksheetFunction.TInv(1 - confLevel / 100, length - variablesI.Count - 1) * std[index];
higherInt[index] = b[index] + sheet.Application.WorksheetFunction.TInv(1 - confLevel / 100, length - variablesI.Count - 1) * std[index];
//System.Diagnostics.Debug.WriteLine("{0}, p={1}, lower = {2}, n={3}, inv={4}, std={5}, inv2={6}",real_p, pValue[index], lowerInt[index], length - 1, sheet.Application.WorksheetFunction.TInv(1-0.975, length - 1), std[index], sheet.Application.WorksheetFunction.TInv(1 - 0.95, length - 1));
}
//
//Prediction
//
if (model.doPrediction)
{
double[,] xDataPred = calcXdata(dataSet2.getNrDataRows(), dataSet2, variablesPrediction);
int[] rightCell = { dataSet2.getRange().Row, dataSet2.getRange().Column + dataSet2.amountOfVariables() }; //{row, column}
prediction(dataSet2.getWorksheet(), xDataPred, b, length, variablesI.Count, X, MSE, rightCell, variablesD[0].name);
setPrediction(false);
}
//
//Draw graphs
//
if (graphs[0])
{
drawGraphs(sheet, yData, yhat, "Scatter plot of fitted values vs. actual values");
}
if (graphs[1])
{
drawGraphs(sheet, yData, error, "Scatter plot of residuals vs. fitted values");
}
if (graphs[2])
{
int varNumber = 1;
foreach (Variable var in variablesI)
{
double[] xDataTemp = new double[xData.GetLength(0)];
for (int x = 0; x < xData.GetLength(0); x++)
{
xDataTemp[x] = xData[x, varNumber];
}
drawGraphs(sheet, xDataTemp, error, "Scatter plot of residuals vs. " + var.name);
varNumber++;
}
}
if (graphs[3])
{
int varNumber = 1;
foreach (Variable var in variablesI)
{
double[] xDataTemp = new double[xData.GetLength(0)];
for (int x = 0; x < xData.GetLength(0); x++)
{
xDataTemp[x] = xData[x, varNumber];
}
drawGraphs(sheet, xDataTemp, yData, "Scatter plot of actual Y values vs. " + var.name);
varNumber++;
}
}
if (graphs[4])
{
int varNumber = 1;
foreach (Variable var in variablesI)
{
double[] xDataTemp = new double[xData.GetLength(0)];
for (int x = 0; x < xData.GetLength(0); x++)
{
xDataTemp[x] = xData[x, varNumber];
}
drawGraphs(sheet, xDataTemp, yhat, "Scatter plot of fitted Y values vs. " + var.name);
varNumber++;
}
}
//
//print results to excel sheet
//
sheet.Cells[16, 2] = a;
row = 17;
foreach (Variable var in variablesI)
{
sheet.Cells[row, 2] = b[row - 16];
row++;
}
FillR(sheet, R2, R, Radj, err);
FillAnova(sheet, anovaResults);
FillRegressionTable(sheet, std, tValue, pValue, lowerInt, higherInt);
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 6]).EntireColumn.ColumnWidth = 13;
((Range)sheet.Cells[1, 7]).EntireColumn.ColumnWidth = 13;
((Range)sheet.Cells[1, 8]).EntireColumn.ColumnWidth = 13;
((Range)sheet.Cells[1, 9]).EntireColumn.ColumnWidth = 13;
((Range)sheet.Cells[1, 10]).EntireColumn.AutoFit();
}
//public void createRegression(List<Variable> variables)
public void createDiscriminant(List <Variable> variablesD, List <Variable> variablesI, DataSet dataSet)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Discriminant");
sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variablesI[0].Range + ")";
int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value);
sheet.Cells[100, 100] = "";
int row = 1;
int column = 1;
sheet.Cells[2, 1] = "Sample Summary";
sheet.Cells[3, 1] = "No";
sheet.Cells[4, 1] = "Yes";
sheet.Cells[1, 2] = "Sample";
sheet.Cells[2, 2] = "Size";
int i = 0;
while (i < variablesI.Count)
{
sheet.Cells[1, 3 + i] = "Mean";
sheet.Cells[2, 3 + i] = variablesI[i].name;
sheet.Cells[8 + i, 1] = variablesI[i].name;
i++;
}
row = 8 + i - 1 + 3;
column = 3 + i - 1;
string let = ColumnIndexToColumnLetter(3 + i - 1);
List <double[]> final = fillTable1(variablesD, variablesI, dataSet, 3, sheet);
double[] coef = calcCoef(final, variablesI.Count, sheet);
sheet.get_Range("A2", "J200").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.get_Range("A2", let + "2").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Cells[7, 1] = "Discriminant Function";
sheet.Cells[7, 2] = "Coefficient";
sheet.get_Range("A7", "B7").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Cells[row, 1] = "Classification Matrix";
sheet.Cells[row, 2] = "No";
sheet.Cells[row, 3] = "Yes";
sheet.Cells[row, 4] = "Correct";
double cut = cutoff(coef, final, variablesI.Count, sheet);
string num = Convert.ToString(row);
row++;
sheet.Cells[row++, 1] = "No";
sheet.Cells[row, 1] = "Yes";
classification(cut, coef, final, variablesI.Count, sheet, row);
sheet.get_Range("A" + num, "D" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
row = row + 3;
num = Convert.ToString(row);
sheet.Cells[row++, 1] = "Summary Classification";
sheet.Cells[row++, 1] = "Correct";
sheet.Cells[row++, 1] = "Base";
sheet.Cells[row, 1] = "Improvement";
sheet.get_Range("A" + num, "B" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
//sheet.get_Range("B3", "B5").NumberFormat = "0.0000";
//sheet.get_Range("B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)).NumberFormat = "0.000";
sheet.get_Range("B18", "B20").NumberFormat = "0.0000";
sheet.get_Range("D18", "E19").NumberFormat = "0.0000";
row = row + 3;
num = Convert.ToString(row);
sheet.Cells[row++, 1] = "Cutoff Score";
sheet.get_Range("A" + num, "A" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Cells[row, 1] = cut;
//sheet.get_Range("B24", AddressConverter.CellAddress(23 + c, 2, false, false)).NumberFormat = "0.0000";
//sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).NumberFormat = "0.000000";
//sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).Cells.HorizontalAlignment = XlHAlign.xlHAlignRight;
Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false;
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit();
}
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]];
}
