Exporting symbolic data analysis solutions to excel files

[gkronber]

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.

[gkronber]

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

[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

[sforsten]

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.

[sforsten]

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

[mkommend]

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

[mkommend]

r9585: Added necessary plugin dependency and improved error handling.

[mkommend]

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.

[mkommend]

r9590: Corrected output path of epplus.

[sforsten]

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.

[sforsten]

r9607: added cell formatting

[mkommend]

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.

[mkommend]

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.

[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.

[ascheibe]

r9658 Updated EPPlus project file to work with Mono

[ascheibe]

r9659 Updated HeuristicLab.EPPlus project file to work with Mono

[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.

[mkommend]

r9699: Improved the performance of the excel export.

[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.

[gkronber]

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

[mkommend]

r9906: Added progress bar to excel export.

[mkommend]

Replying to gkronber:

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

This functionality is traced in ticket #2098.

[gkronber]

These changes can be merged to stable.

[ascheibe]

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