Java tutorial
/* * * The DbUnit Database Testing Framework * Copyright (C)2002-2008, DbUnit.org * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library 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 * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA * */ package org.dbunit.dataset.excel; import java.io.IOException; import java.io.OutputStream; import java.math.BigDecimal; import java.util.Date; import java.util.HashMap; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.dbunit.dataset.Column; import org.dbunit.dataset.DataSetException; import org.dbunit.dataset.IDataSet; import org.dbunit.dataset.ITable; import org.dbunit.dataset.ITableIterator; import org.dbunit.dataset.ITableMetaData; import org.dbunit.dataset.datatype.DataType; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * Writes an {@link IDataSet} to an XLS file or OutputStream. * * @author gommma (gommma AT users.sourceforge.net) * @author Last changed by: $Author$ * @version $Revision$ $Date$ * @since 2.4.0 */ public class XlsDataSetWriter { private static final Logger logger = LoggerFactory.getLogger(XlsDataSetWriter.class); public static final String ZEROS = "0000000000000000000000000000000000000000000000000000"; /** * A special format pattern used to create a custom {@link DataFormat} which * marks {@link Date} values that are stored via POI to an XLS file. * Note that it might produce problems if a normal numeric value uses this format * pattern incidentally. */ public static final String DATE_FORMAT_AS_NUMBER_DBUNIT = "####################"; /** * Instead of recreating a new style object for each numeric cell, which * will cause the code to hit the POI limit of 4000 styles pretty quickly, * only create one per format and reuse the same style for all cells with * the same format. */ private static final Map<Workbook, Map> cellStyleMap = new HashMap<Workbook, Map>(); private CellStyle dateCellStyle; /** * Write the specified dataset to the specified Excel document. */ public void write(IDataSet dataSet, OutputStream out) throws IOException, DataSetException { logger.debug("write(dataSet={}, out={}) - start", dataSet, out); Workbook workbook = createWorkbook(); this.dateCellStyle = createDateCellStyle(workbook); int index = 0; ITableIterator iterator = dataSet.iterator(); while (iterator.next()) { // create the table i.e. sheet ITable table = iterator.getTable(); ITableMetaData metaData = table.getTableMetaData(); Sheet sheet = workbook.createSheet(metaData.getTableName()); // write table metadata i.e. first row in sheet workbook.setSheetName(index, metaData.getTableName()); Row headerRow = sheet.createRow(0); Column[] columns = metaData.getColumns(); for (int j = 0; j < columns.length; j++) { Column column = columns[j]; Cell cell = headerRow.createCell(j); cell.setCellValue(column.getColumnName()); } // write table data for (int j = 0; j < table.getRowCount(); j++) { Row row = sheet.createRow(j + 1); for (int k = 0; k < columns.length; k++) { Column column = columns[k]; Object value = table.getValue(j, column.getColumnName()); if (value != null) { Cell cell = row.createCell(k); if (value instanceof Date) { setDateCell(cell, (Date) value, workbook); } else if (value instanceof BigDecimal) { setNumericCell(cell, (BigDecimal) value, workbook); } else if (value instanceof Long) { setDateCell(cell, new Date(((Long) value).longValue()), workbook); } else { cell.setCellValue(DataType.asString(value)); } } } } index++; } // write xls document workbook.write(out); out.flush(); } protected static CellStyle createDateCellStyle(Workbook workbook) { DataFormat format = workbook.createDataFormat(); short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT); return getCellStyle(workbook, dateFormatCode); } protected static CellStyle getCellStyle(Workbook workbook, short formatCode) { Map<Short, CellStyle> map = findWorkbookCellStyleMap(workbook); CellStyle cellStyle = findCellStyle(workbook, formatCode, map); return cellStyle; } protected static Map<Short, CellStyle> findWorkbookCellStyleMap(Workbook workbook) { Map<Short, CellStyle> map = cellStyleMap.get(workbook); if (map == null) { map = new HashMap<Short, CellStyle>(); cellStyleMap.put(workbook, map); } return map; } protected static CellStyle findCellStyle(Workbook workbook, Short formatCode, Map<Short, CellStyle> map) { CellStyle cellStyle = map.get(formatCode); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(formatCode); map.put(formatCode, cellStyle); } return cellStyle; } protected void setDateCell(Cell cell, Date value, Workbook workbook) { // double excelDateValue = HSSFDateUtil.getExcelDate(value); // cell.setCellValue(excelDateValue); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); long timeMillis = value.getTime(); cell.setCellValue((double) timeMillis); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(this.dateCellStyle); // System.out.println(HSSFDataFormat.getBuiltinFormats()); // TODO Find out correct cell styles for date objects // HSSFCellStyle cellStyleDate = workbook.createCellStyle(); // cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); // // HSSFCellStyle cellStyleDateTimeWithSeconds = workbook.createCellStyle(); // cellStyleDateTimeWithSeconds.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss")); // // HSSFDataFormat dataFormat = workbook.createDataFormat(); // HSSFCellStyle cellStyle = workbook.createCellStyle(); // cellStyle.setDataFormat(dataFormat.getFormat("dd/mm/yyyy hh:mm:ss")); // // SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); // SimpleDateFormat formatter2 = new SimpleDateFormat("dd/MM/yyyy"); // SimpleDateFormat formatter3 = new SimpleDateFormat("HH:mm:ss.SSS"); // // // Date dateValue = (Date)value; // Calendar cal = null; // // // If it is a date value that does not have seconds // if(dateValue.getTime() % 60000 == 0){ //// cellStyle = cellStyleDate; // cal=Calendar.getInstance(); // cal.setTimeInMillis(dateValue.getTime()); // // cell.setCellValue(cal); // cell.setCellStyle(cellStyle); //// cell.setCellValue(cal); // } // else { //// HSSFDataFormatter formatter = new HSSFDataFormatter(); // // // If we have seconds assume that it is only h:mm:ss without date // // TODO Clean implementation where user can control date formats would be nice //// double dateDouble = dateValue.getTime() % (24*60*60*1000); // cal = get1900Cal(dateValue); // // String formatted = formatter3.format(dateValue); // //TODO Format ... //// cellStyle = cellStyleDateTimeWithSeconds; // System.out.println("date formatted:"+formatted); //// HSSFRichTextString s = new HSSFRichTextString(formatted); //// cell.setCellValue(s); // cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); // cell.setCellValue((double)dateValue.getTime()); // cell.setCellStyle(cellStyleDateTimeWithSeconds); // } } protected void setNumericCell(Cell cell, BigDecimal value, Workbook workbook) { if (logger.isDebugEnabled()) logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start", new Object[] { cell, value, workbook }); cell.setCellValue(((BigDecimal) value).doubleValue()); DataFormat df = workbook.createDataFormat(); int scale = ((BigDecimal) value).scale(); short format; if (scale <= 0) { format = df.getFormat("####"); } else { String zeros = createZeros(((BigDecimal) value).scale()); format = df.getFormat("####." + zeros); } if (logger.isDebugEnabled()) logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value); CellStyle cellStyleNumber = getCellStyle(workbook, format); cell.setCellStyle(cellStyleNumber); } // public static Date get1900(Date date) { // Calendar cal = Calendar.getInstance(); // cal.setTimeInMillis(date.getTime() % (24*60*60*1000)); // cal.set(1900, 0, 1); // 1.1.1900 // return cal.getTime(); // } // // public static Calendar get1900Cal(Date date) { // Calendar cal = Calendar.getInstance(); // cal.clear(); //// long hoursInMillis = date.getTime() % (24*60*60*1000); //// long smallerThanDays = date.getTime() % (24*60*60*1000); //// cal.setTimeInMillis(date.getTime() % (24*60*60*1000)); // cal.set(Calendar.SECOND, (int) (date.getTime() % (24*60*60*1000)) / (1000) ); // cal.set(Calendar.MINUTE, (int) (date.getTime() % (24*60*60*1000)) / (1000*60) ); // cal.set(Calendar.HOUR, (int) (date.getTime() % (24*60*60*1000)) / (1000*60*60) ); //// cal.set(1900, 0, 1); // 1.1.1900 // System.out.println(cal.isSet(Calendar.DATE)); // return cal; // } private static String createZeros(int count) { return ZEROS.substring(0, count); } protected Workbook createWorkbook() { return new HSSFWorkbook(); } }