List of usage examples for org.apache.poi.ss.usermodel Row getRowNum
int getRowNum();
From source file:com.nfa.drs.data.StudentWindTunnelFormatXls.java
private List<String> readXlsLines(Path file) { List<String> lines = new ArrayList<>(); try {//from w w w .j ava 2 s . c o m FileInputStream stream = new FileInputStream(file.toFile()); HSSFWorkbook book = new HSSFWorkbook(stream); HSSFSheet sheet = book.getSheetAt(0); for (Row row : sheet) { int rowIndex = row.getRowNum(); while (rowIndex > lines.size() - 1) { lines.add(""); } StringBuilder line = new StringBuilder(); for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { line.append(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { line.append(cell.getNumericCellValue()); } line.append(","); } lines.add(line.toString()); } } catch (IOException ex) { } return lines; }
From source file:com.openitech.db.model.ExcelDataSource.java
License:Apache License
@Override public boolean loadData(boolean reload, int oldRow) { boolean result = false; if (isDataLoaded && !reload) { return false; }//from www . j a va 2 s .c o m if (sourceFile != null) { try { Workbook workBook = WorkbookFactory.create(new FileInputStream(sourceFile)); // HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(sourceFile)); Sheet sheet = workBook.getSheetAt(0); DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY); FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator(); int lastRowNum = sheet.getLastRowNum(); boolean isFirstLineHeader = true; //count = sheet. - (isFirstLineHeader ? 1 : 0); int tempCount = 0; for (int j = 0; j <= lastRowNum; j++) { //zane se z 0 Row row = row = sheet.getRow(j); if (row == null) { continue; } // display row number in the console. System.out.println("Row No.: " + row.getRowNum()); if (isFirstLineHeader && row.getRowNum() == 0) { populateHeaders(row); continue; } tempCount++; Map<String, DataColumn> values; if (rowValues.containsKey(row.getRowNum())) { values = rowValues.get(row.getRowNum()); } else { values = new HashMap<String, DataColumn>(); rowValues.put(row.getRowNum(), values); } // once get a row its time to iterate through cells. int lastCellNum = row.getLastCellNum(); for (int i = 0; i <= lastCellNum; i++) { DataColumn dataColumn = new DataColumn(); Cell cell = row.getCell(i); if (cell == null) { continue; } System.out.println("Cell No.: " + cell.getColumnIndex()); System.out.println("Value: " + dataFormatter.formatCellValue(cell)); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator)); } else { dataColumn = new DataColumn(dataFormatter.formatCellValue(cell)); } switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // cell type numeric. System.out.println("Numeric value: " + cell.getNumericCellValue()); dataColumn = new DataColumn(dataFormatter.formatCellValue(cell)); break; } case Cell.CELL_TYPE_STRING: // cell type string. System.out.println("String value: " + cell.getStringCellValue()); dataColumn = new DataColumn(dataFormatter.formatCellValue(cell)); break; case Cell.CELL_TYPE_BOOLEAN: // cell type string. System.out.println("String value: " + cell.getBooleanCellValue()); dataColumn.setValue(cell.getBooleanCellValue(), Boolean.class); break; case Cell.CELL_TYPE_FORMULA: // cell type string. System.out.println( "Formula value: " + dataFormatter.formatCellValue(cell, formulaEvaluator)); dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator)); break; default: dataColumn.setValue(cell.getStringCellValue(), String.class); break; } values.put(getColumnName(cell.getColumnIndex()).toUpperCase(), dataColumn); } } count = tempCount; isDataLoaded = true; //se postavim na staro vrstico ali 1 if (oldRow > 0) { absolute(oldRow); } else { first(); } result = true; } catch (Exception ex) { Logger.getLogger(ExcelDataSource.class.getName()).log(Level.SEVERE, null, ex); result = false; } } return result; }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public void upExcel(FileUploadEvent event) throws ParseException { try {//from w ww . j a va 2 s . c om upFile = event.getFile(); XSSFWorkbook workBook = new XSSFWorkbook(event.getFile().getInputstream()); XSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator<Row> rowIterator = hssfSheet.rowIterator(); boolean exist = false; int filaDuplicada = 0; boolean estado = false; boolean firstLinea = true; while (rowIterator.hasNext()) { Row hssfRow = rowIterator.next(); if (firstLinea) { firstLinea = false; } else { Multitabla xls = new Multitabla(); hssfRow.getCell(1).setCellType(XSSFCell.CELL_TYPE_STRING); xls.setDESCRIPCION(hssfRow.getCell(2).getStringCellValue()); xls.setABREV(hssfRow.getCell(3).getStringCellValue()); xls.setEMPRESA(Integer.valueOf(idempresa)); xls.setESTADO(true); if (hssfRow.getCell(1) != null && hssfRow.getCell(0).getStringCellValue().equalsIgnoreCase("Si")) { xls.setPalias(hssfRow.getCell(1).getStringCellValue()); listDetalleMultitablaTablaUp.add(xls); } else { listMultitablaTablaUp.add(xls); } exist = false; for (int i = 0; i < listMultitablaTablaUp.size() - 1; i++) { if (listMultitablaTablaUp.get(i).getDESCRIPCION().equalsIgnoreCase( hssfRow.getCell(2).getStringCellValue()) && hssfRow.getRowNum() > 1) { exist = true; break; } } if (exist) { filaDuplicada = hssfRow.getRowNum() + 1; break; } } } if (exist) { WebUtil.MensajeAlerta("Registro Duplicado. Fila : " + filaDuplicada + ". \n Verifique el Excel e Intntelo otra vez."); listMultitablaTablaUp.clear(); } } catch (IOException e) { System.out.println("Error en el Procesamiento : " + e.getMessage()); } }
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public void upExcel(FileUploadEvent event) throws ParseException, Exception { try {//ww w.j ava 2s . c o m listPaletaUp = new ArrayList<Paleta>(); listDPaletaUp = new ArrayList<Dpaleta>(); listerros = new ArrayList<Object[]>(); listderros = new ArrayList<Object[]>(); upFile = event.getFile(); XSSFWorkbook workBook = new XSSFWorkbook(event.getFile().getInputstream()); XSSFSheet hssfSheet = workBook.getSheetAt(0); XSSFSheet hssfSheetD = workBook.getSheetAt(1); Iterator<Row> rowIterator = hssfSheet.rowIterator(); boolean exist = false; int filaDuplicada = 0; int filaDuplicadaD = 0; boolean estado = false; boolean firstLinea = true; int k = 2; while (rowIterator.hasNext()) { Row hssfRow = rowIterator.next(); if (firstLinea) { firstLinea = false; } else { Paleta xls = new Paleta(); xls.setIdempresa(hssfRow.getCell(0).getStringCellValue()); xls.setIdregistropaleta(hssfRow.getCell(1).getStringCellValue()); xls.setIdemisor(hssfRow.getCell(2).getStringCellValue()); xls.setIdoperacion(hssfRow.getCell(3).getStringCellValue()); String tempN = null; if (hssfRow.getCell(4) != null) { tempN = hssfRow.getCell(4).getStringCellValue(); } xls.setNumoperacion(tempN); xls.setIdmotivopaleta(hssfRow.getCell(5).getStringCellValue()); xls.setIddocumento(hssfRow.getCell(6).getStringCellValue()); xls.setSerie(hssfRow.getCell(7).getStringCellValue()); xls.setNumero(hssfRow.getCell(8).getStringCellValue()); //de string a timestamp y a string denuevo DateFormat formatter; // formatter = new SimpleDateFormat("dd/MM/yyyy"); // Date date = (Date) formatter.parse(); Timestamp timeStampDate = new Timestamp(hssfRow.getCell(9).getDateCellValue().getTime()); SimpleDateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss"); String Fecha = dateFormat.format(timeStampDate); xls.setFecha(Fecha); xls.setPeriodo(String.valueOf(hssfRow.getCell(10).getNumericCellValue())); xls.setIdestado(hssfRow.getCell(11).getStringCellValue()); xls.setIdclieprov(String.valueOf(hssfRow.getCell(12).getNumericCellValue())); xls.setNropaleta(hssfRow.getCell(13).getStringCellValue()); xls.setIdenvase(hssfRow.getCell(14).getStringCellValue()); hssfRow.getCell(15).setCellType(XSSFCell.CELL_TYPE_STRING); xls.setIdsucursal((String) hssfRow.getCell(15).getStringCellValue()); String tempA = null; if (hssfRow.getCell(16) != null) { tempA = hssfRow.getCell(16).getStringCellValue(); } xls.setIdalmacen(tempA); String tempEm = null; if (hssfRow.getCell(17) != null) { tempEm = hssfRow.getCell(17).getStringCellValue(); } xls.setIdembalaje(tempEm); xls.setIdcultivo(hssfRow.getCell(18).getStringCellValue()); xls.setIdvariedad(hssfRow.getCell(19).getStringCellValue()); String tempO = null; if (hssfRow.getCell(20) != null) { tempO = hssfRow.getCell(20).getStringCellValue(); } xls.setObservaciones(tempO); xls.setVentana(hssfRow.getCell(21).getStringCellValue()); xls.setCantidad(String.valueOf(hssfRow.getCell(22).getNumericCellValue())); xls.setCerrado(String.valueOf(hssfRow.getCell(23).getNumericCellValue())); xls.setSincroniza(hssfRow.getCell(24).getStringCellValue()); //de string a timestamp y a string denuevo Timestamp timeStampDate2 = new Timestamp(hssfRow.getCell(25).getDateCellValue().getTime()); String FechaC = dateFormat.format(timeStampDate2); xls.setFechacreacion(FechaC); String temp = null; if (hssfRow.getCell(26) != null) { temp = hssfRow.getCell(26).getStringCellValue(); } xls.setNromanual(temp); hssfRow.getCell(27).setCellType(XSSFCell.CELL_TYPE_STRING); System.out.print( xls.getIdregistropaleta() + " " + (String) hssfRow.getCell(27).getStringCellValue()); xls.setIdclieprov_destino((String) hssfRow.getCell(27).getStringCellValue()); xls.setTipo(hssfRow.getCell(28).getStringCellValue()); exist = false; boolean vali = validarPaleExcel(xls, k); if (vali) { listPaletaUp.add(xls); } for (int i = 0; i < listPaletaUp.size() - 1; i++) { if ((listPaletaUp.get(i).getIdempresa() .equalsIgnoreCase(hssfRow.getCell(0).getStringCellValue()) && listPaletaUp.get(i).getIdregistropaleta() .equalsIgnoreCase(hssfRow.getCell(1).getStringCellValue())) && hssfRow.getRowNum() > 1) { exist = true; break; } } if (exist) { filaDuplicada = hssfRow.getRowNum() + 1; break; } } k++; } if (exist) { WebUtil.MensajeAlerta("Registro Duplicado. Fila : " + filaDuplicada + ". \n Verifique el Excel e Intntelo otra vez."); listPaletaUp.clear(); } Iterator<Row> rowIteratorD = hssfSheetD.rowIterator(); firstLinea = true; exist = false; int l = 0; while (rowIteratorD.hasNext()) { Row hssfRow = rowIteratorD.next(); if (firstLinea) { firstLinea = false; } else { Dpaleta xls = new Dpaleta(); xls.setIdempresa((int) hssfRow.getCell(0).getNumericCellValue()); xls.setIdregistropaleta(hssfRow.getCell(1).getStringCellValue()); xls.setItem(hssfRow.getCell(2).getStringCellValue()); xls.setIdclieprov(String.valueOf(hssfRow.getCell(3).getNumericCellValue())); xls.setIdlote(hssfRow.getCell(4).getStringCellValue()); xls.setIdcondicion(hssfRow.getCell(5).getStringCellValue()); xls.setIdtalla(hssfRow.getCell(6).getStringCellValue()); xls.setIdcolor(hssfRow.getCell(7).getStringCellValue()); xls.setCantidad(hssfRow.getCell(8).getNumericCellValue()); xls.setIdembalaje(hssfRow.getCell(9).getStringCellValue()); hssfRow.getCell(10).setCellType(XSSFCell.CELL_TYPE_STRING); xls.setIdproducto(hssfRow.getCell(10).getStringCellValue()); hssfRow.getCell(11).setCellType(XSSFCell.CELL_TYPE_STRING); xls.setIdlotep(hssfRow.getCell(11).getStringCellValue()); xls.setIdconsumidor(hssfRow.getCell(12).getStringCellValue()); xls.setIdlotecampo(hssfRow.getCell(13).getStringCellValue()); xls.setIdpresentacion(hssfRow.getCell(14).getStringCellValue()); exist = false; boolean vali = ValidarDPaleExcel(xls, l); if (vali) { listDPaletaUp.add(xls); } for (int i = 0; i < listDPaletaUp.size() - 1; i++) { if ((listDPaletaUp.get(i).getIdempresa() == (int) hssfRow.getCell(0).getNumericCellValue() && listDPaletaUp.get(i).getIdregistropaleta() .equalsIgnoreCase(hssfRow.getCell(1).getStringCellValue()) && listDPaletaUp.get(i).getItem() .equalsIgnoreCase(hssfRow.getCell(2).getStringCellValue())) && hssfRow.getRowNum() > 1) { exist = true; break; } } if (exist) { filaDuplicada = hssfRow.getRowNum() + 1; break; } } l++; } if (exist) { WebUtil.MensajeAlerta("Registro Duplicado. Fila : " + filaDuplicadaD + ". \n Verifique el Excel De Detalle e Intntelo otra vez."); listPaletaUp.clear(); } RequestContext.getCurrentInstance().update(":datos:tbas:tblRepet_data"); RequestContext.getCurrentInstance().update(":datos:tbas:tblDRepet"); RequestContext.getCurrentInstance().execute("PF('dlgIngrPro').show()"); } catch (IOException e) { System.out.println("Error en el Procesamiento : " + e.getMessage()); } }
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 {/* w w w.j a va2s . c om*/ 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 deleteTestSuitesFromXLSX(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {//from ww w . j ava 2 s. c o m 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
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 {// w w w. j av a2s. c o 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
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 {/* w ww. java2 s . co m*/ 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
public void addNew(String filePath, String testName, String cellValue[]) throws PhrescoException { try {// w w w.j a v a 2s . co 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); } }
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 {/* w w w. ja v a 2 s.c om*/ 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); } }