LinkedExcelTable.java

//$HeadURL$
/*----------------------------------------------------------------------------
 This file is part of deegree, http://deegree.org/
 Copyright (C) 2001-2009 by:
 - Department of Geography, University of Bonn -
 and
 - lat/lon GmbH -

 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

 Contact information:

 lat/lon GmbH
 Aennchenstr. 19, 53177 Bonn
 Germany
 http://lat-lon.de/

 Department of Geography, University of Bonn
 Prof. Dr. Klaus Greve
 Postfach 1147, 53001 Bonn
 Germany
 http://www.geographie.uni-bonn.de/deegree/

 e-mail: info@deegree.org
 ----------------------------------------------------------------------------*/
package org.deegree.igeo.dataadapter;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.deegree.datatypes.Types;
import org.deegree.framework.log.ILogger;
import org.deegree.framework.log.LoggerFactory;
import org.deegree.framework.util.Pair;
import org.deegree.igeo.config.LinkedFileTableType;

/**
 * concrect {@link LinkedTable} for accessing Excel tables
 * 
 * @author <a href="mailto:name@deegree.org">Andreas Poth</a>
 * @author last edited by: $Author$
 * 
 * @version $Revision$, $Date$
 */
public class LinkedExcelTable extends LinkedFileTable {

    private static final ILogger LOG = LoggerFactory.getLogger( LinkedExcelTable.class );

    private String[] columnNames;

    private int[] types;

    private Workbook workbook;

    private Sheet sheet;

    /**
     * 
     * @param linkedTableType
     * @param file
     * @param sheetName
     * @throws IOException
     */
    public LinkedExcelTable( LinkedFileTableType linkedTableType, File file ) throws IOException {
        super( linkedTableType );
        if ( file.getAbsolutePath().toLowerCase().endsWith( ".xls" ) ) {
            workbook = new HSSFWorkbook( new FileInputStream( file ) );
        } else {
            workbook = new XSSFWorkbook( new FileInputStream( file ) );
        }
        sheet = workbook.getSheetAt( 0 );
        LOG.logDebug( "load first excel sheet" );

        Iterator<Row> rowIter = sheet.rowIterator();
        Row firstRow = rowIter.next();

        List<String> headerNames = new ArrayList<String>();
        List<Integer> headerTypes = new ArrayList<Integer>();

        for ( Iterator<Cell> cit = firstRow.cellIterator(); cit.hasNext(); ) {
            Cell cell = cit.next();
            String cellValue = cell.getRichStringCellValue().getString();
            headerNames.add( cellValue );
            headerTypes.add( getCellType( cell.getCellType() ) );
        }
        columnNames = headerNames.toArray( new String[headerNames.size()] );
        types = new int[headerTypes.size()];
        for ( int i = 0; i < types.length; i++ ) {
            types[i] = headerTypes.get( i );
        }
    }

    private int getCellType( int type ) {
        int tp = Types.VARCHAR;
        switch ( type ) {
        case Cell.CELL_TYPE_BLANK:
            tp = Types.VARCHAR;
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            tp = Types.BOOLEAN;
            break;
        case Cell.CELL_TYPE_NUMERIC:
            tp = Types.DOUBLE;
            break;
        case Cell.CELL_TYPE_STRING:
            tp = Types.VARCHAR;
            break;
        default:
            tp = Types.VARCHAR;
        }
        return tp;
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.deegree.igeo.mapmodel.LinkedTable#getRow(int)
     */
    public Object[] getRow( int rowNo )
                            throws IOException {
        Object[] row = new Object[columnNames.length];
        Row hssfRow = sheet.getRow( rowNo );
        for ( int i = 0; i < row.length; i++ ) {
            row[i] = hssfRow.getCell( i ).getRichStringCellValue().getString();
        }
        return row;
    }

    /*
     * (non-Javadoc)
     * 
     * @see
     * org.deegree.igeo.mapmodel.LinkedTable#getRow(org.deegree.framework.util.Pair<java.lang.String,java.lang.String
     * >[])
     */
    public Object[][] getRows( Pair<String, Object>... keys )
                            throws IOException {

        List<Object[]> rows = new ArrayList<Object[]>( 10 );
        Iterator<Row> rowIter = sheet.rowIterator();

        // dummy call to read first (header) row
        rowIter.next();

        Object[] row = new Object[columnNames.length];
        while ( rowIter.hasNext() ) {
            Row hssfRow = rowIter.next();
            row = readRow( row, hssfRow );
            boolean match = true;
            for ( Pair<String, Object> pair : keys ) {
                int idx = getIndexForColumnName( pair.first );
                if ( !pair.second.equals( row[idx] ) ) {
                    match = false;
                    break;
                }
            }
            if ( match ) {
                rows.add( row );
                row = new Object[columnNames.length];
            }
        }
        return rows.toArray( new Object[rows.size()][] );
    }

    private Object[] readRow( Object[] row, Row hssfRow ) {
        for ( int i = 0; i < row.length; i++ ) {
            int type = Cell.CELL_TYPE_STRING;
            Cell cell = hssfRow.getCell( i );
            if ( cell != null ) {
                type = cell.getCellType();
                switch ( type ) {
                case Cell.CELL_TYPE_BLANK:
                    row[i] = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    row[i] = hssfRow.getCell( i ).getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    row[i] = hssfRow.getCell( i ).getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_STRING:
                    row[i] = hssfRow.getCell( i ).getRichStringCellValue().toString();
                    break;
                default:
                    break;
                }
            }
        }
        return row;
    }

    private int getIndexForColumnName( String name ) {
        for ( int i = 0; i < columnNames.length; i++ ) {
            if ( columnNames[i].equalsIgnoreCase( name ) ) {
                return i;
            }
        }
        return -1;
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.deegree.igeo.mapmodel.LinkedTable#getColumnCount()
     */
    public int getColumnCount() {
        return columnNames.length;
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.deegree.igeo.mapmodel.LinkedTable#getRowCount()
     */
    public int getRowCount() {
        return sheet.getLastRowNum();
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.deegree.igeo.dataadapter.LinkedTable#getColumnTypes()
     */
    public int[] getColumnTypes() {
        return types;
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.deegree.igeo.mapmodel.LinkedTable#getColumnNames()
     */
    public String[] getColumnNames() {
        return columnNames;
    }

}