List of usage examples for org.apache.poi.openxml4j.opc OPCPackage open
public static OPCPackage open(InputStream in) throws InvalidFormatException, IOException
From source file:com.myjeeva.poi.ExcelXSSFRowCallbackHandler.java
License:Apache License
public ExcelXSSFRowCallbackHandler(String filePath, ExcelRowContentCallback rowCallback) throws InvalidFormatException { this(OPCPackage.open(filePath), rowCallback); }
From source file:com.netsteadfast.greenstep.util.SimpleUtils.java
License:Apache License
/** * http://stackoverflow.com/questions/12593752/why-do-i-failed-to-read-excel-2007-using-poi * /*from w ww . j av a 2s.c om*/ * @param inp * @return * @throws IOException * @throws InvalidFormatException */ public static Workbook createPOIWorkbook(InputStream inp) throws IOException, InvalidFormatException { // If clearly doesn't do mark/reset, wrap up if (!inp.markSupported()) { inp = new PushbackInputStream(inp, 8); } if (POIFSFileSystem.hasPOIFSHeader(inp)) { return new HSSFWorkbook(inp); } if (DocumentFactoryHelper.hasOOXMLHeader(inp)) { return new XSSFWorkbook(OPCPackage.open(inp)); } throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream"); }
From source file:com.opendoorlogistics.core.tables.io.XmlParserLoader.java
License:Open Source License
private void doImport() { if (!file.exists()) { throw new RuntimeException("Excel file does not exist: " + file.getAbsolutePath()); }/*from w w w .ja v a2s . com*/ OPCPackage pkg = null; try { pkg = OPCPackage.open(file); importOPCPackage(pkg); // revert for read-only closing pkg.revert(); if (processingApi != null) { processingApi.postStatusMessage("Finished loading, now opening file..."); } } catch (Exception e) { if (pkg != null) { // revert for read-only closing pkg.revert(); } report.setFailed(e); throw new RuntimeException(e); } }
From source file:com.opendoorlogistics.core.tables.io.XmlParserLoader.java
License:Open Source License
private void doImport(InputStream stream) { try (OPCPackage pkg = OPCPackage.open(stream)) { importOPCPackage(pkg);/* ww w .j a va2 s.c o m*/ } catch (Exception e) { report.setFailed(e); throw new RuntimeException(e); } }
From source file:com.ostrichemulators.semtool.poi.main.LowMemXlsReader.java
public LowMemXlsReader(InputStream stream) throws IOException { log.debug("reading with lo-mem xls reader"); sharedStrings = new ArrayList<>(); try {//from ww w . j a v a2s. c o m pkg = OPCPackage.open(stream); reader = new XSSFReader(pkg); styles = reader.getStylesTable(); sheetNameIdLkp = readSheetInfo(reader); populateSharedStrings(reader); } catch (OpenXML4JException e) { throw new IOException("unexpected error" + e.getLocalizedMessage(), e); } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXLSX(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {//from w ww .java 2 s. c om FileInputStream myInput = new FileInputStream(filePath); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { XSSFSheet mySheet1 = myWorkBook.getSheetAt(j); if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) { myWorkBook.removeSheetAt(j); hasTrue = true; break; } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); break; } } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void readTestSuiteFromXLSX(List<TestSuite> excels, StringBuilder sb) throws FileNotFoundException, InvalidFormatException, IOException, UnknownHostException, PhrescoException { Iterator<Row> rowIterator; FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 2; i++) { rowIterator.next();//from ww w .java 2 s . c om } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(2))) && !getValue(next.getCell(2)).equalsIgnoreCase("Total")) { TestSuite createObject = createObject(next); excels.add(createObject); } } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestCasesFromXLSX(String filePath, String testSuiteName, String testCaseId) { boolean hasTrue = false; List<TestCase> testCases = new ArrayList<TestCase>(); TestCase tstCase = new TestCase(); Iterator<Row> rowIterator; try {//from w w w .j a v a 2s . c om FileInputStream myInput = new FileInputStream(filePath); 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(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) { mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; XSSFRow row = mySheet.getRow(newNum); if (row != null) { mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1); } hasTrue = true; break; } } } } if (hasTrue) { for (int j = 0; j < numberOfSheets; j++) { XSSFSheet myXSSFSheet = myWorkBook.getSheetAt(j); if (myXSSFSheet.getSheetName().equals(testSuiteName)) { rowIterator = myXSSFSheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); TestCase createObject = readTest(next); testCases.add(createObject); } float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; int totalTestCases = testCases.size(); for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } } if (tstCase.getStatus().equalsIgnoreCase("success")) { totalPass = totalPass - 1; } else if (tstCase.getStatus().equalsIgnoreCase("failure")) { totalFail = totalFail - 1; } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable - 1; } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked - 1; } 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 (StringUtils.isNotEmpty(tstCase.getTestCaseId()) && createObject.getName().equals(testSuiteName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, totalTestCases, "delete"); } } } } } myInput.close(); FileOutputStream outFile = new FileOutputStream(filePath); myWorkBook.write(outFile); outFile.close(); } } catch (Exception e) { } return hasTrue; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void updateTestCaseToXLSX(String fileName, com.photon.phresco.commons.model.TestCase tstCase, List<TestCase> testCases, StringBuilder sb) throws FileNotFoundException, InvalidFormatException, IOException, UnknownHostException, PhrescoException { 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(fileName)) { Iterator<Row> rowIterator = mySheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next();/* w w w. j a va 2s.c o m*/ } while (rowIterator.hasNext()) { Row next = rowIterator.next(); if (StringUtils.isNotEmpty(getValue(next.getCell(1)))) { TestCase createObject = readTest(next); testCases.add(createObject); if (tstCase != null && createObject.getTestCaseId().equals(tstCase.getTestCaseId())) { Cell preConditionsCell = next.getCell(5); preConditionsCell.setCellValue(tstCase.getPreconditions()); Cell stepsCell = next.getCell(6); stepsCell.setCellValue(tstCase.getSteps()); Cell expectedCell = next.getCell(9); expectedCell.setCellValue(tstCase.getExpectedResult()); Cell actualCell = next.getCell(10); actualCell.setCellValue(tstCase.getActualResult()); Cell statusCell = next.getCell(11); statusCell.setCellValue(tstCase.getStatus()); Cell commentCell = next.getCell(12); commentCell.setCellValue(tstCase.getBugComment()); } } } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { float totalPass = 0; float totalFail = 0; float totalNotApplicable = 0; float totalBlocked = 0; for (TestCase testCase : testCases) { String testCaseStatus = testCase.getStatus(); String testId = tstCase.getTestCaseId(); String status = tstCase.getStatus(); if (testCaseStatus.equalsIgnoreCase("success") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalPass = totalPass + 1; } else if (testCaseStatus.equalsIgnoreCase("failure") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalFail = totalFail + 1; } else if (testCaseStatus.equalsIgnoreCase("notApplicable") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalNotApplicable = totalNotApplicable + 1; } else if (testCaseStatus.equalsIgnoreCase("blocked") && !testCase.getTestCaseId().equalsIgnoreCase(testId)) { totalBlocked = totalBlocked + 1; } if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("success") && status.equalsIgnoreCase("success")) { totalPass = totalPass + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("failure") && status.equalsIgnoreCase("failure")) { totalFail = totalFail + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("notApplicable") && status.equalsIgnoreCase("notApplicable")) { totalNotApplicable = totalNotApplicable + 1; } else if (testCase.getTestCaseId().equals(testId) && !testCase.getStatus().equalsIgnoreCase("blocked") && status.equalsIgnoreCase("blocked")) { totalBlocked = totalBlocked + 1; } 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 (StringUtils.isNotEmpty(testId) && createObject.getName().equals(fileName)) { updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1, 0, ""); } } } } } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { myInput.close(); FileOutputStream outFile = new FileOutputStream(sb.toString()); myWorkBook.write(outFile); outFile.close(); } } } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public void addNew(String filePath, String testName, String cellValue[]) throws PhrescoException { try {//w ww . j a v a2 s . c o m //FileInputStream myInput = new FileInputStream(filePath); int numCol; int cellno = 0; CellStyle tryStyle[] = new CellStyle[20]; String sheetName = testName; //String cellValue[] = {"","",testName,success, fail,"","","",total,testCoverage,"","",""}; Iterator<Row> rowIterator; File testDir = new File(filePath); StringBuilder sb = new StringBuilder(filePath); if (testDir.isDirectory()) { FilenameFilter filter = new PhrescoFileFilter("", "xlsx"); File[] listFiles = testDir.listFiles(filter); if (listFiles.length != 0) { for (File file1 : listFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); OPCPackage opc = OPCPackage.open(myInput); XSSFWorkbook myWorkBook = new XSSFWorkbook(opc); XSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).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]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter xlsFilter = new PhrescoFileFilter("", "xls"); File[] xlsListFiles = testDir.listFiles(xlsFilter); if (xlsListFiles.length != 0) { for (File file2 : xlsListFiles) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet mySheet = myWorkBook.getSheetAt(0); rowIterator = mySheet.rowIterator(); numCol = 13; Row next; for (Cell cell : mySheet.getRow((mySheet.getLastRowNum()) - 2)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; } do { int flag = 0; next = rowIterator.next(); if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index") && ((mySheet.getLastRowNum() - next.getRowNum()) < 3)) { for (Cell cell : next) { cell.setCellType(1); if (cell.getStringCellValue().equalsIgnoreCase("total")) { mySheet.shiftRows((mySheet.getLastRowNum() - 1), (mySheet.getPhysicalNumberOfRows() - 1), 1); flag = 1; } if (flag == 1) break; } if (flag == 1) break; } } while (rowIterator.hasNext()); Row r = null; if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { r = mySheet.createRow(mySheet.getLastRowNum() - 2); } else { r = mySheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); // used only when sheet is 'index' if (i == 2) sheetName = cellValue[i]; cell.setCellStyle(tryStyle[i]); } if ((mySheet.getSheetName().trim()).equalsIgnoreCase("Index")) { Sheet fromSheet = myWorkBook.getSheetAt((myWorkBook.getNumberOfSheets() - 1)); Sheet toSheet = myWorkBook.createSheet(sheetName); int i = 0; Iterator<Row> copyFrom = fromSheet.rowIterator(); Row fromRow, toRow; CellStyle newSheetStyle[] = new CellStyle[20]; Integer newSheetType[] = new Integer[100]; String newSheetValue[] = new String[100]; do { fromRow = copyFrom.next(); if (fromRow.getRowNum() == 24) { break; } toRow = toSheet.createRow(i); int numCell = 0; for (Cell cell : fromRow) { Cell newCell = toRow.createCell(numCell); cell.setCellType(1); newSheetStyle[numCell] = cell.getCellStyle(); newCell.setCellStyle(newSheetStyle[numCell]); newSheetType[numCell] = cell.getCellType(); newCell.setCellType(newSheetType[numCell]); if (fromRow.getCell(0).getStringCellValue().length() != 1 && fromRow.getCell(0).getStringCellValue().length() != 2 && fromRow.getCell(0).getStringCellValue().length() != 3) { newSheetValue[numCell] = cell.getStringCellValue(); newCell.setCellValue(newSheetValue[numCell]); } numCell = numCell + 1; if (numCell == 15) { break; } } i = i + 1; } while (copyFrom.hasNext()); } // write to file FileOutputStream fileOut = new FileOutputStream(sb.toString()); myWorkBook.write(fileOut); myInput.close(); fileOut.close(); } else { FilenameFilter odsFilter = new PhrescoFileFilter("", "ods"); File[] odsListFiles = testDir.listFiles(odsFilter); for (File file1 : odsListFiles) { if (file1.isFile()) { sb.append(File.separator); sb.append(file1.getName()); break; } } File file = new File(sb.toString()); addTestSuiteToOds(file, cellValue); } } } } catch (Exception e) { // throw new PhrescoException(e); } }