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

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

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

Introduction

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

Prototype

public StylesTable getStylesTable() throws IOException, InvalidFormatException

Source Link

Document

Opens up the Styles Table, parses it, and returns a handy object for working with cell styles

Usage

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

License:Open Source License

private void read(int sheetNumber) throws RuntimeException {
    ReadOnlySharedStringsTable strings;//  ww  w  .j a  v  a  2 s  .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  va  2 s .  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 (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.
 *//* www .ja v a2 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();

    }

}

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

License:Open Source License

/**
 * @param pkg//from  www. j  a v  a2  s.c o m
 * @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.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 w w.j  a v a 2 s.  c o  m*/
    // 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

/**
 *  //from  w w w .  j  a  va  2  s  . 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  a v 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 HashMap<String, String> listXlsxColumnNames(File file) throws IOException, FileReaderException {
    OPCPackage opc = null;//from w ww .java 2 s  . c  om

    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;// w w w  .j  av a  2 s  .  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;// w  w  w  .  j av  a 2s  . co 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;
}