Bug 469485 - Add support for exporting formulas

Change-Id: I1a7a5ecaad53469fd38eeff516dfddc569839f7b
Signed-off-by: Dirk Fauth <dirk.fauth@googlemail.com>

2 files changed, 86 insertions, 43 deletions

diff --git a/org.eclipse.nebula.widgets.nattable.core/src/org/eclipse/nebula/widgets/nattable/export/NatExporter.java b/org.eclipse.nebula.widgets.nattable.core/src/org/eclipse/nebula/widgets/nattable/export/NatExporter.java
index 71a841ca..ebab8460 100644
--- a/org.eclipse.nebula.widgets.nattable.core/src/org/eclipse/nebula/widgets/nattable/export/NatExporter.java
+++ b/org.eclipse.nebula.widgets.nattable.core/src/org/eclipse/nebula/widgets/nattable/export/NatExporter.java
@@ -18,6 +18,8 @@ import java.util.Map;
 import org.eclipse.nebula.widgets.nattable.Messages;
 import org.eclipse.nebula.widgets.nattable.NatTable;
 import org.eclipse.nebula.widgets.nattable.config.IConfigRegistry;
+import org.eclipse.nebula.widgets.nattable.formula.command.DisableFormulaEvaluationCommand;
+import org.eclipse.nebula.widgets.nattable.formula.command.EnableFormulaEvaluationCommand;
 import org.eclipse.nebula.widgets.nattable.layer.ILayer;
 import org.eclipse.nebula.widgets.nattable.layer.cell.ILayerCell;
 import org.eclipse.nebula.widgets.nattable.print.command.PrintEntireGridCommand;
@@ -50,10 +52,13 @@ public class NatExporter {
      *            The ConfigRegistry of the NatTable instance to export, that
      *            contains the necessary export configurations.
      */
-    public void exportSingleLayer(final ILayer layer,
+    public void exportSingleLayer(
+            final ILayer layer,
             final IConfigRegistry configRegistry) {
+
         final ILayerExporter exporter = configRegistry.getConfigAttribute(
-                ExportConfigAttributes.EXPORTER, DisplayMode.NORMAL);
+                ExportConfigAttributes.EXPORTER,
+                DisplayMode.NORMAL);
 
         final OutputStream outputStream = exporter.getOutputStream(this.shell);
         if (outputStream == null) {
@@ -102,8 +107,10 @@ public class NatExporter {
      *            used as sheet titles while the values are the instances to
      *            export.
      */
-    public void exportMultipleNatTables(final ILayerExporter exporter,
+    public void exportMultipleNatTables(
+            final ILayerExporter exporter,
             final Map<String, NatTable> natTablesMap) {
+
         final OutputStream outputStream = exporter.getOutputStream(this.shell);
         if (outputStream == null) {
             return;
@@ -159,11 +166,14 @@ public class NatExporter {
      * @param layer
      * @param configRegistry
      */
-    protected void exportLayer(final ILayerExporter exporter,
-            final OutputStream outputStream, final String layerName,
-            final ILayer layer, final IConfigRegistry configRegistry) {
-        IClientAreaProvider originalClientAreaProvider = layer
-                .getClientAreaProvider();
+    protected void exportLayer(
+            final ILayerExporter exporter,
+            final OutputStream outputStream,
+            final String layerName,
+            final ILayer layer,
+            final IConfigRegistry configRegistry) {
+
+        IClientAreaProvider originalClientAreaProvider = layer.getClientAreaProvider();
 
         // This needs to be done so that the layer can return all the cells
         // not just the ones visible in the viewport
@@ -174,11 +184,15 @@ public class NatExporter {
         // the values are calculated
         layer.doCommand(new CalculateSummaryRowValuesCommand());
 
+        // if a FormulaDataProvider is involved, we need to ensure that the
+        // formula evaluation is disabled so the formula itself is exported
+        // instead of the calculated value
+        layer.doCommand(new DisableFormulaEvaluationCommand());
+
         ProgressBar progressBar = null;
 
         if (this.shell != null) {
-            Shell childShell = new Shell(this.shell.getDisplay(), SWT.DIALOG_TRIM
-                    | SWT.APPLICATION_MODAL);
+            Shell childShell = new Shell(this.shell.getDisplay(), SWT.DIALOG_TRIM | SWT.APPLICATION_MODAL);
             childShell.setText(Messages.getString("NatExporter.exporting")); //$NON-NLS-1$
 
             int startRow = 0;
@@ -203,21 +217,16 @@ public class NatExporter {
                     progressBar.setSelection(rowPosition);
                 }
 
-                for (int columnPosition = 0; columnPosition < layer
-                        .getColumnCount(); columnPosition++) {
-                    ILayerCell cell = layer.getCellByPosition(columnPosition,
-                            rowPosition);
-
-                    IExportFormatter exportFormatter = configRegistry
-                            .getConfigAttribute(
-                                    ExportConfigAttributes.EXPORT_FORMATTER,
-                                    cell.getDisplayMode(), cell
-                                            .getConfigLabels().getLabels());
-                    Object exportDisplayValue = exportFormatter
-                            .formatForExport(cell, configRegistry);
-
-                    exporter.exportCell(outputStream, exportDisplayValue, cell,
-                            configRegistry);
+                for (int columnPosition = 0; columnPosition < layer.getColumnCount(); columnPosition++) {
+                    ILayerCell cell = layer.getCellByPosition(columnPosition, rowPosition);
+
+                    IExportFormatter exportFormatter = configRegistry.getConfigAttribute(
+                            ExportConfigAttributes.EXPORT_FORMATTER,
+                            cell.getDisplayMode(),
+                            cell.getConfigLabels().getLabels());
+                    Object exportDisplayValue = exportFormatter.formatForExport(cell, configRegistry);
+
+                    exporter.exportCell(outputStream, exportDisplayValue, cell, configRegistry);
                 }
 
                 exporter.exportRowEnd(outputStream, rowPosition);
@@ -226,22 +235,23 @@ public class NatExporter {
             exporter.exportLayerEnd(outputStream, layerName);
         } catch (Exception e) {
             e.printStackTrace(System.err);
-        }
+        } finally {
+            // These must be fired at the end of the thread execution
+            layer.setClientAreaProvider(originalClientAreaProvider);
+            layer.doCommand(new TurnViewportOnCommand());
 
-        // These must be fired at the end of the thread execution
-        layer.setClientAreaProvider(originalClientAreaProvider);
-        layer.doCommand(new TurnViewportOnCommand());
+            layer.doCommand(new EnableFormulaEvaluationCommand());
 
-        if (progressBar != null) {
-            Shell childShell = progressBar.getShell();
-            progressBar.dispose();
-            childShell.dispose();
+            if (progressBar != null) {
+                Shell childShell = progressBar.getShell();
+                progressBar.dispose();
+                childShell.dispose();
+            }
         }
     }
 
     private void setClientAreaToMaximum(ILayer layer) {
-        final Rectangle maxClientArea = new Rectangle(0, 0, layer.getWidth(),
-                layer.getHeight());
+        final Rectangle maxClientArea = new Rectangle(0, 0, layer.getWidth(), layer.getHeight());
 
         layer.setClientAreaProvider(new IClientAreaProvider() {
             @Override
diff --git a/org.eclipse.nebula.widgets.nattable.extension.poi/src/org/eclipse/nebula/widgets/nattable/extension/poi/PoiExcelExporter.java b/org.eclipse.nebula.widgets.nattable.extension.poi/src/org/eclipse/nebula/widgets/nattable/extension/poi/PoiExcelExporter.java
index 3f3aa693..e45f21bf 100644
--- a/org.eclipse.nebula.widgets.nattable.extension.poi/src/org/eclipse/nebula/widgets/nattable/extension/poi/PoiExcelExporter.java
+++ b/org.eclipse.nebula.widgets.nattable.extension.poi/src/org/eclipse/nebula/widgets/nattable/extension/poi/PoiExcelExporter.java
@@ -13,6 +13,7 @@ package org.eclipse.nebula.widgets.nattable.extension.poi;
 
 import java.io.IOException;
 import java.io.OutputStream;
+import java.math.BigDecimal;
 import java.util.Calendar;
 import java.util.Date;
 import java.util.HashMap;
@@ -31,6 +32,7 @@ import org.eclipse.nebula.widgets.nattable.config.IConfigRegistry;
 import org.eclipse.nebula.widgets.nattable.export.ExportConfigAttributes;
 import org.eclipse.nebula.widgets.nattable.export.ILayerExporter;
 import org.eclipse.nebula.widgets.nattable.export.IOutputStreamProvider;
+import org.eclipse.nebula.widgets.nattable.formula.FormulaParser;
 import org.eclipse.nebula.widgets.nattable.layer.cell.ILayerCell;
 import org.eclipse.nebula.widgets.nattable.painter.cell.CellPainterWrapper;
 import org.eclipse.nebula.widgets.nattable.painter.cell.ICellPainter;
@@ -62,6 +64,8 @@ public abstract class PoiExcelExporter implements ILayerExporter {
 
     private String sheetname;
 
+    private FormulaParser formulaParser;
+
     public PoiExcelExporter(IOutputStreamProvider outputStreamProvider) {
         this.outputStreamProvider = outputStreamProvider;
     }
@@ -134,8 +138,7 @@ public abstract class PoiExcelExporter implements ILayerExporter {
         if (columnSpan > 1 || rowSpan > 1) {
             int lastRow = rowPosition + rowSpan - 1;
             int lastColumn = columnPosition + columnSpan - 1;
-            this.xlSheet.addMergedRegion(new CellRangeAddress(rowPosition, lastRow,
-                    columnPosition, lastColumn));
+            this.xlSheet.addMergedRegion(new CellRangeAddress(rowPosition, lastRow, columnPosition, lastColumn));
         }
 
         CellStyleProxy cellStyle = new CellStyleProxy(
@@ -170,6 +173,19 @@ public abstract class PoiExcelExporter implements ILayerExporter {
             xlCell.setCellValue((Date) exportDisplayValue);
         } else if (exportDisplayValue instanceof Number) {
             xlCell.setCellValue(((Number) exportDisplayValue).doubleValue());
+        } else if (this.formulaParser != null) {
+            // formula export is enabled, so we perform checks on the cell
+            // values
+            String cellValue = exportDisplayValue.toString();
+            if (this.formulaParser.isFunction(cellValue)) {
+                xlCell.setCellFormula(this.formulaParser.getFunctionOnly(cellValue));
+            }
+            else if (this.formulaParser.isNumber(cellValue)) {
+                xlCell.setCellValue(new BigDecimal(cellValue).doubleValue());
+            }
+            else {
+                xlCell.setCellValue(exportDisplayValue.toString());
+            }
         } else {
             xlCell.setCellValue(exportDisplayValue.toString());
         }
@@ -190,11 +206,9 @@ public abstract class PoiExcelExporter implements ILayerExporter {
         return false;
     }
 
-    private CellStyle getExcelCellStyle(Color fg, Color bg, FontData fontData,
-            String dataFormat, int hAlign, int vAlign, boolean vertical) {
+    private CellStyle getExcelCellStyle(Color fg, Color bg, FontData fontData, String dataFormat, int hAlign, int vAlign, boolean vertical) {
 
-        CellStyle xlCellStyle = this.xlCellStyles.get(new ExcelCellStyleAttributes(
-                fg, bg, fontData, dataFormat, hAlign, vAlign, vertical));
+        CellStyle xlCellStyle = this.xlCellStyles.get(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical));
 
         if (xlCellStyle == null) {
             xlCellStyle = this.xlWorkbook.createCellStyle();
@@ -242,8 +256,8 @@ public abstract class PoiExcelExporter implements ILayerExporter {
                 xlCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dataFormat));
             }
 
-            this.xlCellStyles.put(new ExcelCellStyleAttributes(fg, bg, fontData,
-                    dataFormat, hAlign, vAlign, vertical), xlCellStyle);
+            this.xlCellStyles.put(
+                    new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical), xlCellStyle);
         }
         return xlCellStyle;
     }
@@ -327,4 +341,23 @@ public abstract class PoiExcelExporter implements ILayerExporter {
         this.sheetname = sheetname;
     }
 
+    /**
+     * Configure the {@link FormulaParser} that should be used to determine
+     * whether formulas should be exported or not. If <code>null</code> is set,
+     * formulas and cell values of type string will be simply exported as
+     * string. If a valid {@link FormulaParser} is set, cell values will get
+     * inspected so that number values are converted to numbers and formulas
+     * will be exported as formulas.
+     *
+     * @param formulaParser
+     *            The {@link FormulaParser} that should be used to determine
+     *            whether cell values should be interpreted as formulas or
+     *            <code>null</code> to disable formula export handling.
+     *
+     * @since 1.4
+     */
+    public void setFormulaParser(FormulaParser formulaParser) {
+        this.formulaParser = formulaParser;
+    }
+
 }