Java tutorial
/* * File : $Source: /alkacon/cvs/alkacon/com.alkacon.opencms.formgenerator/src/com/alkacon/opencms/formgenerator/database/export/CmsCvsExportBean.java,v $ * Date : $Date: 2011/03/24 16:33:49 $ * Version: $Revision: 1.11 $ * * This file is part of the Alkacon OpenCms Add-On Module Package * * Copyright (c) 2010 Alkacon Software GmbH (http://www.alkacon.com) * * The Alkacon OpenCms Add-On Module Package is free software: * you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * The Alkacon OpenCms Add-On Module Package is distributed * in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with the Alkacon OpenCms Add-On Module Package. * If not, see http://www.gnu.org/licenses/. * * For further information about Alkacon Software GmbH, please see the * company website: http://www.alkacon.com. * * For further information about OpenCms, please see the * project website: http://www.opencms.org. */ package com.alkacon.opencms.formgenerator.database.export; import com.alkacon.opencms.formgenerator.CmsForm; import com.alkacon.opencms.formgenerator.CmsFormHandler; import com.alkacon.opencms.formgenerator.database.CmsFormDataAccess; import com.alkacon.opencms.formgenerator.database.CmsFormDataBean; import org.opencms.file.CmsObject; import org.opencms.main.CmsLog; import org.opencms.main.OpenCms; import org.opencms.module.CmsModule; import org.opencms.util.CmsStringUtil; import org.opencms.util.CmsUUID; import java.sql.SQLException; import java.text.Collator; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Collections; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Locale; import org.apache.commons.logging.Log; /** * Bean that supports the data export.<p> * * Works together with * /system/modules/com.alkacon.opencms.formgenerator/elements/datadownload.jsp.<p> * * @author Achim Westermann * * @version $Revision: 1.11 $ * * @since 7.0.4 * */ public class CmsCvsExportBean { /** The default value delimiter for CSV files in Excel. */ public static final String EXCEL_DEFAULT_CSV_DELMITER = ","; /** Request parameter for the start time of the data to export. */ public static final String PARAM_EXPORT_DATA_TIME_END = "endtime"; /** Request parameter for the start time of the data to export. */ public static final String PARAM_EXPORT_DATA_TIME_START = "starttime"; /** The log object for this class. */ private static final Log LOG = CmsLog.getLog(CmsCvsExportBean.class); /** The end time for data sets to export. */ private Date m_endTime; /** The form that was used to input the data to export. */ private CmsFormHandler m_formHandler; /** Needed to read the resource for the UUID. */ private CmsObject m_cms; /** The start time for data sets to export. */ private Date m_startTime; /** * Creates an instance based upon data that was entered with the given form.<p> * * @param formHandler the origin of the data */ public CmsCvsExportBean(final CmsFormHandler formHandler) { this(formHandler.getCmsObject()); m_formHandler = formHandler; } /** * Creates an instance that is not backed by a form but still offers * export functionality via <code>{@link #exportData(String, Locale)}</code>.<p> * * @param cms Needed to read the resource for the UUID */ public CmsCvsExportBean(final CmsObject cms) { m_cms = cms; m_startTime = new Date(0); m_endTime = new Date(Long.MAX_VALUE); } /** * Returns the csv export file content.<p> * * @return the csv export file content * * @throws SQLException if sth goes wrong */ public String exportData() throws SQLException { return exportData(getForm().getFormConfiguration().getFormId(), getForm().getRequestContext().getLocale()); } /** * Returns the CSV export file content.<p> * * @param formId the current selected web form * @param locale the current local * * @return the CSV export file content * * @throws SQLException if something goes wrong */ public String exportData(String formId, Locale locale) throws SQLException { /* * TODO: Access the CmsForm (or CmsFormHandler) and put out all * fields in the exact order - put fields that do not exist any longer * to the back (note: readAllFormFieldNames is required for the old values). */ StringBuffer result = new StringBuffer(); CmsModule module = OpenCms.getModuleManager().getModule(CmsForm.MODULE_NAME); // time format: DateFormat df = null; String formatString = module.getParameter(CmsForm.MODULE_PARAM_EXPORT_TIMEFORMAT); if (CmsStringUtil.isNotEmpty(formatString)) { try { df = new SimpleDateFormat(formatString); } catch (IllegalArgumentException iae) { LOG.warn(Messages.get().getBundle().key(Messages.LOG_WARN_EXPORT_DATEFORMAT_ILLEGAL_2, new Object[] { CmsForm.MODULE_PARAM_EXPORT_TIMEFORMAT, formatString })); } } // line separator: boolean isWindowsLineSeparator = false; boolean isUnixLineSeparator = false; String lineSeparatorParam = module.getParameter(CmsForm.MODULE_PARAM_EXPORTLINESEPARATOR); if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(lineSeparatorParam)) { if (lineSeparatorParam.equals(CmsForm.MODULE_PARAMVALUE_EXPORTLINESEPARATOR_WINDOWS)) { isWindowsLineSeparator = true; } else if (lineSeparatorParam.equals(CmsForm.MODULE_PARAMVALUE_EXPORTLINESEPARATOR_UNIX) || lineSeparatorParam.equals(CmsForm.MODULE_PARAMVALUE_EXPORTLINESEPARATOR_EXCEL)) { isUnixLineSeparator = true; } } // export numbers as string: String nasParam = module.getParameter(CmsForm.MODULE_PARAM_EXPORT_NUMBERASSTRING, CmsStringUtil.FALSE); boolean numberAsString = Boolean.valueOf(nasParam).booleanValue(); // csv delimiter String lineSeparator = EXCEL_DEFAULT_CSV_DELMITER; String configLineSeparator = module.getParameter(CmsForm.MODULE_PARAM_CSV_DELIMITER); if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(configLineSeparator)) { lineSeparator = configLineSeparator.trim(); } // get the column names List<String> columnNames = CmsFormDataAccess.getInstance().readFormFieldNames(formId, getStartTime().getTime(), getEndTime().getTime()); Collections.sort(columnNames, Collator.getInstance(locale)); // get the entries List<CmsFormDataBean> dataEntries = CmsFormDataAccess.getInstance().readForms(formId, getStartTime().getTime(), getEndTime().getTime()); // loop 1 - write the headers: result.append(escapeExcelCsv("Creation date", numberAsString)); result.append(lineSeparator); result.append(escapeExcelCsv("Resource path", numberAsString)); result.append(lineSeparator); result.append(escapeExcelCsv("Resource UUID", numberAsString)); result.append(lineSeparator); Iterator<String> itColumns = columnNames.iterator(); while (itColumns.hasNext()) { String columnName = itColumns.next(); // skip empty columns (previous versions saved CmsEmptyField with empty values which will not be deleted): if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(columnName)) { columnName = escapeExcelCsv(columnName, numberAsString); result.append(columnName); if (itColumns.hasNext()) { result.append(lineSeparator); } } } result.append("\r\n"); // loop 2 - write the data: Iterator<CmsFormDataBean> itRows = dataEntries.iterator(); String path; CmsUUID uuid = null; while (itRows.hasNext()) { CmsFormDataBean row = itRows.next(); // create an entry for each column, even if some rows (data sets) // do not contain the field value because it was // a) not entered // b) the form was changed in structure over time // c) developer errors, hw /sw problems... Date creationDate = new Date(row.getDateCreated()); if (df == null) { result.append(creationDate); } else { result.append(df.format(creationDate)); } DateFormat.getDateTimeInstance(); result.append(lineSeparator); uuid = row.getResourceId(); try { path = m_cms.readResource(uuid).getRootPath(); } catch (Exception e) { path = row.getResourceId().toString(); } result.append(path); result.append(lineSeparator); result.append(String.valueOf(uuid)); result.append(lineSeparator); itColumns = columnNames.iterator(); while (itColumns.hasNext()) { String columnName = itColumns.next(); // skip empty columns (previous versions saved CmsEmptyField with empty values which will not be deleted): if (CmsStringUtil.isNotEmptyOrWhitespaceOnly(columnName)) { String value = row.getFieldValue(columnName); if (value != null) { if (isWindowsLineSeparator) { value = transformWindowsLineseparator(value); } else if (isUnixLineSeparator) { value = transformUnixLineseparator(value); } value = escapeExcelCsv(value, numberAsString); result.append(value); } if (itColumns.hasNext()) { result.append(lineSeparator); } } } result.append("\r\n"); } return result.toString(); } /** * Returns the endTime.<p> * * @return the endTime */ public Date getEndTime() { return m_endTime; } /** * Returns the form handler.<p> * * @return the form handler */ public CmsFormHandler getForm() { return m_formHandler; } /** * Returns the startTime.<p> * * @return the startTime */ public Date getStartTime() { return m_startTime; } /** * Sets the endTime.<p> * * @param endTime the endTime to set */ public void setEndTime(Date endTime) { m_endTime = endTime; } /** * Sets the startTime.<p> * * @param startTime the startTime to set */ public void setStartTime(Date startTime) { m_startTime = startTime; } /** * Escapes CSV values for Excel.<p> * * @param value the value to escape * @param numberAsString flag to determine if numbers should be marked as Strings by prepending a <code>=</code> * * @return the escaped Excel value */ private String escapeExcelCsv(final String value, boolean numberAsString) { String result = value; StringBuffer buffer = new StringBuffer(value.length() + 8); // support for Microsoft Excel: If Excel detects numbers, it reformats the numbers // (stealing leading zeros or displaying large numbers in +E syntax: if (numberAsString) { boolean isNumber = false; try { Double.valueOf(result); isNumber = true; } catch (Exception e) { // this is no double value } if (!isNumber) { try { Long.valueOf(result); isNumber = true; } catch (Exception e) { // this is no long value } } if (isNumber) { buffer.append("="); } } 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 result; } /** * Replaces all "\r\n" to "\n".<p> * * @param value the value to transform * * @return the input with Unix line separators */ private String transformUnixLineseparator(String value) { return value.replaceAll("\r\n", "\n"); } /** * Replaces all "\n" to "\r\n".<p> * * @param value the value to transform * * @return the input with windows line separators */ private String transformWindowsLineseparator(String value) { return value.replaceAll("\n", "\r\n"); } }