org.eobjects.metamodel.ExcelDataContextStrategy Maven / Gradle / Ivy

Go to download
/**
 * eobjects.org MetaModel
 * Copyright (C) 2010 eobjects.org
 *
 * This copyrighted material is made available to anyone wishing to use, modify,
 * copy, or redistribute it subject to the terms and conditions of the GNU
 * Lesser General Public License, as published by the Free Software Foundation.
 *
 * This program 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 distribution; if not, write to:
 * Free Software Foundation, Inc.
 * 51 Franklin Street, Fifth Floor
 * Boston, MA  02110-1301  USA
 */

package org.eobjects.metamodel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.eobjects.metamodel.data.DataSet;
import org.eobjects.metamodel.data.InMemoryDataSet;
import org.eobjects.metamodel.query.SelectItem;
import org.eobjects.metamodel.schema.Column;
import org.eobjects.metamodel.schema.ColumnType;
import org.eobjects.metamodel.schema.MutableColumn;
import org.eobjects.metamodel.schema.MutableSchema;
import org.eobjects.metamodel.schema.MutableTable;
import org.eobjects.metamodel.schema.Schema;
import org.eobjects.metamodel.schema.Table;
import org.eobjects.metamodel.util.DateUtils;
import org.eobjects.metamodel.util.FileHelper;
import org.eobjects.metamodel.util.FormatHelper;

/**
 * DataContextStrategy to use for Excel files
 */
public final class ExcelDataContextStrategy extends
		QueryPostprocessDataContextStrategy {

	private static final NumberFormat _numberFormat = FormatHelper
			.getUiNumberFormat();
	private final File _file;

	public ExcelDataContextStrategy(File file) {
		if (file == null) {
			throw new IllegalArgumentException("File cannot be null");
		}
		if (!file.exists() || !file.canRead()) {
			throw new IllegalArgumentException("Cannot read from file");
		}
		_file = file;
	}

	@Override
	public DataSet materializeMainSchemaTable(Table table, Column[] columns,
			int maxRows) {
		if (columns == null || columns.length == 0) {
			columns = table.getColumns();
		}

		final SelectItem[] selectItems = new SelectItem[columns.length];
		for (int i = 0; i < columns.length; i++) {
			Column column = columns[i];
			selectItems[i] = new SelectItem(column);
		}

		final List resultData = new ArrayList();

		InputStream in = null;

		try {
			Workbook wb = getWorkbook();
			Sheet sheet = wb.getSheet(table.getName());
			// the first row is the Column names
			if (sheet != null && sheet.getPhysicalNumberOfRows() > 1) {
				Iterator rowIterator = sheet.rowIterator();
				if (rowIterator.hasNext()) {
					// iterate past the column headers
					rowIterator.next();
				}
				while (rowIterator.hasNext()) {
					Row row = rowIterator.next();
					if (maxRows == 0) {
						break;
					}
					maxRows--;
					String[] dataValues = new String[columns.length];
					for (int j = 0; j < columns.length; j++) {
						Cell cell = row.getCell(columns[j].getColumnNumber());
						dataValues[j] = getCellValue(wb, cell);
					}
					resultData.add(dataValues);
				}
			}
		} catch (Exception e) {
			throw new IllegalStateException("Exception reading from file: "
					+ _file, e);
		} finally {
			FileHelper.safeClose(in);
		}

		DataSet result = new InMemoryDataSet(selectItems, resultData);
		return result;
	}

	private Workbook getWorkbook() throws IOException {
		Workbook wb = null;
		InputStream in = null;
		try {
			// first try to open the file using XLS format
			in = new FileInputStream(_file);
			wb = new HSSFWorkbook(in);
		} catch (Exception e) {
			FileHelper.safeClose(in);

			// then try opening the file using XLSX format
			in = new FileInputStream(_file);
			wb = new XSSFWorkbook(in);
		} finally {
			FileHelper.safeClose(in);
		}
		return wb;
	}

	@Override
	protected String getMainSchemaName() throws MetaModelException {
		return _file.getName();
	}

	protected Schema getMainSchema() throws MetaModelException {
		InputStream in = null;
		MutableSchema schema = null;
		try {

			schema = new MutableSchema(getDefaultSchemaName());
			in = new FileInputStream(_file);
			Workbook wb = getWorkbook();

			for (int i = 0; i < wb.getNumberOfSheets(); i++) {
				Sheet currentSheet = wb.getSheetAt(i);
				if (currentSheet.getPhysicalNumberOfRows() > 0) {
					Iterator rowIterator = currentSheet.rowIterator();
					if (rowIterator.hasNext()) {
						MutableTable table = new MutableTable(
								wb.getSheetName(i));
						Row row = rowIterator.next();
						short rowLength = row.getLastCellNum();
						for (int j = 0; j < rowLength; j++) {
							Cell cell = row.getCell(j);
							String columnName = getCellValue(wb, cell);
							if (columnName == null || "".equals(columnName)) {
								columnName = "[Column " + (j + 1) + "]";
							}
							Column column = new MutableColumn(columnName,
									ColumnType.VARCHAR, table, j, true);
							table.addColumn(column);
						}
						table.setSchema(schema);
						schema.addTable(table);
					}
				}
			}

		} catch (Exception e) {
			throw new IllegalStateException("Exception reading from file: "
					+ _file, e);
		} finally {
			FileHelper.safeClose(in);
		}
		return schema;
	}

	private String getCellValue(Workbook wb, Cell cell) {
		if (cell == null) {
			return null;
		}
		String result = null;
		int cellType = cell.getCellType();

		if (cellType == Cell.CELL_TYPE_FORMULA) {
			FormulaEvaluator evaluator = wb.getCreationHelper()
					.createFormulaEvaluator();

			if (logger.isInfoEnabled()) {
				logger.info(
						"cell({},{}) is a formula. Evaluating: {}",
						new Object[] { cell.getRowIndex(),
								cell.getColumnIndex(), cell.getCellFormula() });
			}

			// calculates the formula and puts it's value back into the cell
			cell = evaluator.evaluateInCell(cell);
			cellType = cell.getCellType();
		}

		if (cellType == Cell.CELL_TYPE_BLANK) {
			result = null;
		} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
			result = Boolean.toString(cell.getBooleanCellValue());
		} else if (cellType == Cell.CELL_TYPE_ERROR) {
			result = "[Error " + cell.getErrorCellValue() + "]";
		} else if (cellType == Cell.CELL_TYPE_NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				if (date != null) {
					date = DateUtils.get(date);
					result = DateUtils.createDateFormat().format(date);
				}
			} else {
				// TODO: Consider not formatting it, but simple using
				// Double.toString(...)
				result = _numberFormat.format(cell.getNumericCellValue());
			}
		} else if (cellType == Cell.CELL_TYPE_STRING) {
			result = cell.getRichStringCellValue().getString();
		} else {
			result = cell.toString();
		}

		if (logger.isDebugEnabled()) {
			logger.debug("cell({},{}) resolved to value: {}", new Object[] {
					cell.getRowIndex(), cell.getColumnIndex(), result });
		}

		return result;
	}
}