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

[a.westermann at alkacon.com]

Hi Mathias,

thanks for the contribution. I took this into my environment and will test it.

Kind Regards,
Achim.

-------------------
Achim Westermann

Alkacon Software GmbH  - The OpenCms Experts
http://www.alkacon.com - http://www.opencms.org


Zitat von Mathias Lin | SYSVISION <mail at mathiaslin.com>:

>
> 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.
>
>
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev
>



Achim Westermann
-------------------
Alkacon Software GmbH - The OpenCms Experts
Achim Westermann
An der Wachsfabrik 13
50996 Koeln, DE

Tel: +49 (0)2236 3826-12
Fax: +49 (0)2236 3826-20
Email: a.westermann at alkacon.com

http://www.alkacon.com
http://www.opencms.org