[9580] | 1 | #region License Information
|
---|
| 2 | /* HeuristicLab
|
---|
| 3 | * Copyright (C) 2002-2013 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
|
---|
| 4 | *
|
---|
| 5 | * This file is part of HeuristicLab.
|
---|
| 6 | *
|
---|
| 7 | * HeuristicLab is free software: you can redistribute it and/or modify
|
---|
| 8 | * it under the terms of the GNU General Public License as published by
|
---|
| 9 | * the Free Software Foundation, either version 3 of the License, or
|
---|
| 10 | * (at your option) any later version.
|
---|
| 11 | *
|
---|
| 12 | * HeuristicLab is distributed in the hope that it will be useful,
|
---|
| 13 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
---|
| 14 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
---|
| 15 | * GNU General Public License for more details.
|
---|
| 16 | *
|
---|
| 17 | * You should have received a copy of the GNU General Public License
|
---|
| 18 | * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
|
---|
| 19 | */
|
---|
| 20 | #endregion
|
---|
| 21 |
|
---|
| 22 | using System;
|
---|
| 23 | using System.Collections.Generic;
|
---|
[9626] | 24 | using System.ComponentModel;
|
---|
[9580] | 25 | using System.IO;
|
---|
| 26 | using System.Linq;
|
---|
| 27 | using System.Windows.Forms;
|
---|
| 28 | using HeuristicLab.Encodings.SymbolicExpressionTreeEncoding.Views;
|
---|
| 29 | using HeuristicLab.MainForm;
|
---|
[9626] | 30 | using HeuristicLab.MainForm.WindowsForms;
|
---|
[9580] | 31 | using HeuristicLab.Optimizer;
|
---|
| 32 | using OfficeOpenXml;
|
---|
| 33 | using OfficeOpenXml.Drawing.Chart;
|
---|
| 34 |
|
---|
| 35 | namespace HeuristicLab.Problems.DataAnalysis.Symbolic.Views {
|
---|
| 36 | public class ExportSymbolicSolutionToExcelMenuItem : MainForm.WindowsForms.MenuItem, IOptimizerUserInterfaceItemProvider {
|
---|
| 37 | private const string TRAININGSTART = "TrainingStart";
|
---|
| 38 | private const string TRAININGEND = "TrainingEnd";
|
---|
| 39 | private const string TESTSTART = "TestStart";
|
---|
| 40 | private const string TESTEND = "TestEnd";
|
---|
| 41 |
|
---|
| 42 | public override string Name {
|
---|
| 43 | get { return "Export Symbolic Solution To Excel"; }
|
---|
| 44 | }
|
---|
| 45 | public override IEnumerable<string> Structure {
|
---|
[9860] | 46 | get { return new string[] { "&Data Analysis" }; }
|
---|
[9580] | 47 | }
|
---|
| 48 | public override int Position {
|
---|
[9860] | 49 | get { return 5200; }
|
---|
[9580] | 50 | }
|
---|
| 51 | public override string ToolTipText {
|
---|
| 52 | get { return "Create excel file of symbolic data analysis solutions."; }
|
---|
| 53 | }
|
---|
| 54 |
|
---|
| 55 | protected override void OnToolStripItemSet(EventArgs e) {
|
---|
[9626] | 56 | base.OnToolStripItemSet(e);
|
---|
[9580] | 57 | ToolStripItem.Enabled = false;
|
---|
[9626] | 58 | var menuItem = ToolStripItem.OwnerItem as ToolStripMenuItem;
|
---|
| 59 | if (menuItem != null)
|
---|
| 60 | menuItem.DropDownOpening += menuItem_DropDownOpening;
|
---|
[9580] | 61 | }
|
---|
[9626] | 62 |
|
---|
| 63 | private void menuItem_DropDownOpening(object sender, EventArgs e) {
|
---|
[9580] | 64 | IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
|
---|
[9626] | 65 | Control control = activeView as Control;
|
---|
| 66 | activeView = control.GetNestedControls((c) => c.Visible)
|
---|
| 67 | .OfType<IContentView>().FirstOrDefault(v => v.Content is ISymbolicDataAnalysisSolution && v.Content is IRegressionSolution);
|
---|
| 68 | ToolStripItem.Enabled = activeView != null;
|
---|
[9580] | 69 | }
|
---|
| 70 |
|
---|
| 71 | public override void Execute() {
|
---|
[9626] | 72 | IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
|
---|
| 73 | Control control = activeView as Control;
|
---|
| 74 | activeView = control.GetNestedControls((c) => c.Visible)
|
---|
| 75 | .OfType<IContentView>().First(v => v.Content is ISymbolicDataAnalysisSolution && v.Content is IRegressionSolution);
|
---|
[9584] | 76 | var solution = (ISymbolicDataAnalysisSolution)activeView.Content;
|
---|
[9580] | 77 | var formatter = new SymbolicDataAnalysisExpressionExcelFormatter();
|
---|
[9626] | 78 | var formula = formatter.Format(solution.Model.SymbolicExpressionTree, solution.ProblemData.Dataset);
|
---|
[9580] | 79 |
|
---|
[9626] | 80 |
|
---|
[9580] | 81 | SaveFileDialog saveFileDialog = new SaveFileDialog();
|
---|
| 82 | saveFileDialog.Filter = "Excel Workbook|*.xlsx";
|
---|
| 83 | saveFileDialog.Title = "Save an Excel File";
|
---|
| 84 | if (saveFileDialog.ShowDialog() == DialogResult.OK) {
|
---|
| 85 | string fileName = saveFileDialog.FileName;
|
---|
[9626] | 86 | using (BackgroundWorker bg = new BackgroundWorker()) {
|
---|
| 87 | bg.DoWork += (b, e) => ExportChart(fileName, solution, formula);
|
---|
| 88 | bg.RunWorkerAsync();
|
---|
[9580] | 89 | }
|
---|
[9626] | 90 | }
|
---|
| 91 | }
|
---|
[9580] | 92 |
|
---|
[9626] | 93 | private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
|
---|
| 94 | FileInfo newFile = new FileInfo(fileName);
|
---|
| 95 | if (newFile.Exists) {
|
---|
| 96 | newFile.Delete();
|
---|
| 97 | newFile = new FileInfo(fileName);
|
---|
| 98 | }
|
---|
| 99 | var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
|
---|
[9580] | 100 |
|
---|
[9626] | 101 | using (ExcelPackage package = new ExcelPackage(newFile)) {
|
---|
| 102 | ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
|
---|
| 103 | FormatModelSheet(modelWorksheet, solution, formulaParts);
|
---|
[9580] | 104 |
|
---|
[9626] | 105 | ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
|
---|
| 106 | WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
|
---|
[9585] | 107 |
|
---|
[9626] | 108 | ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
|
---|
| 109 | WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
|
---|
[9580] | 110 |
|
---|
[9626] | 111 | if (solution is IRegressionSolution) {
|
---|
| 112 | ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
|
---|
| 113 | WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);
|
---|
| 114 |
|
---|
| 115 | ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
|
---|
| 116 | AddCharts(chartsWorksheet);
|
---|
[9580] | 117 | }
|
---|
[9626] | 118 | package.Workbook.Properties.Title = "Excel Export";
|
---|
| 119 | package.Workbook.Properties.Author = "HEAL";
|
---|
| 120 | package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
|
---|
| 121 |
|
---|
| 122 | package.Save();
|
---|
[9580] | 123 | }
|
---|
| 124 | }
|
---|
| 125 |
|
---|
| 126 | private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
|
---|
| 127 | int row = 1;
|
---|
| 128 | modelWorksheet.Cells[row, 1].Value = "Model";
|
---|
| 129 | modelWorksheet.Cells[row, 2].Value = solution.Name;
|
---|
| 130 |
|
---|
| 131 | foreach (var part in formulaParts) {
|
---|
| 132 | modelWorksheet.Cells[row, 4].Value = part;
|
---|
| 133 | row++;
|
---|
| 134 | }
|
---|
| 135 |
|
---|
| 136 | row = 2;
|
---|
| 137 | modelWorksheet.Cells[row, 1].Value = "Model Depth";
|
---|
| 138 | modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
|
---|
| 139 | row++;
|
---|
| 140 |
|
---|
| 141 | modelWorksheet.Cells[row, 1].Value = "Model Length";
|
---|
| 142 | modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
|
---|
| 143 | row += 2;
|
---|
| 144 |
|
---|
[9587] | 145 | modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
|
---|
| 146 | modelWorksheet.Cells[row, 2].Value = solution.Model.LowerEstimationLimit;
|
---|
| 147 | modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 148 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9587] | 149 | row++;
|
---|
[9580] | 150 |
|
---|
[9587] | 151 | modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
|
---|
| 152 | modelWorksheet.Cells[row, 2].Value = solution.Model.UpperEstimationLimit;
|
---|
| 153 | modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 154 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9587] | 155 | row += 2;
|
---|
[9580] | 156 |
|
---|
| 157 | modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
|
---|
| 158 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
|
---|
| 159 | modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
|
---|
| 160 | row++;
|
---|
| 161 |
|
---|
| 162 | modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
|
---|
| 163 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
|
---|
| 164 | modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
|
---|
| 165 | row++;
|
---|
| 166 |
|
---|
| 167 | modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
|
---|
| 168 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
|
---|
| 169 | modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
|
---|
| 170 | row++;
|
---|
| 171 |
|
---|
| 172 | modelWorksheet.Cells[row, 1].Value = "Test Partition End";
|
---|
| 173 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
|
---|
| 174 | modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
|
---|
| 175 | row += 2;
|
---|
| 176 |
|
---|
| 177 | string excelTrainingTarget = Indirect("B", true);
|
---|
| 178 | string excelTrainingEstimated = Indirect("C", true);
|
---|
| 179 | string excelTrainingAbsoluteError = Indirect("D", true);
|
---|
| 180 | string excelTrainingRelativeError = Indirect("E", true);
|
---|
| 181 | string excelTrainingMeanError = Indirect("F", true);
|
---|
| 182 | string excelTrainingMSE = Indirect("G", true);
|
---|
| 183 |
|
---|
| 184 | string excelTestTarget = Indirect("B", false);
|
---|
| 185 | string excelTestEstimated = Indirect("C", false);
|
---|
| 186 | string excelTestAbsoluteError = Indirect("D", false);
|
---|
| 187 | string excelTestRelativeError = Indirect("E", false);
|
---|
| 188 | string excelTestMeanError = Indirect("F", false);
|
---|
| 189 | string excelTestMSE = Indirect("G", false);
|
---|
| 190 |
|
---|
| 191 | modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
|
---|
| 192 | modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
|
---|
[9607] | 193 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 194 | row++;
|
---|
| 195 |
|
---|
| 196 | modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
|
---|
| 197 | modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
|
---|
[9607] | 198 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 199 | row++;
|
---|
| 200 |
|
---|
| 201 | modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
|
---|
| 202 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
|
---|
| 203 | modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 204 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 205 | row++;
|
---|
| 206 |
|
---|
| 207 | modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
|
---|
| 208 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
|
---|
| 209 | modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 210 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 211 | row++;
|
---|
| 212 |
|
---|
| 213 | modelWorksheet.Cells[row, 1].Value = "Mean absolute error (training)";
|
---|
| 214 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingAbsoluteError);
|
---|
[9607] | 215 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 216 | row++;
|
---|
| 217 |
|
---|
| 218 | modelWorksheet.Cells[row, 1].Value = "Mean absolute error (test)";
|
---|
| 219 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestAbsoluteError);
|
---|
[9607] | 220 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 221 | row++;
|
---|
| 222 |
|
---|
| 223 | modelWorksheet.Cells[row, 1].Value = "Mean error (training)";
|
---|
| 224 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
|
---|
[9607] | 225 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 226 | row++;
|
---|
| 227 |
|
---|
| 228 | modelWorksheet.Cells[row, 1].Value = "Mean error (test)";
|
---|
| 229 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
|
---|
[9607] | 230 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 231 | row++;
|
---|
| 232 |
|
---|
| 233 | modelWorksheet.Cells[row, 1].Value = "Average relative error (training)";
|
---|
| 234 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingRelativeError);
|
---|
| 235 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
|
---|
| 236 | row++;
|
---|
| 237 |
|
---|
| 238 | modelWorksheet.Cells[row, 1].Value = "Average relative error (test)";
|
---|
| 239 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestRelativeError);
|
---|
| 240 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
|
---|
| 241 | row++;
|
---|
| 242 |
|
---|
| 243 | modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (training)";
|
---|
| 244 | modelWorksheet.Cells[row, 2].Formula = string.Format("TrainingMSE / VAR({0})", excelTrainingTarget);
|
---|
[9607] | 245 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 246 | row++;
|
---|
| 247 |
|
---|
| 248 | modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (test)";
|
---|
| 249 | modelWorksheet.Cells[row, 2].Formula = string.Format("TestMSE / VAR({0})", excelTestTarget);
|
---|
[9607] | 250 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 251 |
|
---|
| 252 | modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
|
---|
| 253 |
|
---|
| 254 | AddModelTreePicture(modelWorksheet, solution.Model);
|
---|
| 255 | }
|
---|
| 256 |
|
---|
| 257 | private string Indirect(string column, bool training) {
|
---|
| 258 | if (training) {
|
---|
| 259 | return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TRAININGSTART, TRAININGEND);
|
---|
| 260 | } else {
|
---|
| 261 | return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TESTSTART, TESTEND);
|
---|
| 262 | }
|
---|
| 263 | }
|
---|
| 264 |
|
---|
| 265 | private void AddCharts(ExcelWorksheet chartsWorksheet) {
|
---|
| 266 | chartsWorksheet.Names.AddFormula("AllId", "OFFSET('Estimated Values'!$A$1,1,0, COUNTA('Estimated Values'!$A:$A)-1)");
|
---|
| 267 | chartsWorksheet.Names.AddFormula("AllTarget", "OFFSET('Estimated Values'!$B$1,1,0, COUNTA('Estimated Values'!$B:$B)-1)");
|
---|
| 268 | chartsWorksheet.Names.AddFormula("AllEstimated", "OFFSET('Estimated Values'!$C$1,1,0, COUNTA('Estimated Values'!$C:$C)-1)");
|
---|
| 269 | chartsWorksheet.Names.AddFormula("TrainingId", "OFFSET('Estimated Values'!$A$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
| 270 | chartsWorksheet.Names.AddFormula("TrainingTarget", "OFFSET('Estimated Values'!$B$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
| 271 | chartsWorksheet.Names.AddFormula("TrainingEstimated", "OFFSET('Estimated Values'!$C$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
| 272 | chartsWorksheet.Names.AddFormula("TestId", "OFFSET('Estimated Values'!$A$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
| 273 | chartsWorksheet.Names.AddFormula("TestTarget", "OFFSET('Estimated Values'!$B$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
| 274 | chartsWorksheet.Names.AddFormula("TestEstimated", "OFFSET('Estimated Values'!$C$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
| 275 |
|
---|
| 276 | var scatterPlot = chartsWorksheet.Drawings.AddChart("scatterPlot", eChartType.XYScatter);
|
---|
| 277 | scatterPlot.SetSize(800, 400);
|
---|
| 278 | scatterPlot.SetPosition(0, 0);
|
---|
| 279 | scatterPlot.Title.Text = "Scatter Plot";
|
---|
| 280 | var seriesAll = scatterPlot.Series.Add("AllTarget", "AllEstimated");
|
---|
| 281 | seriesAll.Header = "All";
|
---|
| 282 | var seriesTraining = scatterPlot.Series.Add("TrainingTarget", "TrainingEstimated");
|
---|
| 283 | seriesTraining.Header = "Training";
|
---|
| 284 | var seriesTest = scatterPlot.Series.Add("TestTarget", "TestEstimated");
|
---|
| 285 | seriesTest.Header = "Test";
|
---|
| 286 |
|
---|
| 287 | var lineChart = chartsWorksheet.Drawings.AddChart("lineChart", eChartType.XYScatterLinesNoMarkers);
|
---|
| 288 | lineChart.SetSize(800, 400);
|
---|
| 289 | lineChart.SetPosition(400, 0);
|
---|
| 290 | lineChart.Title.Text = "LineChart";
|
---|
| 291 | var lineTarget = lineChart.Series.Add("AllTarget", "AllId");
|
---|
| 292 | lineTarget.Header = "Target";
|
---|
| 293 | var lineAll = lineChart.Series.Add("AllEstimated", "AllId");
|
---|
| 294 | lineAll.Header = "All";
|
---|
| 295 | var lineTraining = lineChart.Series.Add("TrainingEstimated", "TrainingId");
|
---|
| 296 | lineTraining.Header = "Training";
|
---|
| 297 | var lineTest = lineChart.Series.Add("TestEstimated", "TestId");
|
---|
| 298 | lineTest.Header = "Test";
|
---|
| 299 | }
|
---|
| 300 |
|
---|
| 301 | private void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
|
---|
| 302 | SymbolicExpressionTreeChart modelTreePicture = new SymbolicExpressionTreeChart();
|
---|
| 303 | modelTreePicture.Tree = model.SymbolicExpressionTree;
|
---|
| 304 | string tmpFilename = Path.GetTempFileName();
|
---|
| 305 | modelTreePicture.Width = 1000;
|
---|
| 306 | modelTreePicture.Height = 500;
|
---|
| 307 | modelTreePicture.SaveImageAsEmf(tmpFilename);
|
---|
| 308 |
|
---|
| 309 | FileInfo fi = new FileInfo(tmpFilename);
|
---|
| 310 | var excelModelTreePic = modelWorksheet.Drawings.AddPicture("ModelTree", fi);
|
---|
| 311 | excelModelTreePic.SetSize(50);
|
---|
| 312 | excelModelTreePic.SetPosition(2, 0, 6, 0);
|
---|
| 313 | }
|
---|
| 314 |
|
---|
| 315 | private void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, IRegressionSolution solution) {
|
---|
| 316 | string preparedFormula = PrepareFormula(formulaParts);
|
---|
| 317 | int rows = solution.ProblemData.Dataset.Rows;
|
---|
| 318 | estimatedWorksheet.Cells[1, 1].Value = "Id";
|
---|
| 319 | estimatedWorksheet.Cells[1, 2].Value = "Target Variable";
|
---|
| 320 | estimatedWorksheet.Cells[1, 3].Value = "Estimated Values";
|
---|
| 321 | estimatedWorksheet.Cells[1, 4].Value = "Absolute Error";
|
---|
| 322 | estimatedWorksheet.Cells[1, 5].Value = "Relative Error";
|
---|
| 323 | estimatedWorksheet.Cells[1, 6].Value = "Error";
|
---|
| 324 | estimatedWorksheet.Cells[1, 7].Value = "Squared Error";
|
---|
| 325 | estimatedWorksheet.Cells[1, 9].Value = "Unbounded Estimated Values";
|
---|
| 326 | estimatedWorksheet.Cells[1, 10].Value = "Bounded Estimated Values";
|
---|
| 327 |
|
---|
| 328 | estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
|
---|
| 329 |
|
---|
| 330 | int targetIndex = solution.ProblemData.Dataset.VariableNames.ToList().FindIndex(x => x.Equals(solution.ProblemData.TargetVariable)) + 1;
|
---|
| 331 | for (int i = 0; i < rows; i++) {
|
---|
| 332 | estimatedWorksheet.Cells[i + 2, 1].Value = i;
|
---|
| 333 | estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress;
|
---|
| 334 | estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2);
|
---|
| 335 | }
|
---|
[9607] | 336 | estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 337 |
|
---|
| 338 | estimatedWorksheet.Cells["C2:C" + (rows + 1)].Formula = "J2";
|
---|
[9607] | 339 | estimatedWorksheet.Cells["C2:C" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 340 | estimatedWorksheet.Cells["D2:D" + (rows + 1)].Formula = "ABS(B2 - C2)";
|
---|
[9607] | 341 | estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9600] | 342 | estimatedWorksheet.Cells["E2:E" + (rows + 1)].Formula = "ABS(D2 / B2)";
|
---|
[9607] | 343 | estimatedWorksheet.Cells["E2:E" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 344 | estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "C2 - B2";
|
---|
[9607] | 345 | estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 346 | estimatedWorksheet.Cells["G2:G" + (rows + 1)].Formula = "POWER(F2, 2)";
|
---|
[9607] | 347 | estimatedWorksheet.Cells["G2:G" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 348 |
|
---|
[9607] | 349 | estimatedWorksheet.Cells["I2:I" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9583] | 350 | estimatedWorksheet.Cells["J2:J" + (rows + 1)].Formula = "IFERROR(IF(I2 > Model!EstimationLimitUpper, Model!EstimationLimitUpper, IF(I2 < Model!EstimationLimitLower, Model!EstimationLimitLower, I2)), AVERAGE(Model!EstimationLimitLower, Model!EstimationLimitUpper))";
|
---|
[9607] | 351 | estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 352 | }
|
---|
| 353 |
|
---|
| 354 | private string PrepareFormula(string[] formulaParts) {
|
---|
| 355 | string preparedFormula = formulaParts[0];
|
---|
| 356 | foreach (var part in formulaParts.Skip(2)) {
|
---|
| 357 | var varMap = part.Split(new string[] { " = " }, StringSplitOptions.None);
|
---|
| 358 | var columnName = "$" + varMap[1] + "1";
|
---|
| 359 | preparedFormula = preparedFormula.Replace(columnName, "Inputs!$" + varMap[1] + "{0}"); //{0} will be replaced later with the row number
|
---|
| 360 | }
|
---|
| 361 | return preparedFormula;
|
---|
| 362 | }
|
---|
| 363 |
|
---|
| 364 | private void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
|
---|
[9699] | 365 | //remark the performance of EPPlus drops dramatically
|
---|
| 366 | //if the data is not written row wise (from left to right) due the internal indices used.
|
---|
| 367 | var variableNames = dataset.VariableNames.Select((v, i) => new { variable = v, index = i + 1 }).ToDictionary(v => v.variable, v => v.index);
|
---|
| 368 | var nameMapping = list.Select(x => x.Split('=')[0].Trim()).ToArray();
|
---|
| 369 |
|
---|
| 370 | for (int row = 1; row <= dataset.Rows + 1; row++) {
|
---|
| 371 | for (int column = 1; column < nameMapping.Length + 1; column++) {
|
---|
| 372 | int variableIndex = variableNames[nameMapping[column - 1]];
|
---|
| 373 | inputsWorksheet.Cells[row, column].Formula = datasetWorksheet.Cells[row, variableIndex].FullAddress;
|
---|
[9580] | 374 | }
|
---|
| 375 | }
|
---|
| 376 | }
|
---|
| 377 |
|
---|
| 378 | private void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
|
---|
[9699] | 379 | //remark the performance of EPPlus drops dramatically
|
---|
| 380 | //if the data is not written row wise (from left to right) due the internal indices used.
|
---|
[9580] | 381 | Dataset dataset = problemData.Dataset;
|
---|
| 382 | var variableNames = dataset.VariableNames.ToList();
|
---|
[9699] | 383 | var doubleVariables = new HashSet<string>(dataset.DoubleVariables);
|
---|
| 384 |
|
---|
| 385 | for (int col = 1; col <= variableNames.Count; col++)
|
---|
[9580] | 386 | datasetWorksheet.Cells[1, col].Value = variableNames[col - 1];
|
---|
[9699] | 387 |
|
---|
| 388 | for (int row = 0; row < dataset.Rows; row++) {
|
---|
| 389 | for (int col = 0; col < variableNames.Count; col++) {
|
---|
| 390 | if (doubleVariables.Contains(variableNames[col]))
|
---|
| 391 | datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetDoubleValue(variableNames[col], row);
|
---|
| 392 | else
|
---|
| 393 | datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetValue(col, row);
|
---|
[9580] | 394 | }
|
---|
| 395 | }
|
---|
| 396 | }
|
---|
| 397 | }
|
---|
| 398 | }
|
---|