Opened 6 years ago

Closed 4 years ago

#1730 closed feature request (done)

Exporting symbolic data analysis solutions to excel files

Reported by: gkronber Owned by: ascheibe
Priority: high Milestone: HeuristicLab 3.3.9
Component: Problems.DataAnalysis.Symbolic Version: 3.3.9
Keywords: Cc:

Description

The export functionality for symbolic data analysis solutions is hard to find. It would be nice if this feature is more easily accessible for instance through a button in the simplifier views or in the solution views.

Change History (38)

comment:1 Changed 5 years ago by gkronber

  • Summary changed from Export button for symbolic data analysis solutions to Exporting symbolic data analysis solutions to excel files

comment:2 Changed 4 years ago by gkronber

  • Priority changed from medium to high

Should use http://epplus.codeplex.com/ as external library.

comment:3 Changed 4 years ago by sforsten

  • Component changed from Problems.DataAnalysis to Problems.DataAnalysis.Symbolic
  • Owner changed from gkronber to sforsten
  • Status changed from new to accepted
  • Version changed from 3.3.5 to branch

comment:4 Changed 4 years ago by sforsten

r9509: create branch ExportSymbolicDataAnalysisSolutions
r9510: branch project ExtLibs
r9511: branch project Problems.DataAnalysis.Symbolic
r9512: branch project PluginInfrastructure
r9513: branch project Problems.DataAnalysis.Symbolic.Views
r9514: add solution files and build commands to branch, finished branch creation

comment:5 Changed 4 years ago by gkronber

  • Milestone changed from HeuristicLab 3.3.x Backlog to HeuristicLab 3.3.9

comment:6 Changed 4 years ago by sforsten

  • Owner changed from sforsten to mkommend
  • Status changed from accepted to reviewing

r9574: branch project Encodings.SymbolicExpressionTreeEncoding.Views
r9580:

  • added SymbolicDataAnalysisExpressionExcelFormatter
  • changed modifiers in SymbolicExpressionTreeChart of methods SaveImageAsBitmap and SaveImageAsEmf to public
  • added menu item ExportSymbolicSolutionToExcelMenuItem to export a symbolic solution to an excel file
  • added EPPlus-3.1.3 to ExtLibs

r9581:

  • accidently added bin directory
  • changed svn properties

ExportSymbolicSolutionToExcelMenuItem should be changed to also export specific properties of different symbolic solutions (Classification etc.). Currently, it is designed to support symbolic regression solutions.

comment:7 Changed 4 years ago by sforsten

r9583: added error handling, if estimated value evaluates to an error like #NAN or #NUM in Excel

comment:8 Changed 4 years ago by mkommend

r9584: Minor code changes and organization in the export to excel functionality.

comment:9 Changed 4 years ago by mkommend

r9585: Added necessary plugin dependency and improved error handling.

comment:10 Changed 4 years ago by mkommend

  • Owner changed from mkommend to gkronber
  • Version changed from branch to 3.3.9

r9587: Integrated excel export for symbolic datanalysis solutions in the trunk.

  • Added epplus to extlibs.
  • Moved estimation limits to SymbolicDataAnalysisModel.
  • Added menu item & exporter for the excel export.

comment:11 Changed 4 years ago by mkommend

r9590: Corrected output path of epplus.

comment:12 Changed 4 years ago by sforsten

  • Owner changed from gkronber to sforsten
  • Status changed from reviewing to assigned

r9600: added "ABS" again in the calculation of the relative error which was removed in r9585. If the target is negative, the absolute value prevents that the formula would result in a negative relative error.

comment:13 Changed 4 years ago by sforsten

  • Owner changed from sforsten to mkommend
  • Status changed from assigned to reviewing

comment:14 Changed 4 years ago by mkommend

  • Owner changed from mkommend to gkronber

comment:15 Changed 4 years ago by sforsten

  • Owner changed from gkronber to sforsten
  • Status changed from reviewing to assigned

r9607: added cell formatting

comment:16 Changed 4 years ago by sforsten

  • Owner changed from sforsten to mkommend
  • Status changed from assigned to reviewing

comment:17 Changed 4 years ago by mkommend

  • Status changed from reviewing to assigned

Following improvements should be made:

  • The menu item should be enabled if any view displays a symbolic dataanalysis solution
  • Excel export for classification solutions should be deactivated, because it currently does not really work.
  • The export should be done asynchronously to avoid blocking the GUI.
  • Inputs should be listed in the same order as they occur in the dataset.
  • Line colors of charts should match.

Last edited 4 years ago by mkommend (previous) (diff)

comment:18 Changed 4 years ago by mkommend

  • Status changed from assigned to accepted

comment:19 Changed 4 years ago by mkommend

  • Owner changed from mkommend to gkronber
  • Status changed from accepted to reviewing

r9626: 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.

Line colors are not support in the EPP library.

comment:20 Changed 4 years ago by gkronber

Review comments:

  • there should be a visual cue when exporting files in the background. It can take some time until the file is actually written to disc and I thought exporting does not work.
  • Exporting takes a lot of time. Try exporting a model using the largest feature-selection benchmark problem with 200 variables (after several minutes waiting I canceled).
  • I think the cells should use scientific formatting with three digits after the comma and an exponent. I'm not sure what excel shows for very small or very large numbers when the format is '0.000'. I'd like to see e.g. '1.234E-05' and '1.234E+05' in the same column.

I tested the export functionality on my machine and it works. I have not yet reviewed the all changes to the source code. Overall I believe the performance issue mentioned above should be analyzed and ideally fixed before we integrate this into the stable branch.

comment:21 Changed 4 years ago by gkronber

  • Owner changed from gkronber to mkommend
  • Status changed from reviewing to assigned

comment:22 Changed 4 years ago by ascheibe

  • Owner changed from mkommend to ascheibe
  • Status changed from assigned to accepted

comment:23 Changed 4 years ago by ascheibe

r9658 Updated EPPlus project file to work with Mono

comment:24 Changed 4 years ago by ascheibe

  • Owner changed from ascheibe to mkommend
  • Status changed from accepted to assigned

comment:25 Changed 4 years ago by ascheibe

  • Owner changed from mkommend to ascheibe
  • Status changed from assigned to accepted

comment:26 Changed 4 years ago by ascheibe

r9659 Updated HeuristicLab.EPPlus project file to work with Mono

comment:27 Changed 4 years ago by ascheibe

  • Owner changed from ascheibe to mkommend
  • Status changed from accepted to assigned

comment:28 Changed 4 years ago by ascheibe

Just for your information: I tried exporting on Linux/Mono. The first thing which is a problem is the exporting of the symbolic expression tree as emf. I commented it out and a file gets actually written. It can be opened with Gnumeric, but there are some things missing, e.g. the charts. Interestingly opening the file on Windows with Excel does not work as it says that the file is damaged.

comment:29 follow-up: Changed 4 years ago by mkommend

r9699: Improved the performance of the excel export.

comment:30 in reply to: ↑ 29 Changed 4 years ago by mkommend

Replying to mkommend:

r9699: Improved the performance of the excel export.

Right now exporting a linear regression solution on the largest feature selection problem which uses all 200 input variables takes ~ 1 min on my notebook and creates a 40MB xslx file.

comment:31 follow-up: Changed 4 years ago by gkronber

Excel formatter should also support Boolean functions (AND,OR,IF,...)

comment:32 Changed 4 years ago by mkommend

r9906: Added progress bar to excel export.

comment:33 in reply to: ↑ 31 Changed 4 years ago by mkommend

Replying to gkronber:

Excel formatter should also support Boolean functions (AND,OR,IF,...)

This functionality is traced in ticket #2098.

comment:34 Changed 4 years ago by mkommend

  • Owner changed from mkommend to gkronber
  • Status changed from assigned to reviewing

comment:35 Changed 4 years ago by gkronber

  • Owner changed from gkronber to mkommend
  • Status changed from reviewing to readytorelease

These changes can be merged to stable.

comment:36 Changed 4 years ago by ascheibe

  • Owner changed from mkommend to ascheibe
  • Status changed from readytorelease to reviewing

comment:37 Changed 4 years ago by ascheibe

  • Status changed from reviewing to readytorelease

comment:38 Changed 4 years ago by ascheibe

  • Resolution set to done
  • Status changed from readytorelease to closed

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

Note: See TracTickets for help on using tickets.