OpenCms: Fix for Alkacon OAMP Formgenerator, CSV Export
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
}
