Free cookie consent management tool by TermsFeed Policy Generator

source: trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems/ExportSymbolicSolutionToExcelMenuItem.cs @ 9607

Last change on this file since 9607 was 9607, checked in by sforsten, 11 years ago

#1730: added cell formatting

File size: 19.2 KB
Line 
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
22using System;
23using System.Collections.Generic;
24using System.IO;
25using System.Linq;
26using System.Windows.Forms;
27using HeuristicLab.Encodings.SymbolicExpressionTreeEncoding.Views;
28using HeuristicLab.MainForm;
29using HeuristicLab.Optimizer;
30using OfficeOpenXml;
31using OfficeOpenXml.Drawing.Chart;
32
33namespace HeuristicLab.Problems.DataAnalysis.Symbolic.Views {
34  public class ExportSymbolicSolutionToExcelMenuItem : MainForm.WindowsForms.MenuItem, IOptimizerUserInterfaceItemProvider {
35    private const string TRAININGSTART = "TrainingStart";
36    private const string TRAININGEND = "TrainingEnd";
37    private const string TESTSTART = "TestStart";
38    private const string TESTEND = "TestEnd";
39
40    public override string Name {
41      get { return "Export Symbolic Solution To Excel"; }
42    }
43    public override IEnumerable<string> Structure {
44      get { return new string[] { "&Edit" }; }
45    }
46    public override int Position {
47      get { return 2500; }
48    }
49    public override string ToolTipText {
50      get { return "Create excel file of symbolic data analysis solutions."; }
51    }
52
53    protected override void OnToolStripItemSet(EventArgs e) {
54      ToolStripItem.Enabled = false;
55    }
56    protected override void OnActiveViewChanged(object sender, EventArgs e) {
57      IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
58      ToolStripItem.Enabled = activeView != null && activeView.Content is ISymbolicDataAnalysisSolution;
59    }
60
61    public override void Execute() {
62      var activeView = (IContentView)MainFormManager.MainForm.ActiveView;
63      var solution = (ISymbolicDataAnalysisSolution)activeView.Content;
64      var formatter = new SymbolicDataAnalysisExpressionExcelFormatter();
65      var formula = formatter.Format(solution.Model.SymbolicExpressionTree);
66      var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
67
68      SaveFileDialog saveFileDialog = new SaveFileDialog();
69      saveFileDialog.Filter = "Excel Workbook|*.xlsx";
70      saveFileDialog.Title = "Save an Excel File";
71      if (saveFileDialog.ShowDialog() == DialogResult.OK) {
72        string fileName = saveFileDialog.FileName;
73        FileInfo newFile = new FileInfo(fileName);
74        if (newFile.Exists) {
75          newFile.Delete();
76          newFile = new FileInfo(fileName);
77        }
78        using (ExcelPackage package = new ExcelPackage(newFile)) {
79          ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
80          FormatModelSheet(modelWorksheet, solution, formulaParts);
81
82          ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
83          WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
84
85          ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
86          WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
87
88          if (solution is IRegressionSolution) {
89            ExcelWorksheet estimatedWorksheet = package.Workbook.Worksheets.Add("Estimated Values");
90            WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as IRegressionSolution);
91
92            ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
93            AddCharts(chartsWorksheet);
94          }
95          package.Workbook.Properties.Title = "Excel Export";
96          package.Workbook.Properties.Author = "HEAL";
97          package.Workbook.Properties.Comments = "Excel export of a symbolic data analysis solution from HeuristicLab";
98
99          package.Save();
100        }
101      }
102    }
103
104    private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
105      int row = 1;
106      modelWorksheet.Cells[row, 1].Value = "Model";
107      modelWorksheet.Cells[row, 2].Value = solution.Name;
108
109      foreach (var part in formulaParts) {
110        modelWorksheet.Cells[row, 4].Value = part;
111        row++;
112      }
113
114      row = 2;
115      modelWorksheet.Cells[row, 1].Value = "Model Depth";
116      modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Depth;
117      row++;
118
119      modelWorksheet.Cells[row, 1].Value = "Model Length";
120      modelWorksheet.Cells[row, 2].Value = solution.Model.SymbolicExpressionTree.Length;
121      row += 2;
122
123      modelWorksheet.Cells[row, 1].Value = "Estimation Limits Lower";
124      modelWorksheet.Cells[row, 2].Value = solution.Model.LowerEstimationLimit;
125      modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
126      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
127      row++;
128
129      modelWorksheet.Cells[row, 1].Value = "Estimation Limits Upper";
130      modelWorksheet.Cells[row, 2].Value = solution.Model.UpperEstimationLimit;
131      modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
132      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
133      row += 2;
134
135      modelWorksheet.Cells[row, 1].Value = "Trainings Partition Start";
136      modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.Start;
137      modelWorksheet.Names.Add(TRAININGSTART, modelWorksheet.Cells[row, 2]);
138      row++;
139
140      modelWorksheet.Cells[row, 1].Value = "Trainings Partition End";
141      modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TrainingPartition.End;
142      modelWorksheet.Names.Add(TRAININGEND, modelWorksheet.Cells[row, 2]);
143      row++;
144
145      modelWorksheet.Cells[row, 1].Value = "Test Partition Start";
146      modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.Start;
147      modelWorksheet.Names.Add(TESTSTART, modelWorksheet.Cells[row, 2]);
148      row++;
149
150      modelWorksheet.Cells[row, 1].Value = "Test Partition End";
151      modelWorksheet.Cells[row, 2].Value = solution.ProblemData.TestPartition.End;
152      modelWorksheet.Names.Add(TESTEND, modelWorksheet.Cells[row, 2]);
153      row += 2;
154
155      string excelTrainingTarget = Indirect("B", true);
156      string excelTrainingEstimated = Indirect("C", true);
157      string excelTrainingAbsoluteError = Indirect("D", true);
158      string excelTrainingRelativeError = Indirect("E", true);
159      string excelTrainingMeanError = Indirect("F", true);
160      string excelTrainingMSE = Indirect("G", true);
161
162      string excelTestTarget = Indirect("B", false);
163      string excelTestEstimated = Indirect("C", false);
164      string excelTestAbsoluteError = Indirect("D", false);
165      string excelTestRelativeError = Indirect("E", false);
166      string excelTestMeanError = Indirect("F", false);
167      string excelTestMSE = Indirect("G", false);
168
169      modelWorksheet.Cells[row, 1].Value = "Pearson's R² (training)";
170      modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTrainingTarget, excelTrainingEstimated);
171      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
172      row++;
173
174      modelWorksheet.Cells[row, 1].Value = "Pearson's R² (test)";
175      modelWorksheet.Cells[row, 2].Formula = string.Format("POWER(PEARSON({0},{1}),2)", excelTestTarget, excelTestEstimated);
176      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
177      row++;
178
179      modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (training)";
180      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMSE);
181      modelWorksheet.Names.Add("TrainingMSE", modelWorksheet.Cells[row, 2]);
182      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
183      row++;
184
185      modelWorksheet.Cells[row, 1].Value = "Mean Squared Error (test)";
186      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMSE);
187      modelWorksheet.Names.Add("TestMSE", modelWorksheet.Cells[row, 2]);
188      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
189      row++;
190
191      modelWorksheet.Cells[row, 1].Value = "Mean absolute error (training)";
192      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingAbsoluteError);
193      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
194      row++;
195
196      modelWorksheet.Cells[row, 1].Value = "Mean absolute error (test)";
197      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestAbsoluteError);
198      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
199      row++;
200
201      modelWorksheet.Cells[row, 1].Value = "Mean error (training)";
202      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingMeanError);
203      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
204      row++;
205
206      modelWorksheet.Cells[row, 1].Value = "Mean error (test)";
207      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestMeanError);
208      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
209      row++;
210
211      modelWorksheet.Cells[row, 1].Value = "Average relative error (training)";
212      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTrainingRelativeError);
213      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
214      row++;
215
216      modelWorksheet.Cells[row, 1].Value = "Average relative error (test)";
217      modelWorksheet.Cells[row, 2].Formula = string.Format("AVERAGE({0})", excelTestRelativeError);
218      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.00%";
219      row++;
220
221      modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error (training)";
222      modelWorksheet.Cells[row, 2].Formula = string.Format("TrainingMSE / VAR({0})", excelTrainingTarget);
223      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
224      row++;
225
226      modelWorksheet.Cells[row, 1].Value = "Normalized Mean Squared error  (test)";
227      modelWorksheet.Cells[row, 2].Formula = string.Format("TestMSE / VAR({0})", excelTestTarget);
228      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
229
230      modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
231
232      AddModelTreePicture(modelWorksheet, solution.Model);
233    }
234
235    private string Indirect(string column, bool training) {
236      if (training) {
237        return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TRAININGSTART, TRAININGEND);
238      } else {
239        return string.Format("INDIRECT(\"'Estimated Values'!{0}\"&{1}+2&\":{0}\"&{2}+1)", column, TESTSTART, TESTEND);
240      }
241    }
242
243    private void AddCharts(ExcelWorksheet chartsWorksheet) {
244      chartsWorksheet.Names.AddFormula("AllId", "OFFSET('Estimated Values'!$A$1,1,0, COUNTA('Estimated Values'!$A:$A)-1)");
245      chartsWorksheet.Names.AddFormula("AllTarget", "OFFSET('Estimated Values'!$B$1,1,0, COUNTA('Estimated Values'!$B:$B)-1)");
246      chartsWorksheet.Names.AddFormula("AllEstimated", "OFFSET('Estimated Values'!$C$1,1,0, COUNTA('Estimated Values'!$C:$C)-1)");
247      chartsWorksheet.Names.AddFormula("TrainingId", "OFFSET('Estimated Values'!$A$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
248      chartsWorksheet.Names.AddFormula("TrainingTarget", "OFFSET('Estimated Values'!$B$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
249      chartsWorksheet.Names.AddFormula("TrainingEstimated", "OFFSET('Estimated Values'!$C$1,Model!TrainingStart + 1,0, Model!TrainingEnd - Model!TrainingStart)");
250      chartsWorksheet.Names.AddFormula("TestId", "OFFSET('Estimated Values'!$A$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
251      chartsWorksheet.Names.AddFormula("TestTarget", "OFFSET('Estimated Values'!$B$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
252      chartsWorksheet.Names.AddFormula("TestEstimated", "OFFSET('Estimated Values'!$C$1,Model!TestStart + 1,0, Model!TestEnd - Model!TestStart)");
253
254      var scatterPlot = chartsWorksheet.Drawings.AddChart("scatterPlot", eChartType.XYScatter);
255      scatterPlot.SetSize(800, 400);
256      scatterPlot.SetPosition(0, 0);
257      scatterPlot.Title.Text = "Scatter Plot";
258      var seriesAll = scatterPlot.Series.Add("AllTarget", "AllEstimated");
259      seriesAll.Header = "All";
260      var seriesTraining = scatterPlot.Series.Add("TrainingTarget", "TrainingEstimated");
261      seriesTraining.Header = "Training";
262      var seriesTest = scatterPlot.Series.Add("TestTarget", "TestEstimated");
263      seriesTest.Header = "Test";
264
265      var lineChart = chartsWorksheet.Drawings.AddChart("lineChart", eChartType.XYScatterLinesNoMarkers);
266      lineChart.SetSize(800, 400);
267      lineChart.SetPosition(400, 0);
268      lineChart.Title.Text = "LineChart";
269      var lineTarget = lineChart.Series.Add("AllTarget", "AllId");
270      lineTarget.Header = "Target";
271      var lineAll = lineChart.Series.Add("AllEstimated", "AllId");
272      lineAll.Header = "All";
273      var lineTraining = lineChart.Series.Add("TrainingEstimated", "TrainingId");
274      lineTraining.Header = "Training";
275      var lineTest = lineChart.Series.Add("TestEstimated", "TestId");
276      lineTest.Header = "Test";
277    }
278
279    private void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
280      SymbolicExpressionTreeChart modelTreePicture = new SymbolicExpressionTreeChart();
281      modelTreePicture.Tree = model.SymbolicExpressionTree;
282      string tmpFilename = Path.GetTempFileName();
283      modelTreePicture.Width = 1000;
284      modelTreePicture.Height = 500;
285      modelTreePicture.SaveImageAsEmf(tmpFilename);
286
287      FileInfo fi = new FileInfo(tmpFilename);
288      var excelModelTreePic = modelWorksheet.Drawings.AddPicture("ModelTree", fi);
289      excelModelTreePic.SetSize(50);
290      excelModelTreePic.SetPosition(2, 0, 6, 0);
291    }
292
293    private void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, IRegressionSolution solution) {
294      string preparedFormula = PrepareFormula(formulaParts);
295      int rows = solution.ProblemData.Dataset.Rows;
296      estimatedWorksheet.Cells[1, 1].Value = "Id";
297      estimatedWorksheet.Cells[1, 2].Value = "Target Variable";
298      estimatedWorksheet.Cells[1, 3].Value = "Estimated Values";
299      estimatedWorksheet.Cells[1, 4].Value = "Absolute Error";
300      estimatedWorksheet.Cells[1, 5].Value = "Relative Error";
301      estimatedWorksheet.Cells[1, 6].Value = "Error";
302      estimatedWorksheet.Cells[1, 7].Value = "Squared Error";
303      estimatedWorksheet.Cells[1, 9].Value = "Unbounded Estimated Values";
304      estimatedWorksheet.Cells[1, 10].Value = "Bounded Estimated Values";
305
306      estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
307
308      int targetIndex = solution.ProblemData.Dataset.VariableNames.ToList().FindIndex(x => x.Equals(solution.ProblemData.TargetVariable)) + 1;
309      for (int i = 0; i < rows; i++) {
310        estimatedWorksheet.Cells[i + 2, 1].Value = i;
311        estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress;
312        estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2);
313      }
314      estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
315
316      estimatedWorksheet.Cells["C2:C" + (rows + 1)].Formula = "J2";
317      estimatedWorksheet.Cells["C2:C" + (rows + 1)].Style.Numberformat.Format = "0.000";
318      estimatedWorksheet.Cells["D2:D" + (rows + 1)].Formula = "ABS(B2 - C2)";
319      estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.000";
320      estimatedWorksheet.Cells["E2:E" + (rows + 1)].Formula = "ABS(D2 / B2)";
321      estimatedWorksheet.Cells["E2:E" + (rows + 1)].Style.Numberformat.Format = "0.000";
322      estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "C2 - B2";
323      estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.000";
324      estimatedWorksheet.Cells["G2:G" + (rows + 1)].Formula = "POWER(F2, 2)";
325      estimatedWorksheet.Cells["G2:G" + (rows + 1)].Style.Numberformat.Format = "0.000";
326
327      estimatedWorksheet.Cells["I2:I" + (rows + 1)].Style.Numberformat.Format = "0.000";
328      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))";
329      estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
330    }
331
332    private string PrepareFormula(string[] formulaParts) {
333      string preparedFormula = formulaParts[0];
334      foreach (var part in formulaParts.Skip(2)) {
335        var varMap = part.Split(new string[] { " = " }, StringSplitOptions.None);
336        var columnName = "$" + varMap[1] + "1";
337        preparedFormula = preparedFormula.Replace(columnName, "Inputs!$" + varMap[1] + "{0}");   //{0} will be replaced later with the row number
338      }
339      return preparedFormula;
340    }
341
342    private void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
343      int rows = dataset.Rows;
344      var variableNames = dataset.VariableNames.ToList();
345      int cur = 1;
346      foreach (var variableMapping in list) {
347        var varMap = variableMapping.Split(new string[] { " = " }, StringSplitOptions.None);
348        if (varMap.Count() != 2) throw new ArgumentException("variableMapping is not correct");
349        int column = variableNames.FindIndex(x => x.Equals(varMap[0])) + 1;
350        inputsWorksheet.Cells[1, cur].Value = varMap[0];
351        for (int i = 2; i <= rows + 1; i++) {
352          inputsWorksheet.Cells[i, cur].Formula = datasetWorksheet.Cells[i, column].FullAddress;
353        }
354        cur++;
355      }
356    }
357
358    private void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
359      Dataset dataset = problemData.Dataset;
360      var variableNames = dataset.VariableNames.ToList();
361      for (int col = 1; col <= variableNames.Count; col++) {
362        datasetWorksheet.Cells[1, col].Value = variableNames[col - 1];
363        if (dataset.DoubleVariables.Contains(variableNames[col - 1])) {
364          datasetWorksheet.Cells[2, col].LoadFromCollection(dataset.GetDoubleValues(variableNames[col - 1]));
365        } else {
366          var coll = Enumerable.Range(0, dataset.Rows).Select(x => dataset.GetValue(x, col - 1));
367          datasetWorksheet.Cells[2, col].LoadFromCollection(coll);
368        }
369      }
370    }
371  }
372}
Note: See TracBrowser for help on using the repository browser.