/*
* Copyright (c) 2014 Red Hat, Inc. and/or its affiliates.
*
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the Eclipse Public License v1.0
* which accompanies this distribution, and is available at
* http://www.eclipse.org/legal/epl-v10.html
*
* Contributors:
* Cheng Fang - Initial API and implementation
*/
package org.jberet.support.io;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.batch.api.BatchProperty;
import javax.batch.api.chunk.ItemReader;
import javax.enterprise.context.Dependent;
import javax.inject.Inject;
import javax.inject.Named;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.jberet.support._private.SupportLogger;
import org.jberet.support._private.SupportMessages;
/**
* An implementation of {@code javax.batch.api.chunk.ItemReader} for reading Excel files. Current implementation is
* based on Apache POI user model API.
*
* @see ExcelUserModelItemWriter
* @see ExcelItemReaderWriterBase
* @see ExcelEventItemReader
* @see ExcelStreamingItemReader
* @since 1.1.0
*/
@Named
@Dependent
public class ExcelUserModelItemReader extends ExcelItemReaderWriterBase implements ItemReader {
/**
* A positive integer indicating the start position in the input resource. It is optional and defaults to 0
* (starting from the 1st data item). If a header row is present, the start point should be after the header row.
*/
@Inject
@BatchProperty
protected int start;
/**
* A positive integer indicating the end position in the input resource. It is optional and defaults to
* {@code Integer.MAX_VALUE}.
*/
@Inject
@BatchProperty
protected int end;
/**
* The index (0-based) of the target sheet to read, defaults to 0.
*/
@Inject
@BatchProperty
protected int sheetIndex;
/**
* The physical row number of the header.
*/
@Inject
@BatchProperty
protected Integer headerRow;
protected InputStream inputStream;
protected FormulaEvaluator formulaEvaluator;
protected Iterator<Row> rowIterator;
protected int minColumnCount;
/**
* Mapping between column label -> header name, e.g, {A : name, B: age}, or {1: name, 2: age} for R1C1 notation.
* Only applicable when {@link #beanType} is not {@code List}.
*/
protected Map<String, String> headerMapping;
@Override
public void open(final Serializable checkpoint) throws Exception {
/**
* The row number to start reading. It may be different from the injected field start. During a restart,
* we would start reading from where it ended during the last run.
*/
if (this.end == 0) {
this.end = Integer.MAX_VALUE;
}
if (headerRow == null) {
if (header == null) {
throw SupportMessages.MESSAGES.invalidReaderWriterProperty(null, null, "header | headerRow");
}
headerRow = -1;
}
if (start == headerRow) {
start += 1;
}
final int startRowNumber = checkpoint == null ? this.start : (Integer) checkpoint;
if (startRowNumber < this.start || startRowNumber > this.end
|| startRowNumber < 0 || startRowNumber <= headerRow) {
throw SupportMessages.MESSAGES.invalidStartPosition(startRowNumber, this.start, this.end);
}
inputStream = getInputStream(resource, false);
initWorkbookAndSheet(startRowNumber);
if (header != null) {
minColumnCount = header.length;
}
}
@Override
public Object readItem() throws Exception {
if (currentRowNum == this.end) {
return null;
}
Row row;
while (rowIterator.hasNext()) {
row = rowIterator.next();
currentRowNum = row.getRowNum();
final short lastCellNum = row.getLastCellNum();
if (lastCellNum == -1) { // no cell in the current row
continue;
}
final int lastColumn = Math.max(lastCellNum, minColumnCount);
if (java.util.List.class.isAssignableFrom(beanType)) {
final List<Object> resultList = new ArrayList<Object>();
for (int cn = 0; cn < lastColumn; cn++) {
final Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
if (c == null) { // The spreadsheet is empty in this cell
resultList.add(null);
} else {
resultList.add(getCellValue(c, c.getCellType()));
}
}
return resultList;
} else {
final Map<String, Object> resultMap = new HashMap<String, Object>();
for (int cn = 0; cn < header.length; cn++) {
final Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
if (c != null) {
resultMap.put(header[cn], getCellValue(c, c.getCellType()));
}
}
if (java.util.Map.class.isAssignableFrom(beanType)) {
return resultMap;
} else {
if (objectMapper == null) {
initJsonFactoryAndObjectMapper();
}
final Object readValue = objectMapper.convertValue(resultMap, beanType);
if (!skipBeanValidation) {
ItemReaderWriterBase.validate(readValue);
}
return readValue;
}
}
}
return null;
}
@Override
public Serializable checkpointInfo() throws Exception {
return currentRowNum;
}
@Override
public void close() throws Exception {
if (inputStream != null) {
try {
inputStream.close();
} catch (final IOException e) {
SupportLogger.LOGGER.tracef(e, "Failed to close InputStream %s for resource %s", inputStream, resource);
}
inputStream = null;
}
}
protected Object getCellValue(final Cell c, final int cellType) {
final Object cellValue;
switch (cellType) {
case Cell.CELL_TYPE_STRING:
cellValue = c.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = c.getBooleanCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue = DateUtil.isCellDateFormatted(c) ? c.getDateCellValue() : c.getNumericCellValue();
break;
case Cell.CELL_TYPE_BLANK:
cellValue = null;
break;
case Cell.CELL_TYPE_FORMULA:
if (formulaEvaluator == null) {
formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
}
formulaEvaluator.evaluateFormulaCell(c);
cellValue = getCellValue(c, c.getCachedFormulaResultType());
break;
default:
cellValue = c.getStringCellValue();
break;
}
return cellValue;
}
protected void initWorkbookAndSheet(int startRowNumber) throws Exception {
workbook = WorkbookFactory.create(inputStream);
if (sheetName != null) {
sheet = workbook.getSheet(sheetName);
}
if (sheet == null) {
sheet = workbook.getSheetAt(sheetIndex);
}
startRowNumber = Math.max(startRowNumber, sheet.getFirstRowNum());
rowIterator = sheet.rowIterator();
if (startRowNumber > 0) {
while (rowIterator.hasNext()) {
final Row row = rowIterator.next();
currentRowNum = row.getRowNum();
if (header == null && headerRow == currentRowNum) {
header = getCellStringValues(row);
}
if (currentRowNum >= startRowNumber - 1) {
break;
}
}
}
}
}