Read specific sheet using spring batch?

Question

What is the way of reading a specific sheet from an Excel file using spring-batch-excel?

Specifically, I want to parse different sheets within an Excel file in a different manner, using a org.springframework.batch.item.excel.poi.PoiItemReader.

I can't see how to do this with the PoiItemReader, in that is appears to read each sheet in the document. Is there a way to handle sheets differently in the row mapper perhaps? Is it possible without writing a custom POI reader?

Answer 1

No way with out writing custom reader

            import java.util.Iterator;
            import java.util.LinkedList;
            import java.util.List;

            import org.apache.poi.ss.usermodel.Cell;
            import org.apache.poi.ss.usermodel.CellType;
            import org.apache.poi.ss.usermodel.DataFormatter;
            import org.apache.poi.ss.usermodel.FormulaEvaluator;
            import org.apache.poi.ss.usermodel.Row;
            import org.springframework.batch.extensions.excel.Sheet;
            import org.springframework.lang.Nullable;

            public class PoiSheet implements Sheet {

                private final DataFormatter dataFormatter = new DataFormatter();

                private final org.apache.poi.ss.usermodel.Sheet delegate;

                private final int numberOfRows;

                private final String name;

                private FormulaEvaluator evaluator;

                /**
                * Constructor which takes the delegate sheet.
                * @param delegate the apache POI sheet
                */
                PoiSheet(final org.apache.poi.ss.usermodel.Sheet delegate) {
                    super();
                    this.delegate = delegate;
                    this.numberOfRows = this.delegate.getLastRowNum() + 1;
                    this.name = this.delegate.getSheetName();
                }

                /**
                * {@inheritDoc}
                */
                @Override
                public int getNumberOfRows() {
                    return this.numberOfRows;
                }

                /**
                * {@inheritDoc}
                */
                @Override
                public String getName() {
                    return this.name;
                }

                /**
                * {@inheritDoc}
                */
                @Override
                @Nullable
                public String[] getRow(final int rowNumber) {
                    final Row row = this.delegate.getRow(rowNumber);
                    return map(row);
                }

                @Nullable
                private String[] map(Row row) {
                    if (row == null) {
                        return null;
                    }
                    final List<String> cells = new LinkedList<>();
                    final int numberOfColumns = row.getLastCellNum();

                    for (int i = 0; i < numberOfColumns; i++) {
                        Cell cell = row.getCell(i);
                        CellType cellType = cell.getCellType();
                        if (cellType == CellType.FORMULA) {
                            cells.add(this.dataFormatter.formatCellValue(cell, getFormulaEvaluator()));
                        }
                        else {
                            cells.add(this.dataFormatter.formatCellValue(cell));
                        }
                    }
                    return cells.toArray(new String[0]);
                }

                /**
                * Lazy getter for the {@code FormulaEvaluator}. Takes some time to create an
                * instance, so if not necessary don't create it.
                * @return the {@code FormulaEvaluator}
                */
                private FormulaEvaluator getFormulaEvaluator() {
                    if (this.evaluator == null) {
                        this.evaluator = this.delegate.getWorkbook().getCreationHelper().createFormulaEvaluator();
                    }
                    return this.evaluator;
                }

                @Override
                public Iterator<String[]> iterator() {
                    return new Iterator<String[]>() {
                        private final Iterator<Row> delegateIter = PoiSheet.this.delegate.iterator();

                        @Override
                        public boolean hasNext() {
                            return this.delegateIter.hasNext();
                        }

                        @Override
                        public String[] next() {
                            return map(this.delegateIter.next());
                        }
                    };
                }

            }

Excel Reader

    import java.io.File;
    import java.io.FileNotFoundException;
    import java.io.InputStream;

    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.springframework.batch.extensions.excel.AbstractExcelItemReader;
    import org.springframework.batch.extensions.excel.Sheet;
    import org.springframework.core.io.Resource;

    public class ExcelSheetItemReader <T> extends AbstractExcelItemReader<T> {

        private Workbook workbook;

        private InputStream inputStream;
        
        private int sheetIndex = 0;

        @Override
        protected Sheet getSheet(final int sheet) {
            return new PoiSheet(this.workbook.getSheetAt(sheetIndex));
        }

        @Override
        protected int getNumberOfSheets() {
            return 1;
        }

        @Override
        protected void doClose() throws Exception {
            super.doClose();
            if (this.inputStream != null) {
                this.inputStream.close();
                this.inputStream = null;
            }

            if (this.workbook != null) {
                this.workbook.close();
                this.workbook = null;
            }
        }

        /**
        * Open the underlying file using the {@code WorkbookFactory}. Prefer {@code File}
        * based access over an {@code InputStream}. Using a file will use fewer resources
        * compared to an input stream. The latter will need to cache the whole sheet
        * in-memory.
        * @param resource the {@code Resource} pointing to the Excel file.
        * @param password the password for opening the file
        * @throws Exception is thrown for any errors.
        */
        @Override
        protected void openExcelFile(final Resource resource, String password) throws Exception {

            try {
                File file = resource.getFile();
                this.workbook = WorkbookFactory.create(file, password, false);
            }
            catch (FileNotFoundException ex) {
                this.inputStream = resource.getInputStream();
                this.workbook = WorkbookFactory.create(this.inputStream, password);
            }
            this.workbook.setMissingCellPolicy(Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        }

        public int getSheetIndex() {
            return sheetIndex;
        }

        public void setSheetIndex(int sheetIndex) {
            this.sheetIndex = sheetIndex;
        }
    }

Another example can be found here