Changeset 9626


Ignore:
Timestamp:
06/13/13 19:45:37 (6 years ago)
Author:
mkommend
Message:

#1730: Implemented new features for the excel export:

  • The menu item is enabled if any view containing a symreg solution is displayed.
  • Classifications are not longer supported.
  • Export is performed asynchronously.
  • Inputs are sorted in the same order if a dataset is passed.
Location:
trunk/sources
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • trunk/sources/HeuristicLab.MainForm.WindowsForms/3.3/Controls/ControlExtensions.cs

    r9456 r9626  
    2121
    2222using System;
     23using System.Collections.Generic;
    2324using System.Runtime.InteropServices;
    2425using System.Windows.Forms;
     
    4546      }
    4647    }
     48
     49    public static IEnumerable<Control> GetNestedControls(this Control control, Func<Control, bool> condition = null) {
     50      if (control == null) yield break;
     51      if (condition == null) condition = (c) => true;
     52
     53      Queue<Control> unprocessed = new Queue<Control>();
     54      unprocessed.Enqueue(control);
     55
     56      while (unprocessed.Count > 0) {
     57        Control c = unprocessed.Dequeue();
     58        if (condition(c)) yield return c;
     59        foreach (Control child in c.Controls)
     60          unprocessed.Enqueue(child);
     61      }
     62    }
    4763  }
    4864}
  • trunk/sources/HeuristicLab.MainForm.WindowsForms/3.3/MainForms/MainForm.cs

    r9456 r9626  
    386386      ToolStripMenuItem item = new ToolStripMenuItem();
    387387      this.SetToolStripItemProperties(item, menuItem);
     388      this.InsertItem(menuItem.Structure, typeof(ToolStripMenuItem), item, menuStrip.Items);
    388389      if (menuItem is MenuItem) {
    389390        MenuItem menuItemBase = (MenuItem)menuItem;
     
    392393        item.DisplayStyle = menuItemBase.ToolStripItemDisplayStyle;
    393394      }
    394       this.InsertItem(menuItem.Structure, typeof(ToolStripMenuItem), item, menuStrip.Items);
    395395    }
    396396
  • trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems/ExportSymbolicSolutionToExcelMenuItem.cs

    r9607 r9626  
    2222using System;
    2323using System.Collections.Generic;
     24using System.ComponentModel;
    2425using System.IO;
    2526using System.Linq;
     
    2728using HeuristicLab.Encodings.SymbolicExpressionTreeEncoding.Views;
    2829using HeuristicLab.MainForm;
     30using HeuristicLab.MainForm.WindowsForms;
    2931using HeuristicLab.Optimizer;
    3032using OfficeOpenXml;
     
    5254
    5355    protected override void OnToolStripItemSet(EventArgs e) {
     56      base.OnToolStripItemSet(e);
    5457      ToolStripItem.Enabled = false;
    55     }
    56     protected override void OnActiveViewChanged(object sender, EventArgs e) {
     58      var menuItem = ToolStripItem.OwnerItem as ToolStripMenuItem;
     59      if (menuItem != null)
     60        menuItem.DropDownOpening += menuItem_DropDownOpening;
     61    }
     62
     63    private void menuItem_DropDownOpening(object sender, EventArgs e) {
    5764      IContentView activeView = MainFormManager.MainForm.ActiveView as IContentView;
    58       ToolStripItem.Enabled = activeView != null && activeView.Content is ISymbolicDataAnalysisSolution;
     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;
    5969    }
    6070
    6171    public override void Execute() {
    62       var activeView = (IContentView)MainFormManager.MainForm.ActiveView;
     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);
    6376      var solution = (ISymbolicDataAnalysisSolution)activeView.Content;
    6477      var formatter = new SymbolicDataAnalysisExpressionExcelFormatter();
    65       var formula = formatter.Format(solution.Model.SymbolicExpressionTree);
    66       var formulaParts = formula.Split(new string[] { Environment.NewLine }, StringSplitOptions.None);
     78      var formula = formatter.Format(solution.Model.SymbolicExpressionTree, solution.ProblemData.Dataset);
     79
    6780
    6881      SaveFileDialog saveFileDialog = new SaveFileDialog();
     
    7184      if (saveFileDialog.ShowDialog() == DialogResult.OK) {
    7285        string fileName = saveFileDialog.FileName;
    73         FileInfo newFile = new FileInfo(fileName);
    74         if (newFile.Exists) {
    75           newFile.Delete();
    76           newFile = new FileInfo(fileName);
     86        using (BackgroundWorker bg = new BackgroundWorker()) {
     87          bg.DoWork += (b, e) => ExportChart(fileName, solution, formula);
     88          bg.RunWorkerAsync();
    7789        }
    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();
     90      }
     91    }
     92
     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);
     100
     101      using (ExcelPackage package = new ExcelPackage(newFile)) {
     102        ExcelWorksheet modelWorksheet = package.Workbook.Worksheets.Add("Model");
     103        FormatModelSheet(modelWorksheet, solution, formulaParts);
     104
     105        ExcelWorksheet datasetWorksheet = package.Workbook.Worksheets.Add("Dataset");
     106        WriteDatasetToExcel(datasetWorksheet, solution.ProblemData);
     107
     108        ExcelWorksheet inputsWorksheet = package.Workbook.Worksheets.Add("Inputs");
     109        WriteInputSheet(inputsWorksheet, datasetWorksheet, formulaParts.Skip(2), solution.ProblemData.Dataset);
     110
     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);
    100117        }
     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();
    101123      }
    102124    }
  • trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic/3.4/Formatters/SymbolicDataAnalysisExpressionExcelFormatter.cs

    r9587 r9626  
    2323using System.Collections.Generic;
    2424using System.Globalization;
     25using System.Linq;
    2526using System.Text;
    2627using HeuristicLab.Common;
     
    6667      return string.Format("${0}1", variableNameMapping[variabelName]);
    6768    }
    68 
    6969    public string Format(ISymbolicExpressionTree symbolicExpressionTree) {
     70      return Format(symbolicExpressionTree, null);
     71    }
     72
     73    public string Format(ISymbolicExpressionTree symbolicExpressionTree, Dataset dataset) {
    7074      var stringBuilder = new StringBuilder();
     75      if (dataset != null) CalculateVariableMapping(symbolicExpressionTree, dataset);
     76
    7177      stringBuilder.Append("=");
    7278      stringBuilder.Append(FormatRecursively(symbolicExpressionTree.Root));
     79
    7380      foreach (var variable in variableNameMapping) {
    7481        stringBuilder.AppendLine();
     
    7683      }
    7784      return stringBuilder.ToString();
     85    }
     86
     87    private void CalculateVariableMapping(ISymbolicExpressionTree tree, Dataset dataset) {
     88      int columnIndex = 0;
     89      int inputIndex = 0;
     90      var usedVariables = tree.IterateNodesPrefix().OfType<VariableTreeNode>().Select(v => v.VariableName).Distinct();
     91      foreach (var variable in dataset.VariableNames) {
     92        columnIndex++;
     93        if (!usedVariables.Contains(variable)) continue;
     94        inputIndex++;
     95        variableNameMapping[variable] = GetExcelColumnName(inputIndex);
     96      }
    7897    }
    7998
     
    137156        stringBuilder.Append(")");
    138157      } else if (symbol is Logarithm) {
    139         stringBuilder.Append("LOG(");
    140         stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
    141         stringBuilder.Append(", EXP(1))"); // Excel does not use the natural logarithm, therefor the base has to be set
     158        stringBuilder.Append("LN(");
     159        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
     160        stringBuilder.Append(")");
    142161      } else if (symbol is Multiplication) {
    143162        for (int i = 0; i < node.SubtreeCount; i++) {
Note: See TracChangeset for help on using the changeset viewer.