[opencms-dev] Fix for Alkacon OAMP Formgenerator, CsvExport

[Mathias Lin | SYSVISION]

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 escapeExcelCsv method, 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
    }

-----
Mathias Lin
SYSVISION Ltd., China
http://www.sysvision.com
-- 
View this message in context: http://www.nabble.com/Fix-for-Alkacon-OAMP-Formgenerator%2C-CsvExport-tp24140914p24140914.html
Sent from the OpenCMS - Dev mailing list archive at Nabble.com.