Changeset 9699


Ignore:
Timestamp:
07/05/13 15:56:58 (6 years ago)
Author:
mkommend
Message:

#1730: Improved the performance of the excel export.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/sources/HeuristicLab.Problems.DataAnalysis.Symbolic.Views/3.4/MenuItems/ExportSymbolicSolutionToExcelMenuItem.cs

    r9626 r9699  
    363363
    364364    private void WriteInputSheet(ExcelWorksheet inputsWorksheet, ExcelWorksheet datasetWorksheet, IEnumerable<string> list, Dataset dataset) {
    365       int rows = dataset.Rows;
    366       var variableNames = dataset.VariableNames.ToList();
    367       int cur = 1;
    368       foreach (var variableMapping in list) {
    369         var varMap = variableMapping.Split(new string[] { " = " }, StringSplitOptions.None);
    370         if (varMap.Count() != 2) throw new ArgumentException("variableMapping is not correct");
    371         int column = variableNames.FindIndex(x => x.Equals(varMap[0])) + 1;
    372         inputsWorksheet.Cells[1, cur].Value = varMap[0];
    373         for (int i = 2; i <= rows + 1; i++) {
    374           inputsWorksheet.Cells[i, cur].Formula = datasetWorksheet.Cells[i, column].FullAddress;
     365      //remark the performance of EPPlus drops dramatically
     366      //if the data is not written row wise (from left to right) due the internal indices used.
     367      var variableNames = dataset.VariableNames.Select((v, i) => new { variable = v, index = i + 1 }).ToDictionary(v => v.variable, v => v.index);
     368      var nameMapping = list.Select(x => x.Split('=')[0].Trim()).ToArray();
     369
     370      for (int row = 1; row <= dataset.Rows + 1; row++) {
     371        for (int column = 1; column < nameMapping.Length + 1; column++) {
     372          int variableIndex = variableNames[nameMapping[column - 1]];
     373          inputsWorksheet.Cells[row, column].Formula = datasetWorksheet.Cells[row, variableIndex].FullAddress;
    375374        }
    376         cur++;
    377375      }
    378376    }
    379377
    380378    private void WriteDatasetToExcel(ExcelWorksheet datasetWorksheet, IDataAnalysisProblemData problemData) {
     379      //remark the performance of EPPlus drops dramatically
     380      //if the data is not written row wise (from left to right) due the internal indices used.
    381381      Dataset dataset = problemData.Dataset;
    382382      var variableNames = dataset.VariableNames.ToList();
    383       for (int col = 1; col <= variableNames.Count; col++) {
     383      var doubleVariables = new HashSet<string>(dataset.DoubleVariables);
     384
     385      for (int col = 1; col <= variableNames.Count; col++)
    384386        datasetWorksheet.Cells[1, col].Value = variableNames[col - 1];
    385         if (dataset.DoubleVariables.Contains(variableNames[col - 1])) {
    386           datasetWorksheet.Cells[2, col].LoadFromCollection(dataset.GetDoubleValues(variableNames[col - 1]));
    387         } else {
    388           var coll = Enumerable.Range(0, dataset.Rows).Select(x => dataset.GetValue(x, col - 1));
    389           datasetWorksheet.Cells[2, col].LoadFromCollection(coll);
     387
     388      for (int row = 0; row < dataset.Rows; row++) {
     389        for (int col = 0; col < variableNames.Count; col++) {
     390          if (doubleVariables.Contains(variableNames[col]))
     391            datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetDoubleValue(variableNames[col], row);
     392          else
     393            datasetWorksheet.Cells[row + 2, col + 1].Value = dataset.GetValue(col, row);
    390394        }
    391395      }
Note: See TracChangeset for help on using the changeset viewer.