Example usage for org.apache.poi.openxml4j.opc OPCPackage open

Introduction

In this page you can find the example usage for org.apache.poi.openxml4j.opc OPCPackage open.

Prototype

public static OPCPackage open(InputStream in) throws InvalidFormatException, IOException 

Source Link

Document

Open a package.

Usage

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private void writeTestCasesToXLSX(String testSuiteName, String[] cellValue, String status, int numCol,
        int cellno, CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException {
    Iterator<Row> rowIterator;
    try {/*from ww  w  .  j a  va2s  .  c  o m*/
        FileInputStream myInput = new FileInputStream(sb.toString());
        OPCPackage opc = OPCPackage.open(myInput);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(opc);
        int numberOfSheets = myWorkBook.getNumberOfSheets();
        for (int j = 0; j < numberOfSheets; j++) {
            XSSFSheet mySheet = myWorkBook.getSheetAt(j);
            if (mySheet.getSheetName().equals(testSuiteName)) {
                rowIterator = mySheet.rowIterator();
                Row next;
                for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) {
                    tryStyle[cellno] = cell.getCellStyle();
                    cellno = cellno + 1;
                }
                float totalPass = 0;
                float totalFail = 0;
                float totalNotApp = 0;
                float totalBlocked = 0;
                float notExecuted = 0;
                float totalTestCases = 0;
                for (int i = 0; i <= 22; i++) {
                    rowIterator.next();
                }
                do {
                    next = rowIterator.next();
                    if (StringUtils.isNotEmpty(getValue(next.getCell(1)))
                            && !getValue(next.getCell(0)).equalsIgnoreCase("S.NO")) {
                        String value = getValue(next.getCell(11));
                        if (StringUtils.isNotEmpty(value)) {
                            if (value.equalsIgnoreCase("success")) {
                                totalPass = totalPass + 1;
                            } else if (value.equalsIgnoreCase("failure")) {
                                totalFail = totalFail + 1;
                            } else if (value.equalsIgnoreCase("notApplicable")) {
                                totalNotApp = totalNotApp + 1;
                            } else if (value.equalsIgnoreCase("blocked")) {
                                totalBlocked = totalBlocked + 1;
                            }
                        } else {
                            notExecuted = notExecuted + 1;
                        }
                    }
                } while (rowIterator.hasNext());
                //to update the status in the index page 
                if (status.equalsIgnoreCase("success")) {
                    totalPass = totalPass + 1;
                } else if (status.equalsIgnoreCase("failure")) {
                    totalFail = totalFail + 1;
                } else if (status.equalsIgnoreCase("notApplicable")) {
                    totalNotApp = totalNotApp + 1;
                } else if (status.equalsIgnoreCase("blocked")) {
                    totalBlocked = totalBlocked + 1;
                } else {
                    notExecuted = notExecuted + 1;
                }
                totalTestCases = totalPass + totalFail + totalNotApp + totalBlocked + notExecuted;
                XSSFSheet mySheet1 = myWorkBook.getSheetAt(0);
                rowIterator = mySheet1.rowIterator();
                for (int i = 0; i <= 2; i++) {
                    rowIterator.next();
                }
                while (rowIterator.hasNext()) {
                    Row next1 = rowIterator.next();
                    if (StringUtils.isNotEmpty(getValue(next1.getCell(2)))
                            && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) {
                        TestSuite createObject = createObject(next1);
                        if (createObject.getName().equals(testSuiteName)) {
                            addCalculationsToIndex(totalPass, totalFail, totalNotApp, totalBlocked, notExecuted,
                                    totalTestCases, next1);
                        }
                    }
                }

                Row r = null;
                if (mySheet.getSheetName().equalsIgnoreCase("Index")) {
                    r = mySheet.createRow(next.getRowNum() - 1);

                } else {
                    r = mySheet.createRow(next.getRowNum() + 1);
                }
                for (int i = 0; i < numCol; i++) {
                    Cell cell = r.createCell(i);
                    cell.setCellValue(cellValue[i]);

                    cell.setCellStyle(tryStyle[i]);
                }
                FileOutputStream fileOut = new FileOutputStream(sb.toString());
                myWorkBook.write(fileOut);
                myInput.close();
                fileOut.close();
            }

        }
    } catch (PhrescoException e) {
        throw new PhrescoException(e);
    } catch (IOException e) {
        throw new PhrescoException(e);
    } catch (InvalidFormatException e) {
        throw new PhrescoException(e);
    }
}

---

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

License:Apache License

/**
 * Excel  field  Integer,Long,Double,Date,String,Boolean
 *
 * @param inputstream//  w w w  .  ja va  2  s.  co  m
 * @param pojoClass
 * @param params
 * @return
 * @throws Exception
 */
public com.qihang.winter.poi.excel.entity.result.ExcelImportResult importExcelByIs(InputStream inputstream,
        Class<?> pojoClass, com.qihang.winter.poi.excel.entity.ImportParams params) throws Exception {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel import start ,class is {}", pojoClass);
    }
    List<T> result = new ArrayList<T>();
    Workbook book = null;
    boolean isXSSFWorkbook = true;
    if (!(inputstream.markSupported())) {
        inputstream = new PushbackInputStream(inputstream, 8);
    }
    if (POIFSFileSystem.hasPOIFSHeader(inputstream)) {
        book = new HSSFWorkbook(inputstream);
        isXSSFWorkbook = false;
    } else if (POIXMLDocument.hasOOXMLHeader(inputstream)) {
        book = new XSSFWorkbook(OPCPackage.open(inputstream));
    }
    createErrorCellStyle(book);
    Map<String, PictureData> pictures;
    for (int i = 0; i < params.getSheetNum(); i++) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(" start to read excel by is ,startTime is {}", new Date().getTime());
        }
        if (isXSSFWorkbook) {
            pictures = com.qihang.winter.poi.util.PoiPublicUtil
                    .getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);
        } else {
            pictures = com.qihang.winter.poi.util.PoiPublicUtil
                    .getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book);
        }
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(" end to read excel by is ,endTime is {}", new Date().getTime());
        }
        result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(" end to read excel list by pos ,endTime is {}", new Date().getTime());
        }
    }
    String excelName = "";
    if (params.isNeedSave()) {
        excelName = saveThisExcel(params, pojoClass, isXSSFWorkbook, book);
    }
    return new com.qihang.winter.poi.excel.entity.result.ExcelImportResult(result, verfiyFail, book, excelName);
}

---

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

License:Apache License

public <T> List<T> readExcel(InputStream inputstream, Class<?> pojoClass,
        com.qihang.winter.poi.excel.entity.ImportParams params, ISaxRowRead rowRead,
        com.qihang.winter.poi.handler.inter.IExcelReadRowHanlder hanlder) {
    try {//  w w w . ja  v a2  s .c  o m
        OPCPackage opcPackage = OPCPackage.open(inputstream);
        return readExcel(opcPackage, pojoClass, params, rowRead, hanlder);
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw new com.qihang.winter.poi.exception.excel.ExcelImportException(e.getMessage());
    }
}

---

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

/**
 * Excel/*from   www  . j  a  va  2 s.com*/
 * 
 * @param path
 *            
 * @param sheetName
 *            sheet
 * @param minColumns
 *            
 * @return
 * @throws SAXException
 * @throws ParserConfigurationException
 * @throws OpenXML4JException
 * @throws IOException
 */
public static List<List<String[]>> readerExcel(InputStream in, int minColumns)
        throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
    OPCPackage p = OPCPackage.open(in);
    XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, null, minColumns);
    List<List<String[]>> list = xlsx2csv.process();
    p.close();
    return list;
}

---

From source file:com.surenpi.autotest.suite.parser.ExcelSuiteParser.java

License:Apache License

@Override
public Suite parse(InputStream suiteInputStream) throws Exception {
    if (POIFSFileSystem.hasPOIFSHeader(suiteInputStream)) {
        workbook = new HSSFWorkbook(suiteInputStream);
    } else if (POIXMLDocument.hasOOXMLHeader(suiteInputStream)) {
        workbook = new XSSFWorkbook(OPCPackage.open(suiteInputStream));
    } else {//from w  w w  . j a  v  a2  s.  com
        throw new IllegalArgumentException("Unknow format excel file.");
    }

    suite = new Suite();
    suite.setRows("1");
    List<SuitePage> pageList = new ArrayList<SuitePage>();
    suite.setPageList(pageList);

    sheetsParse(pageList);

    return suite;
}

---

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

License:Open Source License

public Object call() throws Exception {
    OPCPackage pkg = null;//from   ww  w  .ja v  a 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;
}

---

From source file:com.test.demo.ccbpay.XLSX2CSV.java

License:Apache License

/**
 * excel/* w w w.j  a  v  a  2 s. co m*/
 * @param in ?
 * @param minColumns 
 * @return list
 * @throws Exception 
 */
public static List<String[]> getRecords(InputStream in, int minColumns) throws Exception {
    if (in == null) {
        throw new Exception("?null");
    }
    // The package open is instantaneous, as it should be.
    OPCPackage p = OPCPackage.open(in);
    XLSX2CSV xlsx2csv = new XLSX2CSV(p, minColumns);
    List<String[]> list = xlsx2csv.process();
    p.close();
    return list;
}

---

From source file:com.test.demo.ccbpay.XLSX2CSV.java

License:Apache License

/**
 * excel//  w  w w .  ja  v  a2 s  .  co m
 * @param filePath xlsx?
 * @param minColumns 
 * @return list
 * @throws Exception 
 */
public static List<String[]> getRecords(String filePath, int minColumns) throws Exception {

    File f = new File(filePath);
    if (!f.exists()) {
        throw new Exception("?");
    }
    // The package open is instantaneous, as it should be.
    OPCPackage p = OPCPackage.open(f);
    XLSX2CSV xlsx2csv = new XLSX2CSV(p, minColumns);
    List<String[]> list = xlsx2csv.process();
    p.close();
    return list;
}

---

From source file:com.xipin.est.ucontroller.excel.ExcelImportXLSXUtil.java

License:Apache License

public static List<String[]> readerExcel(InputStream i, String sheetName, int minColumns)
        throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
    OPCPackage p = OPCPackage.open(i);
    ExcelImportXLSXUtil xlsx2csv = new ExcelImportXLSXUtil(p, System.out, sheetName, minColumns);
    List<String[]> list = xlsx2csv.process();
    p.close();/*from  w w  w  . j a  va 2s .c o  m*/
    return list;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• »