ExcelPropertyTable.java

package com.canoo.ant.table;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelPropertyTable extends APropertyTable {

    private static final Logger LOG = Logger.getLogger(ExcelPropertyTable.class);

    public ExcelPropertyTable() {
    }

    protected boolean hasJoinTable() {
    	final Object sheet;
		try {
			sheet = getWorkbook().getSheet(KEY_JOIN);
		} 
		catch (final IOException e) {
			throw new RuntimeException("Failed to read container: >" + getContainer() + "<", e);
		}
    	return sheet != null;
    }

    private HSSFWorkbook getWorkbook() throws IOException {
        final File file = getContainer();
        if (!file.exists()) {
        	throw new FileNotFoundException("File not found >" + file.getAbsolutePath() + "< " + getContainer());
        }
        else if (!file.isFile() ||!file.canRead()) {
        	throw new IllegalArgumentException("No a regular readable file: >" + file.getAbsolutePath() + "<");
        }
        final POIFSFileSystem excelFile = new POIFSFileSystem(new FileInputStream(file));
        return new HSSFWorkbook(excelFile);
	}

	protected List read(final String sheetName) throws IOException {
        final HSSFWorkbook workbook = getWorkbook();
        final HSSFSheet sheet = getSheet(workbook, sheetName);

        final int lastRowNum = sheet.getLastRowNum();
        final List header = new ArrayList();
        final HSSFRow headerRow = sheet.getRow(0);
        for (short i = 0; i < headerRow.getLastCellNum(); i++) {
        	final HSSFCell cell = headerRow.getCell(i);
        	if (cell != null)
        		header.add(stringValueOf(workbook, sheet, headerRow, cell));
        	else
        		header.add(null);
        }
        final List result = new LinkedList();
        for (int rowNo = 1; rowNo <= lastRowNum; rowNo++) { // last Row is included
            final HSSFRow row = sheet.getRow(rowNo);
            if (row != null) // surprising, but row can be null
            {
	            final Properties props = new Properties();
	            for (short i = 0; i < header.size(); i++) {
	            	final String headerName = (String) header.get(i);
	            	if (headerName != null) // handle empty cols
	            	{
		            	final HSSFCell cell = row.getCell(i);
		            	final String value = stringValueOf(workbook, sheet, row, cell);
		                putValue(value, headerName, props);
	            	}
	            }
	            result.add(props);
            }
        }

        return result;
    }

	private HSSFSheet getSheet(final HSSFWorkbook workbook, final String sheetName)
	{
		final HSSFSheet sheet;
		if (sheetName == null) {
        	sheet = workbook.getSheetAt(0); // no name specified, take the first sheet
        }
        else {
        	sheet = workbook.getSheet(sheetName);
        }
        if (null == sheet) {
        	String msg = "No sheet \"" + sheetName + "\" found in file " + getContainer() + ". Available sheets: ";
        	for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        		if (i != 0)
        			msg += ", ";
				msg += workbook.getSheetName(i);
			}
        	throw new IllegalArgumentException(msg);
        }
		return sheet;
	}
    
    protected void putValue(String value, Object key, Properties props) {
        props.put(key, value);
    }
    
    private String stringValueOf(final HSSFWorkbook workbook, final HSSFSheet sheet, final HSSFRow row, final HSSFCell cell) {
        if (null == cell) {
            return EMPTY;
        }
        final int cellValueType;
        if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
           	final HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
           	evaluator.setCurrentRow(row);
           	cellValueType = evaluator.evaluateFormulaCell(cell);
        }
        else {
        	cellValueType = cell.getCellType();
        }
        
        switch (cellValueType) {
            case (HSSFCell.CELL_TYPE_STRING):
                return cell.getRichStringCellValue().getString();
            case (HSSFCell.CELL_TYPE_NUMERIC):
                final HSSFDataFormat dataFormat = workbook.createDataFormat();
            	if (HSSFDateUtil.isCellDateFormatted(cell))
            		return excelDateToString(dataFormat, cell);
            	else
            		return excelNumberToString(dataFormat, cell);
            case (HSSFCell.CELL_TYPE_BLANK):
                return "";
            case (HSSFCell.CELL_TYPE_BOOLEAN):
                return "" + cell.getBooleanCellValue();
            default:
                LOG.warn("Cell Type not supported: " + cell.getCellType());
                return EMPTY;
        }
    }
    
    private String excelNumberToString(HSSFDataFormat dataFormat, HSSFCell _cell)
	{
    	final String excelFormat = dataFormat.getFormat(_cell.getCellStyle().getDataFormat());
    	final String javaFormat = excelNumberFormat2Java(excelFormat);
    	
    	LOG.debug("Excel date format >" + excelFormat + "< converted to >" + javaFormat + "< for " + _cell.getNumericCellValue());
    	String response = new DecimalFormat(javaFormat).format(_cell.getNumericCellValue());
    	
    	return response;
	}

	private String excelNumberFormat2Java(final String _excelFormat)
	{
		if ("general".equalsIgnoreCase(_excelFormat))
			return "#.##"; // default seems to be 2 decimals (if any)
		else
			return _excelFormat;
	}

	private String excelDateToString(final HSSFDataFormat dataFormat, final HSSFCell _cell)
	{
    	final String excelFormat = dataFormat.getFormat(_cell.getCellStyle().getDataFormat());
    	final String javaFormat = excelDateFormat2Java(excelFormat);
    	LOG.debug("Excel date format >" + excelFormat + "< converted to >" + javaFormat + "<");
    	
    	final Date date = HSSFDateUtil.getJavaDate(_cell.getNumericCellValue());
    	return new SimpleDateFormat(javaFormat).format(date);
	}

	static String excelDateFormat2Java(String format)
    {
    	// Y -> y
    	format = format.replaceAll("Y", "y");
    	// DD -> dd
    	format = format.replaceAll("DD", "dd");
    	// remove \
    	format = format.replaceAll("\\\\", "");
    	// MM for minutes -> mm
    	format = format.replaceAll("HH:MM", "HH:mm");
    	// SS -> ss
    	format = format.replaceAll("SS", "ss");
    	// WW -> w
    	format = format.replaceAll("WW", "w");

    	return format;
    }
}