XlsDataSetWriter.java

/*
 *
 * 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.CellType;
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(CellType.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();
    }
}