Changeset 11541


Ignore:
Timestamp:
11/12/14 15:58:32 (6 years ago)
Author:
bburlacu
Message:

#2098: Updated SymbolicDiscriminantFunctionClassificationSolutionExcelExporter (added the missing functionality).

Location:
trunk/sources
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views/3.4/SymbolicDiscriminantFunctionClassificationSolutionExcelExporter.cs

    r11523 r11541  
    66using HeuristicLab.Problems.DataAnalysis.Symbolic.Views;
    77using OfficeOpenXml;
     8using OfficeOpenXml.Drawing.Chart;
    89
    910namespace HeuristicLab.Problems.DataAnalysis.Symbolic.Classification.Views {
    1011  public class SymbolicDiscriminantFunctionClassificationSolutionExcelExporter : SymbolicSolutionExcelExporter {
    11     protected override void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
    12       ExportChart(fileName, solution as ISymbolicClassificationSolution, formula);
    13     }
    14 
    15     private void ExportChart(string fileName, ISymbolicClassificationSolution solution, string formula) {
     12    public override void Export(IDataAnalysisSolution solution, string fileName) {
     13      var symbDiscriminantSolution = solution as SymbolicDiscriminantFunctionClassificationSolution;
     14      if (symbDiscriminantSolution == null) throw new NotSupportedException("This solution cannot be exported to Excel");
     15      var formatter = new SymbolicDataAnalysisExpressionExcelFormatter();
     16      var formula = formatter.Format(symbDiscriminantSolution.Model.SymbolicExpressionTree, solution.ProblemData.Dataset);
     17      ExportChart(fileName, symbDiscriminantSolution, formula);
     18    }
     19
     20    private void ExportChart(string fileName, SymbolicDiscriminantFunctionClassificationSolution solution, string formula) {
    1621      FileInfo newFile = new FileInfo(fileName);
    1722      if (newFile.Exists) {
     
    3540
    3641        ExcelWorksheet chartsWorksheet = package.Workbook.Worksheets.Add("Charts");
    37         AddCharts(chartsWorksheet);
     42        AddCharts(chartsWorksheet, solution);
    3843        package.Workbook.Properties.Title = "Excel Export";
    3944        package.Workbook.Properties.Author = "HEAL";
     
    4247        package.Save();
    4348      }
    44     }
    45 
    46     protected void WriteEstimatedWorksheet(ExcelWorksheet estimatedWorksheet, ExcelWorksheet datasetWorksheet, string[] formulaParts, ISymbolicDataAnalysisSolution solution) {
    47       WriteEstimatedWorksheet(estimatedWorksheet, datasetWorksheet, formulaParts, solution as SymbolicDiscriminantFunctionClassificationSolution);
    48     }
    49 
    50     protected override void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
    51       FormatModelSheet(modelWorksheet, solution as SymbolicDiscriminantFunctionClassificationSolution, formulaParts);
    5249    }
    5350
     
    180177      estimatedWorksheet.Cells[1, 3].Value = "Estimated Values"; // C
    181178      estimatedWorksheet.Cells[1, 4].Value = "Estimated Class Values"; // D
    182       estimatedWorksheet.Cells[1, 6].Value = "Error"; // E
     179      estimatedWorksheet.Cells[1, 6].Value = "Error"; // F
    183180      estimatedWorksheet.Cells[1, 7].Value = "Squared Error"; // G
    184181      estimatedWorksheet.Cells[1, 9].Value = "Unbounded Estimated Values"; // I
    185182      estimatedWorksheet.Cells[1, 10].Value = "Bounded Estimated Values"; // J
    186 
    187       estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
     183      estimatedWorksheet.Cells[1, 11].Value = "Random Key"; // K
    188184
    189185      var thresholds = solution.Model.Thresholds.Where(x => !double.IsInfinity(x)).ToList();
    190186      var thresholdsFormula = GenerateThresholdsFormula(thresholds);
     187
     188      const int columnIndex = 13; // index of beginning columns for class values
     189      for (int i = 0; i <= thresholds.Count; ++i) {
     190        estimatedWorksheet.Cells[1, i + columnIndex].Value = "Class " + i;
     191        if (i < thresholds.Count)
     192          estimatedWorksheet.Cells[1, i + columnIndex + thresholds.Count + 1].Value = "Threshold " + i;
     193      }
     194      estimatedWorksheet.Cells[1, 1, 1, 10].AutoFitColumns();
    191195
    192196      int targetIndex = solution.ProblemData.Dataset.VariableNames.ToList().FindIndex(x => x.Equals(solution.ProblemData.TargetVariable)) + 1;
     
    195199        estimatedWorksheet.Cells[i + 2, 2].Formula = datasetWorksheet.Cells[i + 2, targetIndex].FullAddress; // target values
    196200        estimatedWorksheet.Cells[i + 2, 9].Formula = string.Format(preparedFormula, i + 2); // formula (estimated) values
     201
     202        string condition = string.Empty;
     203        string rowRef = "C" + (i + 2);
     204
     205        int nClasses = thresholds.Count + 1;
     206        for (int j = columnIndex; j < columnIndex + nClasses; ++j) {
     207          int idx = j - columnIndex + 5; // row index for the threshold values
     208          if (j == columnIndex) {
     209            condition = rowRef + " < Model!$B$" + idx;
     210          } else if (j > columnIndex && j < columnIndex + thresholds.Count) {
     211            condition = "AND(" + rowRef + "> Model!$B$" + (idx - 1) + ", " + rowRef + " < Model!$B$" + idx + ")";
     212          } else if (j == columnIndex + thresholds.Count) {
     213            condition = rowRef + " > Model!$B$" + (idx - 1);
     214          }
     215          estimatedWorksheet.Cells[i + 2, j].Formula = "IF(" + condition + ", " + rowRef + ", #N/A)";
     216
     217          if (j < columnIndex + thresholds.Count)
     218            estimatedWorksheet.Cells[i + 2, j + nClasses].Formula = "Model!$B$" + idx;
     219        }
    197220      }
    198221      estimatedWorksheet.Cells["B2:B" + (rows + 1)].Style.Numberformat.Format = "0.000";
     
    213236      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))";
    214237      estimatedWorksheet.Cells["J2:J" + (rows + 1)].Style.Numberformat.Format = "0.000";
     238
     239      estimatedWorksheet.Cells["K2:K" + (rows + 1)].Formula = "RAND()";
     240      estimatedWorksheet.Cells["K2:K" + (rows + 1)].Style.Numberformat.Format = "0.000";
     241    }
     242
     243    private void AddCharts(ExcelWorksheet chartsWorksheet, DiscriminantFunctionClassificationSolution solution) {
     244      List<char> columns = new List<char> { 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
     245      var scatterPlot = chartsWorksheet.Drawings.AddChart("scatterPlot", eChartType.XYScatter);
     246      scatterPlot.SetSize(800, 400);
     247      scatterPlot.SetPosition(0, 0);
     248      scatterPlot.Title.Text = "Scatter Plot";
     249      var thresholds = solution.Model.Thresholds.Where(x => !double.IsInfinity(x)).ToList();
     250      chartsWorksheet.Names.AddFormula("XKey", "OFFSET('Estimated Values'!$K$1,1,0, COUNTA('Estimated Values'!$K:$K)-1)");
     251
     252      for (int i = 0; i <= thresholds.Count; ++i) {
     253        string header = "Class" + i;
     254        chartsWorksheet.Names.AddFormula(header, String.Format("OFFSET('Estimated Values'!${0}$1, 1, 0, COUNTA('Estimated Values'!${0}:${0})-1)", columns[i]));
     255        var series = scatterPlot.Series.Add(header, "XKey");
     256        series.Header = header;
     257
     258        if (i < thresholds.Count) {
     259          chartsWorksheet.Names.AddFormula("Threshold" + i, String.Format("OFFSET('Estimated Values'!${0}$1, 1, 0, COUNTA('Estimated Values'!${0}:${0})-1)", columns[i + thresholds.Count + 1]));
     260          var s = scatterPlot.Series.Add("Threshold" + i, "XKey");
     261          s.Header = "Threshold" + i;
     262        }
     263      }
    215264    }
    216265
    217266    // this method assumes that the thresholds list is sorted in ascending order
    218     private string GenerateThresholdsFormula(List<double> thresholds) {
     267    private static string GenerateThresholdsFormula(List<double> thresholds) {
    219268      if (thresholds.Count == 1) {
    220269        return String.Format("IF(J2 < {0}, 0, 1)", thresholds[0]);
  • trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/Exporters/SymbolicSolutionExcelExporter.cs

    r11523 r11541  
    4444    }
    4545
    46     public void Export(IDataAnalysisSolution solution, string fileName) {
     46    public virtual void Export(IDataAnalysisSolution solution, string fileName) {
    4747      var symbSolution = solution as ISymbolicDataAnalysisSolution;
    4848      if (symbSolution == null) throw new NotSupportedException("This solution cannot be exported to Excel");
     
    5252    }
    5353
    54     protected virtual void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
     54    private void ExportChart(string fileName, ISymbolicDataAnalysisSolution solution, string formula) {
    5555      FileInfo newFile = new FileInfo(fileName);
    5656      if (newFile.Exists) {
     
    8585    }
    8686
    87     protected virtual void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
     87    private void FormatModelSheet(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisSolution solution, IEnumerable<string> formulaParts) {
    8888      int row = 1;
    8989      modelWorksheet.Cells[row, 1].Value = "Model";
     
    224224    }
    225225
    226     protected virtual void AddCharts(ExcelWorksheet chartsWorksheet) {
     226    private void AddCharts(ExcelWorksheet chartsWorksheet) {
    227227      chartsWorksheet.Names.AddFormula("AllId", "OFFSET('Estimated Values'!$A$1,1,0, COUNTA('Estimated Values'!$A:$A)-1)");
    228228      chartsWorksheet.Names.AddFormula("AllTarget", "OFFSET('Estimated Values'!$B$1,1,0, COUNTA('Estimated Values'!$B:$B)-1)");
     
    260260    }
    261261
    262     protected virtual void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
     262    protected void AddModelTreePicture(ExcelWorksheet modelWorksheet, ISymbolicDataAnalysisModel model) {
    263263      SymbolicExpressionTreeChart modelTreePicture = new SymbolicExpressionTreeChart();
    264264      modelTreePicture.Tree = model.SymbolicExpressionTree;
Note: See TracChangeset for help on using the changeset viewer.