Changeset 9931 for stable/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems/ExportSymbolicSolutionToExcelMenuItem.cs

Timestamp:

09/03/13 15:46:01 (11 years ago)

Author:

ascheibe

Message:

#1730 merged r9587,r9590, r9600, r9607, r9626, r9658, r9659, r9699, r9906 into stable

Location:

stable

Files:

• . (modified) (1 prop)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Views (modified) (1 prop)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems (copied) (copied from trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems)
• HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems/ExportSymbolicSolutionToExcelMenuItem.cs (modified) (12 diffs)

stable/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems/ExportSymbolicSolutionToExcelMenuItem.cs

@@ -22 +22 @@
 using System;
 using System.Collections.Generic;
+using System.ComponentModel;
 using System.IO;
 using System.Linq;
@@ -27 +28 @@
 using HeuristicLab.Encodings.SymbolicExpressionTreeEncoding.Views;
 using HeuristicLab.MainForm;
+using HeuristicLab.MainForm.WindowsForms;
 using HeuristicLab.Optimizer;
 using OfficeOpenXml;
@@ -52 +54 @@
 
     protected override void OnToolStripItemSet(EventArgs e) {
+      base.OnToolStripItemSet(e);
       ToolStripItem.Enabled = false;
-    }
-    protected override void OnActiveViewChanged(object sender, EventArgs e) {
+      var menuItem = ToolStripItem.OwnerItem as ToolStripMenuItem;
+      if (menuItem != null)
+        menuItem.DropDownOpening += menuItem_DropDownOpening;
+    }
+
+    private void menuItem_DropDownOpening(object sender, EventArgs e) {
       IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
-      ToolStripItem.Enabled = activeView != null && activeView.Content is ISymbolicDataAnalysisSolution;
+      Control control = activeView as Control;
+      activeView = control.GetNestedControls((c) => c.Visible)
+        .OfType<IContentView>().FirstOrDefault(v => v.Content is ISymbolicDataAnalysisSolution && v.Content is IRegressionSolution);
+      ToolStripItem.Enabled = activeView != null;
     }
 
     public override void Execute() {
-      var activeView = (IContentView)MainFormManager.MainForm.ActiveView;
+      IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
+      Control control = activeView as Control;
+      activeView = control.GetNestedControls((c) => c.Visible)
+        .OfType<IContentView>().First(v => v.Content is ISymbolicDataAnalysisSolution && v.Content is IRegressionSolution);
       var solution = (ISymbolicDataAnalysisSolution)activeView.Content;
       var formatter = new SymbolicDataAnalysisExpressionExcelFormatter();
-      var formula = formatter.Format(solution.Model.SymbolicExpressionTree);
-      var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
+      var formula = formatter.Format(solution.Model.SymbolicExpressionTree, solution.ProblemData.Dataset);
+      control = (Control)activeView;
+
 
       SaveFileDialog saveFileDialog = new SaveFileDialog();
@@ -71 +85 @@
       if (saveFileDialog.ShowDialog() == DialogResult.OK) {
         string fileName = saveFileDialog.FileName;
-        FileInfo newFile = new FileInfo(fileName);
-        if (newFile.Exists) {
-          newFile.Delete();
-          newFile = new FileInfo(fileName);
+        using (BackgroundWorker bg = new BackgroundWorker()) {
+          MainFormManager.GetMainForm<MainForm.WindowsForms.MainForm>().AddOperationProgressToView(control, "Exportion solution to " + fileName + ".");
+          bg.DoWork += (b, e) => ExportChart(fileName, solution, formula);
+          bg.RunWorkerCompleted += (o, e) => MainFormManager.GetMainForm<MainForm.WindowsForms.MainForm>().RemoveOperationProgressFromView(control);
+          bg.RunWorkerAsync();
         }
-        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();
       }
     }
@@ -124 +149 @@
       modelWorksheet.Cells[row, 2].Value = solution.Model.LowerEstimationLimit;
       modelWorksheet.Names.Add("EstimationLimitLower", modelWorksheet.Cells[row, 2]);
+      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
       row++;
 
@@ -129 +155 @@
       modelWorksheet.Cells[row, 2].Value = solution.Model.UpperEstimationLimit;
       modelWorksheet.Names.Add("EstimationLimitUpper", modelWorksheet.Cells[row, 2]);
+      modelWorksheet.Cells[row, 2].Style.Numberformat.Format = "0.000";
       row += 2;
 
@@ -167 +194 @@
       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++;
 
@@ -176 +205 @@
       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.000";
       row++;
 
@@ -181 +211 @@
       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.000";
       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.000";
       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.000";
       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.000";
       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.000";
       row++;
 
@@ -211 +246 @@
       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.000";
       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.000";
 
       modelWorksheet.Cells["A1:B" + row].AutoFitColumns();
@@ -300 +337 @@
         estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2);
       }
+      estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
 
       estimatedWorksheet.Cells["C2:C" + (rows + 1)].Formula = "J2";
-
+      estimatedWorksheet.Cells["C2:C" + (rows + 1)].Style.Numberformat.Format = "0.000";
       estimatedWorksheet.Cells["D2:D" + (rows + 1)].Formula = "ABS(B2 - C2)";
-      estimatedWorksheet.Cells["E2:E" + (rows + 1)].Formula = "D2 / B2";
+      estimatedWorksheet.Cells["D2:D" + (rows + 1)].Style.Numberformat.Format = "0.000";
+      estimatedWorksheet.Cells["E2:E" + (rows + 1)].Formula = "ABS(D2 / B2)";
+      estimatedWorksheet.Cells["E2:E" + (rows + 1)].Style.Numberformat.Format = "0.000";
       estimatedWorksheet.Cells["F2:F" + (rows + 1)].Formula = "C2 - B2";
+      estimatedWorksheet.Cells["F2:F" + (rows + 1)].Style.Numberformat.Format = "0.000";
       estimatedWorksheet.Cells["G2:G" + (rows + 1)].Formula = "POWER(F2, 2)";
-
+      estimatedWorksheet.Cells["G2:G" + (rows + 1)].Style.Numberformat.Format = "0.000";
+
+      estimatedWorksheet.Cells["I2:I" + (rows + 1)].Style.Numberformat.Format = "0.000";
       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))";
+      estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
     }
 
@@ -322 +366 @@
 
     private void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
-      int rows = dataset.Rows;
-      var variableNames = dataset.VariableNames.ToList();
-      int cur = 1;
-      foreach (var variableMapping in list) {
-        var varMap = variableMapping.Split(new string[] { " = " }, StringSplitOptions.None);
-        if (varMap.Count() != 2) throw new ArgumentException("variableMapping is not correct");
-        int column = variableNames.FindIndex(x => x.Equals(varMap[0])) + 1;
-        inputsWorksheet.Cells[1, cur].Value = varMap[0];
-        for (int i = 2; i <= rows + 1; i++) {
-          inputsWorksheet.Cells[i, cur].Formula = datasetWorksheet.Cells[i, column].FullAddress;
+      //remark the performance of EPPlus drops dramatically 
+      //if the data is not written row wise (from left to right) due the internal indices used.
+      var variableNames = dataset.VariableNames.Select((v, i) => new { variable = v, index = i + 1 }).ToDictionary(v => v.variable, v => v.index);
+      var nameMapping = list.Select(x => x.Split('=')[0].Trim()).ToArray();
+
+      for (int row = 1; row <= dataset.Rows + 1; row++) {
+        for (int column = 1; column < nameMapping.Length + 1; column++) {
+          int variableIndex = variableNames[nameMapping[column - 1]];
+          inputsWorksheet.Cells[row, column].Formula = datasetWorksheet.Cells[row, variableIndex].FullAddress;
         }
-        cur++;
       }
     }
 
     private void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
+      //remark the performance of EPPlus drops dramatically 
+      //if the data is not written row wise (from left to right) due the internal indices used.
       Dataset dataset = problemData.Dataset;
       var variableNames = dataset.VariableNames.ToList();
-      for (int col = 1; col <= variableNames.Count; col++) {
+      var doubleVariables = new HashSet<string>(dataset.DoubleVariables);
+
+      for (int col = 1; col <= variableNames.Count; col++)
         datasetWorksheet.Cells[1, col].Value = variableNames[col - 1];
-        if (dataset.DoubleVariables.Contains(variableNames[col - 1])) {
-          datasetWorksheet.Cells[2, col].LoadFromCollection(dataset.GetDoubleValues(variableNames[col - 1]));
-        } else {
-          var coll = Enumerable.Range(0, dataset.Rows).Select(x => dataset.GetValue(x, col - 1));
-          datasetWorksheet.Cells[2, col].LoadFromCollection(coll);
+
+      for (int row = 0; row < dataset.Rows; row++) {
+        for (int col = 0; col < variableNames.Count; col++) {
+          if (doubleVariables.Contains(variableNames[col]))
+            datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetDoubleValue(variableNames[col], row);
+          else
+            datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetValue(row, col);
         }
       }