private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
FileInfo newFile = new FileInfo(fileName);
if (newFile.Exists) {
newFile.Delete();
newFile = new FileInfo(fileName);
}
var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
using (ExcelPackage package = new ExcelPackage(newFile)) {
ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
FormatModelSheet(modelWorksheet, solution, formulaParts);
ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
if (solution is IRegressionSolution) {
ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);
ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
AddCharts(chartsWorksheet);
}
package.Workbook.Properties.Title = "Excel Export";
package.Workbook.Properties.Author = "HEAL";
package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
package.Save();
}
}
private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula)
{
FileInfo newFile = new FileInfo(fileName);
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(fileName);
}
var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
using (ExcelPackage package = new ExcelPackage(newFile)) {
ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
FormatModelSheet(modelWorksheet, solution, formulaParts);
ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
if (solution is IRegressionSolution)
{
ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);
ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
AddCharts(chartsWorksheet);
}
package.Workbook.Properties.Title = "Excel Export";
package.Workbook.Properties.Author = "HEAL";
package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
package.Save();
}
}
private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
int row = 1;
modelWorksheet.Cells[row, 1].Value = "Model";
modelWorksheet.Cells[row, 2].Value = solution.Name;
foreach (var part in formulaParts) {
modelWorksheet.Cells[row, 4].Value = part;
row++;
}
row = 2;
modelWorksheet.Cells[row, 1].Value = "Model Depth";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
row++;
modelWorksheet.Cells[row, 1].Value = "Model Length";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
row += 2;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
modelWorksheet.Cells[row, 2].Value = Math.Max(solution.Model.LowerEstimationLimit, -9.99999999999999E+307); // minimal value supported by excel
modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
modelWorksheet.Cells[row, 2].Value = Math.Min(solution.Model.UpperEstimationLimit, 9.99999999999999E+307); // maximal value supported by excel
modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row += 2;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
row += 2;
string excelTrainingTarget = Indirect("B", true);
string excelTrainingEstimated = Indirect("C", true);
string excelTrainingAbsoluteError = Indirect("D", true);
string excelTrainingRelativeError = Indirect("E", true);
string excelTrainingMeanError = Indirect("F", true);
string excelTrainingMSE = Indirect("G", true);
string excelTestTarget = Indirect("B", false);
string excelTestEstimated = Indirect("C", false);
string excelTestAbsoluteError = Indirect("D", false);
string excelTestRelativeError = Indirect("E", false);
string excelTestMeanError = Indirect("F", false);
string excelTestMSE = Indirect("G", false);
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean absolute error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingAbsoluteError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean absolute error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestAbsoluteError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Average relative error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingRelativeError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
row++;
modelWorksheet.Cells[row, 1].Value = "Average relative error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestRelativeError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("TrainingMSE / VAR({0})", excelTrainingTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("TestMSE / VAR({0})", excelTestTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
AddModelTreePicture(modelWorksheet, solution.Model);
}
private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable <string> formulaParts)
{
int row = 1;
modelWorksheet.Cells[row, 1].Value = "Model";
modelWorksheet.Cells[row, 2].Value = solution.Name;
foreach (var part in formulaParts)
{
modelWorksheet.Cells[row, 4].Value = part;
row++;
}
row = 2;
modelWorksheet.Cells[row, 1].Value = "Model Depth";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
row++;
modelWorksheet.Cells[row, 1].Value = "Model Length";
modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
row += 2;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
modelWorksheet.Cells[row, 2].Value = Math.Max(solution.Model.LowerEstimationLimit, -9.99999999999999E+307); // minimal value supported by excel
modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
modelWorksheet.Cells[row, 2].Value = Math.Min(solution.Model.UpperEstimationLimit, 9.99999999999999E+307); // maximal value supported by excel
modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row += 2;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
row++;
modelWorksheet.Cells[row, 1].Value = "Test Partition End";
modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
row += 2;
string excelTrainingTarget = Indirect("B", true);
string excelTrainingEstimated = Indirect("C", true);
string excelTrainingAbsoluteError = Indirect("D", true);
string excelTrainingRelativeError = Indirect("E", true);
string excelTrainingMeanError = Indirect("F", true);
string excelTrainingMSE = Indirect("G", true);
string excelTestTarget = Indirect("B", false);
string excelTestEstimated = Indirect("C", false);
string excelTestAbsoluteError = Indirect("D", false);
string excelTestRelativeError = Indirect("E", false);
string excelTestMeanError = Indirect("F", false);
string excelTestMSE = Indirect("G", false);
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean absolute error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingAbsoluteError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean absolute error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestAbsoluteError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Mean error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Average relative error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingRelativeError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
row++;
modelWorksheet.Cells[row, 1].Value = "Average relative error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestRelativeError);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (training)";
modelWorksheet.Cells[row, 2].Formula = string.Format("TrainingMSE / VAR({0})", excelTrainingTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
row++;
modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (test)";
modelWorksheet.Cells[row, 2].Formula = string.Format("TestMSE / VAR({0})", excelTestTarget);
modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000E+00";
modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
AddModelTreePicture(modelWorksheet, solution.Model);
}
