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