[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);
|
---|
[9906] | 79 | control = (Control)activeView;
|
---|
[9580] | 80 |
|
---|
[9626] | 81 |
|
---|
[9580] | 82 | SaveFileDialog saveFileDialog = new SaveFileDialog();
|
---|
| 83 | saveFileDialog.Filter = "Excel Workbook|*.xlsx";
|
---|
| 84 | saveFileDialog.Title = "Save an Excel File";
|
---|
| 85 | if (saveFileDialog.ShowDialog() == DialogResult.OK) {
|
---|
| 86 | string fileName = saveFileDialog.FileName;
|
---|
[9626] | 87 | using (BackgroundWorker bg = new BackgroundWorker()) {
|
---|
[9906] | 88 | MainFormManager.GetMainForm<MainForm.WindowsForms.MainForm>().AddOperationProgressToView(control, "Exportion solution to " + fileName + ".");
|
---|
[9626] | 89 | bg.DoWork += (b, e) => ExportChart(fileName, solution, formula);
|
---|
[9906] | 90 | bg.RunWorkerCompleted += (o, e) => MainFormManager.GetMainForm<MainForm.WindowsForms.MainForm>().RemoveOperationProgressFromView(control);
|
---|
[9626] | 91 | bg.RunWorkerAsync();
|
---|
[9580] | 92 | }
|
---|
[9626] | 93 | }
|
---|
| 94 | }
|
---|
[9580] | 95 |
|
---|
[9626] | 96 | private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
|
---|
| 97 | FileInfo newFile = new FileInfo(fileName);
|
---|
| 98 | if (newFile.Exists) {
|
---|
| 99 | newFile.Delete();
|
---|
| 100 | newFile = new FileInfo(fileName);
|
---|
| 101 | }
|
---|
| 102 | var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
|
---|
[9580] | 103 |
|
---|
[9626] | 104 | using (ExcelPackage package = new ExcelPackage(newFile)) {
|
---|
| 105 | ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
|
---|
| 106 | FormatModelSheet(modelWorksheet, solution, formulaParts);
|
---|
[9580] | 107 |
|
---|
[9626] | 108 | ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
|
---|
| 109 | WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
|
---|
[9585] | 110 |
|
---|
[9626] | 111 | ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
|
---|
| 112 | WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
|
---|
[9580] | 113 |
|
---|
[9626] | 114 | if (solution is IRegressionSolution) {
|
---|
| 115 | ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
|
---|
| 116 | WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);
|
---|
| 117 |
|
---|
| 118 | ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
|
---|
| 119 | AddCharts(chartsWorksheet);
|
---|
[9580] | 120 | }
|
---|
[9626] | 121 | package.Workbook.Properties.Title = "Excel Export";
|
---|
| 122 | package.Workbook.Properties.Author = "HEAL";
|
---|
| 123 | package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
|
---|
| 124 |
|
---|
| 125 | package.Save();
|
---|
[9580] | 126 | }
|
---|
| 127 | }
|
---|
| 128 |
|
---|
| 129 | private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
|
---|
| 130 | int row = 1;
|
---|
| 131 | modelWorksheet.Cells[row, 1].Value = "Model";
|
---|
| 132 | modelWorksheet.Cells[row, 2].Value = solution.Name;
|
---|
| 133 |
|
---|
| 134 | foreach (var part in formulaParts) {
|
---|
| 135 | modelWorksheet.Cells[row, 4].Value = part;
|
---|
| 136 | row++;
|
---|
| 137 | }
|
---|
| 138 |
|
---|
| 139 | row = 2;
|
---|
| 140 | modelWorksheet.Cells[row, 1].Value = "Model Depth";
|
---|
| 141 | modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
|
---|
| 142 | row++;
|
---|
| 143 |
|
---|
| 144 | modelWorksheet.Cells[row, 1].Value = "Model Length";
|
---|
| 145 | modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
|
---|
| 146 | row += 2;
|
---|
| 147 |
|
---|
[9587] | 148 | modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
|
---|
| 149 | modelWorksheet.Cells[row, 2].Value = solution.Model.LowerEstimationLimit;
|
---|
| 150 | modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 151 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9587] | 152 | row++;
|
---|
[9580] | 153 |
|
---|
[9587] | 154 | modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
|
---|
| 155 | modelWorksheet.Cells[row, 2].Value = solution.Model.UpperEstimationLimit;
|
---|
| 156 | modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 157 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9587] | 158 | row += 2;
|
---|
[9580] | 159 |
|
---|
| 160 | modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
|
---|
| 161 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
|
---|
| 162 | modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
|
---|
| 163 | row++;
|
---|
| 164 |
|
---|
| 165 | modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
|
---|
| 166 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
|
---|
| 167 | modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
|
---|
| 168 | row++;
|
---|
| 169 |
|
---|
| 170 | modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
|
---|
| 171 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
|
---|
| 172 | modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
|
---|
| 173 | row++;
|
---|
| 174 |
|
---|
| 175 | modelWorksheet.Cells[row, 1].Value = "Test Partition End";
|
---|
| 176 | modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
|
---|
| 177 | modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
|
---|
| 178 | row += 2;
|
---|
| 179 |
|
---|
| 180 | string excelTrainingTarget = Indirect("B", true);
|
---|
| 181 | string excelTrainingEstimated = Indirect("C", true);
|
---|
| 182 | string excelTrainingAbsoluteError = Indirect("D", true);
|
---|
| 183 | string excelTrainingRelativeError = Indirect("E", true);
|
---|
| 184 | string excelTrainingMeanError = Indirect("F", true);
|
---|
| 185 | string excelTrainingMSE = Indirect("G", true);
|
---|
| 186 |
|
---|
| 187 | string excelTestTarget = Indirect("B", false);
|
---|
| 188 | string excelTestEstimated = Indirect("C", false);
|
---|
| 189 | string excelTestAbsoluteError = Indirect("D", false);
|
---|
| 190 | string excelTestRelativeError = Indirect("E", false);
|
---|
| 191 | string excelTestMeanError = Indirect("F", false);
|
---|
| 192 | string excelTestMSE = Indirect("G", false);
|
---|
| 193 |
|
---|
| 194 | modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
|
---|
| 195 | modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
|
---|
[9607] | 196 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 197 | row++;
|
---|
| 198 |
|
---|
| 199 | modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
|
---|
| 200 | modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
|
---|
[9607] | 201 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 202 | row++;
|
---|
| 203 |
|
---|
| 204 | modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
|
---|
| 205 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
|
---|
| 206 | modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 207 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 208 | row++;
|
---|
| 209 |
|
---|
| 210 | modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
|
---|
| 211 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
|
---|
| 212 | modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
|
---|
[9607] | 213 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 214 | row++;
|
---|
| 215 |
|
---|
| 216 | modelWorksheet.Cells[row, 1].Value = "Mean absolute error (training)";
|
---|
| 217 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingAbsoluteError);
|
---|
[9607] | 218 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 219 | row++;
|
---|
| 220 |
|
---|
| 221 | modelWorksheet.Cells[row, 1].Value = "Mean absolute error (test)";
|
---|
| 222 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestAbsoluteError);
|
---|
[9607] | 223 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 224 | row++;
|
---|
| 225 |
|
---|
| 226 | modelWorksheet.Cells[row, 1].Value = "Mean error (training)";
|
---|
| 227 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
|
---|
[9607] | 228 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 229 | row++;
|
---|
| 230 |
|
---|
| 231 | modelWorksheet.Cells[row, 1].Value = "Mean error (test)";
|
---|
| 232 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
|
---|
[9607] | 233 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 234 | row++;
|
---|
| 235 |
|
---|
| 236 | modelWorksheet.Cells[row, 1].Value = "Average relative error (training)";
|
---|
| 237 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingRelativeError);
|
---|
| 238 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
|
---|
| 239 | row++;
|
---|
| 240 |
|
---|
| 241 | modelWorksheet.Cells[row, 1].Value = "Average relative error (test)";
|
---|
| 242 | modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestRelativeError);
|
---|
| 243 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
|
---|
| 244 | row++;
|
---|
| 245 |
|
---|
| 246 | modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (training)";
|
---|
| 247 | modelWorksheet.Cells[row, 2].Formula = string.Format("TrainingMSE / VAR({0})", excelTrainingTarget);
|
---|
[9607] | 248 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 249 | row++;
|
---|
| 250 |
|
---|
| 251 | modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (test)";
|
---|
| 252 | modelWorksheet.Cells[row, 2].Formula = string.Format("TestMSE / VAR({0})", excelTestTarget);
|
---|
[9607] | 253 | modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 254 |
|
---|
| 255 | modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
|
---|
| 256 |
|
---|
| 257 | AddModelTreePicture(modelWorksheet, solution.Model);
|
---|
| 258 | }
|
---|
| 259 |
|
---|
| 260 | private string Indirect(string column, bool training) {
|
---|
| 261 | if (training) {
|
---|
| 262 | return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TRAININGSTART, TRAININGEND);
|
---|
| 263 | } else {
|
---|
| 264 | return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TESTSTART, TESTEND);
|
---|
| 265 | }
|
---|
| 266 | }
|
---|
| 267 |
|
---|
| 268 | private void AddCharts(ExcelWorksheet chartsWorksheet) {
|
---|
| 269 | chartsWorksheet.Names.AddFormula("AllId", "OFFSET('Estimated Values'!$A$1,1,0, COUNTA('Estimated Values'!$A:$A)-1)");
|
---|
| 270 | chartsWorksheet.Names.AddFormula("AllTarget", "OFFSET('Estimated Values'!$B$1,1,0, COUNTA('Estimated Values'!$B:$B)-1)");
|
---|
| 271 | chartsWorksheet.Names.AddFormula("AllEstimated", "OFFSET('Estimated Values'!$C$1,1,0, COUNTA('Estimated Values'!$C:$C)-1)");
|
---|
| 272 | chartsWorksheet.Names.AddFormula("TrainingId", "OFFSET('Estimated Values'!$A$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
| 273 | chartsWorksheet.Names.AddFormula("TrainingTarget", "OFFSET('Estimated Values'!$B$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
| 274 | chartsWorksheet.Names.AddFormula("TrainingEstimated", "OFFSET('Estimated Values'!$C$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
|
---|
| 275 | chartsWorksheet.Names.AddFormula("TestId", "OFFSET('Estimated Values'!$A$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
| 276 | chartsWorksheet.Names.AddFormula("TestTarget", "OFFSET('Estimated Values'!$B$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
| 277 | chartsWorksheet.Names.AddFormula("TestEstimated", "OFFSET('Estimated Values'!$C$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
|
---|
| 278 |
|
---|
| 279 | var scatterPlot = chartsWorksheet.Drawings.AddChart("scatterPlot", eChartType.XYScatter);
|
---|
| 280 | scatterPlot.SetSize(800, 400);
|
---|
| 281 | scatterPlot.SetPosition(0, 0);
|
---|
| 282 | scatterPlot.Title.Text = "Scatter Plot";
|
---|
| 283 | var seriesAll = scatterPlot.Series.Add("AllTarget", "AllEstimated");
|
---|
| 284 | seriesAll.Header = "All";
|
---|
| 285 | var seriesTraining = scatterPlot.Series.Add("TrainingTarget", "TrainingEstimated");
|
---|
| 286 | seriesTraining.Header = "Training";
|
---|
| 287 | var seriesTest = scatterPlot.Series.Add("TestTarget", "TestEstimated");
|
---|
| 288 | seriesTest.Header = "Test";
|
---|
| 289 |
|
---|
| 290 | var lineChart = chartsWorksheet.Drawings.AddChart("lineChart", eChartType.XYScatterLinesNoMarkers);
|
---|
| 291 | lineChart.SetSize(800, 400);
|
---|
| 292 | lineChart.SetPosition(400, 0);
|
---|
| 293 | lineChart.Title.Text = "LineChart";
|
---|
| 294 | var lineTarget = lineChart.Series.Add("AllTarget", "AllId");
|
---|
| 295 | lineTarget.Header = "Target";
|
---|
| 296 | var lineAll = lineChart.Series.Add("AllEstimated", "AllId");
|
---|
| 297 | lineAll.Header = "All";
|
---|
| 298 | var lineTraining = lineChart.Series.Add("TrainingEstimated", "TrainingId");
|
---|
| 299 | lineTraining.Header = "Training";
|
---|
| 300 | var lineTest = lineChart.Series.Add("TestEstimated", "TestId");
|
---|
| 301 | lineTest.Header = "Test";
|
---|
| 302 | }
|
---|
| 303 |
|
---|
| 304 | private void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
|
---|
| 305 | SymbolicExpressionTreeChart modelTreePicture = new SymbolicExpressionTreeChart();
|
---|
| 306 | modelTreePicture.Tree = model.SymbolicExpressionTree;
|
---|
| 307 | string tmpFilename = Path.GetTempFileName();
|
---|
| 308 | modelTreePicture.Width = 1000;
|
---|
| 309 | modelTreePicture.Height = 500;
|
---|
| 310 | modelTreePicture.SaveImageAsEmf(tmpFilename);
|
---|
| 311 |
|
---|
| 312 | FileInfo fi = new FileInfo(tmpFilename);
|
---|
| 313 | var excelModelTreePic = modelWorksheet.Drawings.AddPicture("ModelTree", fi);
|
---|
| 314 | excelModelTreePic.SetSize(50);
|
---|
| 315 | excelModelTreePic.SetPosition(2, 0, 6, 0);
|
---|
| 316 | }
|
---|
| 317 |
|
---|
| 318 | private void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, IRegressionSolution solution) {
|
---|
| 319 | string preparedFormula = PrepareFormula(formulaParts);
|
---|
| 320 | int rows = solution.ProblemData.Dataset.Rows;
|
---|
| 321 | estimatedWorksheet.Cells[1, 1].Value = "Id";
|
---|
| 322 | estimatedWorksheet.Cells[1, 2].Value = "Target Variable";
|
---|
| 323 | estimatedWorksheet.Cells[1, 3].Value = "Estimated Values";
|
---|
| 324 | estimatedWorksheet.Cells[1, 4].Value = "Absolute Error";
|
---|
| 325 | estimatedWorksheet.Cells[1, 5].Value = "Relative Error";
|
---|
| 326 | estimatedWorksheet.Cells[1, 6].Value = "Error";
|
---|
| 327 | estimatedWorksheet.Cells[1, 7].Value = "Squared Error";
|
---|
| 328 | estimatedWorksheet.Cells[1, 9].Value = "Unbounded Estimated Values";
|
---|
| 329 | estimatedWorksheet.Cells[1, 10].Value = "Bounded Estimated Values";
|
---|
| 330 |
|
---|
| 331 | estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
|
---|
| 332 |
|
---|
| 333 | int targetIndex = solution.ProblemData.Dataset.VariableNames.ToList().FindIndex(x => x.Equals(solution.ProblemData.TargetVariable)) + 1;
|
---|
| 334 | for (int i = 0; i < rows; i++) {
|
---|
| 335 | estimatedWorksheet.Cells[i + 2, 1].Value = i;
|
---|
| 336 | estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress;
|
---|
| 337 | estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2);
|
---|
| 338 | }
|
---|
[9607] | 339 | estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 340 |
|
---|
| 341 | estimatedWorksheet.Cells["C2:C" + (rows + 1)].Formula = "J2";
|
---|
[9607] | 342 | estimatedWorksheet.Cells["C2:C" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 343 | estimatedWorksheet.Cells["D2:D" + (rows + 1)].Formula = "ABS(B2 - C2)";
|
---|
[9607] | 344 | estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9600] | 345 | estimatedWorksheet.Cells["E2:E" + (rows + 1)].Formula = "ABS(D2 / B2)";
|
---|
[9607] | 346 | estimatedWorksheet.Cells["E2:E" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 347 | estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "C2 - B2";
|
---|
[9607] | 348 | estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 349 | estimatedWorksheet.Cells["G2:G" + (rows + 1)].Formula = "POWER(F2, 2)";
|
---|
[9607] | 350 | estimatedWorksheet.Cells["G2:G" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 351 |
|
---|
[9607] | 352 | estimatedWorksheet.Cells["I2:I" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9583] | 353 | 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] | 354 | estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
|
---|
[9580] | 355 | }
|
---|
| 356 |
|
---|
| 357 | private string PrepareFormula(string[] formulaParts) {
|
---|
| 358 | string preparedFormula = formulaParts[0];
|
---|
| 359 | foreach (var part in formulaParts.Skip(2)) {
|
---|
| 360 | var varMap = part.Split(new string[] { " = " }, StringSplitOptions.None);
|
---|
| 361 | var columnName = "$" + varMap[1] + "1";
|
---|
| 362 | preparedFormula = preparedFormula.Replace(columnName, "Inputs!$" + varMap[1] + "{0}"); //{0} will be replaced later with the row number
|
---|
| 363 | }
|
---|
| 364 | return preparedFormula;
|
---|
| 365 | }
|
---|
| 366 |
|
---|
| 367 | private void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
|
---|
[9699] | 368 | //remark the performance of EPPlus drops dramatically
|
---|
| 369 | //if the data is not written row wise (from left to right) due the internal indices used.
|
---|
| 370 | var variableNames = dataset.VariableNames.Select((v, i) => new { variable = v, index = i + 1 }).ToDictionary(v => v.variable, v => v.index);
|
---|
| 371 | var nameMapping = list.Select(x => x.Split('=')[0].Trim()).ToArray();
|
---|
| 372 |
|
---|
| 373 | for (int row = 1; row <= dataset.Rows + 1; row++) {
|
---|
| 374 | for (int column = 1; column < nameMapping.Length + 1; column++) {
|
---|
| 375 | int variableIndex = variableNames[nameMapping[column - 1]];
|
---|
| 376 | inputsWorksheet.Cells[row, column].Formula = datasetWorksheet.Cells[row, variableIndex].FullAddress;
|
---|
[9580] | 377 | }
|
---|
| 378 | }
|
---|
| 379 | }
|
---|
| 380 |
|
---|
| 381 | private void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
|
---|
[9699] | 382 | //remark the performance of EPPlus drops dramatically
|
---|
| 383 | //if the data is not written row wise (from left to right) due the internal indices used.
|
---|
[9580] | 384 | Dataset dataset = problemData.Dataset;
|
---|
| 385 | var variableNames = dataset.VariableNames.ToList();
|
---|
[9699] | 386 | var doubleVariables = new HashSet<string>(dataset.DoubleVariables);
|
---|
| 387 |
|
---|
| 388 | for (int col = 1; col <= variableNames.Count; col++)
|
---|
[9580] | 389 | datasetWorksheet.Cells[1, col].Value = variableNames[col - 1];
|
---|
[9699] | 390 |
|
---|
| 391 | for (int row = 0; row < dataset.Rows; row++) {
|
---|
| 392 | for (int col = 0; col < variableNames.Count; col++) {
|
---|
| 393 | if (doubleVariables.Contains(variableNames[col]))
|
---|
| 394 | datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetDoubleValue(variableNames[col], row);
|
---|
| 395 | else
|
---|
[9906] | 396 | datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetValue(row, col);
|
---|
[9580] | 397 | }
|
---|
| 398 | }
|
---|
| 399 | }
|
---|
| 400 | }
|
---|
| 401 | }
|
---|