source: trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic/3.4/Formatters/SymbolicDataAnalysisExpressionExcelFormatter.cs @ 9626

Last change on this file since 9626 was 9626, checked in by mkommend, 6 years ago

#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.
File size: 9.1 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.Globalization;
25using System.Linq;
26using System.Text;
27using HeuristicLab.Common;
28using HeuristicLab.Core;
29using HeuristicLab.Encodings.SymbolicExpressionTreeEncoding;
30using HeuristicLab.Persistence.Default.CompositeSerializers.Storable;
31
32namespace HeuristicLab.Problems.DataAnalysis.Symbolic {
33  [Item("Excel String Formatter", "String formatter for string representations of symbolic data analysis expressions in Excel syntax.")]
34  [StorableClass]
35  public sealed class SymbolicDataAnalysisExpressionExcelFormatter : NamedItem, ISymbolicExpressionTreeStringFormatter {
36    [StorableConstructor]
37    private SymbolicDataAnalysisExpressionExcelFormatter(bool deserializing) : base(deserializing) { }
38    private SymbolicDataAnalysisExpressionExcelFormatter(SymbolicDataAnalysisExpressionExcelFormatter original, Cloner cloner) : base(original, cloner) { }
39    public SymbolicDataAnalysisExpressionExcelFormatter()
40      : base() {
41      Name = ItemName;
42      Description = ItemDescription;
43    }
44    public override IDeepCloneable Clone(Cloner cloner) {
45      return new SymbolicDataAnalysisExpressionExcelFormatter(this, cloner);
46    }
47    private string GetExcelColumnName(int columnNumber) {
48      int dividend = columnNumber;
49      string columnName = String.Empty;
50
51      while (dividend > 0) {
52        int modulo = (dividend - 1) % 26;
53        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
54        dividend = (int)((dividend - modulo) / 26);
55      }
56
57      return columnName;
58    }
59
60    private readonly Dictionary<string, string> variableNameMapping = new Dictionary<string, string>();
61    private int currentVariableIndex = 0;
62    private string GetColumnToVariableName(string variabelName) {
63      if (!variableNameMapping.ContainsKey(variabelName)) {
64        currentVariableIndex++;
65        variableNameMapping.Add(variabelName, GetExcelColumnName(currentVariableIndex));
66      }
67      return string.Format("${0}1", variableNameMapping[variabelName]);
68    }
69    public string Format(ISymbolicExpressionTree symbolicExpressionTree) {
70      return Format(symbolicExpressionTree, null);
71    }
72
73    public string Format(ISymbolicExpressionTree symbolicExpressionTree, Dataset dataset) {
74      var stringBuilder = new StringBuilder();
75      if (dataset != null) CalculateVariableMapping(symbolicExpressionTree, dataset);
76
77      stringBuilder.Append("=");
78      stringBuilder.Append(FormatRecursively(symbolicExpressionTree.Root));
79
80      foreach (var variable in variableNameMapping) {
81        stringBuilder.AppendLine();
82        stringBuilder.Append(variable.Key + " = " + variable.Value);
83      }
84      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      }
97    }
98
99    private string FormatRecursively(ISymbolicExpressionTreeNode node) {
100      ISymbol symbol = node.Symbol;
101      StringBuilder stringBuilder = new StringBuilder();
102
103      if (symbol is ProgramRootSymbol) {
104        stringBuilder.AppendLine(FormatRecursively(node.GetSubtree(0)));
105      } else if (symbol is StartSymbol)
106        return FormatRecursively(node.GetSubtree(0));
107      else if (symbol is Addition) {
108        stringBuilder.Append("(");
109        for (int i = 0; i < node.SubtreeCount; i++) {
110          if (i > 0) stringBuilder.Append("+");
111          stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
112        }
113        stringBuilder.Append(")");
114      } else if (symbol is Average) {
115        stringBuilder.Append("(1/");
116        stringBuilder.Append(node.SubtreeCount);
117        stringBuilder.Append(")*(");
118        for (int i = 0; i < node.SubtreeCount; i++) {
119          if (i > 0) stringBuilder.Append("+");
120          stringBuilder.Append("(");
121          stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
122          stringBuilder.Append(")");
123        }
124        stringBuilder.Append(")");
125      } else if (symbol is Constant) {
126        ConstantTreeNode constantTreeNode = node as ConstantTreeNode;
127        stringBuilder.Append(constantTreeNode.Value.ToString(CultureInfo.InvariantCulture));
128      } else if (symbol is Cosine) {
129        stringBuilder.Append("COS(");
130        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
131        stringBuilder.Append(")");
132      } else if (symbol is Division) {
133        if (node.SubtreeCount == 1) {
134          stringBuilder.Append("1/");
135          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
136        } else {
137          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
138          stringBuilder.Append("/(");
139          for (int i = 1; i < node.SubtreeCount; i++) {
140            if (i > 1) stringBuilder.Append("*");
141            stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
142          }
143          stringBuilder.Append(")");
144        }
145      } else if (symbol is Exponential) {
146        stringBuilder.Append("EXP(");
147        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
148        stringBuilder.Append(")");
149      } else if (symbol is Square) {
150        stringBuilder.Append("POWER(");
151        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
152        stringBuilder.Append(",2)");
153      } else if (symbol is SquareRoot) {
154        stringBuilder.Append("SQRT(");
155        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
156        stringBuilder.Append(")");
157      } else if (symbol is Logarithm) {
158        stringBuilder.Append("LN(");
159        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
160        stringBuilder.Append(")");
161      } else if (symbol is Multiplication) {
162        for (int i = 0; i < node.SubtreeCount; i++) {
163          if (i > 0) stringBuilder.Append("*");
164          stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
165        }
166      } else if (symbol is Sine) {
167        stringBuilder.Append("SIN(");
168        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
169        stringBuilder.Append(")");
170      } else if (symbol is Subtraction) {
171        stringBuilder.Append("(");
172        if (node.SubtreeCount == 1) {
173          stringBuilder.Append("-");
174          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
175        } else {
176          stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
177          for (int i = 1; i < node.SubtreeCount; i++) {
178            stringBuilder.Append("-");
179            stringBuilder.Append(FormatRecursively(node.GetSubtree(i)));
180          }
181        }
182        stringBuilder.Append(")");
183      } else if (symbol is Tangent) {
184        stringBuilder.Append("TAN(");
185        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
186        stringBuilder.Append(")");
187
188      } else if (symbol is Variable) {
189        VariableTreeNode variableTreeNode = node as VariableTreeNode;
190        stringBuilder.Append(variableTreeNode.Weight.ToString(CultureInfo.InvariantCulture));
191        stringBuilder.Append("*");
192        stringBuilder.Append(GetColumnToVariableName(variableTreeNode.VariableName));// + LagToString(currentLag));
193      } else if (symbol is Power) {
194        stringBuilder.Append("POWER(");
195        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
196        stringBuilder.Append(",ROUND(");
197        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
198        stringBuilder.Append(",0))");
199      } else if (symbol is Root) {
200        stringBuilder.Append("(");
201        stringBuilder.Append(FormatRecursively(node.GetSubtree(0)));
202        stringBuilder.Append(")^(1 / ROUND(");
203        stringBuilder.Append(FormatRecursively(node.GetSubtree(1)));
204        stringBuilder.Append(",0))");
205      } else {
206        throw new NotImplementedException("Excel export of " + node.Symbol + " is not implemented.");
207      }
208      return stringBuilder.ToString();
209    }
210  }
211}
Note: See TracBrowser for help on using the repository browser.