Package csv.impl

Class ExcelReader

All Implemented Interfaces:
TableReader, java.lang.Iterable<java.lang.Object[]>, java.util.Iterator<java.lang.Object[]>

public class ExcelReader
extends AbstractStreamTableReader
Implements Excel reading.

This class reads Excel sheets like a stream, meaning delivering rows one by one from the current sheet. Use this reader if you want to load an Excel file by creating a File and passing it to the constructor.

Example:

java.io.File f = new java.io.File("excel-test.xls");
ExcelReader in = new ExcelReader(f);
while (in.hasNext()) {
    Object columns[] = in.next();
    // Do something here
}
in.close();
Author:
RalphSchuster
See Also:
selectSheet(int), selectSheet(String)
  • Constructor Details

    • ExcelReader

      public ExcelReader()
      Default constructor.
    • ExcelReader

      public ExcelReader​(java.io.File file) throws java.io.FileNotFoundException
      Constructor for reading from a file.
      Parameters:
      file - file to read from
      Throws:
      java.io.FileNotFoundException - when file does not exist
    • ExcelReader

      public ExcelReader​(java.io.InputStream in)
      Constructor to read from an existing stream.
      Parameters:
      in - input stream to be used
    • ExcelReader

      public ExcelReader​(org.apache.poi.ss.usermodel.Workbook workbook)
      Constructor to read from an existing workbook.
      Parameters:
      workbook - the workbook be used
    • ExcelReader

      public ExcelReader​(java.lang.String file) throws java.io.FileNotFoundException
      Constructor for reading from a file.
      Parameters:
      file - file to read from
      Throws:
      java.io.FileNotFoundException - when file does not exist
  • Method Details

    • open

      public void open()
      Opens the stream by retrieving the workbook and selecting the first sheet.
      Specified by:
      open in interface TableReader
      Overrides:
      open in class AbstractTableReader
      See Also:
      AbstractTableReader.open()
    • getWorkbook

      public org.apache.poi.ss.usermodel.Workbook getWorkbook()
      Returns the workbook.
      Returns:
      workbook
    • isSkipBlankRows

      public boolean isSkipBlankRows()
      Returns whether blank rows will be skipped or not while reading.
      Returns:
      true when blank rows are skipped (default), false otherwise
    • setSkipBlankRows

      public void setSkipBlankRows​(boolean skipBlankRows)
      Sets whether blank rows will be skipped or not while reading.
      Parameters:
      skipBlankRows - true when blank rows are skipped (default), false otherwise
    • computeMaxColumnCount

      public int computeMaxColumnCount()
      Computes the max row length of any rows in this sheet.
      Returns:
      int length
    • selectSheet

      public org.apache.poi.ss.usermodel.Sheet selectSheet​(java.lang.String name)
      Select the given sheet to be read from.
      Parameters:
      name - name of sheet
      Returns:
      sheet selected
    • selectSheet

      public org.apache.poi.ss.usermodel.Sheet selectSheet​(org.apache.poi.ss.usermodel.Sheet sheet)
      Select the given sheet to be read from.
      Parameters:
      sheet - sheet to be selected
      Returns:
      sheet selected
    • getNumRows

      public int getNumRows()
      Returns the number of rows in the current sheet.
      Returns:
      number of rows in sheet or -1 if no sheet is selected
      Since:
      2.9.1
    • selectSheet

      public org.apache.poi.ss.usermodel.Sheet selectSheet​(int index)
      Select the given sheet to be read from.
      Parameters:
      index - index of sheet
      Returns:
      sheet selected
    • getSheet

      public org.apache.poi.ss.usermodel.Sheet getSheet()
      Returns the current sheet.
      Returns:
      the current sheet.
    • getLastExcelRow

      public org.apache.poi.ss.usermodel.Row getLastExcelRow()
      Returns the last delivered row. This is the row delivered by last call to next().
      Returns:
      the last row delivered by next()
    • reset

      public void reset()
      Resets the reader by resetting the current row index
      Specified by:
      reset in interface TableReader
      Overrides:
      reset in class AbstractStreamTableReader
      See Also:
      AbstractStreamTableReader.reset(), AbstractTableReader.getRowCount()
    • hasNext

      public boolean hasNext()
      Returns whether there is a row to be read in the current sheet. This implementation stops reading when last row from a sheet was read. You might need to manually select the next sheet if you want to read more rows from other sheets.
      Returns:
      true if a row is available in current sheet.
      See Also:
      Iterator.hasNext(), selectSheet(int)
    • next

      public java.lang.Object[] next()
      Returns the next row. This method increases the internal row index and delivers the next row in the sheet. Values in the array are Java objects depending on the cell type. If the cell contained a formula, the formula is evaluated before returning the row.
      Returns:
      values in row
      See Also:
      Iterator.next(), AbstractTableReader.getRowCount()
    • getValues

      public java.lang.Object[] getValues​(int rowNum)
      Returns the row at the given index. Values in the array are Java objects depending on the cell type. If the cell contained a formula, the formula is evaluated before returning the row.
      Parameters:
      rowNum - row index to read
      Returns:
      values of row
    • getValues

      public java.lang.Object[] getValues​(org.apache.poi.ss.usermodel.Row row)
      Returns the row as Java objects. Values in the array are Java objects depending on the cell type. If the cell contained a formula, the formula is evaluated before returning the row.
      Parameters:
      row - row to read
      Returns:
      values in row
    • getValue

      public java.lang.Object getValue​(int rownum, int cellNum)
      Returns the value of the specified cell. If the cell contained a formula, the formula is evaluated before returning the row.
      Parameters:
      rownum - row index
      cellNum - column index
      Returns:
      value of cell
    • getValue

      public java.lang.Object getValue​(org.apache.poi.ss.usermodel.Row row, int cellNum)
      Returns the value of the specified cell. If the cell contained a formula, the formula is evaluated before returning the row.
      Parameters:
      row - row object
      cellNum - column index
      Returns:
      value of cell
    • getValue

      public java.lang.Object getValue​(org.apache.poi.ss.usermodel.Cell cell)
      Returns the value of the specified cell. If the cell contained a formula, the formula is evaluated before returning the row.
      Parameters:
      cell - cell object
      Returns:
      value of cell
    • evaluateCellValue

      public java.lang.Object evaluateCellValue​(org.apache.poi.ss.usermodel.Cell cell)
      Returns the evaluated cell content. This assumes the cell contains a formula.
      Parameters:
      cell - cell to evaluate
      Returns:
      cell value
    • getFormulaEvaluator

      public org.apache.poi.ss.usermodel.FormulaEvaluator getFormulaEvaluator()
      Returns a formula evaluator for the current workbook. This is for convinience.
      Returns:
      the formula evaluator
    • isEvaluateFormulas

      public boolean isEvaluateFormulas()
      Returns whether formulas shall be evaluated or not (default is true).
      Returns:
      true when formulas are evaluated
    • setEvaluateFormulas

      public void setEvaluateFormulas​(boolean evaluateFormulas)
      Sets whether formulas shall be evaluated or not (default is true).
      Parameters:
      evaluateFormulas - true or false
    • readHeaderRow

      protected void readHeaderRow()
      Reads the header row from next line.
      Overrides:
      readHeaderRow in class AbstractTableReader
      See Also:
      AbstractTableReader.readHeaderRow()
    • retrieveNextRow

      protected void retrieveNextRow()
      Retrieves the next row from the current sheet. The row is then internally stored for evaluation of hasNext() and next(). Blank rows are skipped when isSkipBlankRows() return true.
    • rowHasOnlyBlankCells

      protected boolean rowHasOnlyBlankCells​(org.apache.poi.ss.usermodel.Row row)
      Checks whether row has only blank cells. The method is called from retrieveNextRow().
      Parameters:
      row - the row to check
      Returns:
      boolean when the row has only blank cells
    • getOrCreateRow

      protected org.apache.poi.ss.usermodel.Row getOrCreateRow​(int rowNum)
      Ensures that the sheet contains a row at the given index.
      Parameters:
      rowNum - index of row
      Returns:
      the row from the sheet or a new blank row