List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet rowIterator
@Override @SuppressWarnings("unchecked") public Iterator<Row> rowIterator()
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public void upExcel(FileUploadEvent event) throws ParseException { try {//from www .j av a2s .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 {//from w w w.j ava 2 s . 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.pe.nisira.movil.view.action.ZonaGeneralAction.java
public void upExcel(FileUploadEvent event) throws ParseException { try {/*from www. ja v a 2 s . c o m*/ if (getDatoEdicion().getANCHO() != 0 && getDatoEdicion().getLARGO() != 0) { ListO = new ArrayList<DiagExcel>(); upFile = event.getFile(); XSSFWorkbook workBook = new XSSFWorkbook(upFile.getInputstream()); XSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator<Row> rowIterator = hssfSheet.rowIterator(); List<Double> maxX = new ArrayList<Double>(); List<Double> maxY = new ArrayList<Double>(); boolean firstLinea = true; boolean terminado = false; while (rowIterator.hasNext()) { Row hssfRow = rowIterator.next(); if (firstLinea) { firstLinea = false; } else { DiagExcel O = new DiagExcel(); hssfRow.getCell(0).setCellType(XSSFCell.CELL_TYPE_STRING);//Color hssfRow.getCell(1).setCellType(XSSFCell.CELL_TYPE_STRING);//Layer hssfRow.getCell(2).setCellType(XSSFCell.CELL_TYPE_STRING);//Position X hssfRow.getCell(3).setCellType(XSSFCell.CELL_TYPE_STRING);//Position Y hssfRow.getCell(4).setCellType(XSSFCell.CELL_TYPE_STRING);//Value hssfRow.getCell(5).setCellType(XSSFCell.CELL_TYPE_STRING);//Zona hssfRow.getCell(6).setCellType(XSSFCell.CELL_TYPE_STRING);//Tipo hssfRow.getCell(7).setCellType(XSSFCell.CELL_TYPE_STRING);//Proceso hssfRow.getCell(8).setCellType(XSSFCell.CELL_TYPE_STRING);//nX hssfRow.getCell(9).setCellType(XSSFCell.CELL_TYPE_STRING);//nY hssfRow.getCell(10).setCellType(XSSFCell.CELL_TYPE_STRING);//Punto hssfRow.getCell(11).setCellType(XSSFCell.CELL_TYPE_STRING);//Piso hssfRow.getCell(12).setCellType(XSSFCell.CELL_TYPE_STRING);//TipoZona O.setColor((String) hssfRow.getCell(0).getStringCellValue()); O.setLayer((String) hssfRow.getCell(1).getStringCellValue()); O.setPositionX(Double.parseDouble(hssfRow.getCell(2).getStringCellValue())); O.setPositionY(Double.parseDouble(hssfRow.getCell(3).getStringCellValue())); O.setValue(Double.parseDouble(hssfRow.getCell(4).getStringCellValue())); O.setZona((String) hssfRow.getCell(5).getStringCellValue()); O.setTipo((String) hssfRow.getCell(6).getStringCellValue()); O.setProceso((String) hssfRow.getCell(7).getStringCellValue()); O.setnX(Double.parseDouble(hssfRow.getCell(8).getStringCellValue())); O.setnY(Double.parseDouble(hssfRow.getCell(9).getStringCellValue())); O.setPunto((String) hssfRow.getCell(10).getStringCellValue()); if (!hssfRow.getCell(11).getStringCellValue().equalsIgnoreCase("")) { O.setPisos(Integer.parseInt(hssfRow.getCell(11).getStringCellValue())); } O.setTipoZona(Integer.parseInt(hssfRow.getCell(12).getStringCellValue())); O.setChecked(false); ListO.add(O); } } NuevoDDistribucionUbicacion(); generaZonas(); genDiagZonas(); graZonaGen(); System.out.println("list"); } else { WebUtil.MensajeAdvertencia("Largo o Ancho Son Cero"); } } catch (IOException e) { System.out.println("Error en el Procesamiento : " + e.getMessage()); } catch (Exception ex) { System.out.println("Error en el Procesamiento : " + ex.getMessage()); } }
From source file:com.photon.phresco.eshop.service.EShopPublishService.java
License:Apache License
public List<CategoryHBM> getCategoriesFromExcel() throws Exception { System.out.println("Inside getCategoriesFromExcel"); XSSFWorkbook workbook = new XSSFWorkbook(is); System.out.println("workbook = " + workbook); XSSFSheet sheet = workbook.getSheet(EXCEL_SHEET_CATEGORIES); System.out.println("Sheet = " + sheet); List<CategoryHBM> categories = new ArrayList<CategoryHBM>(10); Iterator<Row> itr = sheet.rowIterator(); System.out.println("itr " + itr); for (int i = 0; i < 1; i++) { itr.next();/* ww w. j av a 2s .co m*/ } while (itr.hasNext()) { try { Row row = itr.next(); System.out.println("Row 0 value" + row.getCell(0)); int categoryId = (int) row.getCell(0).getNumericCellValue(); String categoryName = ServiceUtil.getValue(row.getCell(1)); String categoryImage = ServiceUtil.getValue(row.getCell(2)); String categoryDetailsImage = ServiceUtil.getValue(row.getCell(3)); int categoryParentId = 0; String categoryDescription = ServiceUtil.getValue(row.getCell(5)); CategoryHBM category = new CategoryHBM(categoryId, categoryName, categoryImage, categoryDetailsImage, categoryParentId, categoryDescription); categories.add(category); } catch (Exception e) { e.printStackTrace(); } } return categories; }
From source file:com.photon.phresco.eshop.service.EShopPublishService.java
License:Apache License
public List<ProductHBM> getProductsFromExcel() throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(is); XSSFSheet sheet = workbook.getSheet(EXCEL_SHEET_PRODUCTS); List<ProductHBM> products = new ArrayList<ProductHBM>(10); Iterator<Row> itr = sheet.rowIterator(); for (int i = 0; i < 1; i++) { itr.next();/*from w w w . j av a2 s.c o m*/ } while (itr.hasNext()) { try { Row row = itr.next(); int productId = (int) row.getCell(0).getNumericCellValue(); String productName = ServiceUtil.getValue(row.getCell(1)); System.out.println("productId = " + productId); String manufacturer = ServiceUtil.getValue(row.getCell(2)); int categoryId = (int) row.getCell(3).getNumericCellValue(); System.out.println("categoryId = " + categoryId); String model = ServiceUtil.getValue(row.getCell(4)); String specialProductStr = ServiceUtil.getValue(row.getCell(5)); String newProductStr = ServiceUtil.getValue(row.getCell(6)); Integer specialProduct = (YES.equals(specialProductStr) ? 1 : 0); Integer newProduct = (YES.equals(newProductStr) ? 1 : 0); Double listPrice = row.getCell(7).getNumericCellValue(); System.out.println("listPrice = " + listPrice); Double sellPrice = row.getCell(8).getNumericCellValue(); System.out.println("sellPrice = " + sellPrice); String productDescription = ServiceUtil.getValue(row.getCell(9)); String productImage = ServiceUtil.getValue(row.getCell(10)); String productDetailImage = ServiceUtil.getValue(row.getCell(11)); Date createdDate = new Date(); Date lastUpdatedDate = new Date(); ProductHBM product = new ProductHBM(productId, productName, manufacturer, categoryId, model, specialProduct, newProduct, listPrice, sellPrice, productDescription, productImage, productDetailImage, createdDate, lastUpdatedDate); products.add(product); } catch (Exception e) { System.out.println("Error: " + e.getMessage()); } } return products; }
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 {/*w ww . j av a2 s . co 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
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 w ww . j ava2s . c o m*/ } 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 ww w . ja v a2 s . c o 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
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();//from w ww . j av a 2 s . 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 {/*from ww w . ja va 2s . 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); } }