Java tutorial
///////////////////////////////////////////////////////////////////////////// // // Project ProjectForge Community Edition // www.projectforge.org // // Copyright (C) 2001-2014 Kai Reinhard (k.reinhard@micromata.de) // // ProjectForge is dual-licensed. // // This community edition 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; version 3 of the License. // // This community edition 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 this program; if not, see http://www.gnu.org/licenses/. // ///////////////////////////////////////////////////////////////////////////// package org.projectforge.excel; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; import org.apache.commons.beanutils.ConvertUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.projectforge.common.DateFormatType; public class XlsContentProvider implements ContentProvider { static final int LENGHT_UNIT = 256; public static final String FORMAT_CURRENCY = "#,##0.00;[Red]-#,##0.00"; public static final int LENGTH_BOOLEAN = 5; public static final int LENGTH_COMMENT = 30; public static final int LENGTH_CURRENCY = 11; public static final int LENGTH_DATE = 10; public static final int LENGTH_DATETIME = 15; public static final int LENGTH_DURATION = 10; public static final int LENGTH_EMAIL = 30; public static final int LENGTH_EXTRA_LONG = 80; public static final int LENGTH_ID = 8; public static final int LENGTH_PHONENUMBER = 20; public static final int LENGTH_STD = 30; public static final int LENGTH_TIMESTAMP = 16; public static final int LENGTH_ZIPCODE = 7; public static Font FONT_HEADER; public static short FONT_HEADER_SIZE = 10; public static Font FONT_NORMAL; public static Font FONT_NORMAL_BOLD; public static Font FONT_WHITE_BOLD; static protected Font FONT_RED; static protected Font FONT_RED_BOLD; protected Map<CellFormat, CellStyle> reusableCellFormats = new HashMap<CellFormat, CellStyle>(); protected ExportWorkbook workbook; private final Map<Object, CellFormat> formatMap = new HashMap<Object, CellFormat>(); protected final Map<Object, CellFormat> defaultFormatMap = new HashMap<Object, CellFormat>(); private final Map<Integer, Integer> colWidthMap = new HashMap<Integer, Integer>(); private boolean autoFormatCells = true; private final ExportContext exportContext; private List<ExportColumn> columns; public XlsContentProvider(final ExportWorkbook workbook) { this(ExportConfig.getInstance().getDefaultExportContext(), workbook); } public XlsContentProvider(final ExportContext exportContext, final ExportWorkbook workbook) { this.exportContext = exportContext; this.workbook = workbook; createFonts(); defaultFormatMap.put(Integer.class, new CellFormat("#,##0", CellStyle.ALIGN_RIGHT)); defaultFormatMap.put(Number.class, new CellFormat("#,###.######", CellStyle.ALIGN_RIGHT)); defaultFormatMap.put(Date.class, new CellFormat( ExcelDateFormats.getExcelFormatString(exportContext, DateFormatType.DATE_TIME_MINUTES))); defaultFormatMap.put(java.sql.Date.class, new CellFormat(ExcelDateFormats.getExcelFormatString(exportContext, DateFormatType.DATE))); defaultFormatMap.put(java.sql.Timestamp.class, new CellFormat( ExcelDateFormats.getExcelFormatString(exportContext, DateFormatType.DATE_TIME_MILLIS))); } @Override public XlsContentProvider updateSheetStyle(final ExportSheet sheet) { for (final Map.Entry<Integer, Integer> entry : colWidthMap.entrySet()) { sheet.setColumnWidth(entry.getKey(), entry.getValue()); } return this; } /** * If true then first row and even/odd rows will be formatted with bordered cells. * @param autoFormatCells */ public void setAutoFormatCells(final boolean autoFormatCells) { this.autoFormatCells = autoFormatCells; } /** * Highlights even and odd rows and sets first column bold if even and odd rows are configured. * @see org.projectforge.excel.ContentProvider#updateRowStyle(org.projectforge.excel.ExportRow) */ @Override public XlsContentProvider updateRowStyle(final ExportRow row) { if (autoFormatCells == true) { for (final ExportCell cell : row.getCells()) { final CellFormat format = cell.ensureAndGetCellFormat(); format.setFillForegroundColor(HSSFColor.WHITE.index); switch (row.getRowNum()) { /* * case 0: font = FONT_HEADER; break; */ case 0: format.setFont(FONT_NORMAL_BOLD); // alignment = CellStyle.ALIGN_CENTER; break; default: format.setFont(FONT_NORMAL); if (row.getRowNum() % 2 == 0) { format.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); } break; } } } return this; } @Override public XlsContentProvider updateCellStyle(final ExportCell cell) { final CellFormat format = cell.ensureAndGetCellFormat(); CellStyle cellStyle = reusableCellFormats.get(format); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); reusableCellFormats.put(format, cellStyle); format.copyToCellStyle(cellStyle); if (format.getFillForegroundColor() != null) { cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } cellStyle.setBorderBottom((short) 1); cellStyle.setBorderLeft((short) 1); cellStyle.setBorderRight((short) 1); cellStyle.setBorderTop((short) 1); cellStyle.setWrapText(true); final String dataFormat = format.getDataFormat(); if (dataFormat != null) { final short df = workbook.getDataFormat(format.getDataFormat()); cellStyle.setDataFormat(df); } } cell.setCellStyle(cellStyle); return this; } @Override public XlsContentProvider setValue(final ExportCell cell, final Object value) { setValue(cell, value, null); return this; } /** * Override this method if you need to convert complex data types, e. g. value is a DateHolder object you may return value.getDate(). * Please note, that only some object types are supported by {@link Cell} and by this implementation. * @param value * @return null at default. */ public Object getCustomizedValue(final Object value) { return null; } /** * */ @Override public XlsContentProvider setValue(final ExportCell cell, final Object value, final String property) { final Cell poiCell = cell.getPoiCell(); final Object customizedValue = getCustomizedValue(value); if (customizedValue != null) { if (customizedValue instanceof Calendar) { poiCell.setCellValue((Calendar) customizedValue); } else if (customizedValue instanceof Date) { poiCell.setCellValue((Date) customizedValue); } else if (customizedValue instanceof String) { poiCell.setCellValue((String) customizedValue); } else { poiCell.setCellValue(String.valueOf(customizedValue)); } } else if (value instanceof Date) { // Attention: Time zone is not given! poiCell.setCellValue((Date) value); } else if (value instanceof Calendar) { poiCell.setCellValue((Calendar) value); } else if (value instanceof Boolean) { poiCell.setCellValue(((Boolean) value).booleanValue()); } else if (value instanceof Number) { poiCell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Formula) { poiCell.setCellFormula(((Formula) value).getExpr()); } else { poiCell.setCellValue(ConvertUtils.convert(value)); } CellFormat cellFormat = getCellFormat(cell, value, property, formatMap); if (cellFormat == null) { cellFormat = getCellFormat(cell, value, property, defaultFormatMap); } if (cellFormat == null) { cellFormat = new CellFormat(); cellFormat.setAlignment(CellStyle.ALIGN_LEFT); cellFormat.setDataFormat("@"); cellFormat.setWrapText(true); } cell.setCellFormat(cellFormat); return this; } /** * @param cell * @param value * @param property * @param map * @return A clone of a pre-defined cell format if found, otherwise null. */ private CellFormat getCellFormat(final ExportCell cell, final Object value, final String property, final Map<Object, CellFormat> map) { CellFormat format = null; if (property != null) { format = map.get(property); } if (format == null && columns != null) { for (final ExportColumn column : columns) { if (column.getName().equals(property) == true) { format = map.get(column); break; } } } if (format == null) { Class<?> clazz = value == null ? null : value.getClass(); while (format == null && clazz != null) { format = map.get(clazz); clazz = clazz.getSuperclass(); } } final CellFormat customizedCellFormat = getCustomizedCellFormat(format, value); if (customizedCellFormat != null) { format = customizedCellFormat; } if (format == null) { return null; } return format.clone(); } /** * Override this method for creating own cell formats. * @param format May-be null if no mapping was found for the given value. * @param value * @return null at default. */ protected CellFormat getCustomizedCellFormat(final CellFormat format, final Object value) { return null; } @Override public XlsContentProvider putFormat(final Object obj, final CellFormat cellFormat) { formatMap.put(obj, cellFormat); return this; } @Override public XlsContentProvider putFormat(final Enum<?> col, final CellFormat cellFormat) { putFormat(col.name(), cellFormat); return this; } @Override public XlsContentProvider putFormat(final Object obj, final String dataFormat) { formatMap.put(obj, new CellFormat(dataFormat)); return this; } @Override public XlsContentProvider putFormat(final Enum<?> col, final String dataFormat) { putFormat(col.name(), dataFormat); return this; } @Override public XlsContentProvider putFormat(final ExportColumn col, final String dataFormat) { registerColumn(col); putFormat((Object) col, dataFormat); return this; } @Override public XlsContentProvider putFormat(final String dataFormat, final Enum<?>... cols) { for (final Enum<?> col : cols) { putFormat(col, dataFormat); } return this; } @Override public XlsContentProvider putColWidth(final int colIdx, final int charLength) { this.colWidthMap.put(colIdx, charLength * LENGHT_UNIT); return this; } @Override public XlsContentProvider setColWidths(final int... charLengths) { for (int colIdx = 0; colIdx < charLengths.length; colIdx++) { putColWidth(colIdx, charLengths[colIdx]); } return this; } /** * @param columns the columns to set * @return this for chaining. */ private XlsContentProvider registerColumn(final ExportColumn column) { if (this.columns == null) { this.columns = new LinkedList<ExportColumn>(); } this.columns.add(column); return this; } public ExportContext getExportContext() { return exportContext; } public ContentProvider newInstance() { return new XlsContentProvider(this.exportContext, this.workbook); } @Override public ExportWorkbook getWorkbook() { return workbook; } private void createFonts() { FONT_HEADER = workbook.createFont(); FONT_HEADER.setFontHeightInPoints(FONT_HEADER_SIZE); FONT_HEADER.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_NORMAL_BOLD = workbook.createFont(); FONT_NORMAL_BOLD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_WHITE_BOLD = workbook.createFont(); FONT_WHITE_BOLD.setColor(HSSFColor.WHITE.index); FONT_WHITE_BOLD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_RED = workbook.createFont(); FONT_RED.setColor(HSSFColor.RED.index); FONT_RED_BOLD = workbook.createFont(); FONT_RED_BOLD.setColor(HSSFColor.RED.index); FONT_RED_BOLD.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); FONT_NORMAL = workbook.createFont(); } }