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.daphne.es.showcase.excel.service.ExcelDataService.java

License:Apache License

@Async
public void importExcel2007(final User user, final InputStream is) {

    ExcelDataService proxy = ((ExcelDataService) AopContext.currentProxy());

    BufferedInputStream bis = null;
    try {//from w ww  .ja  v a2s .  c  o m
        long beginTime = System.currentTimeMillis();

        List<ExcelData> dataList = Lists.newArrayList();

        bis = new BufferedInputStream(is);
        OPCPackage pkg = OPCPackage.open(bis);
        XSSFReader r = new XSSFReader(pkg);

        XMLReader parser = XMLReaderFactory.createXMLReader();
        ContentHandler handler = new Excel2007ImportSheetHandler(proxy, dataList, batchSize);
        parser.setContentHandler(handler);

        Iterator<InputStream> sheets = r.getSheetsData();
        while (sheets.hasNext()) {
            InputStream sheet = null;
            try {
                sheet = sheets.next();
                InputSource sheetSource = new InputSource(sheet);
                parser.parse(sheetSource);
            } catch (Exception e) {
                throw e;
            } finally {
                IOUtils.closeQuietly(sheet);
            }
        }

        //??batchSize?
        if (dataList.size() > 0) {
            proxy.doBatchSave(dataList);
        }

        long endTime = System.currentTimeMillis();
        Map<String, Object> context = Maps.newHashMap();
        context.put("seconds", (endTime - beginTime) / 1000);
        notificationApi.notify(user.getId(), "excelImportSuccess", context);
    } catch (Exception e) {
        log.error("excel import error", e);
        Map<String, Object> context = Maps.newHashMap();
        context.put("error", e.getMessage());
        notificationApi.notify(user.getId(), "excelImportError", context);
    } finally {
        IOUtils.closeQuietly(bis);
    }
}

From source file:com.dhenton9000.excel.ExcelParser.java

public SheetResults parse(InputStream inputStream) throws Exception {

    OPCPackage pkg = OPCPackage.open(inputStream);
    XSSFReader reader = new XSSFReader(pkg);
    this.sst = reader.getSharedStringsTable();

    SAXParserFactory saxFactory = SAXParserFactory.newInstance();
    SAXParser saxParser = saxFactory.newSAXParser();
    XMLReader parser = saxParser.getXMLReader();
    parser.setContentHandler(this);

    // There should only be one sheet
    final Iterator<InputStream> it = reader.getSheetsData();
    final InputStream sheet = it.next();

    final InputSource sheetSource = new InputSource(sheet);
    parser.parse(sheetSource);//  ww  w  .j a v  a 2s  . co  m
    sheet.close();

    return getSheetResults();

}

From source file:com.github.poi.LoadPasswordProtectedXlsxStreaming.java

License:Apache License

public static void main(String[] args) {
    try {/*from w  ww  .  ja  v  a 2s . c  om*/
        if (args.length != 2) {
            throw new Exception("Expected 2 params: filename and password");
        }
        XlsxUtils.checkTempFiles();
        String filename = args[0];
        String password = args[1];
        try (FileInputStream fis = new FileInputStream(filename);
                InputStream unencryptedStream = XlsxUtils.decrypt(fis, password);
                AesZipFileZipEntrySource source = AesZipFileZipEntrySource
                        .createZipEntrySource(unencryptedStream);
                OPCPackage pkg = OPCPackage.open(source)) {
            XSSFReader reader = new XSSFReader(pkg);
            SheetIterator iter = (SheetIterator) reader.getSheetsData();
            int count = 0;
            while (iter.hasNext()) {
                iter.next();
                count++;
            }
            System.out.println("sheet count: " + count);
        }
        XlsxUtils.checkTempFiles();
    } catch (Throwable t) {
        t.printStackTrace();
    }
}

From source file:com.googlecode.sqlsheet.stream.XlsxSheetIterator.java

License:Apache License

@Override
protected void postConstruct() throws SQLException {
    try {/*from  w w  w.  ja  v  a  2s.c om*/
        //Open and pre process XLSX file
        xlsxPackage = OPCPackage.open(getFileName().getPath(), PackageAccess.READ);
        strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
        styles = xssfReader.getStylesTable();
        //Find appropriate sheet
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (iter.hasNext()) {
            InputStream stream = iter.next();
            String currentSheetName = iter.getSheetName();
            if (currentSheetName.equalsIgnoreCase(getSheetName())
                    || ("\"" + currentSheetName + "\"").equalsIgnoreCase(getSheetName())) {
                handler = new XSSFSheetEventHandler(styles, strings);
                XMLInputFactory factory = XMLInputFactory.newInstance();
                reader = factory.createXMLEventReader(stream);
                //Start sheet processing
                while (reader.hasNext() && getCurrentSheetRowIndex() == 0) {
                    processNextEvent();
                }
                processNextRecords();
            }
        }
    } catch (Exception e) {
        throw new SQLException(e.getMessage(), e);
    }
}

From source file:com.jkoolcloud.tnt4j.streams.inputs.ExcelSXSSFRowStream.java

License:Apache License

/**
 * Reads XSSF (XLXS) format excel file using Apache POI streaming SXSSF API.
 *
 * @param xlsxFile/*from  www . j a  va 2 s  .  c  o  m*/
 *            excel XSSF format file to read
 *
 * @throws IOException
 *             if excel file or workbook can't be read
 * @throws SAXException
 *             if file contained XML reading fails
 * @throws OpenXML4JException
 *             if file contained XML reading fails
 */
protected void readXLXS(File xlsxFile) throws IOException, SAXException, OpenXML4JException {
    try (OPCPackage xlsxPackage = OPCPackage.open(xlsxFile, PackageAccess.READ)) {
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(xlsxPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator sIter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sIter.hasNext()) {
            try (InputStream sStream = sIter.next()) {
                String sheetName = sIter.getSheetName();
                boolean match = sheetNameMatcher == null || sheetNameMatcher.matcher(sheetName).matches();
                if (!match) {
                    continue;
                }

                SXSSFSheet sheet = new SXSSFSheet(new SXSSFWorkbook(), null);
                processSXSSFSheet(styles, strings, new XLSXSheetContentHandler(this, sheet), sStream);
            }
        }
    }
}

From source file:com.lfwer.common.XLSX2CSV.java

License:Apache License

/**
 * Initiates the processing of the XLS workbook file to CSV.
 *
 * @throws IOException/*  w  w  w .j a  v a 2 s.  com*/
 *             If reading the data from the package fails.
 * @throws SAXException
 *             if parsing the XML data fails.
 */
public void process() throws IOException, OpenXML4JException, SAXException {
    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
    XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
    int index = 0;
    while (iter.hasNext()) {
        InputStream stream = iter.next();
        String sheetName = iter.getSheetName();
        this.output.println();
        this.output.println(sheetName + " [index=" + index + "]:");
        processSheet(styles, strings, new SheetToCSV(), stream);
        stream.close();
        ++index;
    }
}

From source file:com.ljh.excel.parser.FromHowTo.java

License:Apache License

public static void processAllSheets(String filename) throws Exception {
    OPCPackage pkg = OPCPackage.open(filename);
    XSSFReader r = new XSSFReader(pkg);
    SharedStringsTable sst = r.getSharedStringsTable();

    XMLReader parser = fetchSheetParser(sst);

    Iterator<InputStream> sheets = r.getSheetsData();
    while (sheets.hasNext()) {
        System.out.println("Processing new sheet:\n");
        InputStream sheet = sheets.next();
        InputSource sheetSource = new InputSource(sheet);
        parser.parse(sheetSource);/*  w w w  .  ja  v  a 2  s  .c om*/
        sheet.close();
        System.out.println("");
    }
}

From source file:com.myjeeva.poi.ExcelReader.java

License:Open Source License

private void read(int sheetNumber) throws RuntimeException {
    ReadOnlySharedStringsTable strings;//www . j  av  a  2s  .  c o  m
    try {
        strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator worksheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

        for (int sheetIndex = 0; worksheets.hasNext(); sheetIndex++) {
            InputStream stream = worksheets.next();
            if (null != sheetCallback)
                this.sheetCallback.startSheet(sheetIndex, worksheets.getSheetName());

            if ((READ_ALL == sheetNumber) || (sheetIndex == sheetNumber)) {
                readSheet(styles, strings, stream);
            }
            IOUtils.closeQuietly(stream);

            if (null != sheetCallback)
                this.sheetCallback.endSheet();
        }
    } catch (IOException ioe) {
        LOG.error(ioe.getMessage(), ioe.getCause());
    } catch (SAXException se) {
        LOG.error(se.getMessage(), se.getCause());
    } catch (OpenXML4JException oxe) {
        LOG.error(oxe.getMessage(), oxe.getCause());
    } catch (ParserConfigurationException pce) {
        LOG.error(pce.getMessage(), pce.getCause());
    }
}

From source file:com.myjeeva.poi.ExcelReader.java

License:Open Source License

private void read(String sheetName) throws RuntimeException {
    ReadOnlySharedStringsTable strings;//  ww w .ja va2 s . co  m
    try {
        strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator worksheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

        for (int sheetIndex = 0; worksheets.hasNext(); sheetIndex++) {
            InputStream stream = worksheets.next();
            if (null != sheetCallback)
                this.sheetCallback.startSheet(sheetIndex, worksheets.getSheetName());

            if (sheetName.equals(worksheets.getSheetName())) {
                readSheet(styles, strings, stream);
            }
            IOUtils.closeQuietly(stream);

            if (null != sheetCallback)
                this.sheetCallback.endSheet();
        }
    } catch (IOException ioe) {
        LOG.error(ioe.getMessage(), ioe.getCause());
    } catch (SAXException se) {
        LOG.error(se.getMessage(), se.getCause());
    } catch (OpenXML4JException oxe) {
        LOG.error(oxe.getMessage(), oxe.getCause());
    } catch (ParserConfigurationException pce) {
        LOG.error(pce.getMessage(), pce.getCause());
    }
}

From source file:com.myjeeva.poi.ExcelXSSFRowCallbackHandler.java

License:Apache License

/**
 * Parses the file, passing each row to the given callback.
 * At the end closes the opc package and underling input stream.
 *//*from w  ww  . j av  a 2  s.  c  o m*/
public void parse() throws Exception {

    XSSFReader reader = new XSSFReader(this.opcPackage);

    StylesTable styles = reader.getStylesTable();
    ReadOnlySharedStringsTable sharedStrings = new ReadOnlySharedStringsTable(this.opcPackage);

    ContentHandler handler = new XSSFSheetXMLHandler(styles, sharedStrings,
            new ExcelXSSFRowCallbackSheetContentsHandler(this.rowCallback), true);

    XMLReader parser = XMLReaderFactory.createXMLReader();
    parser.setContentHandler(handler);

    InputStream sheetInputStream = reader.getSheetsData().next();

    try {

        parser.parse(new InputSource(sheetInputStream));

    } finally {

        IOUtils.closeQuietly(sheetInputStream);
        this.opcPackage.close();

    }

}