Example usage for org.apache.poi.xssf.eventusermodel XSSFReader getSheetsData

List of usage examples for org.apache.poi.xssf.eventusermodel XSSFReader getSheetsData

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.eventusermodel.*
  5. XSSFReader
  6. getSheetsData

Introduction

In this page you can find the example usage for org.apache.poi.xssf.eventusermodel XSSFReader getSheetsData.

Prototype

public Iterator<InputStream> getSheetsData() throws IOException, InvalidFormatException

Source Link

Document

Returns an Iterator which will let you get at all the different Sheets in turn.

Usage

From source file:com.opendoorlogistics.core.tables.io.XmlParserLoader.java

License:Open Source License

private SchemaSheetInformation importSchema(XSSFReader r, StylesTable styles, ReadOnlySharedStringsTable sst) {
    try {/* w w  w .  j ava  2 s.  co  m*/
        XSSFReader.SheetIterator it = (XSSFReader.SheetIterator) r.getSheetsData();
        while (it.hasNext()) {
            try (InputStream sheet = it.next()) {
                String name = it.getSheetName();
                if (Strings.equalsStd(PoiIO.SCHEMA_SHEET_NAME, name)) {
                    InputSource sheetSource = new InputSource(sheet);
                    ReadSchemaSheet readSchemaSheet = new ReadSchemaSheet();
                    parseSheet(styles, sst, sheetSource, readSchemaSheet);
                    return readSchemaSheet.finish(report);
                }
            }
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

    return null;
}

From source file:com.opendoorlogistics.core.tables.io.XmlParserLoader.java

License:Open Source License

/**
 * @param pkg//from w w  w .j ava  2s  . com
 * @throws IOException
 * @throws OpenXML4JException
 * @throws InvalidFormatException
 * @throws SAXException
 */
private void importOPCPackage(OPCPackage pkg)
        throws IOException, OpenXML4JException, InvalidFormatException, SAXException {
    XSSFReader r = new XSSFReader(pkg);
    StylesTable styles = r.getStylesTable();
    ReadOnlySharedStringsTable sst = new ReadOnlySharedStringsTable(pkg);

    SchemaSheetInformation schema = importSchema(r, styles, sst);

    // read table definitions first
    XSSFReader.SheetIterator it = (XSSFReader.SheetIterator) r.getSheetsData();
    ArrayList<Integer> tableIndices = new ArrayList<>();
    ArrayList<Integer> headerRows = new ArrayList<>();
    while (it.hasNext()) {
        try (InputStream sheet = it.next()) {
            String name = it.getSheetName();

            if (!Strings.equalsStd(PoiIO.SCHEMA_SHEET_NAME, name)) {
                baseMessage = "Loading Excel, analysing sheet " + name;
                postStatus(null);

                InputSource sheetSource = new InputSource(sheet);
                ReadTableDefinition rtd = new ReadTableDefinition(name, schema != null ? schema.schema : null,
                        true);
                parseSheet(styles, sst, sheetSource, rtd);
                tableIndices.add(ds.getTableCount());
                headerRows.add(rtd.getHeaderRow());
                DatastoreCopier.copyTableDefinition(rtd.createTableDefinition(), ds);
            }
        }
    }

    // Then actual tables
    it = (XSSFReader.SheetIterator) r.getSheetsData();
    int i = 0;
    while (it.hasNext()) {
        try (InputStream sheet = it.next()) {
            InputSource sheetSource = new InputSource(sheet);
            String name = it.getSheetName();
            if (!Strings.equalsStd(PoiIO.SCHEMA_SHEET_NAME, name)) {
                baseMessage = "Loading sheet " + name + " into memory";
                postStatus(null);

                ODLTable table = ds.getTableAt(i);
                int headerRow = headerRows.get(i);
                ReadIntoTableHandler readerHandler = new ReadIntoTableHandler(table, headerRow + 1,
                        Integer.MAX_VALUE);
                parseSheet(styles, sst, sheetSource, readerHandler);
                i++;
            }
        }

    }
}

From source file:com.qihang.winter.poi.excel.imports.sax.SaxReadExcel.java

License:Apache License

private <T> List<T> readExcel(OPCPackage opcPackage, Class<?> pojoClass,
        com.qihang.winter.poi.excel.entity.ImportParams params, ISaxRowRead rowRead,
        com.qihang.winter.poi.handler.inter.IExcelReadRowHanlder hanlder) {
    try {/*from www.j  ava2s  . com*/
        XSSFReader xssfReader = new XSSFReader(opcPackage);
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        if (rowRead == null) {
            rowRead = new com.qihang.winter.poi.excel.imports.sax.parse.SaxRowRead(pojoClass, params, hanlder);
        }
        XMLReader parser = fetchSheetParser(sst, rowRead);
        Iterator<InputStream> sheets = xssfReader.getSheetsData();
        int sheetIndex = 0;
        while (sheets.hasNext() && sheetIndex < params.getSheetNum()) {
            sheetIndex++;
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        }
        return rowRead.getList();
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw new com.qihang.winter.poi.exception.excel.ExcelImportException("SAX?");
    }
}

From source file:com.rcv.StreamingCVRReader.java

License:Open Source License

List<CastVoteRecord> parseCVRFile(List<CastVoteRecord> castVoteRecords, Set<String> precinctIDs)
        throws UnrecognizedCandidatesException, OpenXML4JException, SAXException, IOException {

    // cache the cvr list so it is accessible in callbacks
    cvrList = castVoteRecords;/*from w  ww .  ja  v  a  2s  .  c  om*/
    // cache precinctIDs set so it is accessible in callbacks
    this.precinctIDs = precinctIDs;

    // open the zip package
    OPCPackage pkg = OPCPackage.open(excelFilePath);
    // pull out strings
    ReadOnlySharedStringsTable sharedStrings = new ReadOnlySharedStringsTable(pkg);
    // XSSF reader is used to extract styles data
    XSSFReader xssfReader = new XSSFReader(pkg);
    // styles data is used for creating ContentHandler
    StylesTable styles = xssfReader.getStylesTable();
    // SheetContentsHandler is used to handle parsing callbacks
    SheetContentsHandler sheetContentsHandler = new SheetContentsHandler() {
        // function: startRow
        // purpose: startRow callback handler during xml parsing
        // param: i the row which has started
        @Override
        public void startRow(int i) {
            if (i >= firstVoteRowIndex) {
                beginCVR();
            }
        }

        // function: endRow
        // purpose: endRow callback handler during xml parsing
        // row has completed, we will create a new cvr object
        // param: i the row which has ended
        @Override
        public void endRow(int i) {
            if (i >= firstVoteRowIndex) {
                endCVR();
            }
        }

        // function: cell
        // purpose: cell callback handler during xml parsing
        // param: s cell address encoded as col,row
        // param: s1 cell data
        // param: xssfComment additional cell data (unused)
        @Override
        public void cell(String s, String s1, XSSFComment xssfComment) {
            // address contains the row and col of this cell
            Pair<Integer, Integer> address = getCellAddress(s);
            int col = address.getKey();
            int row = address.getValue();
            if (row >= firstVoteRowIndex) {
                cvrCell(col, s1);
            }
        }

        // function: headerFooter
        // purpose: header footer callback from xml parsing - unused
        // param: s header footer data
        // param: b header footer data
        // param: s1 header footer data
        @Override
        public void headerFooter(String s, boolean b, String s1) {
            Logger.log(Level.WARNING, String.format("Unexpected XML data: %s %b %s", s, b, s1));
        }
    };

    // create the ContentHandler to handle parsing callbacks
    ContentHandler handler = new XSSFSheetXMLHandler(styles, sharedStrings, sheetContentsHandler, true);

    // create the XML reader and set content handler
    XMLReader xmlReader = XMLReaderFactory.createXMLReader();
    xmlReader.setContentHandler(handler);
    // trigger parsing
    xmlReader.parse(new InputSource(xssfReader.getSheetsData().next()));

    // throw if there were any unrecognized candidates -- this is considered bad
    if (this.unrecognizedCandidateCounts.size() > 0) {
        throw new UnrecognizedCandidatesException(unrecognizedCandidateCounts);
    }

    // return the input list with additions
    return cvrList;
}

From source file:com.sitech.chn.s98800.s98820.s882q.util.XLSXCovertCSVReader.java

License:Apache License

/**
 *  /*w  w w. j  a  v  a2s  .  c  o  m*/
 * 
 * @throws IOException
 * @throws OpenXML4JException
 * @throws ParserConfigurationException
 * @throws SAXException
 */
public List<List<String[]>> process()
        throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {

    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
    XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
    List<String[]> list = null;
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
    List<List<String[]>> listAll = new ArrayList<List<String[]>>();
    while (iter.hasNext()) {
        InputStream stream = iter.next();
        String sheetNameTemp = iter.getSheetName();
        list = processSheet(styles, strings, stream);
        listAll.add(list);
        stream.close();
    }
    return listAll;
}

From source file:com.sonicle.webtop.contacts.io.input.ContactExcelFileReader.java

License:Open Source License

private void readXlsxContacts(File file, BeanHandler beanHandler) throws IOException, FileReaderException {
    OPCPackage opc = null;/*  w  w w.  j  av a 2 s  .  co m*/
    HashMap<String, Integer> columnIndexes = listXlsxColumnIndexes(file);
    XlsRowHandler rowHandler = new XlsRowHandler(this, columnIndexes, beanHandler);

    try {
        opc = OPCPackage.open(file, PackageAccess.READ);
        XSSFReader reader = new XSSFReader(opc);
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc);
        StylesTable styles = reader.getStylesTable();

        XlsxRowsHandler rowsHandler = null;
        XSSFReader.SheetIterator sit = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sit.hasNext()) {
            InputStream is = null;
            try {
                is = sit.next();
                if (StringUtils.equals(sit.getSheetName(), sheet)) {
                    XMLReader xmlReader = SAXHelper.newXMLReader();
                    rowsHandler = new XlsxRowsHandler(is, headersRow, firstDataRow, lastDataRow, rowHandler);
                    ContentHandler xhandler = new XSSFSheetXMLHandler(styles, null, strings, rowsHandler, fmt,
                            false);
                    xmlReader.setContentHandler(xhandler);
                    xmlReader.parse(new InputSource(is));
                }
            } catch (SAXException | ParserConfigurationException ex) {
                throw new FileReaderException(ex, "Error processing file content");
            } catch (NullPointerException ex) {
                // Thrown when stream is forcibly closed. Simply ignore this!
            } finally {
                IOUtils.closeQuietly(is);
            }
            if (rowsHandler != null)
                break;
        }

    } catch (OpenXML4JException | SAXException ex) {
        throw new FileReaderException(ex, "Error opening file");
    } finally {
        IOUtils.closeQuietly(opc);
    }
}

From source file:com.sonicle.webtop.core.io.input.ExcelFileReader.java

License:Open Source License

public List<String> listXlsxSheets(File file) throws IOException, FileReaderException {
    ArrayList<String> sheets = new ArrayList<>();
    OPCPackage opc = null;// w  ww.  j ava 2  s  .c  o m

    try {
        opc = OPCPackage.open(file, PackageAccess.READ);
        XSSFReader reader = new XSSFReader(opc);
        XSSFReader.SheetIterator sit = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sit.hasNext()) {
            InputStream is = null;
            try {
                is = sit.next();
                String name = sit.getSheetName();
                if (name != null)
                    sheets.add(name);
            } finally {
                IOUtils.closeQuietly(is);
            }
        }
    } catch (OpenXML4JException ex) {
        throw new FileReaderException(ex, "Error opening file");
    } finally {
        IOUtils.closeQuietly(opc);
    }
    return sheets;
}

From source file:com.sonicle.webtop.core.io.input.ExcelFileReader.java

License:Open Source License

public HashMap<String, String> listXlsxColumnNames(File file) throws IOException, FileReaderException {
    OPCPackage opc = null;//from  w ww .  ja v a  2s.  co  m

    try {
        opc = OPCPackage.open(file, PackageAccess.READ);
        XSSFReader reader = new XSSFReader(opc);
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc);
        StylesTable styles = reader.getStylesTable();

        XlsxColumnsHandler columnsHandler = null;
        XSSFReader.SheetIterator sit = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sit.hasNext()) {
            InputStream is = null;
            try {
                is = sit.next();
                if (StringUtils.equals(sit.getSheetName(), sheet)) {
                    XMLReader xmlReader = SAXHelper.newXMLReader();
                    columnsHandler = new XlsxColumnsHandler(is, headersRow, firstDataRow, lastDataRow);
                    ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, columnsHandler, fmt,
                            false);
                    xmlReader.setContentHandler(handler);
                    xmlReader.parse(new InputSource(is));
                }
            } catch (SAXException | ParserConfigurationException ex) {
                throw new FileReaderException(ex, "Error processing file content");
            } catch (NullPointerException ex) {
                // Thrown when stream is forcibly closed. Simply ignore this!
            } finally {
                IOUtils.closeQuietly(is);
            }
            if (columnsHandler != null)
                break;
        }
        return columnsHandler.columnNames;

    } catch (OpenXML4JException | SAXException ex) {
        throw new FileReaderException(ex, "Error opening file");
    } finally {
        IOUtils.closeQuietly(opc);
    }
}

From source file:com.sonicle.webtop.core.io.input.ExcelFileReader.java

License:Open Source License

public HashMap<String, Integer> listXlsxColumnIndexes(File file) throws IOException, FileReaderException {
    OPCPackage opc = null;/*from  w w  w .j a v a  2s .c  o  m*/

    try {
        opc = OPCPackage.open(file, PackageAccess.READ);
        XSSFReader reader = new XSSFReader(opc);
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc);
        StylesTable styles = reader.getStylesTable();

        XlsxColumnsHandler columnsHandler = null;
        XSSFReader.SheetIterator sit = (XSSFReader.SheetIterator) reader.getSheetsData();
        while (sit.hasNext()) {
            InputStream is = null;
            try {
                is = sit.next();
                if (StringUtils.equals(sit.getSheetName(), sheet)) {
                    XMLReader xmlReader = SAXHelper.newXMLReader();
                    columnsHandler = new XlsxColumnsHandler(is, headersRow, firstDataRow, lastDataRow);
                    ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, columnsHandler, fmt,
                            false);
                    xmlReader.setContentHandler(handler);
                    xmlReader.parse(new InputSource(is));
                }
            } catch (SAXException | ParserConfigurationException ex) {
                throw new FileReaderException(ex, "Error processing file content");
            } catch (NullPointerException ex) {
                // Thrown when stream is forcibly closed. Simply ignore this!
            } finally {
                IOUtils.closeQuietly(is);
            }
            if (columnsHandler != null)
                break;
        }
        return columnsHandler.columnIndexes;

    } catch (OpenXML4JException | SAXException ex) {
        throw new FileReaderException(ex, "Error opening file");
    } finally {
        IOUtils.closeQuietly(opc);
    }
}

From source file:com.talend.excel.xssf.event.ExcelReader.java

License:Open Source License

public Object call() throws Exception {
    OPCPackage pkg = null;//from   w w w  .j  a  va 2  s  .c o  m
    try {
        if (fileURL != null) {
            pkg = OPCPackage.open(fileURL);
        } else {
            pkg = PackageHelper.open(is);
        }
        XSSFReader r = new XSSFReader(pkg);

        StylesTable styles = r.getStylesTable();
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
        sheetContentsHandler = new DefaultTalendSheetContentsHandler(cache);
        DataFormatter formatter = new DataFormatter();
        boolean formulasNotResults = false;

        XMLReader parser = XMLReaderFactory.createXMLReader();
        ContentHandler handler = new TalendXSSFSheetXMLHandler(styles, strings, sheetContentsHandler, formatter,
                formulasNotResults);
        parser.setContentHandler(handler);

        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) r.getSheetsData();
        List<InputStream> iss = new ArrayList<InputStream>();
        while (sheets.hasNext()) {
            InputStream sheet = sheets.next();
            String sheetName = sheets.getSheetName();

            boolean match = false;

            for (int i = 0; i < sheetNames.size(); i++) {
                if ((asRegexs.get(i) && sheetName.matches(sheetNames.get(i)))
                        || (!asRegexs.get(i) && sheetName.equals(sheetNames.get(i)))) {
                    match = true;
                    iss.add(sheet);
                    break;
                }
            }

            if (!match) {
                sheet.close();
            }
        }

        if (iss.size() < 1) {
            throw new RuntimeException("No match sheets");
        }

        for (InputStream is : iss) {
            try {
                InputSource sheetSource = new InputSource(is);
                sheetSource.setEncoding(charset);
                parser.parse(sheetSource);
            } finally {
                is.close();
            }
        }

    } finally {
        if (pkg != null) {
            pkg.revert();
        }
        cache.notifyErrorOccurred();
    }
    return null;
}