List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:com.mum.processexceldata.RateService.java
public void importCallRates(String path, Date effectiveDate) { try {//www. ja va2s .co m FileInputStream file = new FileInputStream( new File("C:\\Users\\demodem\\Downloads\\Calling_Codes.xls")); System.out.println("File:" + file); // HSSFWorkbook book = new HSSFWorkbook(file); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { HSSFSheet sheet = workbook.getSheetAt(i); processCallRates(sheet, effectiveDate); } } catch (Exception e) { System.out.println("Error in reading the file."); } }
From source file:com.mycompany.mavenproject1.MainExecutor.java
/** * * @param inputExcel/*w ww. j a v a2 s . co m*/ * @return */ static LinkedHashSet<String> getAllPlayerNames(HSSFWorkbook inputExcel) { LinkedHashSet<String> playerlist = new LinkedHashSet<String>(); for (int i = 0; i < inputExcel.getNumberOfSheets(); i++) { Iterator<Row> rowIterator = inputExcel.getSheetAt(i).iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); for (int n = 0; n < 2; n++) { Cell cell = cellIterator.next(); String possibleNewName = cell.getStringCellValue(); playerlist.add(possibleNewName); } } } return playerlist; }
From source file:com.mycompany.mavenproject1.MainExecutor.java
static Map<String, Object[]> getNewWorkbookData(HSSFWorkbook inputExcel, Iterator<String> playersIterator) { Map<String, Object[]> data = new HashMap<String, Object[]>(); data.put("1", new Object[] { "Player", "FSP.1", "FSW.1", "SSP.1", "SSW.1", "ACE.1", "DBF.1", "WNR.1", "UFE.1", "BPC.1", "BPW.1", "NPA.1", "NPW.1" }); int key = 2;// www. jav a 2 s . c o m while (playersIterator.hasNext()) { String player = playersIterator.next(); for (int i = 0; i < inputExcel.getNumberOfSheets(); i++) { Iterator<Row> rowIterator = inputExcel.getSheetAt(i).iterator(); rowIterator.next(); int row_counter = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); Object[] rowValues = new Object[13]; String player_1_cell = row.getCell(0).getStringCellValue(); String player_2_cell = row.getCell(1).getStringCellValue(); int dataRangeLow = 0; int dataRangeHigh = 0; if (player.equals(player_1_cell)) { dataRangeLow = 6; dataRangeHigh = 17; rowValues[0] = player; } else if (player.equals(player_2_cell)) { dataRangeLow = 24; dataRangeHigh = 35; rowValues[0] = player; } else { // Go to another row. } int cell_counter = 0; for (int j = dataRangeLow; j < dataRangeHigh; j++) { Cell cell = row.getCell(j); System.out.println(cell_counter); if (cell != null) rowValues[j - dataRangeLow + 1] = cell.getNumericCellValue(); if (cell_counter == 5) { Object debug = new Object(); } cell_counter++; data.put("" + key, rowValues); key++; } System.out.println(row_counter); row_counter++; } } } return data; }
From source file:com.netxforge.netxstudio.models.importer.MasterDataImporter.java
License:Open Source License
public void process(InputStream is) { HSSFWorkbook workBook; try {//w w w .j av a2 s. co m workBook = new HSSFWorkbook(is); // Multi pass the worksheets, do not add objects after the first // pass. // int passes = 2; for (int passIndex = 1; passIndex <= passes; passIndex++) { // Clear after each pass... unresolvedReferences.clear(); if (ImportActivator.DEBUG) { System.out.println("DATA IMPORTER: pass=" + passIndex); } for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) { ProcessWorkSheet pw = new ProcessWorkSheet(passIndex, sheetIndex, workBook.getSheetAt(sheetIndex)); // We only add object on the first pass List<RowResult> sheetResult = pw.getSheetResult(); if (!pw.isMultiRefSheet() && passIndex == 1) { for (final RowResult rowResult : sheetResult) { resource.getContents().add(rowResult.getEObject()); this.addToGlobalIndex(rowResult.getIndex(), rowResult.getEObject()); } } } if (ImportActivator.DEBUG) { printResult(); printIndex(); } } } catch (final Exception e) { throw new IllegalStateException(e); } }
From source file:com.netxforge.netxstudio.models.importer.MasterDataImporterBackup.java
License:Open Source License
public void process(InputStream is) { HSSFWorkbook workBook; try {//from w ww . j ava 2s .co m workBook = new HSSFWorkbook(is); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { ProcessWorkSheet pw = new ProcessWorkSheet(i, workBook.getSheetAt(i)); List<RowResult> sheetResult = pw.getSheetResult(); if (!pw.isMultiRefSheet()) { for (final RowResult rowResult : sheetResult) { EObject resultObject = EcoreUtil.copy(rowResult.getEObject()); IndexedObject io = new IndexedObject(rowResult.getIndex(), resultObject); resultList.add(io); } } } printResult(); } catch (final Exception e) { throw new IllegalStateException(e); } }
From source file:com.netxforge.netxstudio.server.dataimport.MasterDataImporter.java
License:Open Source License
public void process(HSSFWorkbook workBook) { try {//from w w w . j a va2 s . c o m for (int i = 0; i < workBook.getNumberOfSheets(); i++) { processWorkSheet(i, workBook.getSheetAt(i)); } for (final RowResult rowResult : rowResults) { processEReferences(rowResult.getEObject(), rowResult.getRow()); final Resource resource = dataProvider.getResource(rowResult.getEObject().eClass()); resource.getContents().add(rowResult.getEObject()); } } catch (final Exception e) { throw new IllegalStateException(e); } }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXls(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {//from w w w . j a v a 2 s. c o m FileInputStream myInput = new FileInputStream(filePath); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); HSSFSheet 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++) { HSSFSheet 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
public boolean deleteTestCasesFromXls(String filePath, String testSuiteName, String testCaseId) { boolean hasTrue = false; List<TestCase> testCases = new ArrayList<TestCase>(); TestCase tstCase = new TestCase(); Iterator<Row> rowIterator; try {/*from www. j a v a2 s .co m*/ FileInputStream myInput = new FileInputStream(filePath); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet 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)))) { tstCase = readTest(next); mySheet.removeRow(next); int rowNum = next.getRowNum(); int newNum = rowNum + 1; HSSFRow 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++) { HSSFSheet myHSSFSheet = myWorkBook.getSheetAt(j); if (myHSSFSheet.getSheetName().equals(testSuiteName)) { rowIterator = myHSSFSheet.rowIterator(); for (int i = 0; i <= 23; i++) { rowIterator.next(); } while (rowIterator.hasNext()) { Row next = rowIterator.next(); TestCase createObject = readTest(next); if (StringUtils.isNotEmpty(createObject.getTestCaseId())) { 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; // } HSSFSheet 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 List<TestCase> readTestCase(String filePath, String fileName, com.photon.phresco.commons.model.TestCase tstCase) throws PhrescoException { List<TestCase> testCases = new ArrayList<TestCase>(); try {// w w w . j a va 2 s. co m 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; } } updateTestCaseToXLSX(fileName, tstCase, testCases, sb); } else { FilenameFilter filter1 = new PhrescoFileFilter("", "xls"); File[] listFiles1 = testDir.listFiles(filter1); if (listFiles1.length != 0) { for (File file2 : listFiles1) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet mySheet = myWorkBook.getSheetAt(j); if (mySheet.getSheetName().equals(fileName)) { readTestFromSheet(tstCase, testCases, mySheet); if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { updateIndexPage(fileName, tstCase, testCases, myWorkBook); } if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) { myInput.close(); FileOutputStream outFile = new FileOutputStream(sb.toString()); myWorkBook.write(outFile); outFile.close(); } } } } else { FilenameFilter odsFilter = new PhrescoFileFilter("", "ods"); File[] odsListFiles = testDir.listFiles(odsFilter); for (File file2 : odsListFiles) { if (file2.isFile()) { sb.append(File.separator); sb.append(file2.getName()); break; } } testCases = readTestCasesFormODS(sb, testCases, fileName, tstCase); } } } } catch (Exception e) { } return testCases; }
From source file:com.photon.phresco.framework.commons.FrameworkUtil.java
License:Apache License
private void writeTestCaseToXLS(String testSuiteName, String[] cellValue, String status, int numCol, int cellno, CellStyle[] tryStyle, StringBuilder sb) throws PhrescoException { Iterator<Row> rowIterator; try {/*from w w w. j a v a 2 s . co m*/ FileInputStream myInput = new FileInputStream(sb.toString()); HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput); int numberOfSheets = myWorkBook.getNumberOfSheets(); for (int j = 0; j < numberOfSheets; j++) { HSSFSheet myHssfSheet = myWorkBook.getSheetAt(j); if (myHssfSheet.getSheetName().equals(testSuiteName)) { rowIterator = myHssfSheet.rowIterator(); Row next; for (Cell cell : myHssfSheet.getRow((myHssfSheet.getLastRowNum()) - 1)) { tryStyle[cellno] = cell.getCellStyle(); cellno = cellno + 1; if (cellno == 15) { break; } } 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; HSSFSheet mySheetHssf = myWorkBook.getSheetAt(0); rowIterator = mySheetHssf.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 (myHssfSheet.getSheetName().equalsIgnoreCase("Index")) { r = myHssfSheet.createRow(next.getRowNum() - 1); } else { r = myHssfSheet.createRow(next.getRowNum() + 1); } for (int i = 0; i < numCol; i++) { Cell cell = r.createCell(i); cell.setCellValue(cellValue[i]); if (tryStyle[i] != null) { 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 (FileNotFoundException e) { throw new PhrescoException(e); } catch (IOException e) { throw new PhrescoException(e); } }