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

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

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

Introduction

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

Prototype

public XSSFReader(OPCPackage pkg) throws IOException, OpenXML4JException

Source Link

Document

Creates a new XSSFReader, for the given package

Usage

From source file:org.generationcp.middleware.util.PoiEventUserModel.java

License:Open Source License

public void areSheetRowsOverMaxLimit(String filename, int sheetIndex, int maxLimit) throws Exception {

    this.maxLimit = maxLimit;

    OPCPackage pkg = OPCPackage.open(filename);
    XSSFReader r = new XSSFReader(pkg);

    XMLReader parser = this.fetchSheetParser();

    InputStream sheet2 = null;// w ww.j av a  2 s .c om
    // rId2 found by processing the Workbook
    // Seems to either be rId# or rSheet#
    try {
        sheet2 = r.getSheet("rId" + (sheetIndex + 1));
    } catch (Exception e) {
        PoiEventUserModel.LOG.error("Sheet could not be retrieved", e);
    }

    if (sheet2 == null) {
        try {
            sheet2 = r.getSheet("rSheet" + (sheetIndex + 1));
        } catch (Exception e) {
            PoiEventUserModel.LOG.error("Sheet could not be retrieved", e);
        }
    }

    InputSource sheetSource = new InputSource(sheet2);
    parser.parse(sheetSource);
    if (sheet2 != null) {
        sheet2.close();
    }
}

From source file:org.generationcp.middleware.util.PoiEventUserModel.java

License:Open Source License

public void isAnySheetRowsOverMaxLimit(String filename, int maxLimit) throws Exception {

    this.maxLimit = maxLimit;

    OPCPackage pkg = OPCPackage.open(filename);
    XSSFReader r = new XSSFReader(pkg);

    XMLReader parser = this.fetchSheetParser();

    for (int i = 1; i < 10; i++) {

        InputStream sheet = null;
        // rId2 found by processing the Workbook
        // Seems to either be rId# or rSheet#
        try {//  w w  w  . j  a  v a  2 s.c  o m
            sheet = r.getSheet("rId" + i);
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (sheet == null) {
            try {
                sheet = r.getSheet("rSheet" + i);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (sheet != null) {
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            sheet.close();
        } else {
            break;
        }

    }

}

From source file:org.jberet.support.io.ExcelStreamingItemReader.java

License:Open Source License

@Override
protected void initWorkbookAndSheet(final int startRowNumber) throws Exception {
    InputStream workbookDataInputStream = null;
    XMLStreamReader workbookStreamReader = null;

    try {/*from  w  w  w . j av a  2s  . c om*/
        final OPCPackage opcPackage = OPCPackage.open(inputStream);
        final XSSFReader xssfReader = new XSSFReader(opcPackage);
        workbookDataInputStream = xssfReader.getWorkbookData();
        final XMLInputFactory xmlInputFactory = XMLInputFactory.newInstance();
        workbookStreamReader = xmlInputFactory.createXMLStreamReader(workbookDataInputStream);
        sharedStringsTable = xssfReader.getSharedStringsTable();

        /*
        sample sheet element:
        <sheets>
        <sheet name="Movies" sheetId="1" state="visible" r:id="rId2"/>
        <sheet name="Person" sheetId="2" state="visible" r:id="rId3"/>
        </sheets>
         */
        while (workbookStreamReader.hasNext()) {
            if (workbookStreamReader.next() == XMLStreamConstants.START_ELEMENT
                    && "sheet".equals(workbookStreamReader.getLocalName())) {
                final String shn = workbookStreamReader.getAttributeValue(null, "name");
                final String shId = workbookStreamReader.getAttributeValue(null, "sheetId");
                if ((sheetName != null && sheetName.equals(shn))
                        || (sheetName == null && String.valueOf(this.sheetIndex + 1).equals(shId))) {
                    //this is the target sheet
                    final String relationshipId = workbookStreamReader.getAttributeValue(schemaRelationships,
                            "id");
                    sheetInputStream = xssfReader.getSheet(relationshipId);
                    sheetStreamReader = xmlInputFactory.createXMLStreamReader(sheetInputStream);
                    break;
                }
            }
        }
    } finally {
        if (workbookDataInputStream != null) {
            try {
                workbookDataInputStream.close();

            } catch (final Exception e) {
                //ignore
            }
        }
        if (workbookStreamReader != null) {
            try {
                workbookStreamReader.close();
            } catch (final Exception e) {
                //ignore
            }
        }
    }

    /*
    sample row element:
    <row r="1" customFormat="false" ht="15" hidden="false" customHeight="false" outlineLevel="0" collapsed="false">
    <c r="A1" s="0" t="s">
        <v>0</v>
    </c>
    <c r="B1" s="0" t="s">
        <v>1</v>
    </c>
    <c r="C1" s="0" t="s">
        <v>2</v>
    </c>
    <c r="D1" s="0" t="s">
        <v>3</v>
    </c>
    </row>
            
    For inlineStr:
    <c r="A1" t="inlineStr">
    <is>
        <t>Date</t>
    </is>
    </c>
            
    Note: a blank cell does not show up in xml at all. So for list type beanType, need to detect blank cell and add
    null; for map or custom beanType, need to link to the correct header column by r attribute.
     */
    if (header == null) {
        headerMapping = new HashMap<String, String>();
        outerLoop: while (sheetStreamReader.hasNext()) {
            if (sheetStreamReader.next() == XMLStreamConstants.START_ELEMENT
                    && "row".equals(sheetStreamReader.getLocalName())) {
                final int rowNum = Integer.parseInt(sheetStreamReader.getAttributeValue(null, "r"));

                if (headerRow + 1 == rowNum) {
                    // got the header row, next loop through header row cells
                    final List<String> headerVals = new ArrayList<String>();
                    while (sheetStreamReader.hasNext()) {
                        final int event = sheetStreamReader.next();
                        if (event == XMLStreamConstants.START_ELEMENT
                                && "c".equals(sheetStreamReader.getLocalName())) {
                            final String label = getColumnLabel(sheetStreamReader.getAttributeValue(null, "r"));
                            final String value = getCellStringValue();
                            headerVals.add(value);
                            headerMapping.put(label, value);
                        } else if (event == XMLStreamConstants.END_ELEMENT
                                && "row".equals(sheetStreamReader.getLocalName())) {
                            header = headerVals.toArray(new String[headerVals.size()]);
                            currentRowNum = rowNum - 1;
                            break outerLoop;
                        }
                    }
                }
            }
        }
    }

    //fast forward to the start row, which may not immediately follow header row
    while (currentRowNum < startRowNumber - 1 && sheetStreamReader.hasNext()) {
        if (sheetStreamReader.next() == XMLStreamConstants.START_ELEMENT
                && "row".equals(sheetStreamReader.getLocalName())) {
            currentRowNum = Integer.parseInt(sheetStreamReader.getAttributeValue(null, "r")) - 1;
        } else if (sheetStreamReader.next() == XMLStreamConstants.END_ELEMENT
                && "row".equals(sheetStreamReader.getLocalName())) {
            if (currentRowNum >= startRowNumber - 1) {
                break;
            }
        }
    }
}

From source file:org.jreserve.gui.poi.read.xlsx.XlsxReader.java

License:Open Source License

@Override
public T read(File file) throws IOException {
    try {//w w  w  . j a v  a 2s . c om
        pkg = OPCPackage.open(file);
        XSSFReader reader = new XSSFReader(pkg);
        readReader(reader);
        return getResult();
    } catch (Exception ex) {
        String path = file == null ? null : file.getAbsolutePath();
        String msg = String.format("Unabel to read file '%s'!", path);
        logger.log(Level.SEVERE, msg, ex);
        throw new IOException(msg, ex);
    } finally {
        close();
    }
}

From source file:org.pentaho.di.trans.steps.excelinput.staxpoi.StaxPoiWorkbook.java

License:Apache License

private void openFile(OPCPackage pkg, String encoding) throws KettleException {
    InputStream workbookData = null;
    XMLStreamReader workbookReader = null;
    try {/* w  w  w .  j  a va  2  s. c  o m*/
        reader = new XSSFReader(pkg);
        sheetNameIDMap = new LinkedHashMap<String, String>();
        workbookData = reader.getWorkbookData();
        XMLInputFactory factory = XMLInputFactory.newInstance();
        workbookReader = factory.createXMLStreamReader(workbookData);
        while (workbookReader.hasNext()) {
            if (workbookReader.next() == XMLStreamConstants.START_ELEMENT
                    && workbookReader.getLocalName().equals("sheet")) {
                String sheetName = workbookReader.getAttributeValue(null, "name");
                String sheetID = workbookReader.getAttributeValue(RELATION_NS_URI, "id");
                sheetNameIDMap.put(sheetName, sheetID);
            }
        }
        sheetNames = new String[sheetNameIDMap.size()];
        int i = 0;
        for (String sheetName : sheetNameIDMap.keySet()) {
            sheetNames[i++] = sheetName;
        }
    } catch (Exception e) {
        throw new KettleException(e);
    } finally {
        if (workbookReader != null) {
            try {
                workbookReader.close();
            } catch (XMLStreamException e) {
                throw new KettleException(e);
            }
        }
        if (workbookData != null) {
            try {
                workbookData.close();
            } catch (IOException e) {
                throw new KettleException(e);
            }
        }
    }
}

From source file:org.talend.dataprep.schema.xls.streaming.StreamingSheetTest.java

License:Open Source License

@Before
public void setUp() throws Exception {
    OPCPackage pkg = OPCPackage.open(StreamingSheetTest.class.getResourceAsStream("../dates.xlsx"));
    XSSFReader reader = new XSSFReader(pkg);

    SharedStringsTable sst = reader.getSharedStringsTable();
    StylesTable styles = reader.getStylesTable();

    Iterator<InputStream> iter = reader.getSheetsData();
    XMLEventReader parser = XMLInputFactory.newInstance().createXMLEventReader(iter.next());
    final StreamingSheetReader streamingSheetReader = new StreamingSheetReader(sst, styles, parser, 10);
    streamingSheet = new StreamingSheet("name", streamingSheetReader);
}

From source file:org.talend.dataprep.schema.xls.streaming.StreamingWorkbookReader.java

License:Open Source License

public void init(File f) {
    try {// w ww .ja  va2s .  c o  m
        if (builder.getPassword() != null) {
            // Based on: https://poi.apache.org/encryption.html
            POIFSFileSystem poifs = new POIFSFileSystem(f);
            EncryptionInfo info = new EncryptionInfo(poifs);
            Decryptor d = Decryptor.getInstance(info);
            d.verifyPassword(builder.getPassword());
            pkg = OPCPackage.open(d.getDataStream(poifs));
        } else {
            pkg = OPCPackage.open(f);
        }

        XSSFReader reader = new XSSFReader(pkg);

        SharedStringsTable sst = reader.getSharedStringsTable();
        StylesTable styles = reader.getStylesTable();

        loadSheets(reader, sst, styles, builder.getRowCacheSize());
    } catch (IOException e) {
        throw new OpenException("Failed to open file", e);
    } catch (OpenXML4JException | XMLStreamException e) {
        throw new ReadException("Unable to read workbook", e);
    } catch (GeneralSecurityException e) {
        throw new ReadException("Unable to read workbook - Decryption failed", e);
    }
}

From source file:org.talend.dataprep.schema.xls.XlsUtilsTest.java

License:Open Source License

@Test
public void get_active_sheets() throws Exception {

    OPCPackage container = OPCPackage.open(getClass().getResourceAsStream("000_DTA_DailyTimeLog.xlsm"));
    XSSFReader xssfReader = new XSSFReader(container);

    List<String> names = XlsUtils.getActiveSheetsFromWorkbookSpec(xssfReader.getWorkbookData());

    Assertions.assertThat(names).isNotEmpty() //
            .containsExactly("MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY",
                    "WEEK SUMMARY");

}

From source file:org.talend.repository.ui.wizards.metadata.connection.files.excel.ExcelReader.java

License:Open Source License

private void init() throws BiffException, IOException {
    // hywang modified for excel 2007
    //        if (excelPath.endsWith(".xls")) { //$NON-NLS-1$
    // isXlsx = false;
    //        } else if (excelPath.endsWith(".xlsx")) { //$NON-NLS-1$
    // isXlsx = true;
    // }//w w w .  jav  a2s .  c o m

    if (!isXlsx) {
        WorkbookSettings worksetting = new WorkbookSettings();
        //worksetting.setEncoding("ISO-8859-15"); //$NON-NLS-1$
        worksetting.setCellValidationDisabled(true);
        worksetting.setSuppressWarnings(true);
        workbook = Workbook.getWorkbook(new File(excelPath), worksetting);
    } else {
        // modify for bug 12174.
        File file = new File(excelPath);
        OPCPackage clone = null;
        try {
            FileInputStream in = new FileInputStream(file);
            OPCPackage open = OPCPackage.open(in);
            clone = PackageHelper.clone(open, createTempFile());
            open.close();

            // Package createPackage = Package.openOrCreate(file);
            // clone = PackageHelper.clone(createPackage);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (OpenXML4JException e) {
            e.printStackTrace();
        }
        if (clone != null) {
            List<String> sheetlist = new ArrayList<String>();
            // modified for bug TDI-26614, Use XSSF and SAX (Event API) to parse excel 2007, only need small memory
            // footprint
            if (isXlsx && (EVENT_MODE).equals(generationMode)) {
                try {
                    XSSFReader xssfReader = new XSSFReader(clone);
                    XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
                    while (sheets.hasNext()) {
                        sheets.next();
                        String sheetName = sheets.getSheetName();
                        sheetlist.add(sheetName);
                    }
                } catch (OpenXML4JException e) {
                    CommonExceptionHandler.process(e);
                }
            } else {
                xwb = new XSSFWorkbook(clone);
                for (XSSFSheet sheet : xwb) {
                    sheetlist.add(sheet.getSheetName());
                }
            }
            sheetNamesForXlsx = new String[sheetlist.size()];
            for (int i = 0; i < sheetlist.size(); i++) {
                sheetNamesForXlsx[i] = sheetlist.get(i);
            }
            sheetlist.clear();
        }
    }

}

From source file:packtest.FromHowTo.java

License:Apache License

public void processFirstSheet(String filename) throws Exception {
    OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ);
    try {//w  ww .j av a 2  s .  co m
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();

        XMLReader parser = fetchSheetParser(sst);

        // process the first sheet
        InputStream sheet2 = r.getSheetsData().next();
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheet2.close();
    } finally {
        pkg.close();
    }
}