Mathias Lin | 林马

When exporting the Formgenerator data to CSV, there might be some problems with exporting numbers and trailing zeros when opening the file in Excel. I.e. if you have a column with zip codes and some values start with 0, they will get lost in Excel. Also, when exporting large numbers, Excel usually displays them withe the +E syntax.
 
To avoid it, the csv should escape such numbers with a = in front of it, i.e.the CSV should read:
,="07635",
 
We modified the class com.alkacon.opencms.formgenerator.database.export.CmsCvsExportBean.java, method escapeExcelCsv: checking if the value can be parsed as Double or Long, if so, put an = in front of the escaped value.
(The = cannot be put in front of all types of fields; if put in front of an escaped string value that contains commas, it will break the format; so we
really need to check if it's a number value).

    /** 
     * Escapes CSV values for excel.<p> 
     * 
     * @param value the value to escape 
     * 
     * @return the escaped excel value. 
     */
    private String escapeExcelCsv(final String value) {
        // Begin: SYSVISION modification
        boolean isNumber=false;
        try {Long.valueOf(value);isNumber=true;} catch (Exception e) {};
        try {Double.valueOf(value);isNumber=true;} catch (Exception e) {};
        // End: SYSVISION modification

        String result = value;
        StringBuffer buffer = new StringBuffer();
        buffer.append("\"");
        char[] chars = value.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            // escape double quote escape delimiter within value: 
            if ('"' == chars[i]) {
                buffer.append("\"");
            }
            buffer.append(chars[i]);
        }
        buffer.append("\"");
        result = buffer.toString();
        return (isNumber?"=":"") + result;  // SYSVISION modification
    }

Loading mentions Retweet