List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet
@Override
public HSSFSheet getSheet(String name)
From source file:Compras.Conciliacion.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: try {//from w ww . java2 s .co m javax.swing.JFileChooser archivo = new javax.swing.JFileChooser(); archivo.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null; if (archivo.showSaveDialog(null) == archivo.APPROVE_OPTION) { ruta = archivo.getSelectedFile().getAbsolutePath(); if (ruta != null) { File archivoXLS = new File(ruta + ".xls"); File plantilla = new File("imagenes/plantillaConciliacion.xls"); Session session = HibernateUtil.getSessionFactory().openSession(); ArrayList datos = new ArrayList(); Query query = session.createSQLQuery( "select compania.nombre, orden.tipo_nombre, orden.modelo, orden.no_serie, clientes.nombre as nombres,orden.id_orden \n" + "from orden inner join compania on compania.id_compania=orden.id_compania inner join clientes on clientes.id_clientes=orden.id_cliente\n" + "where orden.id_orden=" + Integer.parseInt(orden) + ""); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); datos = (ArrayList) query.list(); // Path FROM = Paths.get("imagenes/plantillaConciliacion.xls"); Path TO = Paths.get(ruta + ".xls"); //sobreescribir el fichero de destino, si existe, y copiar // los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); HSSFWorkbook libro = new HSSFWorkbook(fsFileSystem); libro.getSheet("Conciliacion").getRow(0).getCell(6) .setCellValue("CONCILIACIN PARA FACTURACIN"); for (int i = 0; i < datos.size(); i++) { java.util.HashMap map = (java.util.HashMap) datos.get(i); libro.getSheet("Conciliacion").getRow(1).getCell(2) .setCellValue(map.get("nombre").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(2) .setCellValue(map.get("tipo_nombre").toString()); libro.getSheet("Conciliacion").getRow(3).getCell(2) .setCellValue(map.get("modelo").toString()); libro.getSheet("Conciliacion").getRow(4).getCell(2) .setCellValue(map.get("no_serie").toString()); libro.getSheet("Conciliacion").getRow(5).getCell(2) .setCellValue(map.get("nombres").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(12) .setCellValue(map.get("id_orden").toString()); } HSSFCellStyle borde_d = libro.createCellStyle(); borde_d.setBorderBottom(CellStyle.BORDER_THIN); borde_d.setBorderTop(CellStyle.BORDER_THIN); borde_d.setBorderRight(CellStyle.BORDER_THIN); borde_d.setBorderLeft(CellStyle.BORDER_THIN); borde_d.setAlignment(CellStyle.ALIGN_RIGHT); HSSFCellStyle borde_i = libro.createCellStyle(); borde_i.setBorderBottom(CellStyle.BORDER_THIN); borde_i.setBorderTop(CellStyle.BORDER_THIN); borde_i.setBorderRight(CellStyle.BORDER_THIN); borde_i.setBorderLeft(CellStyle.BORDER_THIN); borde_i.setAlignment(CellStyle.ALIGN_LEFT); HSSFCellStyle borde_c = libro.createCellStyle(); borde_c.setBorderBottom(CellStyle.BORDER_THIN); borde_c.setBorderTop(CellStyle.BORDER_THIN); borde_c.setBorderRight(CellStyle.BORDER_THIN); borde_c.setBorderLeft(CellStyle.BORDER_THIN); borde_c.setAlignment(CellStyle.ALIGN_CENTER); HSSFCellStyle borde_dr = libro.createCellStyle(); borde_dr.setBorderBottom(CellStyle.BORDER_THIN); borde_dr.setBorderTop(CellStyle.BORDER_THIN); borde_dr.setBorderRight(CellStyle.BORDER_THIN); borde_dr.setBorderLeft(CellStyle.BORDER_THIN); borde_dr.setAlignment(CellStyle.ALIGN_RIGHT); borde_dr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_dr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_dr.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_ir = libro.createCellStyle(); borde_ir.setBorderBottom(CellStyle.BORDER_THIN); borde_ir.setBorderTop(CellStyle.BORDER_THIN); borde_ir.setBorderRight(CellStyle.BORDER_THIN); borde_ir.setBorderLeft(CellStyle.BORDER_THIN); borde_ir.setAlignment(CellStyle.ALIGN_LEFT); borde_ir.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_ir.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_ir.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_cr = libro.createCellStyle(); borde_cr.setBorderBottom(CellStyle.BORDER_THIN); borde_cr.setBorderTop(CellStyle.BORDER_THIN); borde_cr.setBorderRight(CellStyle.BORDER_THIN); borde_cr.setBorderLeft(CellStyle.BORDER_THIN); borde_cr.setAlignment(CellStyle.ALIGN_CENTER); borde_cr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_cr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_cr.setFillForegroundColor(HSSFColor.YELLOW.index); DecimalFormat formatoDecimal = new DecimalFormat("####0.0"); DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); int miRenglon = 9; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == true || (boolean) t_datos.getValueAt(i, 4) == true) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue("V"); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } //font1.setColor(BaseColor.WHITE); libro.getSheet("Conciliacion").createRow(miRenglon); libro.getSheet("Conciliacion") .addMergedRegion(new CellRangeAddress(miRenglon, miRenglon, 0, 14)); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue("Faltante en Vales"); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0).setCellStyle(borde_c); miRenglon++; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == false && (boolean) t_datos.getValueAt(i, 4) == false && t_datos.getValueAt(i, 5) != null) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue(""); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } FileOutputStream archivo1 = new FileOutputStream(archivoXLS); libro.write(archivo1); archivo1.close(); Desktop.getDesktop().open(archivoXLS); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:Control.CtrlCredencial.java
/** * This method is used to read the data's from an excel file. * * @param fileName - Name of the excel file. *///www.j a v a2s. c o m public ArrayList readExcelFile(String fileName) { /** * Create a new instance for cellDataList */ List cellDataList = new ArrayList(); try { /** * Create a new instance for FileInputStream class */ FileInputStream fileInputStream = new FileInputStream(fileName); /** * Create a new instance for POIFSFileSystem class */ //POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); /* * Create a new instance for HSSFWorkBook Class */ HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet hssfSheet = workbook.getSheet("Pregrado - 2013-02"); /** * Iterate the rows and cells of the spreadsheet to get all the * datas. */ Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); List cellTempList = new ArrayList(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); cellTempList.add(hssfCell); // } cellDataList.add(cellTempList); } } catch (Exception e) { e.printStackTrace(); } /** * Call the printToConsole method to print the cell data in the console. */ return credencialesToArray(cellDataList); }
From source file:data.pkg.ReadWriteExcelFile.java
public static void readXLSFile(String path) throws IOException { File excel = new File(path); FileInputStream fis = new FileInputStream(excel); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet ws = wb.getSheet("Sheet1"); int rowNum = ws.getLastRowNum() + 1; int colNum = ws.getRow(0).getLastCellNum(); for (int i = 0; i < rowNum; i++) { HSSFRow row = ws.getRow(i);/*w w w .j av a2 s .co m*/ for (int j = 0; j < colNum; j++) { HSSFCell cell = row.getCell(j); if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { long value = Long.parseLong(cell.toString()); if (j == 0) { dataOpen.addDeslocamento(value); System.out.println(value); } if (j == 1) { dataOpen.addForca(value); System.out.println(value); } } } } }
From source file:ddf.metrics.reporting.internal.rest.MetricsEndpointTest.java
License:Open Source License
@Test public void testGetMetricsDataAsXls() throws Exception { // Create RRD file that Metrics Endpoint will detect int dateOffset = 900; // 15 minutes in seconds createRrdFile(dateOffset);//from w w w.jav a2 s .c o m UriInfo uriInfo = createUriInfo(); // Get the metrics data from the endpoint MetricsEndpoint endpoint = getEndpoint(); endpoint.setMetricsDir(TEST_DIR); Response response = endpoint.getMetricsData("uptime", "xls", null, null, Integer.toString(dateOffset), "my label", "my title", uriInfo); cleanupRrd(); InputStream xls = (InputStream) response.getEntity(); assertThat(xls, not(nullValue())); HSSFWorkbook wb = new HSSFWorkbook(xls); assertThat(wb.getNumberOfSheets(), equalTo(1)); HSSFSheet sheet = wb.getSheet("Uptime"); assertThat(sheet, not(nullValue())); // Expect 7 rows: title + blank + column headers + 2 rows of samples + blank + // totalQueryCount assertThat(sheet.getPhysicalNumberOfRows(), equalTo(7)); // first row should have title in first cell HSSFRow row = sheet.getRow(0); assertThat(row, not(nullValue())); assertThat(row.getCell(0).getStringCellValue(), startsWith("Uptime for")); // third row should have column headers in first and second cells row = sheet.getRow(2); assertThat(row.getCell(0).getStringCellValue(), equalTo("Timestamp")); assertThat(row.getCell(1).getStringCellValue(), equalTo("Value")); // should have 2 rows of samples' data row = sheet.getRow(3); assertThat(row.getCell(0).getStringCellValue(), not(nullValue())); assertThat(row.getCell(1).getNumericCellValue(), not(nullValue())); row = sheet.getRow(4); assertThat(row.getCell(0).getStringCellValue(), not(nullValue())); assertThat(row.getCell(1).getNumericCellValue(), not(nullValue())); // last row should have totalQueryCount in first cell row = sheet.getRow(sheet.getLastRowNum()); assertThat(row.getCell(0).getStringCellValue(), startsWith("Total Count:")); assertThat(row.getCell(1).getNumericCellValue(), not(nullValue())); }
From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java
License:Open Source License
@Test public void testMetricsXlsDataWithCounter() throws Exception { String rrdFilename = TEST_DIR + "queryCount_Counter" + RRD_FILE_EXTENSION; long endTime = new RrdFileBuilder().rrdFileName(rrdFilename).build(); MetricsRetriever metricsRetriever = new RrdMetricsRetriever(); OutputStream os = metricsRetriever.createXlsData("queryCount", rrdFilename, START_TIME, endTime); InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray()); assertThat(xls, not(nullValue()));//from ww w. j av a 2 s. c o m HSSFWorkbook wb = new HSSFWorkbook(xls); assertThat(wb.getNumberOfSheets(), equalTo(1)); HSSFSheet sheet = wb.getSheet("Query Count"); if (null != sheet) { assertThat(sheet, not(nullValue())); verifyWorksheet(sheet, "Query Count", 6, true); } else { fail(); } }
From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java
License:Open Source License
@Test public void testMetricsXlsDataWithGauge() throws Exception { String rrdFilename = TEST_DIR + "queryCount_Gauge" + RRD_FILE_EXTENSION; long endTime = new RrdFileBuilder().rrdFileName(rrdFilename).dsType(DsType.GAUGE).build(); MetricsRetriever metricsRetriever = new RrdMetricsRetriever(); OutputStream os = metricsRetriever.createXlsData("queryCount", rrdFilename, START_TIME, endTime); InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray()); assertThat(xls, not(nullValue()));//from w w w.j a v a 2s . c om HSSFWorkbook wb = new HSSFWorkbook(xls); assertThat(wb.getNumberOfSheets(), equalTo(1)); HSSFSheet sheet = wb.getSheet("Query Count"); if (null != sheet) { assertThat(sheet, not(nullValue())); verifyWorksheet(sheet, "Query Count", 6, false); } else { fail(); } }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private int[] doImportErlenbacherFW(HSSFWorkbook wb, JProgressBar progress, String efsaID) { int numSuccess = 0; int numFails = 0; HSSFSheet businessSheet = wb.getSheet("Business_List"); HSSFSheet transactionSheet = wb.getSheet("Receivers"); if (transactionSheet != null) { int numRows = transactionSheet.getLastRowNum() + 1; progress.setMaximum(numRows);/*from w ww.j a v a2 s . c o m*/ progress.setValue(0); HSSFRow busRow = getRow(businessSheet, efsaID, 0); String idLST = getStrVal(busRow.getCell(0)); String nameLST = getStrVal(busRow.getCell(1)); String streetLST = getStrVal(busRow.getCell(2)); String streetNoLST = getStrVal(busRow.getCell(3), 10); String zipLST = getStrVal(busRow.getCell(4), 10); String cityLST = getStrVal(busRow.getCell(5)); String countyLST = getStrVal(busRow.getCell(6), 30); String countryLST = getStrVal(busRow.getCell(7)); String vatLST = getStrVal(busRow.getCell(8)); for (int i = 2; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String product = getStrVal(row.getCell(2)); String DeliveryDate_Day = getStrVal(row.getCell(14)); String DeliveryDate_Month = getStrVal(row.getCell(15)); String DeliveryDate_Year = getStrVal(row.getCell(16)); String Amount = getStrVal(row.getCell(12)); String Lot_Invoice = getStrVal(row.getCell(4)); String id = null; String name = getStrVal(row.getCell(5)); // String street = null; String streetNo = null; String zip = null; String city = getStrVal(row.getCell(6)); // String county = null; String country = getStrVal(row.getCell(7)); // String vat = getStrVal(row.getCell(8)); // getCharge_Lieferung(idLST, nameLST, streetLST, streetNoLST, zipLST, cityLST, countyLST, countryLST, null, vatLST, product, null, null, Lot_Invoice, null, null, null, null, null, null, null, DeliveryDate_Day, DeliveryDate_Month, DeliveryDate_Year, Amount, null, null, id, name, street, streetNo, zip, city, county, country, null, vat, "Erlenbacher" + efsaID + "_" + (i + 1), null, null, null, null, null); } } } return new int[] { numSuccess, numFails }; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private int[] doImportMaciel(HSSFWorkbook wb, JProgressBar progress, String efsaID) { int numSuccess = 0; int numFails = 0; HSSFSheet businessSheet = wb.getSheet("Business_List"); HSSFSheet transactionSheet = wb.getSheet("Receivers"); if (transactionSheet != null) { int numRows = transactionSheet.getLastRowNum() + 1; progress.setMaximum(numRows);// ww w . j a v a 2 s . co m progress.setValue(0); HSSFRow busRow = getRow(businessSheet, efsaID, 0); String idLST = getStrVal(busRow.getCell(0)); String nameLST = getStrVal(busRow.getCell(1)); String streetLST = getStrVal(busRow.getCell(2)); String streetNoLST = getStrVal(busRow.getCell(3), 10); String zipLST = getStrVal(busRow.getCell(4), 10); String cityLST = getStrVal(busRow.getCell(5)); String countyLST = getStrVal(busRow.getCell(6), 30); String countryLST = getStrVal(busRow.getCell(7)); String vatLST = getStrVal(busRow.getCell(8)); for (int i = 2; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String addressOther = getStrVal(row.getCell(7)); String product = getStrVal(row.getCell(8)); String DeliveryDate_Day = getStrVal(row.getCell(9)); String DeliveryDate_Month = getStrVal(row.getCell(10)); String DeliveryDate_Year = getStrVal(row.getCell(11)); String Amount = getStrVal(row.getCell(12)); String Lot_Invoice = getStrVal(row.getCell(13)); busRow = getRow(businessSheet, addressOther, 9); if (busRow != null) { String id = getStrVal(busRow.getCell(0)); // String name = getStrVal(busRow.getCell(1)); // String street = getStrVal(busRow.getCell(2)); // String streetNo = getStrVal(busRow.getCell(3), 10); // String zip = getStrVal(busRow.getCell(4), 10); // String city = getStrVal(busRow.getCell(5)); // String county = getStrVal(busRow.getCell(6), 30); String country = getStrVal(busRow.getCell(7)); // String vat = getStrVal(busRow.getCell(8)); // getCharge_Lieferung(idLST, nameLST, streetLST, streetNoLST, zipLST, cityLST, countyLST, countryLST, null, vatLST, product, null, null, Lot_Invoice, null, null, null, null, null, null, null, DeliveryDate_Day, DeliveryDate_Month, DeliveryDate_Year, Amount, null, null, id, name, street, streetNo, zip, city, county, country, null, vat, "LSTZAKNoris" + efsaID + "_" + (i + 1), null, null, null, null, null); } else if (addressOther != null) { System.err.println("busRow = null... addressOther: " + addressOther + "\tRow: " + (i + 1)); } } } } transactionSheet = wb.getSheet("Suppliers"); if (transactionSheet != null) { int numRows = transactionSheet.getLastRowNum() + 1; progress.setMaximum(numRows); progress.setValue(0); HSSFRow busRow = getRow(businessSheet, efsaID, 0); String idLST = getStrVal(busRow.getCell(0)); String nameLST = getStrVal(busRow.getCell(1)); String streetLST = getStrVal(busRow.getCell(2)); String streetNoLST = getStrVal(busRow.getCell(3), 10); String zipLST = getStrVal(busRow.getCell(4), 10); String cityLST = getStrVal(busRow.getCell(5)); String countyLST = getStrVal(busRow.getCell(6), 30); String countryLST = getStrVal(busRow.getCell(7)); String vatLST = getStrVal(busRow.getCell(8)); for (int i = 2; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String addressOther = getStrVal(row.getCell(7)); String product = getStrVal(row.getCell(8)); String DeliveryDate_Day = getStrVal(row.getCell(9)); String DeliveryDate_Month = getStrVal(row.getCell(10)); String DeliveryDate_Year = getStrVal(row.getCell(11)); String Amount = getStrVal(row.getCell(12)); String Lot_Invoice = getStrVal(row.getCell(13)); busRow = getRow(businessSheet, addressOther, 9); if (busRow != null) { String id = getStrVal(busRow.getCell(0)); // String name = getStrVal(busRow.getCell(1)); // String street = getStrVal(busRow.getCell(2)); // String streetNo = getStrVal(busRow.getCell(3), 10); // String zip = getStrVal(busRow.getCell(4), 10); // String city = getStrVal(busRow.getCell(5)); // String county = getStrVal(busRow.getCell(6), 30); String country = getStrVal(busRow.getCell(7)); // String vat = getStrVal(busRow.getCell(8)); // getCharge_Lieferung(id, name, street, streetNo, zip, city, county, country, null, vat, product, null, null, Lot_Invoice, null, null, null, null, null, null, null, DeliveryDate_Day, DeliveryDate_Month, DeliveryDate_Year, Amount, null, null, idLST, nameLST, streetLST, streetNoLST, zipLST, cityLST, countyLST, countryLST, null, vatLST, "LSTZAKNoris" + efsaID + "_Sup_" + (i + 1), null, null, null, null, null); } else if (addressOther != null) { System.err.println( "suppliers busRow = null... addressOther: " + addressOther + "\tRow: " + (i + 1)); } } } } return new int[] { numSuccess, numFails }; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private int[] doImportGaia(HSSFWorkbook wb, JProgressBar progress) { int numSuccess = 0; int numFails = 0; HSSFSheet transactionSheet = wb.getSheet("Transactions"); HSSFSheet businessSheet = wb.getSheet("Business_List"); int numRows = transactionSheet.getLastRowNum() + 1; progress.setMaximum(numRows);// w w w . j a v a 2 s . c o m progress.setValue(0); for (int i = 1; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String idRec = getStrVal(row.getCell(0)); String adressRec = getStrVal(row.getCell(1)); String countryRec = getStrVal(row.getCell(2)); String nameRec = adressRec; String streetRec = null; String streetNoRec = null; String zipRec = null; String cityRec = null; String countyRec = null; String vatRec = null; HSSFRow busRow = getRow(businessSheet, idRec, 0); if (busRow != null) { nameRec = getStrVal(busRow.getCell(1)); // streetRec = getStrVal(busRow.getCell(2)); // streetNoRec = getStrVal(busRow.getCell(3), 10); // zipRec = getStrVal(busRow.getCell(4), 10); // cityRec = getStrVal(busRow.getCell(5)); // countyRec = getStrVal(busRow.getCell(6), 30); countryRec = getStrVal(busRow.getCell(7)); // vatRec = getStrVal(busRow.getCell(8)); // if (!adressRec.startsWith(nameRec)) { //System.err.println("Id issue on recs... " + nameRec + " <> " + adressRec); } } else if (idRec != null) { System.err.println("business not there??? Row: " + (i + 1) + "\tidReceived: " + idRec); } else { System.err.println("idRec is null??? Row: " + (i + 1) + "\t" + nameRec + (nameRec != null ? "" : " -> Station not defined")); } String prodName = getStrVal(row.getCell(3)); String type = getStrVal(row.getCell(4)); if (type != null) prodName += "(" + type + ")"; String idSup = getStrVal(row.getCell(5)); String adressSup = getStrVal(row.getCell(6)); String countrySup = getStrVal(row.getCell(7)); String nameSup = adressSup; String streetSup = null; String streetNoSup = null; String zipSup = null; String citySup = null; String countySup = null; String vatSup = null; busRow = getRow(businessSheet, idSup, 0); if (busRow != null) { nameSup = getStrVal(busRow.getCell(1)); // streetSup = getStrVal(busRow.getCell(2)); // streetNoSup = getStrVal(busRow.getCell(3), 10); // zipSup = getStrVal(busRow.getCell(4), 10); // citySup = getStrVal(busRow.getCell(5)); // countySup = getStrVal(busRow.getCell(6), 30); countrySup = getStrVal(busRow.getCell(7)); // vatSup = getStrVal(busRow.getCell(8)); // if (!adressSup.startsWith(nameSup)) { //System.err.println("Id issue on sups... Row: " + (i+1) + "\t" + nameSup + " <> " + adressSup); } } else if (idSup != null) { System.err.println("business not there??? Row: " + (i + 1) + "\tidSupplier: " + idSup); } else { System.err.println("idSup is null??? Row: " + (i + 1) + "\t" + nameSup + (nameSup != null ? "" : " -> Station not defined")); } String anno = getStrVal(row.getCell(8)); // anno String sitoweb = getStrVal(row.getCell(9)); // Sito Web String oc = getStrVal(row.getCell(10)); // OriginCountry String cqr = (anno == null ? "" : anno) + (sitoweb == null ? "" : "\t" + sitoweb); String serial = "Gaia_" + (i + 1); Integer c1 = null; if (nameSup != null && !nameSup.trim().isEmpty()) { Integer[] c = getCharge_Lieferung(idSup, nameSup.trim(), streetSup, streetNoSup, zipSup, citySup, countySup, countrySup, null, vatSup, prodName, null, null, null, null, null, null, null, null, null, oc, null, null, null, null, null, null, idRec, nameRec.trim(), streetRec, streetNoRec, zipRec, cityRec, countyRec, countryRec, null, vatRec, serial, cqr, null, null, null, null); if (c != null) c1 = c[2]; } if (c1 == null) { System.err.println("Fehlerchenchen_1!! Row: " + (i + 1)); numFails++; } } } return new int[] { numSuccess, numFails }; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private int[] doImportStandard(HSSFWorkbook wb, JProgressBar progress, String filename) { int numSuccess = 0; int numFails = 0; HSSFSheet transactionSheet = wb.getSheet("Transactions"); boolean isSimpleFormat = transactionSheet != null && isSimple(transactionSheet.getRow(0)); boolean isNewFormat = transactionSheet == null && wb.getSheet("NewTransactions") != null || transactionSheet != null && isNew(transactionSheet.getRow(0)); if (isNewFormat && !isSimpleFormat) { doImportNewFormat(wb, progress); } else {/* www . j a va2 s .com*/ boolean isBvl = isBVL(transactionSheet.getRow(0)); HSSFSheet businessSheet = wb.getSheet("Business_List"); int numRows = transactionSheet.getLastRowNum() + 1; progress.setMaximum(numRows); progress.setValue(0); for (int i = isBvl ? 6 : 1; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String serial = getStrVal(row.getCell(0)); // Serial_number String BL0 = getStrVal(row.getCell(1)); // Contact_Region String KP = getStrVal(row.getCell(2)); // Contact_person String idRec = getStrVal(row.getCell(3)); // ID_Address String adressRec = getStrVal(row.getCell(4)); // Address if ((serial == null || serial.trim().isEmpty()) && (adressRec == null || adressRec.trim().isEmpty())) { continue;//break; } if (serials.containsKey(serial)) { String msg = "Row: " + (i + 1) + "\tSerial '" + serial + "' already defined in file '" + serials.get(serial) + "' -> not importing this row!"; System.err.println(msg); logMessages += msg + "\n"; continue; } serials.put(serial, filename); String activityRec = getStrVal(row.getCell(5)); // Activity String nameRec = adressRec; String streetRec = null; String streetNoRec = null; String zipRec = null; String cityRec = null; String countyRec = null; String countryRec = null; String vatRec = null; HSSFRow busRow = getRow(businessSheet, idRec, 0); if (busRow != null) { nameRec = getStrVal(busRow.getCell(1)); // streetRec = getStrVal(busRow.getCell(2)); // streetNoRec = getStrVal(busRow.getCell(3), 10); // zipRec = getStrVal(busRow.getCell(4), 10); // cityRec = getStrVal(busRow.getCell(5)); // countyRec = getStrVal(busRow.getCell(6), 30); countryRec = getStrVal(busRow.getCell(7)); // vatRec = getStrVal(busRow.getCell(8)); // if (!adressRec.toUpperCase().startsWith(nameRec.toUpperCase())) { String msg = "Row: " + (i + 1) + "\tId issue on recs...\t" + nameRec + " <> " + adressRec; System.err.println(msg); logMessages += msg + "\n"; } } else if (idRec != null) { String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidReceived: " + idRec; System.err.println(msg); logMessages += msg + "\n"; } else { String msg = "Row: " + (i + 1) + "\tidRec is null???\t" + adressRec + (adressRec != null ? "" : " -> Station not defined"); System.err.println(msg); logMessages += msg + "\n"; } String prodNameOut = getStrVal(row.getCell(6)); // ProductName String prodNumOut = getStrVal(row.getCell(7)); // ProductNo String dayOut = getStrVal(row.getCell(8)); // Day String monthOut = getStrVal(row.getCell(9)); // Month String yearOut = getStrVal(row.getCell(10)); // Year String amountKG_Out = getStrVal(row.getCell(11)); // amountKG String typePUOut = getStrVal(row.getCell(12)); // typePU String numPUOut = getStrVal(row.getCell(13)); // numPU String lotNo_Out = getStrVal(row.getCell(14)); // String dayMHDOut = getStrVal(row.getCell(15)); String monthMHDOut = getStrVal(row.getCell(16)); String yearMHDOut = getStrVal(row.getCell(17)); // String dayPDOut = getStrVal(row.getCell(18)); String monthPDOut = getStrVal(row.getCell(19)); String yearPDOut = getStrVal(row.getCell(20)); //Date dateOut = getDate(dayOut, monthOut, yearOut); //Date dateMHDOut = getDate(dayMHDOut, monthMHDOut, yearMHDOut); //Date datePDOut = getDate(dayPDOut, monthPDOut, yearPDOut); String idInsp = getStrVal(row.getCell(21)); // ID_Address String adressInsp = getStrVal(row.getCell(22)); // Address String activityInsp = getStrVal(row.getCell(23)); // Activity String nameInsp = adressInsp; String streetInsp = null; String streetNoInsp = null; String zipInsp = null; String cityInsp = null; String countyInsp = null; String countryInsp = null; String vatInsp = null; busRow = getRow(businessSheet, idInsp, 0); if (busRow != null) { nameInsp = getStrVal(busRow.getCell(1)); // streetInsp = getStrVal(busRow.getCell(2)); // streetNoInsp = getStrVal(busRow.getCell(3), 10); // zipInsp = getStrVal(busRow.getCell(4), 10); // cityInsp = getStrVal(busRow.getCell(5)); // countyInsp = getStrVal(busRow.getCell(6), 30); countryInsp = getStrVal(busRow.getCell(7)); // vatInsp = getStrVal(busRow.getCell(8)); // if (!adressInsp.toUpperCase().startsWith(nameInsp.toUpperCase())) { String msg = "Row: " + (i + 1) + "\tId issue on insps...\t" + nameInsp + " <> " + adressInsp; System.err.println(msg); logMessages += msg + "\n"; } } else if (idInsp != null) { String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidInspected: " + idInsp; System.err.println(msg); logMessages += msg + "\n"; } else { String msg = "Row: " + (i + 1) + "\tidInsp is null???\t" + adressInsp + (adressInsp != null ? "" : " -> Station not defined"); System.err.println(msg); logMessages += msg + "\n"; } String oc = ""; String cqr = ""; if (!isSimpleFormat) { oc = getStrVal(row.getCell(44)); // OriginCountry cqr = getStrVal(row.getCell(45)); // Contact_Questions_Remarks } Integer c1 = null; if (nameInsp != null && !nameInsp.trim().isEmpty()) { Integer[] c = getCharge_Lieferung(idInsp, nameInsp, streetInsp, streetNoInsp, zipInsp, cityInsp, countyInsp, countryInsp, activityInsp, vatInsp, prodNameOut, prodNumOut, null, lotNo_Out, dayMHDOut, monthMHDOut, yearMHDOut, dayPDOut, monthPDOut, yearPDOut, oc, dayOut, monthOut, yearOut, amountKG_Out, typePUOut, numPUOut, idRec, nameRec, streetRec, streetNoRec, zipRec, cityRec, countyRec, countryRec, activityRec, vatRec, serial, cqr, null, null, null, null); if (c != null) c1 = c[2]; } if (isSimpleFormat) continue; String prodNameIn = getStrVal(row.getCell(24)); // ProductName String prodNumIn = getStrVal(row.getCell(25)); // ProductNo String dayIn = getStrVal(row.getCell(26)); // Day String monthIn = getStrVal(row.getCell(27)); // Month String yearIn = getStrVal(row.getCell(28)); // Year String amountKG_In = getStrVal(row.getCell(29)); // amountKG String typePUIn = getStrVal(row.getCell(30)); // typePU String numPUIn = getStrVal(row.getCell(31)); // numPU String lotNo_In = getStrVal(row.getCell(32)); // String dayMHDIn = getStrVal(row.getCell(33)); String monthMHDIn = getStrVal(row.getCell(34)); String yearMHDIn = getStrVal(row.getCell(35)); // String dayPDIn = getStrVal(row.getCell(36)); String monthPDIn = getStrVal(row.getCell(37)); String yearPDIn = getStrVal(row.getCell(38)); //Date dateIn = getDate(dayIn, monthIn, yearIn); //Date dateMHDIn = getDate(dayMHDIn, monthMHDIn, yearMHDIn); //Date datePDIn = getDate(dayPDIn, monthPDIn, yearPDIn); String idSup = getStrVal(row.getCell(39)); // ID_Address String adressSup = getStrVal(row.getCell(40)); // Address String activitySup = getStrVal(row.getCell(41)); // Activity String nameSup = adressSup; String streetSup = null; String streetNoSup = null; String zipSup = null; String citySup = null; String countySup = null; String countrySup = null; String vatSup = null; busRow = getRow(businessSheet, idSup, 0); if (busRow != null) { nameSup = getStrVal(busRow.getCell(1)); // streetSup = getStrVal(busRow.getCell(2)); // streetNoSup = getStrVal(busRow.getCell(3), 10); // zipSup = getStrVal(busRow.getCell(4), 10); // citySup = getStrVal(busRow.getCell(5)); // countySup = getStrVal(busRow.getCell(6), 30); countrySup = getStrVal(busRow.getCell(7)); // vatSup = getStrVal(busRow.getCell(8)); // if (!adressSup.toUpperCase().startsWith(nameSup.toUpperCase())) { String msg = "Row: " + (i + 1) + "\tId issue on sups...\t" + nameSup + " <> " + adressSup; System.err.println(msg); logMessages += msg + "\n"; } } else if (idSup != null) { String msg = "Row: " + (i + 1) + "\tbusiness not there???\tidSupplier: " + idSup; System.err.println(msg); logMessages += msg + "\n"; } else { String msg = "Row: " + (i + 1) + "\tidSup is null???\t" + adressSup + (adressSup != null ? "" : " -> Station not defined"); System.err.println(msg); logMessages += msg + "\n"; } String ec = getStrVal(row.getCell(42)); // EndChain String ece = getStrVal(row.getCell(43)); // Explanation_EndChain String ft = getStrVal(row.getCell(46)); // Further_Traceback String ms = getStrVal(row.getCell(47)); // MicrobiologicalSample //if (amountKG_Out != null && amountKG_In != null && Integer.parseInt(amountKG_Out) > Integer.parseInt(amountKG_In)) System.err.println("amountOut > aomountIn!!! Row " + i + "; amountKG_Out: " + amountKG_Out + "; amountKG_In: " + amountKG_In); if (is1SurelyNewer(dayIn, monthIn, yearIn, dayOut, monthOut, yearOut)) { String msg = "Row: " + (i + 1) + "\tDates not in temporal order, dateOut < dateIn!!! , KP: " + KP + ", BL0: " + BL0 + "; dateOut: " + sdfFormat(dayOut, monthOut, yearOut) + "; dateIn: " + sdfFormat(dayIn, monthIn, yearIn); System.err.println(msg); logMessages += msg + "\n"; } Integer c2 = null; if (nameSup != null && !nameSup.trim().isEmpty()) { Integer[] c = getCharge_Lieferung(idSup, nameSup, streetSup, streetNoSup, zipSup, citySup, countySup, countrySup, activitySup, vatSup, prodNameIn, prodNumIn, null, lotNo_In, dayMHDIn, monthMHDIn, yearMHDIn, dayPDIn, monthPDIn, yearPDIn, oc, dayIn, monthIn, yearIn, amountKG_In, typePUIn, numPUIn, idInsp, nameInsp, streetInsp, streetNoInsp, zipInsp, cityInsp, countyInsp, countryInsp, activityInsp, vatInsp, serial, cqr, ec, ece, ft, ms); if (c != null) c2 = c[3]; } if (c1 == null) { // Chargen String msg = "Row: " + (i + 1) + "\tError Type 1 (Batches)!!"; // Fehlerchenchen_1 System.err.println(msg); logMessages += msg + "\n"; numFails++; } else if (c2 == null) { // Lieferungen String msg = "Row: " + (i + 1) + "\tError Type 2 (Deliveries)!! E.g. Station not defined?"; // Fehlerchenchen_2 System.err.println(msg); logMessages += msg + "\n"; /* * getCharge_Lieferung(nameSup, streetSup, streetNoSup, * zipSup, citySup, countySup, countrySup, activitySup, * vatSup, prodNameIn, prodNumIn, lotNo_In, dateMHDIn, * datePDIn, oc, dateIn, amountKG_In, typePUIn, numPUIn, * nameSup, streetSup, streetNoSup, zipSup, citySup, * countySup, countrySup, activityInsp, vatInsp, comment, * false); */ numFails++; } else { if (c2 != null) { Integer cvID = getID("ChargenVerbindungen", new String[] { "Zutat", "Produkt" }, new String[] { c2.toString(), c1.toString() }, null, null); if (cvID == null) { String msg = "Row: " + (i + 1) + "\tError Type 4 (Links)!!"; // Fehlerchenchen_4 System.err.println(msg); logMessages += msg + "\n"; numFails++; } else { numSuccess++; } } } } } } return new int[] { numSuccess, numFails }; }