List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:com.pdf.GetPdf.java
public static void addXls(Document document, String url, String type) throws IOException, DocumentException { Iterator<Row> rowIterator; int colNo;/*from ww w. ja v a 2 s .c o m*/ if (type.equals("xls")) { HSSFWorkbook excelWorkbook = new HSSFWorkbook(new URL(url).openStream()); HSSFSheet my_worksheet = excelWorkbook.getSheetAt(0); rowIterator = my_worksheet.iterator(); colNo = my_worksheet.getRow(0).getLastCellNum(); } else { XSSFWorkbook excelWorkbook1 = new XSSFWorkbook(new URL(url).openStream()); XSSFSheet my_worksheet = excelWorkbook1.getSheetAt(0); rowIterator = my_worksheet.iterator(); colNo = my_worksheet.getRow(0).getLastCellNum(); } PdfPTable my_table = new PdfPTable(colNo); PdfPCell table_cell = null; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Read Rows from Excel document Iterator<Cell> cellIterator = row.cellIterator();//Read every column for every row that is READ while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Fetch CELL if (cell.getCellType() == (Cell.CELL_TYPE_NUMERIC)) { table_cell = new PdfPCell(new Phrase(new Double(cell.getNumericCellValue()).toString())); System.out.println(cell.getNumericCellValue()); my_table.addCell(table_cell); } else if (cell.getCellType() == (Cell.CELL_TYPE_STRING)) { table_cell = new PdfPCell(new Phrase(cell.getStringCellValue())); System.out.println(cell.getStringCellValue()); my_table.addCell(table_cell); } else if (cell.getCellType() == (Cell.CELL_TYPE_FORMULA)) { table_cell = new PdfPCell(new Phrase(cell.getCellFormula())); my_table.addCell(table_cell); } else if (cell.getCellType() == (Cell.CELL_TYPE_BLANK)) { table_cell = new PdfPCell(new Phrase("")); my_table.addCell(table_cell); } else { table_cell = new PdfPCell(new Phrase("")); my_table.addCell(table_cell); } } } document.add(my_table); }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/*from w w w . java2 s.c o m*/ StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 4; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 4); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO)."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Es Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Abreviatura Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla"); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("DESCRIPCION"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("ABREVIATURA"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); libro.write(file); file.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public void upExcel(FileUploadEvent event) throws ParseException { try {//from w w w. j a va2 s .c o m 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 StreamedContent downFormatExcel() throws Exception { InputStream stream = null;//from w w w . j a v a2 s . c o m StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_REGISTROPALE.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file2 = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_PALETA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); XSSFSheet hoja2 = libro.createSheet("IMPORTAR_DET_PALETA"); CreationHelper factory2 = libro.getCreationHelper(); hoja2 = libro.getSheetAt(1); XSSFCellStyle style2 = libro.createCellStyle(); Font font2 = libro.createFont(); Font font12 = libro.createFont(); Drawing drawing2 = hoja2.createDrawingPatriarch(); ClientAnchor anchor12 = factory2.createClientAnchor(); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeightInPoints((short) 8); font12.setFontHeightInPoints((short) 8); font12.setFontName("Arial"); font2.setFontName("Arial"); style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setAlignment(CellStyle.VERTICAL_CENTER); style2.setAlignment(CellStyle.ALIGN_CENTER); style2.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 29; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 5); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString("ADM:\nCampo Obligatorio \n - IDEMPRESA"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMPRESA"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - IDREGISTROPALETA. \n Debe de tener (15) caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDREGISTROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del emisor. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMISOR"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de la operacion.\n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 4) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Operacion.\n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMOPERACION"); celda.setCellComment(comment); } else if (f == 0 && c == 5) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del motivo de Paleta.\n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDMOTIVOPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 6) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id documento. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDDOCUMENTO"); celda.setCellComment(comment); } else if (f == 0 && c == 7) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Serie del Documento. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SERIE"); celda.setCellComment(comment); } else if (f == 0 && c == 8) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Documento.\n -Debe tener 7 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NUMERO"); celda.setCellComment(comment); } else if (f == 0 && c == 9) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHA"); celda.setCellComment(comment); } else if (f == 0 && c == 10) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Perido del ao \n - fromato YYYYMM."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("PERIODO"); celda.setCellComment(comment); } else if (f == 0 && c == 11) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del estado \n -Debe tener 2 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDESTADO"); celda.setCellComment(comment); } else if (f == 0 && c == 12) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del cliente o proveedor \n -Debe tener 11 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV"); celda.setCellComment(comment); } else if (f == 0 && c == 13) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Paleta \n -Debe tener 20 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROPALETA"); celda.setCellComment(comment); } else if (f == 0 && c == 14) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de envase \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDENVASE"); celda.setCellComment(comment); } else if (f == 0 && c == 15) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id la sucursal \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDSUCURSAL"); celda.setCellComment(comment); } else if (f == 0 && c == 16) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del almacen. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDALMACEN"); celda.setCellComment(comment); } else if (f == 0 && c == 17) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id del embalaje. \n -Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDEMBALAJE"); celda.setCellComment(comment); } else if (f == 0 && c == 18) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Id de cultivo. \n -Debe tener 4 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCULTIVO"); celda.setCellComment(comment); } else if (f == 0 && c == 19) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - id de Variadd. \n -Debe tener 3 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDVARIEDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 20) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Observaciones sobre la paleta \n -como maximo 240 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("OBSERVACIONES"); celda.setCellComment(comment); } else if (f == 0 && c == 21) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n -Nombre de la venta \n como maximo 50 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("VENTANA"); celda.setCellComment(comment); } else if (f == 0 && c == 22) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Cantidad. \n - 15 numeros y 2 decimales como maximo."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CANTIDAD"); celda.setCellComment(comment); } else if (f == 0 && c == 23) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Estado de la paleta \n- 1 = cerrado, 0 = Abierto."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("CERRADO"); celda.setCellComment(comment); } else if (f == 0 && c == 24) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Sincroniza \n - N = no , S = si."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("SINCRONIZA"); celda.setCellComment(comment); } else if (f == 0 && c == 25) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("FECHACREACION"); celda.setCellComment(comment); } else if (f == 0 && c == 26) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Numero de Manural\n Debe tener 10 caracteres."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("NROMANUAL"); celda.setCellComment(comment); } else if (f == 0 && c == 27) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - idcliepro-destino\n debe tener 11 caracteres"); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("IDCLIEPROV_DESTINO"); celda.setCellComment(comment); } else if (f == 0 && c == 28) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Tipo de paleta\n debe tener 1 caraccter.."); str.applyFont(font1); str.applyFont(0, 19, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("TIPO"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); hoja.autoSizeColumn((short) 3); hoja.autoSizeColumn((short) 4); hoja.autoSizeColumn((short) 5); hoja.autoSizeColumn((short) 6); hoja.autoSizeColumn((short) 7); hoja.autoSizeColumn((short) 8); hoja.autoSizeColumn((short) 9); hoja.autoSizeColumn((short) 10); hoja.autoSizeColumn((short) 11); hoja.autoSizeColumn((short) 12); hoja.autoSizeColumn((short) 13); hoja.autoSizeColumn((short) 14); hoja.autoSizeColumn((short) 15); hoja.autoSizeColumn((short) 16); hoja.autoSizeColumn((short) 17); hoja.autoSizeColumn((short) 18); hoja.autoSizeColumn((short) 19); hoja.autoSizeColumn((short) 20); hoja.autoSizeColumn((short) 21); hoja.autoSizeColumn((short) 22); hoja.autoSizeColumn((short) 23); hoja.autoSizeColumn((short) 24); hoja.autoSizeColumn((short) 25); hoja.autoSizeColumn((short) 26); hoja.autoSizeColumn((short) 27); hoja.autoSizeColumn((short) 28); for (int f = 0; f < 2; f++) { XSSFRow fila2 = hoja2.createRow(f); if (f == 0) { for (int c = 0; c < 15; c++) { XSSFCell celda2 = fila2.createCell(c); anchor12.setCol1(celda2.getColumnIndex()); anchor12.setCol2(celda2.getColumnIndex() + 8); anchor12.setRow1(fila2.getRowNum()); anchor12.setRow2(fila2.getRowNum() + 8); Comment comment2 = drawing2.createCellComment(anchor12); RichTextString str; switch (c) { case 0: celda2.setCellStyle(style2); str = factory2.createRichTextString( "ADM:\nCampo Obligatorio \n - El Cdigo debe de ser nico."); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMPRESA"); celda2.setCellComment(comment2); break; case 1: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDREGISTROPALETA"); celda2.setCellComment(comment2); break; case 2: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("ITEM"); celda2.setCellComment(comment2); break; case 3: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio "); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCLIEPROV"); celda2.setCellComment(comment2); break; case 4: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTE"); celda2.setCellComment(comment2); break; case 5: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONDICION"); celda2.setCellComment(comment2); break; case 6: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDTALLA"); celda2.setCellComment(comment2); break; case 7: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCOLOR"); celda2.setCellComment(comment2); break; case 8: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("CANTIDAD"); celda2.setCellComment(comment2); break; case 9: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDEMBALAJE"); celda2.setCellComment(comment2); break; case 10: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRODUCTO"); celda2.setCellComment(comment2); break; case 11: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTEP"); celda2.setCellComment(comment2); break; case 12: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDCONSUMIDOR"); celda2.setCellComment(comment2); break; case 13: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDLOTECAMPO"); celda2.setCellComment(comment2); break; case 14: celda2.setCellStyle(style2); str = factory.createRichTextString("ADM:\nCampo Obligatorio"); str.applyFont(font12); str.applyFont(0, 19, font2); comment2.setString(str); comment2.setAuthor("ADM"); celda2.setCellValue("IDPRESENTACION"); celda2.setCellComment(comment2); break; } } } } hoja2.autoSizeColumn((short) 0); hoja2.autoSizeColumn((short) 1); hoja2.autoSizeColumn((short) 2); hoja2.autoSizeColumn((short) 3); hoja2.autoSizeColumn((short) 4); hoja2.autoSizeColumn((short) 5); hoja2.autoSizeColumn((short) 6); hoja2.autoSizeColumn((short) 7); hoja2.autoSizeColumn((short) 8); hoja2.autoSizeColumn((short) 9); hoja2.autoSizeColumn((short) 10); hoja2.autoSizeColumn((short) 11); hoja2.autoSizeColumn((short) 12); hoja2.autoSizeColumn((short) 13); hoja2.autoSizeColumn((short) 14); hoja2.autoSizeColumn((short) 15); libro.write(file2); file2.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_REGISTROPALE.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public void upExcel(FileUploadEvent event) throws ParseException, Exception { try {/*from w ww.ja v a 2 s. c om*/ 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 w w w. 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.framework.commons.FrameworkUtil.java
License:Apache License
public boolean deleteTestSuitesFromXLSX(String filePath, String testSuiteName) { boolean hasTrue = false; Iterator<Row> rowIterator; try {/*from w w w. java 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
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();/* w ww .j ava 2 s . co 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 {// w w w . j a v a 2 s . 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();//from w w w . j a v a2s . 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(); } } } }