[gnumeric] Conditional formats: improve xls export.

[Morten Welinder <mortenw src gnome org>]

commit 8b65ba7e994d140b87ab9500566e20dbd9bf1c3b
Author: Morten Welinder <terra gnome org>
Date:   Wed Mar 12 18:48:00 2014 -0400

    Conditional formats: improve xls export.
    
    This introduces gnm_style_cond_get_alternate_expr, useful for formats
    not supporting the full range of gnumeric's condition types.
    
    Frankly, I suspect that even .gnumeric should be saved this way, but that
    damage is probably done long ago.

 ChangeLog                      |    3 ++
 NEWS                           |    1 +
 plugins/excel/ChangeLog        |    6 +++
 plugins/excel/ms-excel-write.c |   72 +++++++++++++++++++++++++++++++++-------
 src/style-conditions.c         |   52 +++++++++++++++++++++++++++-
 src/style-conditions.h         |    1 +
 6 files changed, 121 insertions(+), 14 deletions(-)
---
diff --git a/ChangeLog b/ChangeLog
index 9ed8514..4533822 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,5 +1,8 @@
 2014-03-12  Morten Welinder  <terra gnome org>
 
+       * src/style-conditions.c (gnm_style_cond_get_alternate_expr): New
+       function.
+
        * src/ssconvert.c (convert): only print "Using exporter ..." if
        --verbose is given, and print it to stderr.
 
diff --git a/NEWS b/NEWS
index 6525cba..f6f4c7d 100644
--- a/NEWS
+++ b/NEWS
@@ -16,6 +16,7 @@ Morten:
        * Import xlsx auto-filters.  [#725460]
        * Update Gnumeric schema.
        * Fix rich text problem.  [#726086]
+       * Improve xls export of conditional formats.
 
 --------------------------------------------------------------------------
 Gnumeric 1.12.12
diff --git a/plugins/excel/ChangeLog b/plugins/excel/ChangeLog
index 2bfab90..c0fba41 100644
--- a/plugins/excel/ChangeLog
+++ b/plugins/excel/ChangeLog
@@ -1,3 +1,9 @@
+2014-03-12  Morten Welinder  <terra gnome org>
+
+       * ms-excel-write.c (cb_write_condition): Use
+       gnm_style_cond_get_alternate_expr for conditions not available in
+       xls.
+
 2014-03-10  Morten Welinder  <terra gnome org>
 
        * xlsx-read.c (xlsx_CT_vertAlign): Read super/subscript.
diff --git a/plugins/excel/ms-excel-write.c b/plugins/excel/ms-excel-write.c
index 1b8ac12..c149c73 100644
--- a/plugins/excel/ms-excel-write.c
+++ b/plugins/excel/ms-excel-write.c
@@ -1014,6 +1014,7 @@ cb_write_condition (GnmStyleConditions const *sc, CondDetails *cd,
        for (i = 0 ; i < det_len ; i++) {
                GnmStyleCond const *cond = g_ptr_array_index (details, i);
                GnmStyle const *s = cond->overlay;
+               GnmExprTop const *alt_texpr;
 
                ms_biff_put_var_next (bp, BIFF_CF);
                header_pos = bp->curpos;
@@ -1143,18 +1144,37 @@ cb_write_condition (GnmStyleConditions const *sc, CondDetails *cd,
                } else
                        flags |= 0x70000;
 
-               expr0_len = (gnm_style_cond_get_expr (cond, 0) == NULL)
-                       ? 0
-                       : excel_write_formula (esheet->ewb,
-                                              gnm_style_cond_get_expr (cond, 0),
+               switch (cond->op) {
+               case GNM_STYLE_COND_CONTAINS_STR:
+               case GNM_STYLE_COND_NOT_CONTAINS_STR:
+               case GNM_STYLE_COND_BEGINS_WITH_STR:
+               case GNM_STYLE_COND_NOT_BEGINS_WITH_STR:
+               case GNM_STYLE_COND_ENDS_WITH_STR:
+               case GNM_STYLE_COND_NOT_ENDS_WITH_STR:
+               case GNM_STYLE_COND_CONTAINS_ERR:
+               case GNM_STYLE_COND_NOT_CONTAINS_ERR:
+               case GNM_STYLE_COND_CONTAINS_BLANKS:
+               case GNM_STYLE_COND_NOT_CONTAINS_BLANKS:
+                       alt_texpr = gnm_style_cond_get_alternate_expr (cond);
+                       break;
+               default:
+                       alt_texpr = NULL;
+               }
+
+               expr0_len = (alt_texpr || gnm_style_cond_get_expr (cond, 0))
+                       ? excel_write_formula (esheet->ewb,
+                                              alt_texpr
+                                              ? alt_texpr
+                                              : gnm_style_cond_get_expr (cond, 0),
                                               esheet->gnum_sheet, 0, 0,
-                                              EXCEL_CALLED_FROM_CONDITION);
-               expr1_len = (gnm_style_cond_get_expr (cond, 1) == NULL)
-                       ? 0
-                       : excel_write_formula (esheet->ewb,
+                                              EXCEL_CALLED_FROM_CONDITION)
+                       : 0;
+               expr1_len = gnm_style_cond_get_expr (cond, 1)
+                       ? excel_write_formula (esheet->ewb,
                                               gnm_style_cond_get_expr (cond, 1),
                                               esheet->gnum_sheet, 0, 0,
-                                              EXCEL_CALLED_FROM_CONDITION);
+                                              EXCEL_CALLED_FROM_CONDITION)
+                       : 0;
 
                type = 1;
                switch (cond->op) {
@@ -1167,9 +1187,13 @@ cb_write_condition (GnmStyleConditions const *sc, CondDetails *cd,
                case GNM_STYLE_COND_GTE:        op = 0x07; break;
                case GNM_STYLE_COND_LTE:        op = 0x08; break;
 
-               default :
-                       g_warning ("unknown condition %d", cond->op);
-               case GNM_STYLE_COND_CUSTOM:     op = 0; type = 2; break;
+               default:
+                       if (alt_texpr)
+                               gnm_expr_top_unref (alt_texpr);
+                       else
+                               g_warning ("unknown condition %d", cond->op);
+               case GNM_STYLE_COND_CUSTOM:
+                       op = 0; type = 2; break;
                }
 
                ms_biff_put_var_seekto (bp, header_pos);
@@ -1553,6 +1577,30 @@ excel_write_prep_conditions (ExcelWriteSheet *esheet)
                        gnm_style_get_conditions (sr->style));
                for (i = 0 ; i < (conds ? conds->len : 0) ; i++) {
                        GnmStyleCond const *cond = g_ptr_array_index (conds, i);
+
+                       switch (cond->op) {
+                       case GNM_STYLE_COND_CONTAINS_STR:
+                       case GNM_STYLE_COND_NOT_CONTAINS_STR:
+                       case GNM_STYLE_COND_BEGINS_WITH_STR:
+                       case GNM_STYLE_COND_NOT_BEGINS_WITH_STR:
+                       case GNM_STYLE_COND_ENDS_WITH_STR:
+                       case GNM_STYLE_COND_NOT_ENDS_WITH_STR:
+                       case GNM_STYLE_COND_CONTAINS_ERR:
+                       case GNM_STYLE_COND_NOT_CONTAINS_ERR:
+                       case GNM_STYLE_COND_CONTAINS_BLANKS:
+                       case GNM_STYLE_COND_NOT_CONTAINS_BLANKS: {
+                               GnmExprTop const *alt_texpr =
+                                       gnm_style_cond_get_alternate_expr (cond);
+                               if (alt_texpr) {
+                                       excel_write_prep_expr (esheet->ewb, alt_texpr);
+                                       gnm_expr_top_unref (alt_texpr);
+                               }
+                               break;
+                       }
+                       default:
+                               ; /* Nothing */
+                       }
+
                        if (gnm_style_cond_get_expr (cond, 0) != NULL)
                                excel_write_prep_expr (esheet->ewb, gnm_style_cond_get_expr (cond, 0));
                        if (gnm_style_cond_get_expr (cond, 1) != NULL)
diff --git a/src/style-conditions.c b/src/style-conditions.c
index f3280c3..c0aaeeb 100644
--- a/src/style-conditions.c
+++ b/src/style-conditions.c
@@ -32,8 +32,8 @@
 #include <parse-util.h>
 #include <gsf/gsf-impl-utils.h>
 #include <string.h>
-#include <parse-util.h>
-#include "gutils.h"
+#include <func.h>
+#include <gutils.h>
 
 #define BLANKS_STRING_FOR_MATCHING " \t\n\r"
 
@@ -241,6 +241,54 @@ gnm_style_cond_set_overlay (GnmStyleCond *cond, GnmStyle *overlay)
        cond->overlay = overlay;
 }
 
+/**
+ * gnm_style_cond_get_alternate_expr:
+ * @cond: condition
+ *
+ * Returns: (transfer full) (allow-none): An custom expression that can be
+ * used in place of @cond.
+ **/
+GnmExprTop const *
+gnm_style_cond_get_alternate_expr (GnmStyleCond const *cond)
+{
+       GnmCellRef self;
+       GnmExpr const *expr;
+       gboolean negate = FALSE;
+
+       g_return_val_if_fail (cond != NULL, NULL);
+
+       gnm_cellref_init (&self, NULL, 0, 0, TRUE);
+
+       switch (cond->op) {
+       case GNM_STYLE_COND_NOT_CONTAINS_ERR:
+               negate = TRUE; /* ...and fall through */
+       case GNM_STYLE_COND_CONTAINS_ERR:
+               expr = gnm_expr_new_funcall1
+                       (gnm_func_lookup_or_add_placeholder ("ISERROR"),
+                        gnm_expr_new_cellref (&self));
+               break;
+
+       case GNM_STYLE_COND_CONTAINS_BLANKS:
+               negate = TRUE; /* ...and fall through */
+       case GNM_STYLE_COND_NOT_CONTAINS_BLANKS:
+               expr = gnm_expr_new_funcall1
+                       (gnm_func_lookup_or_add_placeholder ("ISERROR"),
+                        gnm_expr_new_funcall2
+                        (gnm_func_lookup_or_add_placeholder ("FIND"),
+                         gnm_expr_new_constant (value_new_string (" ")),
+                         gnm_expr_new_cellref (&self)));
+               break;
+
+       default:
+               return NULL;
+       }
+
+       if (negate)
+               expr = gnm_expr_new_funcall1
+                       (gnm_func_lookup_or_add_placeholder ("NOT"), expr);
+
+       return gnm_expr_top_new (expr);
+}
 
 static void
 gnm_style_conditions_finalize (GObject *obj)
diff --git a/src/style-conditions.h b/src/style-conditions.h
index 43e0565..e4790ae 100644
--- a/src/style-conditions.h
+++ b/src/style-conditions.h
@@ -57,6 +57,7 @@ GnmExprTop const *gnm_style_cond_get_expr (GnmStyleCond const *cond,
 void          gnm_style_cond_set_expr (GnmStyleCond *cond,
                                       GnmExprTop const *texpr,
                                       unsigned idx);
+GnmExprTop const *gnm_style_cond_get_alternate_expr (GnmStyleCond const *cond);
 
 Sheet      *gnm_style_cond_get_sheet (GnmStyleCond const *cond);
 void        gnm_style_cond_set_sheet (GnmStyleCond *cond, Sheet *sheet);