List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createCellStyle
@Override
public HSSFCellStyle createCellStyle()
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 w w . j ava2s . com 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:Controlador.ControladorCargueMasivo.java
public static HSSFWorkbook obtenerExcel(DataModel contenidoCeldas, DataModel cabecerasCeldas, String nombreHoja) {// w w w. j a v a 2s.c o m HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); HSSFSheet hssfSheet = hssfWorkbook.createSheet(nombreHoja); int numeroFila = 0; int numeroColumna = 0; HSSFRow hssfRow = hssfSheet.createRow(numeroFila++); HSSFCellStyle hssfCellStyleCabecera = hssfWorkbook.createCellStyle(); hssfCellStyleCabecera.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); hssfCellStyleCabecera.setFillBackgroundColor(new HSSFColor.BLACK().getIndex()); HSSFFont hssfFont = hssfWorkbook.createFont(); hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hssfFont.setColor(HSSFColor.WHITE.index); hssfCellStyleCabecera.setFont(hssfFont); String columnaCabecera; HSSFCell hssfCell = null; List cabecerasExcel = (List) cabecerasCeldas.getWrappedData(); for (int i = 0; i < cabecerasExcel.size(); i++) { columnaCabecera = (String) cabecerasExcel.get(i); hssfCell = hssfRow.createCell((short) numeroColumna++); hssfCell.setCellValue(columnaCabecera); hssfCell.setCellStyle(hssfCellStyleCabecera); } List contenidoExcel = (List) contenidoCeldas.getWrappedData(); List fila = null; Object valor; for (int i = 0; i < contenidoExcel.size(); i++) { fila = (List) contenidoExcel.get(i); hssfRow = hssfSheet.createRow(numeroFila++); numeroColumna = 0; for (int x = 0; x < fila.size(); x++) { valor = fila.get(x); hssfCell = hssfRow.createCell((short) numeroColumna++); hssfCell.setCellValue((String) valor); } } return hssfWorkbook; }
From source file:controladores4.controladorReportes.java
public void GenerarLibroRemuneraciones() { DateFormat date2 = new SimpleDateFormat("dd-MMMM-yyyy"); NumberFormat FORMAT = NumberFormat.getCurrencyInstance(); DecimalFormatSymbols dfs = new DecimalFormatSymbols(); Thread runnable;//from ww w . ja va2 s . c o m runnable = new Thread() { public void run() { try { DateFormat yearDate = new SimpleDateFormat("yyyy"); DateFormat monthDate = new SimpleDateFormat("M"); String month = monthDate.format(new Date()); String year = yearDate.format(new Date()); String per = perDate.format(new Date()); String fecha = formatDate.format(new Date()); controladores.controladorPrincipal miControlador = new controladorPrincipal(); modelos3.modeloRemuneraciones remu = new modeloRemuneraciones(); double uf = remu.obtenerUF(); double utm = remu.obtenerUTM(); String[][] imp2cat = remu.obtenerTablaImpuesto(); //Listas para detalle afp y salud HashMap<String, ArrayList<AfpInfo>> mapAfp = new HashMap<>(); HashMap<String, ArrayList<SaludInfo>> mapSalud = new HashMap<>(); List<AfpInfo> listaAfp = new ArrayList<>(); List<SaludInfo> listaSalud = new ArrayList<>(); modelos.modeloEmpleados emp = new modeloEmpleados(); String[][] data = emp.obtenerRemuneraciones2(getMes(), getYear()); String path = "Libro de remuneraciones - " + per + ".xls"; File file = new File(path); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBold(true); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFSheet sheet_rem = workbook.createSheet("Libro_Remuneraciones"); HSSFSheet sheet_afp = workbook.createSheet("Detalle_AFP"); HSSFSheet sheet_salud = workbook.createSheet("Detalle_Salud"); HSSFRow rowhead; //J para recorrer los datos de los trabajadores int j = 0; int numTrab = data.length; for (int i = 0; i < 2; i++) { //Primera linea rowhead = sheet_rem.createRow((short) 0 + i * 34); rowhead.createCell(0).setCellValue("parte:1"); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:" + (i + 1)); rowhead.createCell(12).setCellValue("parte:2"); rowhead.createCell(15) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(15).setCellStyle(style); rowhead.createCell(23).setCellValue("pag:" + (i + 1)); rowhead.createCell(24).setCellValue("parte:3"); rowhead.createCell(27) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(27).setCellStyle(style); rowhead.createCell(35).setCellValue("pag:" + (i + 1)); //Segunda linea rowhead = sheet_rem.createRow((short) 1 + (i * 34)); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); rowhead.createCell(12).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(14).setCellValue("Mes: " + month); rowhead.createCell(16).setCellValue("Ao: " + year); rowhead.createCell(18).setCellValue("UF: $" + uf); rowhead.createCell(21).setCellValue("UTM: $" + utm); rowhead.createCell(24).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(26).setCellValue("Mes: " + month); rowhead.createCell(28).setCellValue("Ao: " + year); rowhead.createCell(30).setCellValue("UF: $" + uf); rowhead.createCell(33).setCellValue("UTM: $" + utm); //Tercera linea rowhead = sheet_rem.createRow((short) 4 + (i * 34)); rowhead.createCell(0).setCellValue("rut_trabajador"); rowhead.createCell(1).setCellValue("nombre_trabajador"); rowhead.createCell(2).setCellValue("dias_trab"); rowhead.createCell(3).setCellValue("sueldo_base_p"); rowhead.createCell(4).setCellValue("gratificacion"); rowhead.createCell(5).setCellValue("bono_aos"); rowhead.createCell(6).setCellValue("bono_horas"); rowhead.createCell(7).setCellValue("bono_asig_vol"); rowhead.createCell(8).setCellValue("bono_ad"); rowhead.createCell(9).setCellValue("otros_bonos"); rowhead.createCell(10).setCellValue("horas_extra"); rowhead.createCell(11).setCellValue("tot_h_imp"); rowhead.createCell(12).setCellValue("rut_trabajador"); rowhead.createCell(13).setCellValue("nombre_trabajador"); rowhead.createCell(14).setCellValue("colacion"); rowhead.createCell(15).setCellValue("movilizacion"); rowhead.createCell(16).setCellValue("asig_familiar"); rowhead.createCell(17).setCellValue("tot_h_no_imp"); rowhead.createCell(18).setCellValue("tot_haberes"); rowhead.createCell(19).setCellValue("desc_afp"); rowhead.createCell(20).setCellValue("desc_salud"); rowhead.createCell(21).setCellValue("afc_trab"); rowhead.createCell(22).setCellValue("afc_empl"); rowhead.createCell(23).setCellValue("tot_desc_leg"); rowhead.createCell(24).setCellValue("rut_trabajador"); rowhead.createCell(25).setCellValue("nombre_trabajador"); rowhead.createCell(26).setCellValue("imp_renta"); rowhead.createCell(27).setCellValue("caja_comp"); rowhead.createCell(28).setCellValue("anticipo"); rowhead.createCell(29).setCellValue("adelanto"); rowhead.createCell(30).setCellValue("prestamo"); rowhead.createCell(31).setCellValue("tot_desc_men"); rowhead.createCell(32).setCellValue("tot_desc"); rowhead.createCell(33).setCellValue("total_a_pago"); rowhead.createCell(34).setCellValue("sis"); //Cuarta linea for (int k = 0; k < 28; k++, j++) { if (j == numTrab) { break; } int base = Integer.parseInt(data[j][2]) * Integer.parseInt(data[j][28]) / 30; //GRATIFICACION int grat = (int) (base * 0.25); //BONO ANTIGUEDAD int bonoAnt = miControlador.obtenerBonoAnt(data[j][5]); //BONO 300 int totalBon300 = Integer.parseInt(data[j][27]); //BONO ADICIONAL int bonoAd = Integer.parseInt(data[j][11]); //BONO RESPONSABILIDAD int bonoResp = 0; //BONO ADICIONAL double bonoCol1 = Double.parseDouble(data[j][8]); double bonoCol30 = Double.parseDouble(data[j][9]); double bonoCol = bonoCol1 + bonoCol30 / 2; int totalBonCol = (int) Math.round(((double) base * 0.0077777) * bonoCol); //HORAS EXTRA double horasExNor = Double.parseDouble(data[j][12]); double horasExFes = Double.parseDouble(data[j][13]); double cantHorEx = 0; //total de horas extras normales = 1; festivas = 2 double totalHorex = 0; double resHorEx = 0; if (horasExNor > 45) { cantHorEx = 45; totalHorex = 45; resHorEx = horasExNor - 45; } else { cantHorEx = horasExNor; totalHorex = cantHorEx; } if (cantHorEx + horasExFes > 45) { resHorEx = resHorEx + (horasExFes - 45 + cantHorEx) * 2; totalHorex = 45 - cantHorEx; cantHorEx = 45; } else { cantHorEx += horasExFes; totalHorex += horasExFes * 2; } //BONO ASIGNACION VOLUNTARIA double totalBonoAV = base * 0.0077777 * resHorEx; double valorHorEx = (int) ((double) base * 0.0077777 * totalHorex); //TOTAL IMPONIBLE double totImp = base + grat + bonoAnt + bonoAd + bonoResp + totalBonoAV + totalBonCol + totalBon300 + valorHorEx; //DESCUENTO AFP int descAFP = Integer.parseInt(data[j][21]); int totalAFP = (int) (totImp * ((double) descAFP / 10000)); int sis = (int) (totImp * 0.0141); //DESCUENTO SALUD double descSalud = 0, totalSalud = 0; String salud; if (data[j][4].toLowerCase().compareTo("fonasa") == 0) { salud = "FONASA"; descSalud = Integer.parseInt(data[j][22]); totalSalud = (int) (totImp * ((double) descSalud / 10000)); } else { if (data[i][23].compareTo("") == 0) { salud = data[j][4]; } else { salud = data[j][23]; } descSalud = ((double) Integer.parseInt(data[j][24]) / 1000) * uf; totalSalud = descSalud; } //DESCUENTO CESANTIA int ces = (int) (totImp * 0.006); int cesEmp = (int) (totImp * 0.024); //DESCUENTOS LEGALES double descLegales = ces + totalSalud + totalAFP; //TOTAL TRIBUTABLE double totTrib = totImp - totalAFP - totalSalud - ces; int descRenta = 0; double totAux = 0; for (String[] imp2cat1 : imp2cat) { if (totTrib > Float.parseFloat(imp2cat1[0]) / 10 && totTrib <= Float.parseFloat(imp2cat1[1]) / 10) { descRenta = (int) (totTrib * Float.parseFloat(imp2cat1[2]) / 1000 - Float.parseFloat(imp2cat1[3]) / 100); totAux = totTrib - descRenta; break; } } //CAJA COMPENSACION int caja = Integer.parseInt(data[j][15]); //ASIGNACION FAMILIAR int af = Integer.parseInt(data[j][16]); //LIQ ALCANZADO double liqAl = totAux - caja; //COLACION int col = Integer.parseInt(data[j][6]); //TRANSPORTE int trans = Integer.parseInt(data[j][7]); //TOTAL NO IMPONIBLE int noImp = trans + col + af; //ANTICIPO ADELANTO PRESTAMOS int antic = Integer.parseInt(data[j][17]); int adel = Integer.parseInt(data[j][18]); int pres = Integer.parseInt(data[j][19]); int cuo = Integer.parseInt(data[j][20]); int cuoPres = 0; int cuores = Math.max(0, Integer.parseInt(data[j][26]) - 1); if (cuo != 0) { cuoPres = pres / cuo; } //DESCUENTOS MENSUALES int descMensuales = caja + antic + adel + cuoPres + descRenta; //TOTAL HABERES double totalHaberes = noImp + totImp; //TOTAL DESCUENTOS int totDesc = antic + adel + cuoPres + caja; //LIQUIDO double liq = liqAl + col + trans + af - antic - adel - cuoPres; //Agrego los datos de afp a listaAfp String afp = data[j][3]; if (mapAfp.get(afp) == null) { mapAfp.put(afp, new ArrayList<AfpInfo>()); } if (mapSalud.get(salud.toUpperCase()) == null) { mapSalud.put(salud.toUpperCase(), new ArrayList<SaludInfo>()); } //cambie descAfp por totalAfp AfpInfo info = new AfpInfo(data[j][0], data[j][1], totImp, (int) totalAFP, sis, totalAFP); mapAfp.get(afp).add(info); //cambie descSalud por totalSalud SaludInfo sInfo = new SaludInfo(data[j][0], data[j][1], totImp, (int) totalSalud); mapSalud.get(salud.toUpperCase()).add(sInfo); rowhead = sheet_rem.createRow((short) 5 + k + (i * 34)); rowhead.createCell(0).setCellValue(data[j][0]); rowhead.createCell(1).setCellValue(data[j][1]); rowhead.createCell(2).setCellValue(Integer.parseInt(data[j][28])); rowhead.createCell(3).setCellValue(base); rowhead.createCell(4).setCellValue(grat); rowhead.createCell(5).setCellValue(bonoAnt); rowhead.createCell(6).setCellValue(totalBon300); rowhead.createCell(7).setCellValue(totalBonoAV); rowhead.createCell(8).setCellValue(totalBonCol); rowhead.createCell(9).setCellValue(bonoAd); rowhead.createCell(10).setCellValue(valorHorEx); rowhead.createCell(11).setCellValue(totImp); rowhead.createCell(12).setCellValue(data[j][0]); rowhead.createCell(13).setCellValue(data[j][1]); rowhead.createCell(14).setCellValue(col); rowhead.createCell(15).setCellValue(trans); rowhead.createCell(16).setCellValue(af); rowhead.createCell(17).setCellValue(noImp); rowhead.createCell(18).setCellValue(totalHaberes); rowhead.createCell(19).setCellValue(totalAFP); rowhead.createCell(20).setCellValue(totalSalud); rowhead.createCell(21).setCellValue(ces); rowhead.createCell(22).setCellValue(cesEmp); rowhead.createCell(23).setCellValue(descLegales); rowhead.createCell(24).setCellValue(data[j][0]); rowhead.createCell(25).setCellValue(data[j][1]); rowhead.createCell(26).setCellValue(descRenta); rowhead.createCell(27).setCellValue(caja); rowhead.createCell(28).setCellValue(antic); rowhead.createCell(29).setCellValue(adel); rowhead.createCell(30).setCellValue(cuoPres); rowhead.createCell(31).setCellValue(descMensuales); rowhead.createCell(32).setCellValue(totDesc); rowhead.createCell(33).setCellValue(liq); rowhead.createCell(34).setCellValue(sis); } } //Totales rowhead = sheet_rem.createRow(66); rowhead.createCell(2).setCellFormula("SUM(C6:C34,C40:C66)"); rowhead.createCell(3).setCellFormula("SUM(D6:D34,D40:D66)"); rowhead.createCell(4).setCellFormula("SUM(E6:E34,E40:E66)"); rowhead.createCell(5).setCellFormula("SUM(F6:F34,F40:F66)"); rowhead.createCell(6).setCellFormula("SUM(G6:G34,G40:G66)"); rowhead.createCell(7).setCellFormula("SUM(H6:H34,H40:H66)"); rowhead.createCell(8).setCellFormula("SUM(I6:I34,I40:I66)"); rowhead.createCell(9).setCellFormula("SUM(J6:J34,J40:J66)"); rowhead.createCell(10).setCellFormula("SUM(K6:K34,K40:K66)"); rowhead.createCell(11).setCellFormula("SUM(L6:L34,L40:L66)"); rowhead.createCell(14).setCellFormula("SUM(O6:O34,O40:O66)"); rowhead.createCell(15).setCellFormula("SUM(P6:P34,P40:P66)"); rowhead.createCell(16).setCellFormula("SUM(Q6:Q34,Q40:Q66)"); rowhead.createCell(17).setCellFormula("SUM(R6:R34,R40:R66)"); rowhead.createCell(18).setCellFormula("SUM(S6:S34,S40:S66)"); rowhead.createCell(19).setCellFormula("SUM(T6:T34,T40:T66)"); rowhead.createCell(20).setCellFormula("SUM(U6:U34,U40:U66)"); rowhead.createCell(21).setCellFormula("SUM(V6:V34,V40:V66)"); rowhead.createCell(22).setCellFormula("SUM(W6:W34,W40:W66)"); rowhead.createCell(23).setCellFormula("SUM(X6:X34,X40:X66)"); rowhead.createCell(26).setCellFormula("SUM(AA6:AA34,AA40:AA66)"); rowhead.createCell(27).setCellFormula("SUM(AB6:AB34,AB40:AB66)"); rowhead.createCell(28).setCellFormula("SUM(AC6:AC34,AC40:AC66)"); rowhead.createCell(29).setCellFormula("SUM(AD6:AD34,AD40:AD66)"); rowhead.createCell(30).setCellFormula("SUM(AE6:AE34,AE40:AE66)"); rowhead.createCell(31).setCellFormula("SUM(AF6:AF34,AF40:AF66)"); rowhead.createCell(32).setCellFormula("SUM(AG6:AG34,AG40:AG66)"); rowhead.createCell(33).setCellFormula("SUM(AH6:AH34,AH40:AH66)"); rowhead.createCell(34).setCellFormula("SUM(AI6:AI34,AI40:AI66)"); //Hoja detalle afp j = 0; //Primera linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapAfp.keySet()) { List<AfpInfo> aux = mapAfp.get(key); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(5).setCellValue("AFP " + key); rowhead.getCell(5).setCellStyle(style); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_afp"); rowhead.createCell(4).setCellValue("sis"); rowhead.createCell(5).setCellValue("tot_afp"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (AfpInfo usuario : aux) { rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotalImp()); rowhead.createCell(3).setCellValue(usuario.getDescAfp()); rowhead.createCell(4).setCellValue(usuario.getSis()); rowhead.createCell(5).setCellValue(usuario.getTotAfp()); } rowhead = sheet_afp.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); rowhead.createCell(4).setCellFormula("SUM(E" + (j - totalTrab) + ":E" + (j - 1) + ")"); rowhead.createCell(5).setCellFormula("SUM(F" + (j - totalTrab) + ":F" + (j - 1) + ")"); j++; } //Hoja detalle salud j = 0; //Primera linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapSalud.keySet()) { List<SaludInfo> aux = mapSalud.get(key); rowhead = sheet_salud.createRow((short) j++); if (key.toLowerCase().compareTo("fonasa") == 0 || key.toLowerCase().compareTo("sin info") == 0) { rowhead.createCell(5).setCellValue(key); } else { rowhead.createCell(5).setCellValue("Isapre " + key); } rowhead.getCell(5).setCellStyle(style); rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_salud"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (SaludInfo usuario : aux) { rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotImp()); rowhead.createCell(3).setCellValue(usuario.getDescSalud()); } rowhead = sheet_salud.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); j++; } FileOutputStream fileOut; fileOut = new FileOutputStream(file); workbook.write(fileOut); fileOut.close(); JOptionPane.showMessageDialog(null, "Libro de remuneraciones generado con xito", "Operacin exitosa", JOptionPane.INFORMATION_MESSAGE); } catch (IOException ie) { JOptionPane.showMessageDialog(null, "El archivo est siendo ocupado\nCierre el archivo y vuelva a intentarlo", "Error", JOptionPane.INFORMATION_MESSAGE); ie.printStackTrace(); } catch (Exception e) { JOptionPane.showMessageDialog(null, "Error al crear libro de remuneraciones", "Error", JOptionPane.INFORMATION_MESSAGE); e.printStackTrace(); } } }; runnable.start(); }
From source file:controladores4.controladorReportes.java
public void GenerarLibroRemuneracionesAtrasado(final String year, final String month) { DateFormat date2 = new SimpleDateFormat("dd-MMMM-yyyy"); NumberFormat FORMAT = NumberFormat.getCurrencyInstance(); DecimalFormatSymbols dfs = new DecimalFormatSymbols(); Thread runnable;//from w w w. j a va 2s . com runnable = new Thread() { public void run() { try { DateFormat yearDate = new SimpleDateFormat("yyyy"); DateFormat monthDate = new SimpleDateFormat("M"); // String month2 = monthDate.format(new Date()); // String year2 = yearDate.format(new Date()); String per = perDate.format(new Date()); String fecha = formatDate.format(new Date()); controladores.controladorPrincipal miControlador = new controladorPrincipal(); modelos3.modeloRemuneraciones remu = new modeloRemuneraciones(); double uf = remu.obtenerUF(); double utm = remu.obtenerUTM(); String[][] imp2cat = remu.obtenerTablaImpuesto(); //Listas para detalle afp y salud HashMap<String, ArrayList<AfpInfo>> mapAfp = new HashMap<>(); HashMap<String, ArrayList<SaludInfo>> mapSalud = new HashMap<>(); List<AfpInfo> listaAfp = new ArrayList<>(); List<SaludInfo> listaSalud = new ArrayList<>(); modelos.modeloEmpleados emp = new modeloEmpleados(); String[][] data = emp.obtenerRemuneraciones2(Integer.parseInt(month), Integer.parseInt(year)); String path = "Libro de remuneraciones - " + month + "-" + year + ".xls"; File file = new File(path); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBold(true); font.setFontName("Calibri"); font.setFontHeightInPoints((short) 11); style.setFont(font); HSSFSheet sheet_rem = workbook.createSheet("Libro_Remuneraciones"); HSSFSheet sheet_afp = workbook.createSheet("Detalle_AFP"); HSSFSheet sheet_salud = workbook.createSheet("Detalle_Salud"); HSSFRow rowhead; //J para recorrer los datos de los trabajadores int j = 0; int numTrab = data.length; for (int i = 0; i < 2; i++) { //Primera linea rowhead = sheet_rem.createRow((short) 0 + i * 34); rowhead.createCell(0).setCellValue("parte:1"); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:" + (i + 1)); rowhead.createCell(12).setCellValue("parte:2"); rowhead.createCell(15) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(15).setCellStyle(style); rowhead.createCell(23).setCellValue("pag:" + (i + 1)); rowhead.createCell(24).setCellValue("parte:3"); rowhead.createCell(27) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(27).setCellStyle(style); rowhead.createCell(35).setCellValue("pag:" + (i + 1)); //Segunda linea rowhead = sheet_rem.createRow((short) 1 + (i * 34)); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); rowhead.createCell(12).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(14).setCellValue("Mes: " + month); rowhead.createCell(16).setCellValue("Ao: " + year); rowhead.createCell(18).setCellValue("UF: $" + uf); rowhead.createCell(21).setCellValue("UTM: $" + utm); rowhead.createCell(24).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(26).setCellValue("Mes: " + month); rowhead.createCell(28).setCellValue("Ao: " + year); rowhead.createCell(30).setCellValue("UF: $" + uf); rowhead.createCell(33).setCellValue("UTM: $" + utm); //Tercera linea rowhead = sheet_rem.createRow((short) 4 + (i * 34)); rowhead.createCell(0).setCellValue("rut_trabajador"); rowhead.createCell(1).setCellValue("nombre_trabajador"); rowhead.createCell(2).setCellValue("dias_trab"); rowhead.createCell(3).setCellValue("sueldo_base_p"); rowhead.createCell(4).setCellValue("gratificacion"); rowhead.createCell(5).setCellValue("bono_aos"); rowhead.createCell(6).setCellValue("bono_horas"); rowhead.createCell(7).setCellValue("bono_asig_vol"); rowhead.createCell(8).setCellValue("bono_ad"); rowhead.createCell(9).setCellValue("otros_bonos"); rowhead.createCell(10).setCellValue("horas_extra"); rowhead.createCell(11).setCellValue("tot_h_imp"); rowhead.createCell(12).setCellValue("rut_trabajador"); rowhead.createCell(13).setCellValue("nombre_trabajador"); rowhead.createCell(14).setCellValue("colacion"); rowhead.createCell(15).setCellValue("movilizacion"); rowhead.createCell(16).setCellValue("asig_familiar"); rowhead.createCell(17).setCellValue("tot_h_no_imp"); rowhead.createCell(18).setCellValue("tot_haberes"); rowhead.createCell(19).setCellValue("desc_afp"); rowhead.createCell(20).setCellValue("desc_salud"); rowhead.createCell(21).setCellValue("afc_trab"); rowhead.createCell(22).setCellValue("afc_empl"); rowhead.createCell(23).setCellValue("tot_desc_leg"); rowhead.createCell(24).setCellValue("rut_trabajador"); rowhead.createCell(25).setCellValue("nombre_trabajador"); rowhead.createCell(26).setCellValue("imp_renta"); rowhead.createCell(27).setCellValue("caja_comp"); rowhead.createCell(28).setCellValue("anticipo"); rowhead.createCell(29).setCellValue("adelanto"); rowhead.createCell(30).setCellValue("prestamo"); rowhead.createCell(31).setCellValue("tot_desc_men"); rowhead.createCell(32).setCellValue("tot_desc"); rowhead.createCell(33).setCellValue("total_a_pago"); rowhead.createCell(34).setCellValue("sis"); //Cuarta linea for (int k = 0; k < 28; k++, j++) { if (j == numTrab) { break; } int base = Integer.parseInt(data[j][2]) * Integer.parseInt(data[j][28]) / 30; //GRATIFICACION int grat = (int) (base * 0.25); //BONO ANTIGUEDAD int bonoAnt = miControlador.obtenerBonoAnt(data[j][5]); //BONO 300 int totalBon300 = Integer.parseInt(data[j][27]); //BONO ADICIONAL int bonoAd = Integer.parseInt(data[j][11]); //BONO RESPONSABILIDAD int bonoResp = 0; //BONO ADICIONAL double bonoCol1 = Double.parseDouble(data[j][8]); double bonoCol30 = Double.parseDouble(data[j][9]); double bonoCol = bonoCol1 + bonoCol30 / 2; int totalBonCol = (int) Math.round(((double) base * 0.0077777) * bonoCol); //HORAS EXTRA double horasExNor = Double.parseDouble(data[j][12]); double horasExFes = Double.parseDouble(data[j][13]); double cantHorEx = 0; //total de horas extras normales = 1; festivas = 2 double totalHorex = 0; double resHorEx = 0; if (horasExNor > 45) { cantHorEx = 45; totalHorex = 45; resHorEx = horasExNor - 45; } else { cantHorEx = horasExNor; totalHorex = cantHorEx; } if (cantHorEx + horasExFes > 45) { resHorEx = resHorEx + (horasExFes - 45 + cantHorEx) * 2; totalHorex = 45 - cantHorEx; cantHorEx = 45; } else { cantHorEx += horasExFes; totalHorex += horasExFes * 2; } //BONO ASIGNACION VOLUNTARIA double totalBonoAV = base * 0.0077777 * resHorEx; double valorHorEx = (int) ((double) base * 0.0077777 * totalHorex); //TOTAL IMPONIBLE double totImp = base + grat + bonoAnt + bonoAd + bonoResp + totalBonoAV + totalBonCol + totalBon300 + valorHorEx; //DESCUENTO AFP int descAFP = Integer.parseInt(data[j][21]); int totalAFP = (int) (totImp * ((double) descAFP / 10000)); int sis = (int) (totImp * 0.0141); //DESCUENTO SALUD double descSalud = 0, totalSalud = 0; String salud; if (data[j][4].toLowerCase().compareTo("fonasa") == 0) { salud = "FONASA"; descSalud = Integer.parseInt(data[j][22]); totalSalud = (int) (totImp * ((double) descSalud / 10000)); } else { if (data[i][23].compareTo("") == 0) { salud = data[j][4]; } else { salud = data[j][23]; } descSalud = ((double) Integer.parseInt(data[j][24]) / 1000) * uf; totalSalud = descSalud; } //DESCUENTO CESANTIA int ces = (int) (totImp * 0.006); int cesEmp = (int) (totImp * 0.024); //DESCUENTOS LEGALES double descLegales = ces + totalSalud + totalAFP; //TOTAL TRIBUTABLE double totTrib = totImp - totalAFP - totalSalud - ces; int descRenta = 0; double totAux = 0; for (String[] imp2cat1 : imp2cat) { if (totTrib > Float.parseFloat(imp2cat1[0]) / 10 && totTrib <= Float.parseFloat(imp2cat1[1]) / 10) { descRenta = (int) (totTrib * Float.parseFloat(imp2cat1[2]) / 1000 - Float.parseFloat(imp2cat1[3]) / 100); totAux = totTrib - descRenta; break; } } //CAJA COMPENSACION int caja = Integer.parseInt(data[j][15]); //ASIGNACION FAMILIAR int af = Integer.parseInt(data[j][16]); //LIQ ALCANZADO double liqAl = totAux - caja; //COLACION int col = Integer.parseInt(data[j][6]); //TRANSPORTE int trans = Integer.parseInt(data[j][7]); //TOTAL NO IMPONIBLE int noImp = trans + col + af; //ANTICIPO ADELANTO PRESTAMOS int antic = Integer.parseInt(data[j][17]); int adel = Integer.parseInt(data[j][18]); int pres = Integer.parseInt(data[j][19]); int cuo = Integer.parseInt(data[j][20]); int cuoPres = 0; int cuores = Math.max(0, Integer.parseInt(data[j][26]) - 1); if (cuo != 0) { cuoPres = pres / cuo; } //DESCUENTOS MENSUALES int descMensuales = caja + antic + adel + cuoPres + descRenta; //TOTAL HABERES double totalHaberes = noImp + totImp; //TOTAL DESCUENTOS int totDesc = antic + adel + cuoPres + caja; //LIQUIDO double liq = liqAl + col + trans + af - antic - adel - cuoPres; //Agrego los datos de afp a listaAfp String afp = data[j][3]; if (mapAfp.get(afp) == null) { mapAfp.put(afp, new ArrayList<AfpInfo>()); } if (mapSalud.get(salud.toUpperCase()) == null) { mapSalud.put(salud.toUpperCase(), new ArrayList<SaludInfo>()); } AfpInfo info = new AfpInfo(data[j][0], data[j][1], totImp, descAFP, sis, totalAFP); mapAfp.get(afp).add(info); SaludInfo sInfo = new SaludInfo(data[j][0], data[j][1], totImp, descSalud); mapSalud.get(salud.toUpperCase()).add(sInfo); rowhead = sheet_rem.createRow((short) 5 + k + (i * 34)); rowhead.createCell(0).setCellValue(data[j][0]); rowhead.createCell(1).setCellValue(data[j][1]); rowhead.createCell(2).setCellValue(Integer.parseInt(data[j][28])); rowhead.createCell(3).setCellValue(base); rowhead.createCell(4).setCellValue(grat); rowhead.createCell(5).setCellValue(bonoAnt); rowhead.createCell(6).setCellValue(totalBon300); rowhead.createCell(7).setCellValue(totalBonoAV); rowhead.createCell(8).setCellValue(totalBonCol); rowhead.createCell(9).setCellValue(bonoAd); rowhead.createCell(10).setCellValue(valorHorEx); rowhead.createCell(11).setCellValue(totImp); rowhead.createCell(12).setCellValue(data[j][0]); rowhead.createCell(13).setCellValue(data[j][1]); rowhead.createCell(14).setCellValue(col); rowhead.createCell(15).setCellValue(trans); rowhead.createCell(16).setCellValue(af); rowhead.createCell(17).setCellValue(noImp); rowhead.createCell(18).setCellValue(totalHaberes); rowhead.createCell(19).setCellValue(totalAFP); rowhead.createCell(20).setCellValue(totalSalud); rowhead.createCell(21).setCellValue(ces); rowhead.createCell(22).setCellValue(cesEmp); rowhead.createCell(23).setCellValue(descLegales); rowhead.createCell(24).setCellValue(data[j][0]); rowhead.createCell(25).setCellValue(data[j][1]); rowhead.createCell(26).setCellValue(descRenta); rowhead.createCell(27).setCellValue(caja); rowhead.createCell(28).setCellValue(antic); rowhead.createCell(29).setCellValue(adel); rowhead.createCell(30).setCellValue(cuoPres); rowhead.createCell(31).setCellValue(descMensuales); rowhead.createCell(32).setCellValue(totDesc); rowhead.createCell(33).setCellValue(liq); rowhead.createCell(34).setCellValue(sis); } } //Totales rowhead = sheet_rem.createRow(66); rowhead.createCell(2).setCellFormula("SUM(C6:C34,C40:C66)"); rowhead.createCell(3).setCellFormula("SUM(D6:D34,D40:D66)"); rowhead.createCell(4).setCellFormula("SUM(E6:E34,E40:E66)"); rowhead.createCell(5).setCellFormula("SUM(F6:F34,F40:F66)"); rowhead.createCell(6).setCellFormula("SUM(G6:G34,G40:G66)"); rowhead.createCell(7).setCellFormula("SUM(H6:H34,H40:H66)"); rowhead.createCell(8).setCellFormula("SUM(I6:I34,I40:I66)"); rowhead.createCell(9).setCellFormula("SUM(J6:J34,J40:J66)"); rowhead.createCell(10).setCellFormula("SUM(K6:K34,K40:K66)"); rowhead.createCell(11).setCellFormula("SUM(L6:L34,L40:L66)"); rowhead.createCell(14).setCellFormula("SUM(O6:O34,O40:O66)"); rowhead.createCell(15).setCellFormula("SUM(P6:P34,P40:P66)"); rowhead.createCell(16).setCellFormula("SUM(Q6:Q34,Q40:Q66)"); rowhead.createCell(17).setCellFormula("SUM(R6:R34,R40:R66)"); rowhead.createCell(18).setCellFormula("SUM(S6:S34,S40:S66)"); rowhead.createCell(19).setCellFormula("SUM(T6:T34,T40:T66)"); rowhead.createCell(20).setCellFormula("SUM(U6:U34,U40:U66)"); rowhead.createCell(21).setCellFormula("SUM(V6:V34,V40:V66)"); rowhead.createCell(22).setCellFormula("SUM(W6:W34,W40:W66)"); rowhead.createCell(23).setCellFormula("SUM(X6:X34,X40:X66)"); rowhead.createCell(26).setCellFormula("SUM(AA6:AA34,AA40:AA66)"); rowhead.createCell(27).setCellFormula("SUM(AB6:AB34,AB40:AB66)"); rowhead.createCell(28).setCellFormula("SUM(AC6:AC34,AC40:AC66)"); rowhead.createCell(29).setCellFormula("SUM(AD6:AD34,AD40:AD66)"); rowhead.createCell(30).setCellFormula("SUM(AE6:AE34,AE40:AE66)"); rowhead.createCell(31).setCellFormula("SUM(AF6:AF34,AF40:AF66)"); rowhead.createCell(32).setCellFormula("SUM(AG6:AG34,AG40:AG66)"); rowhead.createCell(33).setCellFormula("SUM(AH6:AH34,AH40:AH66)"); rowhead.createCell(34).setCellFormula("SUM(AI6:AI34,AI40:AI66)"); //Hoja detalle afp j = 0; //Primera linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapAfp.keySet()) { List<AfpInfo> aux = mapAfp.get(key); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(5).setCellValue("AFP " + key); rowhead.getCell(5).setCellStyle(style); rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_afp"); rowhead.createCell(4).setCellValue("sis"); rowhead.createCell(5).setCellValue("tot_afp"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (AfpInfo usuario : aux) { rowhead = sheet_afp.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotalImp()); rowhead.createCell(3).setCellValue(usuario.getDescAfp()); rowhead.createCell(4).setCellValue(usuario.getSis()); rowhead.createCell(5).setCellValue(usuario.getTotAfp()); } rowhead = sheet_afp.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); rowhead.createCell(4).setCellFormula("SUM(E" + (j - totalTrab) + ":E" + (j - 1) + ")"); rowhead.createCell(5).setCellFormula("SUM(F" + (j - totalTrab) + ":F" + (j - 1) + ")"); j++; } //Hoja detalle salud j = 0; //Primera linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(3) .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada"); rowhead.getCell(3).setCellStyle(style); rowhead.createCell(11).setCellValue("pag:1"); //Segunda linea rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1"); rowhead.createCell(2).setCellValue("Mes: " + month); rowhead.createCell(4).setCellValue("Ao: " + year); rowhead.createCell(6).setCellValue("UF: $" + uf); rowhead.createCell(9).setCellValue("UTM: $" + utm); j += 2; for (String key : mapSalud.keySet()) { List<SaludInfo> aux = mapSalud.get(key); rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(5).setCellValue("Isapre " + key); rowhead.getCell(5).setCellStyle(style); rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue("rut"); rowhead.createCell(1).setCellValue("nom_trab"); rowhead.createCell(2).setCellValue("tot_h_imp"); rowhead.createCell(3).setCellValue("desc_salud"); rowhead.createCell(8).setCellValue("afiliados: " + aux.size()); for (SaludInfo usuario : aux) { rowhead = sheet_salud.createRow((short) j++); rowhead.createCell(0).setCellValue(usuario.getRut()); rowhead.createCell(1).setCellValue(usuario.getNombre()); rowhead.createCell(2).setCellValue(usuario.getTotImp()); rowhead.createCell(3).setCellValue(usuario.getDescSalud()); } rowhead = sheet_salud.createRow((short) j++); int totalTrab = aux.size(); rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")"); rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")"); j++; } FileOutputStream fileOut; fileOut = new FileOutputStream(file); workbook.write(fileOut); fileOut.close(); JOptionPane.showMessageDialog(null, "Libro de remuneraciones generado con xito", "Operacin exitosa", JOptionPane.INFORMATION_MESSAGE); } catch (IOException ie) { JOptionPane.showMessageDialog(null, "El archivo est siendo ocupado\nCierre el archivo y vuelva a intentarlo", "Error", JOptionPane.INFORMATION_MESSAGE); ie.printStackTrace(); } catch (Exception e) { JOptionPane.showMessageDialog(null, "Error al crear libro de remuneraciones", "Error", JOptionPane.INFORMATION_MESSAGE); e.printStackTrace(); } } }; runnable.start(); }
From source file:corner.orm.tapestry.service.excel.ExcelService.java
License:Apache License
/** * ?titleStyle/*w ww.j av a2s . c o m*/ * * @return */ protected HSSFCellStyle getTitleStyle(HSSFWorkbook wb) { // create title Style HSSFCellStyle titleRowStyle = wb.createCellStyle(); titleRowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 12); titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleRowStyle.setFont(titleFont); return titleRowStyle; }
From source file:corner.orm.tapestry.service.excel.ExcelService.java
License:Apache License
/** * contentCell?,??/*from ww w . j a v a2 s. c o m*/ * @param wb * @return {@link HSSFCellStyle} */ protected HSSFCellStyle getContentCellStyle(HSSFWorkbook wb) { HSSFCellStyle contentRowStyle = wb.createCellStyle(); return contentRowStyle; }
From source file:data.services.EventService.java
public void saveLog(Long evId, List<EntityCar> carList, List<IdealEntityParam> ieplist, List<EntityCar> cutCarList, Sequence seq) throws Exception { Logger log = Logger.getLogger(this.getClass()); //Event ev = eventDao.find(evId); try {/*from w ww .j a v a 2 s . c o m*/ HashSet<String> iepuids = new HashSet(); for (IdealEntityParam iep : ieplist) { iepuids.add(iep.getBaseParam().getUid()); } List<BaseParam> bpList = baseParamService.getParams(); HashMap<Long, String> genMap = subModelService.getGenerationUrls(); HSSFWorkbook workbook = new HSSFWorkbook(); Event ev = eventDao.find(evId); HashMap<Long, SubModel> bodyMap = subModelService.getBodies(); HSSFCellStyle style = workbook.createCellStyle(); //style.setFillBackgroundColor(HSSFColor.ORANGE.index); style.setFillForegroundColor(HSSFColor.ORANGE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFSheet idealEntitySheet = workbook.createSheet("? ??"); HSSFSheet chosenSheet = workbook.createSheet(" ?"); HSSFSheet cutedSheet = workbook.createSheet(" ?"); HSSFSheet moneySheet = workbook.createSheet(" "); HSSFSheet instChosenSheet = workbook.createSheet(""); int complectList = 1; int ierownum = 1; idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getName() + "; " + ev.getId() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue("?: " + ev.getAuthor() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getAddDate() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getScene().getName() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getMoney() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(" ???: " + ev.getPerception().getName() + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue(": " + ev.getRadical() + "(" + ev.getColor() + ")" + ";"); idealEntitySheet.createRow(ierownum++).createCell(0) .setCellValue("?: " + seq.getRadCore() + ";"); HSSFRow ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? A1: "); ierowhead.createCell(1).setCellValue(ev.getStrategyA1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? B1: "); ierowhead.createCell(1).setCellValue(ev.getStrategyB1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? : "); ierowhead.createCell(1).setCellValue(ev.getResourceType().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? -: "); ierowhead.createCell(1).setCellValue(ev.getParamTox1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? ?: "); ierowhead.createCell(1).setCellValue(ev.getValueTox1().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue(" ?: "); ierowhead.createCell(1).setCellValue(seq.getAllowance() + "%"); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? A2: "); ierowhead.createCell(1).setCellValue(ev.getStrategyA2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? B2: "); ierowhead.createCell(1).setCellValue(ev.getStrategyB2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? -, . ?: "); ierowhead.createCell(1).setCellValue(ev.getParamTox2().getName()); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("?? ?, . ?: "); ierowhead.createCell(1).setCellValue(ev.getValueTox2().getName()); ierownum++; int phn = 1; HSSFRow paramsierowhead = idealEntitySheet.createRow(ierownum++); paramsierowhead.createCell(phn++).setCellValue("UID"); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue(" ."); paramsierowhead.createCell(phn++).setCellValue(""); paramsierowhead.createCell(phn++).setCellValue("?"); paramsierowhead.createCell(phn++).setCellValue(" ?."); paramsierowhead.createCell(phn++).setCellValue(""); int A = 0; int B = 0; for (IdealEntityParam iep : ieplist) { int pbn = 0; HSSFRow paramsierowbody = idealEntitySheet.createRow(ierownum++); paramsierowbody.createCell(pbn++).setCellValue(iep.getNumber()); paramsierowbody.createCell(pbn++).setCellValue(iep.getBaseParam().getUid()); paramsierowbody.createCell(pbn++).setCellValue(iep.getBaseParam().getName()); paramsierowbody.createCell(pbn++).setCellValue(iep.getIepClass().getName()); paramsierowbody.createCell(pbn++).setCellValue(iep.getAmin().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getValueMin().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getValueMax().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(iep.getAmax().doubleValue()); paramsierowbody.createCell(pbn++).setCellValue(StringAdapter.getString(iep.getRank())); if (Rank.A.equals(iep.getRank())) { A++; } else if (Rank.B.equals(iep.getRank())) { B++; } } ierownum++; ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? - ?:"); ierowhead.createCell(1).setCellValue((double) A); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? - B"); ierowhead.createCell(1).setCellValue((double) B); ierownum++; ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(0).setCellValue("? :"); ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(1).setCellValue("UID"); ierowhead.createCell(2).setCellValue("?"); for (SequenceValueRange svr : seq.getSequenceValueRanges()) { String uid = svr.getUid(); if (!iepuids.contains(uid)) { ierowhead = idealEntitySheet.createRow(ierownum++); ierowhead.createCell(1).setCellValue(svr.getUid()); ierowhead.createCell(2).setCellValue(baseParamService.getBaseParam(bpList, uid).getName()); } } int rowheadNumCell = 0; HSSFRow rowhead = chosenSheet.createRow(0); rowhead.createCell(rowheadNumCell++).setCellValue(""); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue(""); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue("?"); rowhead.createCell(rowheadNumCell++).setCellValue(" ?"); rowhead.createCell(rowheadNumCell++).setCellValue("??"); rowhead.createCell(rowheadNumCell++).setCellValue("?? ??"); rowhead.createCell(rowheadNumCell++).setCellValue("? A"); rowhead.createCell(rowheadNumCell++).setCellValue(" A"); rowhead.createCell(rowheadNumCell++).setCellValue("? ? A "); rowhead.createCell(rowheadNumCell++).setCellValue("? B"); rowhead.createCell(rowheadNumCell++).setCellValue(" "); rowhead.createCell(rowheadNumCell++).setCellValue("? ? B"); rowhead.createCell(rowheadNumCell++).setCellValue("? ???"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ??"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ???"); rowhead.createCell(rowheadNumCell++).setCellValue("? ?? ???"); int numscell = 0; HSSFRow instrowhead = instChosenSheet.createRow(0); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(" ?"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??? 1"); instrowhead.createCell(numscell++).setCellValue("??? 2"); instrowhead.createCell(numscell++).setCellValue("? ???"); instrowhead.createCell(numscell++).setCellValue("? ??"); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 6, 13)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 14, 18)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 26)); moneySheet.addMergedRegion(new CellRangeAddress(0, 0, 27, 32)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4)); moneySheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5)); HSSFRow firstRowMoneyHead = moneySheet.createRow(0); HSSFRow secondRowMoneyHead = moneySheet.createRow(1); int frn = 0; firstRowMoneyHead.createCell(frn++).setCellValue(""); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue(""); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue("?"); firstRowMoneyHead.createCell(frn++).setCellValue(" ?"); firstRowMoneyHead.createCell(frn++).setCellValue("??? I"); firstRowMoneyHead.createCell(frn + 7).setCellValue("?? I"); firstRowMoneyHead.createCell(frn + 12).setCellValue("??? II"); firstRowMoneyHead.createCell(frn + 20).setCellValue("?? II"); int srn = 6; secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue(" ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?"); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ???"); secondRowMoneyHead.createCell(srn++).setCellValue("- ?? "); secondRowMoneyHead.createCell(srn++).setCellValue(""); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue("- ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue(" ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? ?"); secondRowMoneyHead.createCell(srn++) .setCellValue(" ?? ?? ??? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?"); secondRowMoneyHead.createCell(srn++).setCellValue("? ??"); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? "); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ???"); secondRowMoneyHead.createCell(srn++).setCellValue("- ?? "); secondRowMoneyHead.createCell(srn++).setCellValue(""); secondRowMoneyHead.createCell(srn++).setCellValue("? ?? ?"); int n = 1; int optrownum = 1; if (!carList.isEmpty()) { //while (optrownum < 50000) { for (EntityCar car : carList) { if (optrownum > 60000) { instChosenSheet = workbook.createSheet(" " + complectList++); optrownum = 1; numscell = 0; instrowhead = instChosenSheet.createRow(0); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue("?"); instrowhead.createCell(numscell++).setCellValue(" ?"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue(""); instrowhead.createCell(numscell++).setCellValue("??"); instrowhead.createCell(numscell++).setCellValue("??? 1"); instrowhead.createCell(numscell++).setCellValue("??? 2"); instrowhead.createCell(numscell++).setCellValue("? ???"); instrowhead.createCell(numscell++).setCellValue("? ??"); } Car realCar = car.car; SubModel sm = bodyMap.get(car.car.getCmsqId()); String body = ""; if (sm != null) { body = sm.getBody(); } int m = n + 1; int bpropsCount = 0; int cn = 0; HSSFRow carRow = instChosenSheet.createRow(optrownum); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.car.getCarId())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getModelTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(car.getCarTitle())); carRow.createCell(cn++).setCellValue(StringAdapter.getString(body)); carRow.createCell(cn++).setCellValue(car.fullPrice.doubleValue()); carRow.createCell(cn++).setCellValue(""); carRow.createCell(cn + 6).setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); optrownum++; /*carRow.createCell(3).setCellValue(renderDecimalNoPoint(car.basePrice).replace(".", ",")); carRow.createCell(6).setCellValue(StringAdapter.getString(car.staticRate).replace(".", ",")); carRow.createCell(7).setCellValue(StringAdapter.getString(car.staticRate.add(car.dinamicRate)).replace(".", ","));*/ for (String uid : car.aprops.keySet()) { EntityPropertyHolder eph = car.aprops.get(uid); boolean alternatives = false; List<EntityProperty> insttalledElems = eph.getActiveProperty(); if (insttalledElems.size() > 1) { alternatives = true; } for (EntityProperty ep : insttalledElems) { HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(body); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } optRow.createCell(on++).setCellValue("A"); optRow.createCell(on++).setCellValue(ep.sign); optRow.createCell(on++).setCellValue(ep.type.getName()); optRow.createCell(on++).setCellValue(ep.name); optRow.createCell(on++).setCellValue(ep.title); optRow.createCell(on++).setCellValue(ep.value.doubleValue()); optRow.createCell(on++).setCellValue(eph.dinamicRate.doubleValue()); optrownum++; /*optRow.createCell(1).setCellValue("A"); optRow.createCell(2).setCellValue(ep.type.getName()); optRow.createCell(3).setCellValue(ep.name); optRow.createCell(4).setCellValue(ep.title); optRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ",")); optRow.createCell(6).setCellValue(StringAdapter.getString(eph.dinamicRate).replace(".", ",")); optRow.createCell(7).setCellValue(StringAdapter.getString(eph.staticRate).replace(".", ","));*/ } } for (String uid : car.bprops.keySet()) { EntityPropertyHolder eph = car.bprops.get(uid); boolean alternatives = false; List<EntityProperty> insttalledElems = eph.getActiveProperty(); if (insttalledElems.size() > 1) { alternatives = true; } if (!insttalledElems.isEmpty()) { bpropsCount++; for (EntityProperty ep : eph.getActiveProperty()) { HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(body)); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.price)); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } optRow.createCell(on++).setCellValue("B"); optRow.createCell(on++).setCellValue(ep.sign); optRow.createCell(on++).setCellValue(ep.type.getName()); optRow.createCell(on++).setCellValue(ep.name); optRow.createCell(on++).setCellValue(ep.title); optRow.createCell(on++).setCellValue(ep.value.doubleValue()); optRow.createCell(on++).setCellValue(eph.dinamicRate.doubleValue()); optrownum++; /*HSSFRow optRow = instChosenSheet.createRow((short) optrownum); HSSFCell uidCell = optRow.createCell(0); uidCell.setCellValue(uid); if(alternatives){ //optRow.createCell(8).setCellValue(""); uidCell.setCellStyle(style); } optRow.createCell(1).setCellValue("B"); optRow.createCell(2).setCellValue(ep.type.getName()); optRow.createCell(3).setCellValue(ep.name); optRow.createCell(4).setCellValue(ep.title); optRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ",")); optRow.createCell(6).setCellValue(StringAdapter.getString(eph.dinamicRate).replace(".", ",")); optRow.createCell(7).setCellValue(StringAdapter.getString(eph.staticRate).replace(".", ",")); optrownum++;*/ } } else { BaseParam bp = baseParamService.getBaseParam(uid); HSSFRow optRow = instChosenSheet.createRow(optrownum); int on = 0; optRow.createCell(on++).setCellValue(StringAdapter.getString(car.car.getCarId())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getMarkTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getModelTitle())); optRow.createCell(on++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(car.getCarTitle())); optRow.createCell(on++).setCellValue(StringAdapter.getString(body)); //optRow.createCell(on++).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice()).replace(".", ",")); optRow.createCell(on++).setCellValue(" - "); HSSFCell uidCell = optRow.createCell(on++); uidCell.setCellValue(uid); optRow.createCell(on++).setCellValue("B"); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue(bp.getName()); optRow.createCell(on++).setCellValue(" - "); optRow.createCell(on++).setCellValue("null"); optRow.createCell(on++).setCellValue(" - "); optrownum++; } } /*String aRate = " - "; if(car.dinamicRateA.compareTo(BigDecimal.valueOf(0))>0){ aRate = StringAdapter.getString(car.dinamicRateA).replace(".", ","); } String acount = " - "; if(car.aprops.keySet().size()>0){ acount = StringAdapter.getString(car.aprops.keySet().size()); }*/ String msA = " - "; BigDecimal medA = BigDecimal.valueOf(0); if (car.aprops.keySet().size() > 0) { medA = car.dinamicRateA.divide(BigDecimal.valueOf(car.aprops.keySet().size()), 2, RoundingMode.HALF_UP); msA = StringAdapter.getString(medA).replace(".", ","); } /* String bRate = " - "; if(car.dinamicRateB.compareTo(BigDecimal.valueOf(0))>0){ bRate = StringAdapter.getString(car.dinamicRateB).replace(".", ","); }*/ String bcount = " - "; if (bpropsCount > 0) { bcount = StringAdapter.getString(bpropsCount); } String msB = " - "; BigDecimal medB = BigDecimal.valueOf(0); if (bpropsCount > 0) { medB = car.dinamicRateB.divide(BigDecimal.valueOf(bpropsCount), 2, RoundingMode.HALF_UP); msB = StringAdapter.getString(medB).replace(".", ","); } /*String bsr = " - "; if(car.baseStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ bsr = StringAdapter.getString(car.baseStaticRate2).replace(".", ","); } String fsr = " - "; if(car.freeStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ fsr = StringAdapter.getString(car.freeStaticRate2).replace(".", ","); } String msr = " - "; if(car.monetaryStaticRate2.compareTo(BigDecimal.valueOf(0))>0){ msr = StringAdapter.getString(car.monetaryStaticRate2).replace(".", ","); }*/ Model model = realCar.getModel(); HSSFRow rowbody = chosenSheet.createRow(n); int rowbodynum = 0; rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.car.getCarId())); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowbody.createCell(rowbodynum++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); HSSFCell nameCellWithLink = rowbody.createCell(rowbodynum++); nameCellWithLink.setCellValue(StringAdapter.getString(car.getCarTitle())); HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL); link.setAddress("http://quto.ru/" + model.getMark().getUrl() + "/" + model.getUrl() + "/" + genMap.get(realCar.getCmgqId()) + "/" + bodyMap.get(realCar.getCmsqId()).getUrl() + "/" + realCar.getUrl()); nameCellWithLink.setHyperlink(link); rowbody.createCell(rowbodynum++).setCellValue(StringAdapter.getString(body)); rowbody.createCell(rowbodynum++).setCellValue(car.basePrice.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.fullPrice.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.dinamicRateA.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue((double) car.aprops.keySet().size()); rowbody.createCell(rowbodynum++).setCellValue(medA.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.dinamicRateB.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue((double) bpropsCount); rowbody.createCell(rowbodynum++).setCellValue(medB.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(medA.add(medB).doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.baseStaticRate2.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.freeStaticRate2.doubleValue()); rowbody.createCell(rowbodynum++).setCellValue(car.monetaryStaticRate2.doubleValue()); int rmbn = 0; HSSFRow rowMoneyBody = moneySheet.createRow(m); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.car.getId())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowMoneyBody.createCell(rmbn++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(car.getCarTitle())); rowMoneyBody.createCell(rmbn++).setCellValue(StringAdapter.getString(body)); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.aprops.keySet().size()); rowMoneyBody.createCell(rmbn++).setCellValue((double) bpropsCount); rowMoneyBody.createCell(rmbn++).setCellValue(car.basePrice.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.bFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund1.add(car.bFund1).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue( ev.getMoney().subtract(car.aFund1.add(car.bFund1).add(car.basePrice)).doubleValue()); rowMoneyBody.createCell(rmbn++) .setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.baseStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.freeStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.payStaticCount1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.staticFund1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.monetaryStaticRate1.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.aCount2); rowMoneyBody.createCell(rmbn++).setCellValue((double) car.bCount2); rowMoneyBody.createCell(rmbn++).setCellValue(car.basePrice.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.bFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.aFund2.add(car.bFund2).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue( ev.getMoney().subtract(car.aFund2.add(car.bFund2).add(car.basePrice)).doubleValue()); rowMoneyBody.createCell(rmbn++) .setCellValue(car.dinamicRateA.add(car.dinamicRateB).doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.baseStaticRate2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.freeStaticRate2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.payStaticCount2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.staticFund2.doubleValue()); rowMoneyBody.createCell(rmbn++).setCellValue(car.monetaryStaticRate2.doubleValue()); n++; HSSFRow carStaticRowHead = instChosenSheet.createRow(optrownum); int csn = 0; carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.car.getCarId())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getMarkTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getModelTitle())); carStaticRowHead.createCell(csn++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(car.getCarTitle())); carStaticRowHead.createCell(csn++).setCellValue(StringAdapter.getString(body)); //carStaticRowHead.createCell(3).setCellValue(RenderSupport.renderDecimalNoPoint(ep.getPrice())); carStaticRowHead.createCell(csn++).setCellValue(":"); carStaticRowHead.createCell(csn + 7).setCellValue(car.staticRate.doubleValue()); optrownum++; Map<String, List<EntityProperty>> StaticMap = car.staticProps; for (String uid : StaticMap.keySet()) { List<EntityProperty> insttalledElems = StaticMap.get(uid); if (insttalledElems != null && !insttalledElems.isEmpty()) { boolean alternatives = false; if (insttalledElems.size() > 1) { alternatives = true; } for (EntityProperty ep : insttalledElems) { String title = ep.title; if (!ep.description.trim().equals("")) { title += " - " + ep.description; } HSSFRow carStaticRow = instChosenSheet.createRow(optrownum); int cson = 0; carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.car.getCarId())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getMarkTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getModelTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); carStaticRow.createCell(cson++) .setCellValue(StringAdapter.getString(car.getCarTitle())); carStaticRow.createCell(cson++).setCellValue(StringAdapter.getString(body)); //carStaticRow.createCell(cson++).setCellValue(StringAdapter.getString(ep.getPrice()).replace(".", ",")); carStaticRow.createCell(cson++).setCellValue(" - "); HSSFCell uidCell = carStaticRow.createCell(cson++); uidCell.setCellValue(uid); if (alternatives) { uidCell.setCellStyle(style); } //carStaticRow.createCell(5).setCellValue(uid); carStaticRow.createCell(cson++).setCellValue("S"); carStaticRow.createCell(cson++).setCellValue(ep.sign); carStaticRow.createCell(cson++).setCellValue(ep.type.getName()); carStaticRow.createCell(cson++).setCellValue(ep.name); carStaticRow.createCell(cson++).setCellValue(title); carStaticRow.createCell(cson++).setCellValue(ep.radicalValue); carStaticRow.createCell(cson + 1).setCellValue(ep.value.doubleValue()); /*carStaticRow.createCell(1).setCellValue(ep.name); carStaticRow.createCell(2).setCellValue(ep.title); carStaticRow.createCell(3).setCellValue(ep.type.getName()); carStaticRow.createCell(5).setCellValue(StringAdapter.getString(ep.value).replace(".", ","));*/ optrownum++; } } } /*break;*/ } } int k = 0; HSSFRow rowhead20 = cutedSheet.createRow(k++); HSSFRow rowhead21 = cutedSheet.createRow(k++); HSSFRow rowhead22 = cutedSheet.createRow(k++); int ccn = 0; rowhead20.createCell(0).setCellValue("UID ??"); rowhead21.createCell(ccn++).setCellValue(""); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue(""); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue("?"); rowhead21.createCell(ccn++).setCellValue(" ?"); rowhead22.createCell(0).setCellValue(" ???"); int cutoptrownum = 0; for (IdealEntityParam iep : ieplist) { rowhead20.createCell(ccn).setCellValue(StringAdapter.getString(iep.baseParam.getUid())); rowhead21.createCell(ccn).setCellValue(iep.number + "." + iep.baseParam.getName()); rowhead22.createCell(ccn++).setCellValue(iep.aimBefore.doubleValue()); } if (!cutCarList.isEmpty()) { for (EntityCar car : cutCarList) { SubModel sm = bodyMap.get(car.car.getCmsqId()); String body = ""; if (sm != null) { body = sm.getBody(); } HSSFRow rowbody = cutedSheet.createRow(k++); int con = 0; rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.car.getCarId())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getMarkTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getModelTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.car.getCompletionTitle())); rowbody.createCell(con++).setCellValue(StringAdapter.getString(car.getCarTitle())); rowbody.createCell(con).setCellValue(body); int num = 0; if (car.cutNum != null) { num = car.cutNum; } String cutValue = car.cutVal; if (cutValue == null || cutValue.equals("null")) { cutValue = "? "; } try { Double cutedValue = Double.valueOf(car.cutVal); rowbody.createCell(con + num).setCellValue(cutedValue); } catch (Exception e) { rowbody.createCell(con + num).setCellValue(cutValue.replace(".", ",")); } /*HSSFRow carRow = instCutedSheet.createRow(cutoptrownum); carRow.createCell(0).setCellValue(StringAdapter.getString(car.car.getCarId())); carRow.createCell(1).setCellValue(StringAdapter.getString(car.getMarkTitle())); carRow.createCell(2).setCellValue(StringAdapter.getString(car.getModelTitle())); carRow.createCell(3).setCellValue(StringAdapter.getString(car.getCarTitle())); carRow.createCell(4).setCellValue(RenderSupport.renderDecimalNoPoint(car.basePrice).replace(".", ",")); carRow.createCell(5).setCellValue(RenderSupport.renderDecimalNoPoint(car.fixPrice).replace(".", ",")); carRow.createCell(6).setCellValue(StringAdapter.getString(car.dinamicRateA.add(car.dinamicRateB)).replace(".", ",")); carRow.createCell(7).setCellValue(StringAdapter.getString(car.staticRate).replace(".", ",")); carRow.createCell(8).setCellValue(StringAdapter.getString(car.staticRate.add(car.dinamicRateA.add(car.dinamicRateB))).replace(".", ",")); cutoptrownum++; HSSFRow carRowHead = instCutedSheet.createRow(cutoptrownum); carRowHead.createCell(0).setCellValue(StringAdapter.getString("")); carRowHead.createCell(1).setCellValue(StringAdapter.getString("")); carRowHead.createCell(2).setCellValue(StringAdapter.getString("??")); carRowHead.createCell(3).setCellValue(StringAdapter.getString("?????")); carRowHead.createCell(4).setCellValue(StringAdapter.getString("???")); carRowHead.createCell(5).setCellValue(StringAdapter.getString("%")); carRowHead.createCell(6).setCellValue(StringAdapter.getString("??")); cutoptrownum++;*/ /*for (PropertyName pn : car.car.getPropertyNames()) { CarProperty p = pn.getCarProperty(); if (p != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (p.getUid() != null) { optRow.createCell(0).setCellValue(p.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("?"); if (p.getTitle() != null) { optRow.createCell(2).setCellValue(p.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (pn.getPropertyNameValue() != null && !pn.getPropertyNameValue().equals("")) { optRow.createCell(3).setCellValue(pn.getPropertyNameValue()); } else { optRow.createCell(3).setCellValue("? "); } if (pn.getParamValue() != null && !pn.getParamValue().equals("")) { optRow.createCell(4).setCellValue(pn.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (pn.getPercentValue() != null && !pn.getPercentValue().equals("")) { optRow.createCell(5).setCellValue(pn.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (pn.getRadical() != null && !pn.getRadical().equals("")) { optRow.createCell(6).setCellValue(pn.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } } List<Feature> flist = carService.getFeatures(car.car); for (Feature f : flist) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (f.getUid() != null) { optRow.createCell(0).setCellValue(f.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("??"); optRow.createCell(2).setCellValue(""); if (f.getTitle() != null) { optRow.createCell(3).setCellValue(f.getTitle()); } else { optRow.createCell(3).setCellValue("? "); } if (f.getParamValue() != null) { optRow.createCell(4).setCellValue(f.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (f.getPercentValue() != null) { optRow.createCell(5).setCellValue(f.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (f.getRadical() != null) { optRow.createCell(6).setCellValue(f.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; }*/ /*for (CarOptionValue cov : car.car.getCarOptionValues()) { CarCompletionOption cco = cov.getCCO(); if (cco != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (cco.getUid() != null) { optRow.createCell(0).setCellValue(cco.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue("?"); if (cco.getTitle() != null) { optRow.createCell(2).setCellValue(cco.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (cov.getTitle() != null) { optRow.createCell(3).setCellValue(cov.getTitle()); } else { optRow.createCell(3).setCellValue("? "); } if (cco.getParamValue() != null) { optRow.createCell(4).setCellValue(cco.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (cco.getPercentValue() != null) { optRow.createCell(5).setCellValue(cco.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (cco.getRadical() != null) { optRow.createCell(6).setCellValue(cco.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } } for (CarColorValue ccv : car.car.getCarColorValues()) { Color col = ccv.getColor(); if (col != null) { HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); if (col.getUid() != null) { optRow.createCell(0).setCellValue(col.getUid()); } else { optRow.createCell(0).setCellValue("? "); } optRow.createCell(1).setCellValue(""); if (col.getTitle() != null) { optRow.createCell(2).setCellValue(col.getTitle()); } else { optRow.createCell(2).setCellValue("? "); } if (col.getName() != null) { optRow.createCell(3).setCellValue(col.getName()); } else { optRow.createCell(3).setCellValue("? "); } if (col.getParamValue() != null) { optRow.createCell(4).setCellValue(col.getParamValue()); } else { optRow.createCell(4).setCellValue("? "); } if (col.getPercentValue() != null) { optRow.createCell(5).setCellValue(col.getPercentValue()); } else { optRow.createCell(5).setCellValue("? "); } if (col.getRadical() != null) { optRow.createCell(6).setCellValue(col.getRadical()); } else { optRow.createCell(6).setCellValue("? "); } cutoptrownum++; } }*/ /*for(String uid:car.aprops.keySet()){ for(EntityProperty ep:car.aprops.get(uid).getPropList()){ HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); optRow.createCell(0).setCellValue(uid); optRow.createCell(1).setCellValue(ep.name); optRow.createCell(2).setCellValue(ep.title); optRow.createCell(3).setCellValue(ep.type.getName()); optRow.createCell(4).setCellValue(StringAdapter.getString(ep.value)); cutoptrownum++; } } for(String uid:car.bprops.keySet()){ for(EntityProperty ep:car.bprops.get(uid).getPropList()){ HSSFRow optRow = instCutedSheet.createRow((short) cutoptrownum); optRow.createCell(0).setCellValue(uid); optRow.createCell(1).setCellValue(ep.name); optRow.createCell(2).setCellValue(ep.title); optRow.createCell(3).setCellValue(ep.type.getName()); optRow.createCell(4).setCellValue(StringAdapter.getString(ep.value)); cutoptrownum++; } }*/ } } File f = new File("/usr/local/etc/xls/" + evId + ".xls"); if (f.exists()) { f.delete(); } workbook.write(new FileOutputStream("/usr/local/etc/xls/" + evId + ".xls")); } catch (Exception e) { //log.warn("HSSFWorkbook.getXls()", e); throw new Exception(e); } }
From source file:dataQuality.checkDuplicates.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from w w w. j a v a 2 s . c om*/ dbConn conn = new dbConn(); i = 4; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 7000); shet1.setColumnWidth(5, 5300); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5200); shet1.setColumnWidth(8, 5200); shet1.setColumnWidth(9, 5200); shet1.setColumnWidth(10, 5800); shet1.setColumnWidth(11, 5000); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); // styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); // styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell3 = rw4.createCell(2); cell4 = rw4.createCell(3); cell5 = rw4.createCell(4); cell6 = rw4.createCell(5); cell7 = rw4.createCell(6); cell8 = rw4.createCell(7); cell9 = rw4.createCell(8); cell10 = rw4.createCell(9); cell11 = rw4.createCell(10); cell12 = rw4.createCell(11); cell13 = rw4.createCell(12); cell14 = rw4.createCell(13); cell1.setCellValue("COUNTY NAME"); cell2.setCellValue("PARTNER NAME"); cell3.setCellValue("DISTRICT"); cell4.setCellValue("FACILITY"); cell5.setCellValue("GROUP NAME"); cell6.setCellValue("CLIENT NAME"); cell7.setCellValue("AGE"); cell8.setCellValue("GENDER"); cell9.setCellValue("YEAR"); cell10.setCellValue("SESSION ATTENDED"); cell11.setCellValue("No. of duplicates"); cell11.setCellValue("SERVICE PROVIDER"); cell12.setCellValue("START DATE"); cell13.setCellValue("END DATE"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); cell11.setCellStyle(stylex); cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); //cell14.setCellStyle(stylex); i = 1; String getClients = "SELECT * FROM clients ORDER BY fname"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; sess = val = cds = duplicate = 0; startdate = enddate = ""; // System.out.println("here : "+i); HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; cell1x = rw4x.createCell(0); cell2x = rw4x.createCell(1); cell3x = rw4x.createCell(2); cell4x = rw4x.createCell(3); cell5x = rw4x.createCell(4); cell6x = rw4x.createCell(5); cell7x = rw4x.createCell(6); cell8x = rw4x.createCell(7); cell9x = rw4x.createCell(8); cell10x = rw4x.createCell(9); cell11x = rw4x.createCell(10); cell12x = rw4x.createCell(11); cell13x = rw4x.createCell(12); // cell14x=rw4x.createCell(13); providername = ""; clientid = conn.rs.getString(1); clientname = conn.rs.getString(2) + " " + conn.rs.getString(3) + " " + conn.rs.getString(4); age = conn.rs.getString(5); gender = conn.rs.getString(6); groupid = conn.rs.getString(7); groupings = conn.rs.getString(8); districtid = conn.rs.getString(9); partnerid = conn.rs.getString(10); year = conn.rs.getString(13); providerid = conn.rs.getString(14); if (conn.rs.getString(3).equals(conn.rs.getString(4))) { clientname = conn.rs.getString(2) + " " + conn.rs.getString(4); } cds = conn.rs.getInt("lessons_attended"); start_date = end_date = ""; String serviceprov = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + providerid + "'"; conn.rs1 = conn.st1.executeQuery(serviceprov); if (conn.rs1.next()) { if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { providername = conn.rs1.getString(1) + " " + conn.rs.getString(3); } else { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs.getString(3); } } String checker = "SELECT COUNT(client_id) FROM clients WHERE fname=? && lname=? && client_id!=?"; conn.pst = conn.conn.prepareStatement(checker); conn.pst.setString(1, conn.rs.getString(2)); conn.pst.setString(2, conn.rs.getString(3)); // conn.pst.setString(3, conn.rs.getString(4)); conn.pst.setString(3, clientid); // conn.pst.setString(3, conn.rs.getString(2)); // conn.pst.setString(4, conn.rs.getString(3)); // conn.pst.setString(5, groupid); // conn.pst.setString(6, conn.rs.getString(2)); // conn.pst.setString(7, age); // conn.pst.setString(8, clientid); // conn.pst.setString(8, age); conn.rs1 = conn.pst.executeQuery(); found = 0; if (conn.rs1.next() == true) { found = conn.rs1.getInt(1); } System.out.println("found : " + found); if (found > 0) { duplicate = found; } if (found == 0) { duplicate = 0; } String getCnt = "SELECT district.district_name,county.county_name FROM district JOIN county ON district.county_id=county.county_id WHERE district.district_id='" + districtid + "'"; conn.rs1 = conn.st1.executeQuery(getCnt); if (conn.rs1.next() == true) { district = conn.rs1.getString(1); county = conn.rs1.getString(2); } String getPart = "SELECT partner_name FROM partner WHERE partner_id='" + partnerid + "'"; conn.rs1 = conn.st1.executeQuery(getPart); if (conn.rs1.next() == true) { partner = conn.rs1.getString(1); } if (!groupid.equals("0")) { String getgrp = "SELECT groups.group_name,health_facility.hf_name FROM groups JOIN health_facility ON groups.nhf_id=health_facility.hf_id" + " WHERE groups.group_id='" + groupid + "'"; conn.rs1 = conn.st1.executeQuery(getgrp); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); hf = conn.rs1.getString(2); // SELECT START END DATE DATE FOR GROUP INDIVIDUALS---------------------------------------------- String getDates = "SELECT MAX(session_date),MIN(session_date) FROM sessions WHERE group_id='" + groupid + "' && session_date!=''"; conn.rs1 = conn.st1.executeQuery(getDates); if (conn.rs1.next() == true) { start_date = conn.rs1.getString(2); end_date = conn.rs1.getString(1); } } } if (groupid.equals("0")) { String getgrp1 = "SELECT health_facility.hf_name FROM no_group JOIN health_facility ON no_group.nhf_id=health_facility.hf_id" + " WHERE no_group.name='" + groupings + "'"; conn.rs1 = conn.st1.executeQuery(getgrp1); if (conn.rs1.next() == true) { groupname = "INDIVIDUAL"; hf = conn.rs1.getString(1); } String getDates = "SELECT MAX(session_date),MIN(session_date) FROM sessions WHERE groupings='" + groupings + "' && session_date!=''"; conn.rs1 = conn.st1.executeQuery(getDates); if (conn.rs1.next() == true) { start_date = conn.rs1.getString(2); end_date = conn.rs1.getString(1); } } // OUTPUT ATTENDED-------------------------------- if (duplicate > 0) { // OUTPUT SERVICES PROVIDED================================ cell1x.setCellValue(county); cell2x.setCellValue(partner); cell3x.setCellValue(district); cell4x.setCellValue(hf); cell5x.setCellValue(groupname); cell6x.setCellValue(clientname); cell7x.setCellValue(age); cell8x.setCellValue(gender); cell9x.setCellValue(year); cell10x.setCellValue(cds); // cell11x.setCellValue(duplicate); cell11x.setCellValue(providername); cell12x.setCellValue(start_date); cell13x.setCellValue(end_date); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); cell5x.setCellStyle(styleBorder); cell6x.setCellStyle(styleBorder); cell7x.setCellStyle(styleBorder); cell8x.setCellStyle(styleBorder); cell9x.setCellStyle(styleBorder); cell10x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell11x.setCellStyle(styleBorder); cell12x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); //cell14x.setCellStyle(styleBorder); i++; System.out.println("here : " + i); } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_DUPLICATE_REPORT.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:dataQuality.Syncer.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from w w w . ja v a 2 s.c o m*/ dbConn conn = new dbConn(); String[] dater1 = request.getParameter("startdate").split("/"); String[] dater2 = request.getParameter("enddate").split("/"); String[] bydate = request.getParameter("bydate").split("/"); System.out.println("started"); datekey = bydate[2] + "" + bydate[1] + "" + bydate[0]; startdate = dater1[2] + "-" + dater1[1] + "-" + dater1[0]; enddate = dater2[2] + "-" + dater2[1] + "-" + dater2[0]; sdate = dater1[2] + "_" + dater1[1] + "_" + dater1[0]; ndate = dater2[2] + "_" + dater2[1] + "_" + dater2[0]; submissiondate = bydate[1] + "/" + bydate[0] + "/" + bydate[2]; System.out.println(submissiondate); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); // HSSFSheet shet1=wb.createSheet("Client Enrollments"); HSSFSheet shet2 = wb.createSheet("Session Attendance"); HSSFSheet shet3 = wb.createSheet("Services Provided"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); // // HSSFSheet sheet1 = wb.getSheetAt(0); // shet1.setColumnWidth(0, 4000); // shet1.setColumnWidth(1, 4000); // shet1.setColumnWidth(2, 4000); // shet1.setColumnWidth(3, 7500); // shet1.setColumnWidth(4, 7000); // shet1.setColumnWidth(5, 7300); // shet1.setColumnWidth(6, 2500); // shet1.setColumnWidth(7, 3200); // shet1.setColumnWidth(8, 4200); shet2.setColumnWidth(0, 4000); shet2.setColumnWidth(1, 4000); shet2.setColumnWidth(2, 4000); shet2.setColumnWidth(3, 6500); shet2.setColumnWidth(4, 7000); shet2.setColumnWidth(5, 7300); shet2.setColumnWidth(6, 2500); shet2.setColumnWidth(7, 3200); shet2.setColumnWidth(8, 4200); shet3.setColumnWidth(0, 4000); shet3.setColumnWidth(1, 4000); shet3.setColumnWidth(2, 4000); shet3.setColumnWidth(3, 6500); shet3.setColumnWidth(4, 7000); shet3.setColumnWidth(5, 7300); shet3.setColumnWidth(6, 2500); shet3.setColumnWidth(7, 3200); shet3.setColumnWidth(8, 4200); shet3.setColumnWidth(9, 4200); shet3.setColumnWidth(10, 4800); shet3.setColumnWidth(11, 4000); shet3.setColumnWidth(12, 4300); shet3.setColumnWidth(13, 4000); shet3.setColumnWidth(14, 4300); shet3.setColumnWidth(15, 4000); shet3.setColumnWidth(16, 4200); shet3.setColumnWidth(17, 4200); // HSSFRow rw4=shet1.createRow(0); // rw4.setHeightInPoints(45); // rw4.setRowStyle(style2); //// rw4.createCell(1).setCellValue("Number"); // HSSFCell cell0,cell1,cell2,cell3,cell4,cell5,cell6,cell7,cell8; // // cell0=rw4.createCell(0); // cell1=rw4.createCell(1); // cell2=rw4.createCell(2); // cell3=rw4.createCell(3); // cell4=rw4.createCell(4); // cell5=rw4.createCell(5); // cell6=rw4.createCell(6); // cell7=rw4.createCell(7); // cell8=rw4.createCell(8); // // // cell0 .setCellValue("County name"); // cell1.setCellValue("Partner name"); // cell2.setCellValue("District name"); // cell3.setCellValue("Group name"); // cell4.setCellValue("Service provider"); // cell5.setCellValue("Client name"); // cell6.setCellValue("Age"); // cell7.setCellValue("Gender"); // cell8.setCellValue("Timestamp"); // cell0 .setCellStyle(stylex); // cell1.setCellStyle(stylex); // cell2.setCellStyle(stylex); // cell3.setCellStyle(stylex); // cell4.setCellStyle(stylex); // cell5.setCellStyle(stylex); // cell6.setCellStyle(stylex); // cell7.setCellStyle(stylex); // cell8.setCellStyle(stylex); HSSFRow rw2m = shet2.createRow(0); rw2m.setHeightInPoints(45); rw2m.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cellm0, cellm1, cellm2, cellm3, cellm4, cellm5, cellm6, cellm7, cellm8, cellm9, cellm10, cellmk11; cellm0 = rw2m.createCell(0); cellm1 = rw2m.createCell(1); cellm2 = rw2m.createCell(2); cellm3 = rw2m.createCell(3); cellm4 = rw2m.createCell(4); cellm5 = rw2m.createCell(5); cellm6 = rw2m.createCell(6); cellm7 = rw2m.createCell(7); cellm8 = rw2m.createCell(8); cellm9 = rw2m.createCell(9); cellm10 = rw2m.createCell(10); cellmk11 = rw2m.createCell(11); cellm0.setCellValue("County name"); cellm1.setCellValue("Partner name"); cellm2.setCellValue("District name"); cellm3.setCellValue("Group name"); cellm4.setCellValue("Service provider"); cellm5.setCellValue("Client name"); cellm6.setCellValue("Age"); cellm7.setCellValue("Gender"); cellm8.setCellValue("Message"); cellm9.setCellValue("Status"); cellm10.setCellValue("Session Date"); cellmk11.setCellValue("Timestamp"); cellm0.setCellStyle(stylex); cellm1.setCellStyle(stylex); cellm2.setCellStyle(stylex); cellm3.setCellStyle(stylex); cellm4.setCellStyle(stylex); cellm5.setCellStyle(stylex); cellm6.setCellStyle(stylex); cellm7.setCellStyle(stylex); cellm8.setCellStyle(stylex); cellm9.setCellStyle(stylex); cellm10.setCellStyle(stylex); cellmk11.setCellStyle(stylex); HSSFRow rw4n = shet3.createRow(0); rw4n.setHeightInPoints(45); rw4n.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0n, cell1n, cell2n, cell3n, cell4n, cell5n, cell6n, cell7n, cell8n, cell9n, cell10n, cell11n, cell12n, cell13n, cell14n, cell15n, cell16n, cell17n, cell18n; cell0n = rw4n.createCell(0); cell1n = rw4n.createCell(1); cell2n = rw4n.createCell(2); cell3n = rw4n.createCell(3); cell4n = rw4n.createCell(4); cell5n = rw4n.createCell(5); cell6n = rw4n.createCell(6); cell7n = rw4n.createCell(7); cell8n = rw4n.createCell(8); cell9n = rw4n.createCell(9); cell10n = rw4n.createCell(10); cell11n = rw4n.createCell(11); cell12n = rw4n.createCell(12); cell13n = rw4n.createCell(13); cell14n = rw4n.createCell(14); cell15n = rw4n.createCell(15); cell16n = rw4n.createCell(16); cell17n = rw4n.createCell(17); cell18n = rw4n.createCell(18); cell0n.setCellValue("County name"); cell1n.setCellValue("Partner name"); cell2n.setCellValue("District name"); cell3n.setCellValue("Group name"); cell4n.setCellValue("Service provider"); cell5n.setCellValue("Client name"); cell6n.setCellValue("Age"); cell7n.setCellValue("Gender"); cell8n.setCellValue("Message (s)"); cell9n.setCellValue("Received Contraceptives"); cell10n.setCellValue("Reffered To Service Point"); cell11n.setCellValue("Given Condoms"); cell12n.setCellValue("Screened For TB"); cell13n.setCellValue("Screened For STIs"); cell14n.setCellValue("Partner Tested"); cell15n.setCellValue("Children Tested"); cell16n.setCellValue("Disclosed Status"); cell17n.setCellValue("Submission date"); cell18n.setCellValue("Timestamp"); cell0n.setCellStyle(stylex); cell1n.setCellStyle(stylex); cell2n.setCellStyle(stylex); cell3n.setCellStyle(stylex); cell4n.setCellStyle(stylex); cell5n.setCellStyle(stylex); cell6n.setCellStyle(stylex); cell7n.setCellStyle(stylex); cell8n.setCellStyle(stylex); cell9n.setCellStyle(stylex); cell10n.setCellStyle(stylex); cell11n.setCellStyle(stylex); cell12n.setCellStyle(stylex); cell13n.setCellStyle(stylex); cell14n.setCellStyle(stylex); cell15n.setCellStyle(stylex); cell16n.setCellStyle(stylex); cell17n.setCellStyle(stylex); cell18n.setCellStyle(stylex); pos1 = 1; pos2 = 1; pos3 = 1; //String getClientRegistration="SELECT county.county_name,partner.partner_name,district.district_name,group_id,clients.age,provider_id, " // + "clients.gender,clients.timestamp,clients.fname,clients.mname,clients.lname FROM clients JOIN partner ON clients.partner_id=partner.partner_id " // + "JOIN (district JOIN county ON district.county_id=county.county_id )ON district.district_id=clients.district_id " // + "WHERE STR_TO_DATE(clients.timestamp,'%Y-%m-%d') BETWEEN STR_TO_DATE('"+startdate+"','%Y-%m-%d') AND STR_TO_DATE('"+enddate+"','%Y-%m-%d')"; // conn.rs=conn.st.executeQuery(getClientRegistration); // while(conn.rs.next()){ // countyname=conn.rs.getString(1); // partnername=conn.rs.getString(2); // districtname=conn.rs.getString(3); // age=conn.rs.getInt(5); // gender=conn.rs.getString(7); // timestamp=conn.rs.getString(8); // // clientname=conn.rs.getString(9)+" "+conn.rs.getString(10)+" "+conn.rs.getString(11); // if(conn.rs.getString(10).equals(conn.rs.getString(11))){ // clientname=conn.rs.getString(9)+" "+conn.rs.getString(11); // } // // if(!conn.rs.getString(4).equals("0")){ // String getGroupname="SELECT group_name FROM groups WHERE group_id='"+conn.rs.getString(4)+"'"; // conn.rs1=conn.st1.executeQuery(getGroupname); // if(conn.rs1.next()==true){ // groupname=conn.rs1.getString(1); // } // } // else{ // groupname="INDIVIDUALS"; // } // String getProvider="SELECT fname,mname,lname FROM service_provider WHERE provider_id='"+conn.rs.getString(6)+"'"; // conn.rs1=conn.st1.executeQuery(getProvider); // if(conn.rs1.next()==true){ // providername=conn.rs1.getString(1)+" "+conn.rs1.getString(2)+" "+conn.rs1.getString(3); // if(conn.rs1.getString(2).equals(conn.rs1.getString(3))){ // providername=conn.rs1.getString(1)+" "+conn.rs1.getString(3); // } // } //// ADD TO THE EXCELL OUTPUT.............................................................. // // HSSFRow rw4x=shet1.createRow(pos1); // rw4x.setHeightInPoints(25); // rw4x.setRowStyle(style2); // HSSFCell cell0x,cell1x,cell2x,cell3x,cell4x,cell5x,cell6x,cell7x,cell8x; // cell0x=rw4x.createCell(0); // cell1x=rw4x.createCell(1); // cell2x=rw4x.createCell(2); // cell3x=rw4x.createCell(3); // cell4x=rw4x.createCell(4); // cell5x=rw4x.createCell(5); // cell6x=rw4x.createCell(6); // cell7x=rw4x.createCell(7); // cell8x=rw4x.createCell(8); // // // OUTPUT SERVICES PROVIDED================================ // cell0x .setCellValue(countyname); // cell1x.setCellValue(partnername); // cell2x.setCellValue(districtname); // cell3x.setCellValue(groupname); // cell4x.setCellValue(providername); // cell5x.setCellValue(clientname); // cell6x.setCellValue(age); // cell7x.setCellValue(gender); // cell8x.setCellValue(timestamp); // // cell0x .setCellStyle(stborder); // cell1x.setCellStyle(stborder); // cell2x.setCellStyle(stborder); // cell3x.setCellStyle(stborder); // cell4x.setCellStyle(stborder); // cell5x.setCellStyle(stborder); // cell6x.setCellStyle(stborder); // cell7x.setCellStyle(stborder); // cell8x.setCellStyle(stborder); // // // pos1++; // } // CHECK SESSIONS ATTENDED============================================================== String getSessioner = "SELECT county.county_name,partner.partner_name,district.district_name,group_id,clients.age,provider_id, " + "clients.gender,register2.timestamp,register2.session_no,register2.value,clients.fname,clients.mname,clients.lname,register2.date " + "FROM clients JOIN partner ON clients.partner_id=partner.partner_id " + "JOIN (district JOIN county ON district.county_id=county.county_id )ON district.district_id=clients.district_id " + "JOIN register2 ON register2.client_id=clients.client_id " + "WHERE register2.value<'5' && register2.datekey<'" + datekey + "' && register2.datekey>'0' && STR_TO_DATE(register2.timestamp,'%Y-%m-%d') BETWEEN STR_TO_DATE('" + startdate + "','%Y-%m-%d') AND STR_TO_DATE('" + enddate + "','%Y-%m-%d')" + " ORDER BY clients.client_id "; conn.rs = conn.st.executeQuery(getSessioner); while (conn.rs.next()) { countyname = conn.rs.getString(1); partnername = conn.rs.getString(2); districtname = conn.rs.getString(3); age = conn.rs.getInt(5); gender = conn.rs.getString(7); timestamp = conn.rs.getString(8); sessionno = conn.rs.getInt(9); value = conn.rs.getInt(10); // String sessiondate=conn.rs.getString(14); String[] sessdt = conn.rs.getString(14).split("/"); String sessiondate = sessdt[2] + "-" + sessdt[0] + "-" + sessdt[1]; if (value == 1) { attendance_status = "Present"; } else { attendance_status = "Absent"; } clientname = conn.rs.getString(11) + " " + conn.rs.getString(12) + " " + conn.rs.getString(13); if (conn.rs.getString(12).equals(conn.rs.getString(13))) { clientname = conn.rs.getString(11) + " " + conn.rs.getString(13); } if (!conn.rs.getString(4).equals("0")) { String getGroupname = "SELECT group_name FROM groups WHERE group_id='" + conn.rs.getString(4) + "'"; conn.rs1 = conn.st1.executeQuery(getGroupname); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); } } else { groupname = "INDIVIDUALS"; } String getProvider = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + conn.rs.getString(6) + "'"; conn.rs1 = conn.st1.executeQuery(getProvider); if (conn.rs1.next() == true) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs1.getString(3); if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(3); } } String getMessage = "SELECT message FROM message_codes WHERE message_id='" + sessionno + "'"; conn.rs1 = conn.st1.executeQuery(getMessage); if (conn.rs1.next()) { messagename = conn.rs1.getString(1); } // ADD TO THE EXCELL OUTPUT.............................................................. HSSFRow rw2m1 = shet2.createRow(pos2); rw2m1.setHeightInPoints(25); rw2m1.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cellm01, cellm11, cellm21, cellm31, cellm41, cellm51, cellm61, cellm71, cellm81, cellm91, cellm101, cellm111; cellm01 = rw2m1.createCell(0); cellm11 = rw2m1.createCell(1); cellm21 = rw2m1.createCell(2); cellm31 = rw2m1.createCell(3); cellm41 = rw2m1.createCell(4); cellm51 = rw2m1.createCell(5); cellm61 = rw2m1.createCell(6); cellm71 = rw2m1.createCell(7); cellm81 = rw2m1.createCell(8); cellm91 = rw2m1.createCell(9); cellm101 = rw2m1.createCell(10); cellm111 = rw2m1.createCell(11); cellm01.setCellValue(countyname); cellm11.setCellValue(partnername); cellm21.setCellValue(districtname); cellm31.setCellValue(groupname); cellm41.setCellValue(providername); cellm51.setCellValue(clientname); cellm61.setCellValue(age); cellm71.setCellValue(gender); cellm81.setCellValue(messagename); cellm91.setCellValue(attendance_status); cellm101.setCellValue(sessiondate); cellm111.setCellValue(timestamp); cellm01.setCellStyle(stborder); cellm11.setCellStyle(stborder); cellm21.setCellStyle(stborder); cellm31.setCellStyle(stborder); cellm41.setCellStyle(stborder); cellm51.setCellStyle(stborder); cellm61.setCellStyle(stborder); cellm71.setCellStyle(stborder); cellm81.setCellStyle(stborder); cellm91.setCellStyle(stborder); cellm101.setCellStyle(stborder); cellm111.setCellStyle(stborder); pos2++; System.out.println("register record number : " + pos2); } System.out.println("finished processing register2"); // CHECK SESSIONS ATTENDED============================================================== String getServices = "SELECT county.county_name,partner.partner_name,district.district_name,group_id,clients.age,provider_id, " + "clients.gender,services_provided.timestamp,services_provided.session_no,services_provided.contraceptive_method," + "services_provided.rsp,services_provided.cds_given,services_provided.screened_tb, services_provided.screened_stis, services_provided.tested_partner, services_provided.tested_children, services_provided.disclosed_status" + ",clients.fname,clients.mname,clients.lname,services_provided.submission_date" + " FROM clients JOIN partner ON clients.partner_id=partner.partner_id " + "JOIN (district JOIN county ON district.county_id=county.county_id )ON district.district_id=clients.district_id " + "JOIN services_provided ON services_provided.client_id=clients.client_id " + "WHERE STR_TO_DATE(services_provided.submission_date,'%m/%d/%Y')< STR_TO_DATE(services_provided.submission_date,'" + submissiondate + "') && STR_TO_DATE(services_provided.timestamp,'%Y-%m-%d') BETWEEN STR_TO_DATE('" + startdate + "','%Y-%m-%d') AND STR_TO_DATE('" + enddate + "','%Y-%m-%d') " // + " && (services_provided.contraceptive_method='YES' || services_provided.rsp || services_provided.cds_given>0 || services_provided.screened_tb='YES' || services_provided.screened_stis='YES' || services_provided.tested_partner='YES' || services_provided.tested_children='YES' || services_provided.disclosed_status='YES')" + " ORDER BY clients.client_id "; conn.rs = conn.st.executeQuery(getServices); while (conn.rs.next()) { cm = rsp = screenedTB = ScreenedStis = screenedTb = testedpartner = testedChildren = discosedStatus = messagename = ""; givenCDS = 0; countyname = conn.rs.getString(1); partnername = conn.rs.getString(2); districtname = conn.rs.getString(3); age = conn.rs.getInt(5); gender = conn.rs.getString(7); timestamp = conn.rs.getString(8); sess = conn.rs.getString(9).split(","); cm = conn.rs.getString(10); rsp = conn.rs.getString(11); givenCDS = conn.rs.getInt(12); screenedTb = conn.rs.getString(13); ScreenedStis = conn.rs.getString(14); testedpartner = conn.rs.getString(15); testedChildren = conn.rs.getString(16); discosedStatus = conn.rs.getString(17); String submissiondate = conn.rs.getString(21); clientname = conn.rs.getString(18) + " " + conn.rs.getString(19) + " " + conn.rs.getString(20); if (conn.rs.getString(19).equals(conn.rs.getString(20))) { clientname = conn.rs.getString(18) + " " + conn.rs.getString(20); } for (String session1 : sess) { if (!(session1.equals("") || session1.equals(","))) { String getMess = "SELECT message FROM message_codes WHERE message_id='" + session1 + "'"; conn.rs1 = conn.st1.executeQuery(getMess); if (conn.rs1.next() == true) { messagename += conn.rs1.getString(1) + "\n"; } } } if (!conn.rs.getString(4).equals("0")) { String getGroupname = "SELECT group_name FROM groups WHERE group_id='" + conn.rs.getString(4) + "'"; conn.rs1 = conn.st1.executeQuery(getGroupname); if (conn.rs1.next() == true) { groupname = conn.rs1.getString(1); } } else { groupname = "INDIVIDUALS"; } String getProvider = "SELECT fname,mname,lname FROM service_provider WHERE provider_id='" + conn.rs.getString(6) + "'"; conn.rs1 = conn.st1.executeQuery(getProvider); if (conn.rs1.next() == true) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(2) + " " + conn.rs1.getString(3); if (conn.rs1.getString(2).equals(conn.rs1.getString(3))) { providername = conn.rs1.getString(1) + " " + conn.rs1.getString(3); } } // ADD TO THE EXCELL OUTPUT.............................................................. HSSFRow rw4n1 = shet3.createRow(pos3); rw4n1.setHeightInPoints(25); rw4n1.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0n1, cell1n1, cell2n1, cell3n1, cell4n1, cell5n1, cell6n1, cell7n1, cell8n1, cell9n1, cell10n1, cell11n1, cell12n1, cell13n1, cell14n1, cell15n1, cell16n1, cell17n1, cell18n1; cell0n1 = rw4n1.createCell(0); cell1n1 = rw4n1.createCell(1); cell2n1 = rw4n1.createCell(2); cell3n1 = rw4n1.createCell(3); cell4n1 = rw4n1.createCell(4); cell5n1 = rw4n1.createCell(5); cell6n1 = rw4n1.createCell(6); cell7n1 = rw4n1.createCell(7); cell8n1 = rw4n1.createCell(8); cell9n1 = rw4n1.createCell(9); cell10n1 = rw4n1.createCell(10); cell11n1 = rw4n1.createCell(11); cell12n1 = rw4n1.createCell(12); cell13n1 = rw4n1.createCell(13); cell14n1 = rw4n1.createCell(14); cell15n1 = rw4n1.createCell(15); cell16n1 = rw4n1.createCell(16); cell17n1 = rw4n1.createCell(17); cell18n1 = rw4n1.createCell(18); cell0n1.setCellValue(countyname); cell1n1.setCellValue(partnername); cell2n1.setCellValue(districtname); cell3n1.setCellValue(groupname); cell4n1.setCellValue(providername); cell5n1.setCellValue(clientname); cell6n1.setCellValue(age); cell7n1.setCellValue(gender); cell8n1.setCellValue(messagename); cell9n1.setCellValue(cm); cell10n1.setCellValue(rsp); cell11n1.setCellValue(givenCDS); cell12n1.setCellValue(screenedTb); cell13n1.setCellValue(ScreenedStis); cell14n1.setCellValue(testedpartner); cell15n1.setCellValue(testedChildren); cell16n1.setCellValue(discosedStatus); cell17n1.setCellValue(submissiondate); cell18n1.setCellValue(timestamp); cell0n1.setCellStyle(stborder); cell1n1.setCellStyle(stborder); cell2n1.setCellStyle(stborder); cell3n1.setCellStyle(stborder); cell4n1.setCellStyle(stborder); cell5n1.setCellStyle(stborder); cell6n1.setCellStyle(stborder); cell7n1.setCellStyle(stborder); cell8n1.setCellStyle(stborder); cell9n1.setCellStyle(stborder); cell10n1.setCellStyle(stborder); cell11n1.setCellStyle(stborder); cell12n1.setCellStyle(stborder); cell13n1.setCellStyle(stborder); cell14n1.setCellStyle(stborder); cell15n1.setCellStyle(stborder); cell16n1.setCellStyle(stborder); cell17n1.setCellStyle(stborder); cell18n1.setCellStyle(stborder); pos3++; System.out.println("services record number : " + pos3); } System.out.println("finished all"); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_Raw_Data_Between_" + sdate.trim() + "_AND_" + ndate.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:Datim.datimHTCReport.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//from w w w . j a v a2 s .c o m dbConn conn = new dbConn(); // MALES double MaleAdultTested; double MaleTestedChild; double AdultMaleHIV; double ChildMaleHIV; double FemaleAdultTested1 = 0.0; double FemaleAdultTested4 = 0.0; double FemaleAdultTested9 = 0.0; double FemaleAdultTested14 = 0.0; double FemaleAdultTested19 = 0.0; double FemaleAdultTested24 = 0.0; double FemaleAdultTested49 = 0.0; double FemaleAdultTested50 = 0.0; double FemaleTestedChild1 = 0.0; double FemaleTestedChild4 = 0.0; double FemaleTestedChild9 = 0.0; double FemaleTestedChild14 = 0.0; double FemaleTestedChild19 = 0.0; double FemaleTestedChild24 = 0.0; double FemaleTestedChild49 = 0.0; double FemaleTestedChild50 = 0.0; double AdultFemaleHIV19Neg = 0.0; double AdultFemaleHIV24Neg = 0.0; double AdultFemaleHIV49Neg = 0.0; double AdultFemaleHIV50Neg = 0.0; double AdultFemaleHIV19 = 0.0; double AdultFemaleHIV24 = 0.0; double AdultFemaleHIV49 = 0.0; double AdultFemaleHIV50 = 0.0; double ChildFemaleHIV1 = 0.0; double ChildFemaleHIV4 = 0.0; double ChildFemaleHIV9 = 0.0; double ChildFemaleHIV14 = 0.0; double ChildFemaleHIV1Neg = 0.0; double ChildFemaleHIV4Neg = 0.0; double ChildFemaleHIV9Neg = 0.0; double ChildFemaleHIV14Neg = 0.0; // MALES double MaleAdultTested19Neg = 0.0; double MaleAdultTested21Neg = 0.0; double MaleAdultTested49Neg = 0.0; double MaleAdultTested50Neg = 0.0; double MaleAdultTested19 = 0.0; double MaleAdultTested24 = 0.0; double MaleAdultTested49 = 0.0; double MaleAdultTested50 = 0.0; double MaleTestedChild1 = 0.0; double MaleTestedChild4 = 0.0; double MaleTestedChild9 = 0.0; double MaleTestedChild14 = 0.0; double MaleTestedChild1Neg = 0.0; double MaleTestedChild4Neg = 0.0; double MaleTestedChild9Neg = 0.0; double MaleTestedChild14Neg = 0.0; double AdultMaleHIV19Neg = 0.0; double AdultMaleHIV24Neg = 0.0; double AdultMaleHIV49Neg = 0.0; double AdultMaleHIV50Neg = 0.0; double AdultMaleHIV19 = 0.0; double AdultMaleHIV24 = 0.0; double AdultMaleHIV49 = 0.0; double AdultMaleHIV50 = 0.0; double ChildMaleHIV1 = 0.0; double ChildMaleHIV4 = 0.0; double ChildMaleHIV9 = 0.0; double ChildMaleHIV14 = 0.0; double ChildMaleHIV1Neg = 0.0; double ChildMaleHIV4Neg = 0.0; double ChildMaleHIV9Neg = 0.0; double ChildMaleHIV14Neg = 0.0; year = Integer.parseInt(request.getParameter("year")); reportDuration = request.getParameter("reportDuration"); // year=2015; // reportDuration="4"; period = ""; prevYear = year - 1; maxYearMonth = 0; FemaleAdultTested = 0.0; FemaleTestedChild = 0.0; AdultFemaleHIV = 0.0; ChildFemaleHIV = 0.0; double TotalTested = 0.0; double TotalPositiveFemale = 0.0; double TotalPositiveMale = 0.0; double TotalNegativeFemale = 0.0; double TotalNegativeMale = 0.0; // MALES MaleAdultTested = 0.0; MaleTestedChild = 0.0; AdultMaleHIV = 0.0; ChildMaleHIV = 0.0; double TotalPositive = 0.0; double TotalNegative = 0.0; String county = ""; String district = ""; String facilityname = ""; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { duration = " moh711.yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; period = "DATIM ANNUAL DATA REPORT FOR PEPFAR YEAR : " + year; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { duration = " moh711.yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; period = "DATIM SEMI - ANNUAL DATA REPORT FOR PERIOD : OCT " + prevYear + " to MARCH " + year; } else { duration = " moh711.yearmonth BETWEEN " + year + "04 AND " + year + "09"; period = "DATIM SEMI - ANNUAL DATA REPORT FOR PERIOD : APRIL " + year + " to SEPT " + year; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " moh711.yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; period = "DATIM QUARTERLY DATA REPORT FOR PERIOD : " + conn.rs.getString(2).replace("-", " " + prevYear + " TO ") + " " + prevYear + ""; } else { duration = " moh711.yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; period = "DATIM QUARTERLY DATA REPORT FOR PERIOD : " + conn.rs.getString(2).replace("-", " " + year + " TO ") + " " + year + ""; } } } else if (reportDuration.equals("4")) { month = Integer.parseInt(request.getParameter("month")); // month=5; String getMonthName = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonthName); if (conn.rs.next() == true) { if (month >= 10) { duration = " moh711.yearmonth=" + prevYear + "" + month; period = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + prevYear + ")"; } else { duration = " moh711.yearmonth=" + year + "0" + month; period = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + year + ")"; } } } else { duration = ""; } System.out.println("period is : " + period); // GET FACILITIES TO OUTPUT................................. mflcode = countyName = districtName = facilityName = ""; // facilityId=request.getParameter("facility"); facilityId = "403"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet3 = wb.createSheet("DATIM DATA FROM MOH 711 "); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_LEFT); stborder.setWrapText(true); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); HSSFCellStyle stylemainHeader = wb.createCellStyle(); stylemainHeader.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); stylemainHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylemainHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); stylemainHeader.setAlignment(HSSFCellStyle.ALIGN_LEFT); stylemainHeader.setWrapText(true); HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleHeader.setWrapText(true); HSSFCellStyle styleminiHeader = wb.createCellStyle(); styleminiHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); styleminiHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleminiHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleminiHeader.setAlignment(HSSFCellStyle.ALIGN_LEFT); styleminiHeader.setWrapText(true); HSSFFont fontHeader = wb.createFont(); fontHeader.setColor(HSSFColor.DARK_BLUE.index); styleHeader.setFont(fontHeader); styleHeader.setWrapText(true); shet3.setColumnWidth(0, 4000); for (int i = 1; i <= 17; i++) { shet3.setColumnWidth(i, 2000); } HSSFRow rw0 = shet3.createRow(0); rw0.setHeightInPoints(25); HSSFCell c1, c2, c3, c4, c5, c6, c7, c8; c1 = rw0.createCell(0); c1.setCellValue(period); c1.setCellStyle(stylemainHeader); for (int j = 1; j <= 17; j++) { c1 = rw0.createCell(j); c1.setCellStyle(stylemainHeader); } shet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 17)); Calendar ca = Calendar.getInstance(); int currentyear = ca.get(Calendar.YEAR); String facilitiestable = "subpartnera"; int selectedyear = year; if (selectedyear < currentyear) { if (year < 2014) { //db for 2014 is the smallest facilitiestable = "subpartnera2014"; } else { facilitiestable = "subpartnera" + selectedyear; } } String getName = "SELECT " + facilitiestable + ".SubPartnerNom,district.DistrictNom,county.County," + facilitiestable + ".CentreSanteId FROM " + facilitiestable + " " + "JOIN district ON " + facilitiestable + ".DistrictID=district.DistrictID JOIN county ON " + "district.CountyID=county.CountyID WHERE " + facilitiestable + ".SubPartnerID='" + facilityId + "'"; conn.rs = conn.st.executeQuery(getName); if (conn.rs.next() == true) { facilityName = conn.rs.getString(1); districtName = conn.rs.getString(2); countyName = conn.rs.getString(3); mflcode = conn.rs.getString(4); } String getMaxYearMonth = "SELECT MAX(yearmonth) FROM moh711 WHERE moh711.SubPartnerID='" + facilityId + "' && " + duration; conn.rs = conn.st.executeQuery(getMaxYearMonth); if (conn.rs.next() == true) { maxYearMonth = conn.rs.getInt(1); } // for header--------------------------------------------------------------------------- HSSFCell c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c110, c111, c112, c113, c114, c115, c116, c117, c118, c119, c120, c121, c122, c123, c124, c125, c126, c127, c128, c129, c130, c131, c132, c133, c134, c135, c136; HSSFCell c211, c212, c213, c214, c215, c216, c217; rw0 = shet3.createRow(2); rw0.setHeightInPoints(20); rw0.setHeightInPoints(20); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); c211.setCellValue("COUNTY"); c212.setCellValue("SUB-COUNTY"); c213.setCellValue("FACILITY"); c214.setCellValue("MFL-CODE"); c215.setCellValue("TYPE OF SUPPORT"); c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c119 = rw0.createCell(25); c120 = rw0.createCell(26); c121 = rw0.createCell(27); c122 = rw0.createCell(28); c123 = rw0.createCell(29); c124 = rw0.createCell(30); c125 = rw0.createCell(31); c126 = rw0.createCell(32); c127 = rw0.createCell(33); c128 = rw0.createCell(34); c129 = rw0.createCell(35); c130 = rw0.createCell(36); c131 = rw0.createCell(37); c132 = rw0.createCell(38); c133 = rw0.createCell(39); c134 = rw0.createCell(40); c135 = rw0.createCell(41); c136 = rw0.createCell(42); c216.setCellValue("Total HIV+"); c217.setCellValue("Total HIV+ (F)"); c11.setCellValue("POSITIVE"); c12.setCellValue("FEMALE"); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue(""); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue(""); c111.setCellValue("MALE"); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue(""); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); // // rw0=shet3.createRow(2); // rw0.setHeightInPoints(20); c119.setCellValue("NEGATIVE"); c120.setCellValue("FEMALE"); c121.setCellValue(""); c122.setCellValue(""); c123.setCellValue(""); c124.setCellValue(""); c125.setCellValue(""); c126.setCellValue(""); c127.setCellValue(""); c128.setCellValue(""); c129.setCellValue("MALE"); c130.setCellValue(""); c131.setCellValue(""); c132.setCellValue(""); c133.setCellValue(""); c134.setCellValue(""); c135.setCellValue(""); c136.setCellValue(""); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stylemainHeader); } for (int i = 23; i <= 42; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stylemainHeader); } shet3.addMergedRegion(new CellRangeAddress(2, 2, 8, 15)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 17, 24)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 26, 33)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 35, 42)); rw0 = shet3.createRow(3); rw0.setHeightInPoints(20); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // other data c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c11.setCellValue("Num"); c216.setCellValue("TOTAL HIV+"); c217.setCellValue("TOTAL +VE (F)"); c12.setCellValue("Paeds <15Yr"); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue("Adults 15+Yr"); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue("TOTAL +VE MALE"); c111.setCellValue("Paeds <15Yr"); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue("Adults 15+Yr"); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c11.setCellValue("TOTAL -VE(F)"); c12.setCellValue("Paeds <15Yr"); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue("Adults 15+Yr"); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue("TOTAL -VE(M)"); c111.setCellValue("Paeds <15Yr"); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue("Adults 15+Yr"); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(styleHeader); } shet3.addMergedRegion(new CellRangeAddress(3, 3, 8, 11)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 12, 15)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 17, 20)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 21, 24)); // for (int k = 23; k <= 40; k++) { c113 = rw0.getCell(k); c113.setCellStyle(styleHeader); } shet3.addMergedRegion(new CellRangeAddress(3, 3, 26, 29)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 30, 33)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 35, 38)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 39, 42)); rw0 = shet3.createRow(4); rw0.setHeightInPoints(20); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // for ther est c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c11.setCellValue("NUM"); c216.setCellValue("TOTAL HIV+"); c217.setCellValue("TOTAL +VE(F)"); c12.setCellValue("<1 "); c13.setCellValue("1-4Y"); c14.setCellValue("5-9Y"); c15.setCellValue("10-14Y"); c16.setCellValue("15-19Y"); c17.setCellValue("20-24Y"); c18.setCellValue("25-49Y"); c19.setCellValue("50+Y"); c110.setCellValue("TOTAL +VE MALE"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 16, 16)); c111.setCellValue("<1"); c112.setCellValue("1-4Y"); c113.setCellValue("5-9Y"); c114.setCellValue("10-14Y"); c115.setCellValue("15-19Y"); c116.setCellValue("20-24Y"); c117.setCellValue("25-49Y"); c118.setCellValue("50+Y"); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(styleHeader); } // // for negative c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c11.setCellValue("TOTAL -VE(F)"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 25, 25)); c12.setCellValue("<1"); c13.setCellValue("1-4Y"); c14.setCellValue("5-9Y"); c15.setCellValue("10-14Y"); c16.setCellValue("15-19Y"); c17.setCellValue("20-24Y"); c18.setCellValue("25-49Y"); c19.setCellValue("50+Y"); c110.setCellValue("TOTAL -VE(M)"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 34, 34)); c111.setCellValue("<1"); c112.setCellValue("1-4Y"); c113.setCellValue("5-9Y"); c114.setCellValue("10-14Y"); c115.setCellValue("15-19Y"); c116.setCellValue("20-24Y"); c117.setCellValue("25-49Y"); c118.setCellValue("50+Y"); for (int l = 20; l <= 42; l++) { c113 = rw0.getCell(l); c113.setCellStyle(styleHeader); } int count = 4; TestedAdultMale = TestedAdultFemale = 0; TestedChildMale = TestedChildFemale = 0; HIV_AdultMale = HIV_AdultFemale = 0; HIV_ChildMale = HIV_ChildFemale = 0; //--------------------------------------------------------------------------- String facilid = ""; String facilname = ""; String dsdta = ""; String getfacils = "select SubPartnerId,SubPartnerNom from " + facilitiestable + " where HTC='1' order by SubPartnerNom "; conn.rs2 = conn.st2.executeQuery(getfacils); while (conn.rs2.next()) { facilname = conn.rs2.getString(2); facilid = conn.rs2.getString(1); String getData = "SELECT (sum(VCTClient_Tested_CF) +sum( VCTClient_Tested_AF)+sum(DTCB_Test_Out_AF)+sum(DTCB_Test_In_AF))" //ADULTS TESTED FEMALE + ",(sum(VCTClient_Tested_CM)+ sum(VCTClient_Tested_AM) + sum(DTCB_Test_Out_AM) + sum(DTCB_Test_In_AM))"//ADULTS TESTED MALES + ", (sum(VCTClient_HIV_CF)+ sum(VCTClient_HIV_AF)+sum(DTCC_HIV_In_AF)+ sum(DTCC_HIV_Out_AF))" // ADULTS HIV+ FEMALE + ",(sum(VCTClient_HIV_CM)+sum(VCTClient_HIV_AM)+ sum(DTCC_HIV_In_AM) +sum(DTCC_HIV_Out_AM)) " // ADULTS HIV+ MALE + ", (sum(DTCB_Test_Out_CF) +sum(DTCB_Test_In_CF))" // CHILDREN TOTAL TESTED FEMALE + ", (sum(DTCB_Test_Out_CM) +sum(DTCB_Test_In_CM))" // CHILDREN TOTAL TESTED MALE + ", ( sum(DTCC_HIV_In_CF)+ sum(DTCC_HIV_Out_CF))" // CHILDREN OSITIVE FEMALE + ", (sum(DTCC_HIV_In_CM)+ sum(DTCC_HIV_Out_CM)),county.County,district.DistrictNom," + facilitiestable + ".SubPartnerNom," + facilitiestable + ".CentreSanteId," + facilitiestable + ".HTC_Support1"// CHILDREN POSITIVE MALE + " from moh711 JOIN " + facilitiestable + " ON moh711.SubPartnerID=" + facilitiestable + ".SubPartnerID " + "JOIN district ON " + facilitiestable + ".DistrictID=district.DistrictID " + "JOIN county ON county.CountyID=district.CountyID " + " WHERE " + duration + " and moh711.SubPartnerID='" + facilid + "' "; System.out.println("new : " + getData); conn.rs = conn.st.executeQuery(getData); if (conn.rs.next() == true) { county = conn.rs.getString(9); district = conn.rs.getString(10); facilityname = conn.rs.getString(11); mflcode = conn.rs.getString(12); dsdta = conn.rs.getString(13); TestedAdultFemale = conn.rs.getInt(1); TestedAdultMale = conn.rs.getInt(2); HIV_AdultFemale = conn.rs.getInt(3); HIV_AdultMale = conn.rs.getInt(4); TestedChildFemale = conn.rs.getInt(5); TestedChildMale = conn.rs.getInt(6); HIV_ChildFemale = conn.rs.getInt(7); HIV_ChildMale = conn.rs.getInt(8); String basicDetails = county + "@" + district + "@" + facilityname + "@" + mflcode + "@" + dsdta; String arrayDetails[] = basicDetails.split("@"); count++; rw0 = shet3.createRow(count); int facilno = 0; for (int j = 0; j < arrayDetails.length; j++) { HSSFCell S3cell = rw0.createCell(facilno); S3cell.setCellValue(arrayDetails[j]); // System.out.println(arrayDetails[j]); S3cell.setCellStyle(stborder); System.out.println("facildet pos : " + facilno + " det : " + arrayDetails[j]); facilno++; } System.out.println(facilityname + " TestedAdultFemale " + TestedAdultFemale + "TestedAdultMale " + TestedAdultMale + " TestedChildFemale " + TestedChildFemale + " TestedChildMale " + TestedChildMale + " HIV_AdultFemale " + HIV_AdultFemale + " HIV_AdultMale " + HIV_AdultMale + " HIV_ChildFemale " + HIV_ChildFemale + " HIV_ChildMale " + HIV_ChildMale); // FEMALES FemaleAdultTested19 = (float) Math.round((0.05 * TestedAdultFemale)); FemaleAdultTested24 = (float) Math.round((0.11 * TestedAdultFemale)); FemaleAdultTested49 = (float) Math.round((0.72 * TestedAdultFemale)); FemaleAdultTested50 = (float) Math.round((0.12 * TestedAdultFemale)); FemaleTestedChild1 = (float) Math.round((0.05 * TestedChildFemale)); FemaleTestedChild4 = (float) Math.round((0.26 * TestedChildFemale)); FemaleTestedChild9 = (float) Math.round((0.29 * TestedChildFemale)); FemaleTestedChild14 = (float) Math.round((0.40 * TestedChildFemale)); //postive AdultFemaleHIV19 = (float) Math.round((0.02 * HIV_AdultFemale)); AdultFemaleHIV24 = (float) Math.round((0.09 * HIV_AdultFemale)); AdultFemaleHIV49 = (float) Math.round((0.79 * HIV_AdultFemale)); AdultFemaleHIV50 = (float) Math.round((0.10 * HIV_AdultFemale)); //positve ChildFemaleHIV1 = (float) Math.round((0.13 * HIV_ChildFemale)); ChildFemaleHIV4 = (float) Math.round((0.37 * HIV_ChildFemale)); ChildFemaleHIV9 = (float) Math.round((0.25 * HIV_ChildFemale)); ChildFemaleHIV14 = (float) Math.round((0.25 * HIV_ChildFemale)); // MALES MaleAdultTested19 = (float) Math.round((0.05 * TestedAdultMale)); MaleAdultTested24 = (float) Math.round((0.11 * TestedAdultMale)); MaleAdultTested49 = (float) Math.round((0.72 * TestedAdultMale)); MaleAdultTested50 = (float) Math.round((0.12 * TestedAdultMale)); MaleTestedChild1 = (float) Math.round((0.05 * TestedChildMale)); MaleTestedChild4 = (float) Math.round((0.26 * TestedChildMale)); MaleTestedChild9 = (float) Math.round((0.29 * TestedChildMale)); MaleTestedChild14 = (float) Math.round((0.40 * TestedChildMale)); //positive AdultMaleHIV19 = (float) Math.round((0.02 * HIV_AdultMale)); AdultMaleHIV24 = (float) Math.round((0.09 * HIV_AdultMale)); AdultMaleHIV49 = (float) Math.round((0.79 * HIV_AdultMale)); AdultMaleHIV50 = (float) Math.round((0.10 * HIV_AdultMale)); //positives ChildMaleHIV1 = (float) Math.round((0.13 * HIV_ChildMale)); ChildMaleHIV4 = (float) Math.round((0.37 * HIV_ChildMale)); ChildMaleHIV9 = (float) Math.round((0.25 * HIV_ChildMale)); ChildMaleHIV14 = (float) Math.round((0.25 * HIV_ChildMale)); double splitData; int adderPos = 0; double childSplitData; // TestedAdultFemale=conn.rs.getInt(1); // TestedAdultMale=conn.rs.getInt(2); // HIV_AdultFemale=conn.rs.getInt(3); // HIV_AdultMale=conn.rs.getInt(4); // TestedChildFemale=conn.rs.getInt(5); // TestedChildMale=conn.rs.getInt(6); // HIV_ChildFemale=conn.rs.getInt(7); // HIV_ChildMale=conn.rs.getInt(8); // TotalTested=FemaleTestedChild1+FemaleTestedChild4+FemaleTestedChild9+FemaleTestedChild14+FemaleAdultTested19+FemaleAdultTested24+FemaleAdultTested49+FemaleAdultTested50+ MaleAdultTested19+MaleAdultTested24+MaleAdultTested49+MaleAdultTested50+MaleTestedChild1+MaleTestedChild4+MaleTestedChild9+MaleTestedChild14; // TotalPositiveFemale=AdultFemaleHIV19+AdultFemaleHIV24+AdultFemaleHIV49+AdultFemaleHIV50+ChildFemaleHIV1+ChildFemaleHIV4+ChildFemaleHIV9+ChildFemaleHIV14 ; // TotalPositiveMale1=AdultMaleHIV19+AdultMaleHIV24+AdultMaleHIV49+AdultMaleHIV50+ChildMaleHIV1+ChildMaleHIV4+ChildMaleHIV9+ChildMaleHIV14; // TotalNegativeFemale1=AdultFemaleHIV19Neg+AdultFemaleHIV24Neg+AdultFemaleHIV49Neg+AdultFemaleHIV50Neg+ ChildFemaleHIV1Neg+ChildFemaleHIV4Neg+ChildFemaleHIV9Neg+ChildFemaleHIV14Neg; // TotalNegativeMale1=AdultMaleHIV19Neg+AdultMaleHIV24Neg+AdultMaleHIV49Neg+AdultMaleHIV50Neg+ChildMaleHIV1Neg+ChildMaleHIV4Neg+ChildMaleHIV9Neg+ChildMaleHIV14Neg; adderPos = 0; double Totalhivfemale = 0.0; double Totalhivmale = 0.0; Totalhivfemale = HIV_AdultFemale + HIV_ChildFemale; Totalhivmale = HIV_AdultMale + HIV_ChildMale; // adult female hiv+ splitData = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50; adderPos = 0; while (splitData < HIV_AdultFemale) { AdultFemaleHIV49 += 1; splitData++; } splitData = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50; while (splitData > HIV_AdultFemale) { AdultFemaleHIV49 -= 1; splitData--; } //tested female adults splitData = FemaleAdultTested19 + FemaleAdultTested24 + FemaleAdultTested49 + FemaleAdultTested50; adderPos = 0; while (splitData < TestedAdultFemale) { FemaleAdultTested49 += 1; splitData++; } splitData = FemaleAdultTested19 + FemaleAdultTested24 + FemaleAdultTested49 + FemaleAdultTested50; adderPos = 0; while (splitData > TestedAdultFemale) { FemaleAdultTested49 -= 1; splitData--; } // adult male hiv+ splitData = AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50; adderPos = 0; while (splitData < HIV_AdultMale) { AdultMaleHIV49 += 1; splitData++; } splitData = AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50; adderPos = 0; while (splitData > HIV_AdultMale) { AdultMaleHIV49 -= 1; splitData--; } //tested male adults splitData = MaleAdultTested19 + MaleAdultTested24 + MaleAdultTested49 + MaleAdultTested50; adderPos = 0; while (splitData < TestedAdultMale) { MaleAdultTested49 += 1; splitData++; } splitData = MaleAdultTested19 + MaleAdultTested24 + MaleAdultTested49 + MaleAdultTested50; adderPos = 0; while (splitData > TestedAdultMale) { MaleAdultTested49 -= 1; splitData--; } // for child female childSplitData = ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14; adderPos = 0; while (childSplitData < HIV_ChildFemale) { if (adderPos < 2) { ChildFemaleHIV4 += 1; } else { ChildFemaleHIV9 += 1; } childSplitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == HIV_ChildFemale) { } } childSplitData = ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14; adderPos = 0; while (childSplitData > HIV_ChildFemale) { if (adderPos < 2) { AdultFemaleHIV49 -= 1; } else { AdultFemaleHIV24 -= 1; } childSplitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == HIV_ChildFemale) { } } // for child female tested childSplitData = FemaleTestedChild1 + FemaleTestedChild4 + FemaleTestedChild9 + FemaleTestedChild14; adderPos = 0; while (childSplitData < TestedChildFemale) { if (adderPos < 2) { FemaleTestedChild14 += 1; } else { FemaleTestedChild9 += 1; } childSplitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == TestedChildFemale) { } } childSplitData = FemaleTestedChild1 + FemaleTestedChild4 + FemaleTestedChild9 + FemaleTestedChild14; adderPos = 0; while (childSplitData > TestedChildFemale) { if (adderPos < 2) { FemaleTestedChild14 -= 1; } else { FemaleTestedChild9 -= 1; } childSplitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == TestedChildFemale) { } } // for child male hiv childSplitData = ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; adderPos = 0; while (childSplitData < HIV_ChildMale) { if (adderPos < 2) { ChildMaleHIV4 += 1; } else { ChildMaleHIV9 += 1; } childSplitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == HIV_ChildMale) { } } childSplitData = ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; adderPos = 0; while (childSplitData > HIV_ChildMale) { if (adderPos < 2) { AdultMaleHIV49 -= 1; } else { AdultMaleHIV24 -= 1; } childSplitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == HIV_ChildMale) { } } /// // for child female tested childSplitData = MaleTestedChild1 + MaleTestedChild4 + MaleTestedChild9 + MaleTestedChild14; adderPos = 0; while (childSplitData < TestedChildMale) { if (adderPos < 2) { MaleTestedChild14 += 1; } else { MaleTestedChild9 += 1; } childSplitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == TestedChildMale) { } } childSplitData = MaleTestedChild1 + MaleTestedChild4 + MaleTestedChild9 + MaleTestedChild14; adderPos = 0; while (childSplitData > TestedChildMale) { if (adderPos < 2) { MaleTestedChild14 -= 1; } else { MaleTestedChild9 -= 1; } childSplitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } if (childSplitData == TestedChildMale) { } } System.out.println("Neg nn " + ChildMaleHIV1Neg + " " + ChildMaleHIV4Neg + " " + ChildMaleHIV9Neg + " " + ChildMaleHIV14Neg); System.out.println("tested nn " + MaleTestedChild1 + " " + MaleTestedChild4 + " " + MaleTestedChild9 + " " + MaleTestedChild14); System.out.println("hiv+ nnn " + ChildMaleHIV1 + " " + ChildMaleHIV4 + " " + ChildMaleHIV9 + " " + ChildMaleHIV14); // all positives TotalPositive = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50 + AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50 + ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14 + ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; TotalNegative = AdultFemaleHIV19Neg + AdultFemaleHIV24Neg + AdultFemaleHIV49Neg + AdultFemaleHIV50Neg + AdultMaleHIV19Neg + AdultMaleHIV24Neg + AdultMaleHIV49Neg + AdultMaleHIV50Neg + ChildFemaleHIV1Neg + ChildFemaleHIV4Neg + ChildFemaleHIV9Neg + ChildFemaleHIV14Neg + ChildMaleHIV1Neg + ChildMaleHIV4Neg + ChildMaleHIV9Neg + ChildMaleHIV14Neg; TotalTested = FemaleTestedChild1 + FemaleTestedChild4 + FemaleTestedChild9 + FemaleTestedChild14 + FemaleAdultTested19 + FemaleAdultTested24 + FemaleAdultTested49 + FemaleAdultTested50 + MaleAdultTested19 + MaleAdultTested24 + MaleAdultTested49 + MaleAdultTested50 + MaleTestedChild1 + MaleTestedChild4 + MaleTestedChild9 + MaleTestedChild14; TotalPositiveFemale = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50 + ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14; TotalPositiveMale = AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50 + ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; //System.out.println(MaleTestedChild14 +" bbbbb "+ChildMaleHIV14+" mmmmm "+ (MaleTestedChild14-ChildMaleHIV14)); rw0.setHeightInPoints(20); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // the rest c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c20 = rw0.createCell(16); c110 = rw0.createCell(17); c111 = rw0.createCell(18); c112 = rw0.createCell(19); c113 = rw0.createCell(20); c114 = rw0.createCell(21); c115 = rw0.createCell(22); c116 = rw0.createCell(23); c117 = rw0.createCell(24); //c11.setCellValue(facilname); //String basicDetails=county+"@"+district+"@"+facilityname+"@"+mflcode+"@"+dsdta; c211.setCellValue(county); c212.setCellValue(district); c213.setCellValue(facilityname); c214.setCellValue(mflcode); c215.setCellValue(dsdta); shet3.addMergedRegion(new CellRangeAddress(2, 4, 0, 0)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 1, 1)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 2, 2)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 3, 3)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 4, 4)); // Female c11.setCellValue(TotalTested); c216.setCellValue(TotalPositive); c217.setCellValue(TotalPositiveFemale); shet3.addMergedRegion(new CellRangeAddress(2, 4, 5, 5)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 6, 6)); shet3.addMergedRegion(new CellRangeAddress(3, 4, 7, 7)); c12.setCellValue((float) Math.round(ChildFemaleHIV1)); c13.setCellValue((float) Math.round(ChildFemaleHIV4)); c14.setCellValue((float) Math.round(ChildFemaleHIV9)); c15.setCellValue((float) Math.round(ChildFemaleHIV14)); c16.setCellValue((float) Math.round(AdultFemaleHIV19)); c17.setCellValue((float) Math.round(AdultFemaleHIV24)); c18.setCellValue((float) Math.round(AdultFemaleHIV49)); c19.setCellValue((float) Math.round(AdultFemaleHIV50)); c20.setCellValue(TotalPositiveMale); //male c110.setCellValue((float) Math.round(ChildMaleHIV1)); c111.setCellValue((float) Math.round(ChildMaleHIV4)); c112.setCellValue((float) Math.round(ChildMaleHIV9)); c113.setCellValue((float) Math.round(ChildMaleHIV14)); c114.setCellValue((float) Math.round(AdultMaleHIV19)); c115.setCellValue((float) Math.round(AdultMaleHIV24)); c116.setCellValue((float) Math.round(AdultMaleHIV49)); c117.setCellValue((float) Math.round(AdultMaleHIV50)); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stborder); } // shet3.addMergedRegion(new CellRangeAddress(2,5,0,0)); c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); //negative AdultMaleHIV19Neg = (float) Math.round(MaleAdultTested19) - (float) Math.round(AdultMaleHIV19); AdultMaleHIV24Neg = (float) Math.round(MaleAdultTested24) - (float) Math.round(AdultMaleHIV24); AdultMaleHIV49Neg = (float) Math.round(MaleAdultTested49) - (float) Math.round(AdultMaleHIV49); AdultMaleHIV50Neg = (float) Math.round(MaleAdultTested50) - (float) Math.round(AdultMaleHIV50); // child male negatives ChildMaleHIV1Neg = (float) Math.round(MaleTestedChild1) - (float) Math.round(ChildMaleHIV1); ChildMaleHIV4Neg = (float) Math.round(MaleTestedChild4) - (float) Math.round(ChildMaleHIV4); ChildMaleHIV9Neg = (float) Math.round(MaleTestedChild9) - (float) Math.round(ChildMaleHIV9); ChildMaleHIV14Neg = (float) Math.round(MaleTestedChild14) - (float) Math.round(ChildMaleHIV14); //negative ChildFemaleHIV1Neg = (float) Math.round(FemaleTestedChild1) - (float) Math.round(ChildFemaleHIV1); ChildFemaleHIV4Neg = (float) Math.round(FemaleTestedChild4) - (float) Math.round(ChildFemaleHIV4); ChildFemaleHIV9Neg = (float) Math.round(FemaleTestedChild9) - (float) Math.round(ChildFemaleHIV9); ChildFemaleHIV14Neg = (float) Math.round(FemaleTestedChild14) - (float) Math.round(ChildFemaleHIV14); //negative AdultFemaleHIV19Neg = (float) Math.round(FemaleAdultTested19) - (float) Math.round(AdultFemaleHIV19); AdultFemaleHIV24Neg = (float) Math.round(FemaleAdultTested24) - (float) Math.round(AdultFemaleHIV24); AdultFemaleHIV49Neg = (float) Math.round(FemaleAdultTested49) - (float) Math.round(AdultFemaleHIV49); AdultFemaleHIV50Neg = (float) Math.round(FemaleAdultTested50) - (float) Math.round(AdultFemaleHIV50); TotalNegativeFemale = AdultFemaleHIV19Neg + AdultFemaleHIV24Neg + AdultFemaleHIV49Neg + AdultFemaleHIV50Neg + ChildFemaleHIV1Neg + ChildFemaleHIV4Neg + ChildFemaleHIV9Neg + ChildFemaleHIV14Neg; TotalNegativeMale = AdultMaleHIV19Neg + AdultMaleHIV24Neg + AdultMaleHIV49Neg + AdultMaleHIV50Neg + ChildMaleHIV1Neg + ChildMaleHIV4Neg + ChildMaleHIV9Neg + ChildMaleHIV14Neg; c11.setCellValue((float) Math.round(TotalNegativeFemale)); c12.setCellValue((float) Math.round(ChildFemaleHIV1Neg)); c13.setCellValue((float) Math.round(ChildFemaleHIV4Neg)); c14.setCellValue((float) Math.round(ChildFemaleHIV9Neg)); c15.setCellValue((float) Math.round(ChildFemaleHIV14Neg)); c16.setCellValue((float) Math.round(AdultFemaleHIV19Neg)); c17.setCellValue((float) Math.round(AdultFemaleHIV24Neg)); c18.setCellValue((float) Math.round(AdultFemaleHIV49Neg)); c19.setCellValue((float) Math.round(AdultFemaleHIV50Neg)); c110.setCellValue((float) Math.round(TotalNegativeMale)); c111.setCellValue((float) Math.round(ChildMaleHIV1Neg)); c112.setCellValue((float) Math.round(ChildMaleHIV4Neg)); c113.setCellValue((float) Math.round(ChildMaleHIV9Neg)); c114.setCellValue((float) Math.round(ChildMaleHIV14Neg)); c115.setCellValue((float) Math.round(AdultMaleHIV19Neg)); c116.setCellValue((float) Math.round(AdultMaleHIV24Neg)); c117.setCellValue((float) Math.round(AdultMaleHIV49Neg)); c118.setCellValue((float) Math.round(AdultMaleHIV50Neg)); System.out.println(AdultMaleHIV19Neg + "__________" + AdultMaleHIV24Neg + "__________" + AdultMaleHIV49Neg + "__________" + AdultMaleHIV50Neg + "__________" + ChildMaleHIV1Neg + "__________" + ChildMaleHIV4Neg + "__________" + ChildMaleHIV9Neg + "__________" + ChildMaleHIV14Neg); for (int i = 23; i <= 42; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stborder); } // shet3.addMergedRegion(new CellRangeAddress(2,5,20,20)); } } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.conn != null) { conn.conn.close(); } IdGenerator IG = new IdGenerator(); createdOn = IG.CreatedOn(); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=moh711_DATIM_REPORT_CREATED_ON_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }