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

Go to download
Show more of this group Show more artifacts with this name
Show all versions of MetaModel-excel
The newest version!
/**
 *  This file is part of MetaModel.
 *
 *  MetaModel is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  MetaModel 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 General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with MetaModel.  If not, see .
 */
package dk.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.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
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;

import dk.eobjects.metamodel.data.DataSet;
import dk.eobjects.metamodel.query.SelectItem;
import dk.eobjects.metamodel.schema.Column;
import dk.eobjects.metamodel.schema.ColumnType;
import dk.eobjects.metamodel.schema.Schema;
import dk.eobjects.metamodel.schema.Table;
import dk.eobjects.metamodel.util.FormatHelper;

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

	private static final NumberFormat _numberFormat = FormatHelper.getUiNumberFormat();
	private 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();
		InputStream in = null;

		try {
			in = new FileInputStream(_file);
			HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(in));
			HSSFSheet sheet = wb.getSheet(table.getName());
			// the first row is the Column names
			List resultData = new ArrayList();
			if (sheet != null && sheet.getPhysicalNumberOfRows() > 1) {
				@SuppressWarnings("unchecked")
				Iterator rowIterator = sheet.rowIterator();
				if (rowIterator.hasNext()) {
					// iterate past the column headers
					rowIterator.next();
				}
				while (rowIterator.hasNext()) {
					HSSFRow row = rowIterator.next();
					if (maxRows == 0) {
						break;
					}
					maxRows--;
					String[] dataValues = new String[columns.length];
					for (int j = 0; j < columns.length; j++) {
						HSSFCell cell = row.getCell(columns[j].getColumnNumber());
						dataValues[j] = getCellValue(cell);
					}
					resultData.add(dataValues);
				}
				SelectItem[] selectItems = new SelectItem[columns.length];
				for (int i = 0; i < columns.length; i++) {
					Column column = columns[i];
					selectItems[i] = new SelectItem(column);
				}
				DataSet result = new DataSet(selectItems, resultData);
				return result;

			}

		} catch (Exception e) {
			throw new IllegalStateException("Exception reading from file: " + _file, e);
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
				}
			}
		}
		return null;
	}

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

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

			schema = new Schema(getDefaultSchemaName());
			in = new FileInputStream(_file);
			HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(in));

			for (int i = 0; i < wb.getNumberOfSheets(); i++) {
				HSSFSheet currentSheet = wb.getSheetAt(i);
				if (currentSheet.getPhysicalNumberOfRows() > 0) {
					@SuppressWarnings("unchecked")
					Iterator rowIterator = currentSheet.rowIterator();
					if (rowIterator.hasNext()) {
						Table table = new Table(wb.getSheetName(i));
						HSSFRow row = rowIterator.next();
						short rowLength = row.getLastCellNum();
						for (int j = 0; j < rowLength; j++) {
							HSSFCell cell = row.getCell(j);
							String columnName = getCellValue(cell);
							if (columnName == null || "".equals(columnName)) {
								columnName = "[Column " + (j + 1) + "]";
							}
							Column column = new Column(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 {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {

				}
			}
		}
		return schema;
	}

	private String getCellValue(HSSFCell cell) {
		if (cell == null) {
			return null;
		}
		String result;
		if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
			result = null;
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
			result = Boolean.toString(cell.getBooleanCellValue());
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR) {
			result = "[Error " + cell.getErrorCellValue() + "]";
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
			result = cell.getCellFormula();
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			result = _numberFormat.format(cell.getNumericCellValue());
		} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
			result = cell.getRichStringCellValue().getString();
		} else {
			result = cell.toString();
		}
		return result;
	}
}