com.welemski.wrench.poi.util.SpreadsheetReader Maven / Gradle / Ivy
The newest version!
/*
* The MIT License
*
* Copyright 2016 Lemuel Raganas.
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
* THE SOFTWARE.
*/
package com.welemski.wrench.poi.util;
import com.welemski.wrench.poi.delegate.DidReadCellDelegate;
import com.welemski.wrench.poi.delegate.DidReadRowDelegate;
import com.welemski.wrench.poi.delegate.DidReadSheetDelegate;
import com.welemski.wrench.poi.delegate.DidReadWorkbook;
import com.welemski.wrench.poi.filter.CellFilter;
import com.welemski.wrench.poi.filter.RowFilter;
import com.welemski.wrench.poi.filter.SheetFilter;
import com.welemski.wrench.delegate.Delegable;
import com.welemski.wrench.delegate.ErrorDelegate;
import com.welemski.wrench.delegate.ObjectDelegate;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
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;
/**
*
* @author Lemuel Raganas
*/
public class SpreadsheetReader extends Delegable implements SheetReader{
private final String kDidBegin = "didBegin";
private final String kDidEnd = "didEnd";
private final String kDidError = "error";
private final String kDidReadWorkbook = "didReadWorkbook";
private final String kDidReadRow = "didReadRow";
private final String kDidReadCell = "didReadCell";
private final String kDidReadSheet = "didReadSheet";
private final String kRowFilter = "rowFilter";
private final String kCellFilter = "cellFilter";
private final String kSheetFilter = "sheetFilter";
protected int currentSheetIndex = 0;
protected int currentRowIndex = 0;
protected int currentCellIndex = 0;
protected ArrayList sheets = new ArrayList<>();
private File file = null;
private List errors = new ArrayList<>();
private Workbook workbook;
private HashMap properties = new HashMap<>();
private List rows = new ArrayList<>();
public SpreadsheetReader(){
}
public SpreadsheetReader(String filePath){
this.file = new File(filePath);
}
public SpreadsheetReader(File file){
this.file = file;
}
public SpreadsheetReader(Workbook workbook){
this.workbook = workbook;
}
protected boolean startReading(){
this.rows.clear();
this.invokeObjectDelegate(kDidBegin, this);
try{
if(workbook == null){
workbook = getWorkbookFromFile(file);
}
this.extractSheets(this.workbook);
if(this.hasDelegate(kDidReadWorkbook)){
DidReadWorkbook delegate = (DidReadWorkbook)this.getDelegate(kDidReadWorkbook);
delegate.workbook(this, workbook);
}
this.invokeObjectDelegate(kDidEnd, this);
return true;
} catch (IOException | InvalidFormatException ex){
this.errors.add(ex);
this.invokeErrorDelegate(kDidError, this, ex);
this.invokeObjectDelegate(kDidEnd, this);
return false;
}
}
protected Workbook getWorkbookFromFile(File file) throws IOException, InvalidFormatException{
Workbook wb = null;
String fileExt = FilenameUtils.getExtension(file.getAbsolutePath());
if(fileExt.equals("xls")){
try (NPOIFSFileSystem npoiFs = new NPOIFSFileSystem(file)) {
wb = new HSSFWorkbook(npoiFs);
}
}else if(fileExt.equals("xlsx")){
try (OPCPackage opcPkg = OPCPackage.open(file)) {
wb = new XSSFWorkbook(opcPkg);
}
}
return wb;
}
protected void extractSheets(Workbook workbook) {
if(workbook == null){
return;
}
int numberOfSheets = workbook.getNumberOfSheets();
for(int i=0 ; i < numberOfSheets ; i++ ){
this.currentSheetIndex = i;
boolean skip = false;
Sheet sheetAt = workbook.getSheetAt(i);
if(this.hasDelegate(kSheetFilter)){
skip = !((SheetFilter) this.getDelegate(kSheetFilter)).filter(this, sheetAt,i);
}
if(skip){
continue; // Skip
}
this.sheets.add(sheetAt);
if(this.hasDelegate(kDidReadSheet)){
DidReadSheetDelegate delegate = (DidReadSheetDelegate) this.getDelegate(kDidReadSheet);
delegate.sheet(this, sheetAt,i);
}
this.extractRows(sheetAt);
}
}
protected void extractRows(Sheet sheet){
if(sheet == null){
return;
}
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
for(int i=0 ; i < physicalNumberOfRows ; i++ ){
this.currentRowIndex = i;
boolean skip = false;
Row row = sheet.getRow(i);
SpreadsheetRow sr = new SpreadsheetRow(row);
if(this.hasDelegate(kRowFilter)){
skip = !((RowFilter)this.getDelegate(kRowFilter)).filter(this, sr, i);
}
if(skip){
continue;
}
this.rows.add(sr);
if(this.hasDelegate(kDidReadRow)){
((DidReadRowDelegate)this.getDelegate(kDidReadRow)).row(this, sr, i);
}
this.extractCells(row);
}
}
protected void extractCells(Row row){
if(row == null){
return;
}
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
for(int i=0 ; i < physicalNumberOfCells ; i++){
this.currentCellIndex = i;
boolean skip = false;
Cell cell = row.getCell(i);
if(this.hasDelegate(kCellFilter)){
skip = !((CellFilter)this.getDelegate(kCellFilter)).filter(this, cell, i);
}
if(skip){
continue;
}
if(this.hasDelegate(kDidReadCell)){
((DidReadCellDelegate)this.getDelegate(kDidReadCell)).cell(this, cell, i);
}
}
}
@Override
public boolean read() {
return this.startReading();
}
@Override
public void didBegin(ObjectDelegate delegate) {
this.addDelegate("didBegin", delegate);
}
@Override
public void didEnd(ObjectDelegate delegate) {
this.addDelegate("didEnd", delegate);
}
@Override
public void didError(ErrorDelegate delegate) {
this.addDelegate("didError", delegate);
}
@Override
public void didReadWorkbook(DidReadWorkbook delegate) {
this.addDelegate("didReadWorkbook", delegate);
}
@Override
public void didReadRow(DidReadRowDelegate delegate) {
this.addDelegate("didReadRow", delegate);
}
@Override
public void didReadCell(DidReadCellDelegate delegate) {
this.addDelegate("didReadCell", delegate);
}
@Override
public void setRowFilter(RowFilter filter) {
this.addDelegate("rowFilter", filter);
}
@Override
public void setCellFilter(CellFilter filter) {
this.addDelegate("cellFilter", filter);
}
@Override
public Exception[] getErrors() {
Exception[] e = new Exception[this.errors.size()];
this.errors.toArray(e);
return e;
}
@Override
public void didReadSheet(DidReadSheetDelegate delegate) {
this.addDelegate("didReadSheet", delegate);
}
@Override
public void setSheetFilter(SheetFilter filter) {
this.addDelegate(kSheetFilter, filter);
}
@Override
public Workbook getWorkbook() {
return this.workbook;
}
@Override
public List getSheets() {
return this.sheets;
}
@Override
public void setProperty(String forKey, Object value) {
this.properties.put(forKey, value);
}
@Override
public Object getProperty(String forKey) {
return this.properties.get(forKey);
}
@Override
public List getRows() {
return this.rows;
}
}