/******************************************************************************** * Copyright (c) 2019 [Open Lowcode SAS](https://openlowcode.com/) * * This program and the accompanying materials are made available under the * terms of the Eclipse Public License 2.0 which is available at * http://www.eclipse.org/legal/epl-2.0 . * * SPDX-License-Identifier: EPL-2.0 ********************************************************************************/ package org.openlowcode.tools.file; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; 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.ss.usermodel.WorkbookFactory; /** * A parser reading a Microsoft Excel format file using Apache POI * * @author <a href="https://openlowcode.com/" rel="nofollow">Open Lowcode * SAS</a> * */ public class ExcelReader implements FileParser { private Workbook workbook; private Sheet activesheet; private Iterator<Row> rowIterator; /** * Opens the specific file, and the active workbook, * * @param data reader * @throws IOException if any problem reading the file * @throws InvalidFormatException if any issue is encountered during parsing */ public ExcelReader(InputStream data) throws IOException, InvalidFormatException { workbook = WorkbookFactory.create(data); activesheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); rowIterator = activesheet.rowIterator(); } /** * @return the index of the active worksheet */ public int getActiveSheetIndex() { return workbook.getActiveSheetIndex(); } /** * get the list of sheet names * * @return the list of sheet names * @throws IOException if any issue is encountered accessing the file * @throws InvalidFormatException if file is not of the correct format */ public String[] getSheetNameList() throws IOException, InvalidFormatException { ArrayList<String> sheetnames = new ArrayList<String>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { sheetnames.add(workbook.getSheetAt(i).getSheetName()); } return sheetnames.toArray(new String[0]); } /** * put as active sheet the sheet with the given name * * @param sheetname name of the sheet * @throws IOException if any issue is encountered accessing the file * @throws InvalidFormatException if file is not of the correct format */ public void GoToSheet(String sheetname) throws IOException, InvalidFormatException { Sheet potentialnewsheet = workbook.getSheet(sheetname); if (potentialnewsheet != null) { activesheet = potentialnewsheet; rowIterator = activesheet.rowIterator(); } } /** * Reads the giving cell, and provides the best object possible. Is able to read * the value of fields that are formulas * * @param cell * @return the following objects * <ul> * <li>a string in case the cell is text</li> * <li>a date in case the cell is a date</li> * <li>A double in case it is a number</li> * <li>null in case it is another type</li> * </ul> */ public Object cellToObject(Cell cell) { switch (cell.getCellType()) { case BOOLEAN: return null; case STRING: return cell.getStringCellValue(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } case FORMULA: switch (cell.getCachedFormulaResultType()) { case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } case STRING: return cell.getRichStringCellValue().getString(); default: return null; } case BLANK: return null; default: return null; } } @Override public Object[] parseOneLine() throws IOException { if (rowIterator.hasNext()) { Row rowtoread = rowIterator.next(); ArrayList<Object> rowcontent = new ArrayList<Object> (); for (int i = 0; i < rowtoread.getLastCellNum(); i++) { Cell cell = rowtoread.getCell(i); if (cell == null) rowcontent.add(null); if (cell != null) rowcontent.add(cellToObject(cell)); } return rowcontent.toArray(); } return null; } @Override public void close() throws IOException { workbook.close(); } @Override protected void finalize() throws Throwable { super.finalize(); workbook.close(); } }