List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setAlignment
@Override public void setAlignment(HorizontalAlignment align)
From source file:com.xnradmin.core.test.TestJson.java
private static int processYsmcSubLevel2(String type1, String type2, String content, HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row, int flag) throws JSONException, org.json.JSONException { JSONArray data = new JSONArray(content); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? for (int i = 0; i < data.length(); i++) { JSONObject temp = (JSONObject) data.get(i); System.out.println(temp); StringBuffer sb = new StringBuffer(); sb.append("??").append(temp.get("name")); sb.append(" | ").append(temp.get("level")); sb.append(" | ??").append(temp.get("format")); sb.append(" | ??").append(temp.get("unit")); sb.append(" | ?:").append(temp.get("own_brand")); sb.append(" | ?2: ").append(temp.get("sell_brand")); sb.append(" | ").append(temp.get("class1")); sb.append(" | ").append(type1); sb.append(" | ?").append(type2); sb.append(" | ?").append(temp.get("price")); sb.append(" | ").append(temp.get("commodity_total_price")); System.out.println(sb.toString()); int f = flag + 1; row = sheet.createRow(f);//from w w w .j a va2s.c o m System.out.println("row: " + f); HSSFCell cell0 = row.createCell(0); cell0.setCellStyle(style); cell0.setCellValue(temp.get("name").toString()); HSSFCell cell1 = row.createCell(1); cell1.setCellStyle(style); cell1.setCellValue(temp.get("level").toString()); HSSFCell cell2 = row.createCell(2); cell2.setCellStyle(style); cell2.setCellValue(temp.get("format").toString()); HSSFCell cell3 = row.createCell(3); cell3.setCellStyle(style); cell3.setCellValue(temp.get("unit").toString()); HSSFCell cell4 = row.createCell(4); cell4.setCellStyle(style); cell4.setCellValue(temp.get("own_brand").toString()); HSSFCell cell5 = row.createCell(5); cell5.setCellStyle(style); cell5.setCellValue(temp.get("sell_brand").toString()); HSSFCell cell6 = row.createCell(6); cell6.setCellStyle(style); cell6.setCellValue(temp.get("class1").toString()); HSSFCell cell7 = row.createCell(7); cell7.setCellStyle(style); cell7.setCellValue(temp.get("price").toString()); HSSFCell cell8 = row.createCell(8); cell8.setCellStyle(style); cell8.setCellValue(temp.get("commodity_total_price").toString()); HSSFCell cell9 = row.createCell(9); cell9.setCellStyle(style); cell9.setCellValue(type1); HSSFCell cell10 = row.createCell(10); cell10.setCellStyle(style); cell10.setCellValue(type2); // row.createCell(0).setCellValue(temp.get("name").toString()); // row.createCell(1).setCellValue(temp.get("format").toString()); // row.createCell(2).setCellValue(temp.get("unit").toString()); // row.createCell(3).setCellValue(temp.get("own_brand").toString()); // row.createCell(4).setCellValue(temp.get("sell_brand").toString()); // row.createCell(5).setCellValue(temp.get("class1").toString()); // row.createCell(6).setCellValue(temp.get("price").toString()); flag = f; } return flag; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
public static void writeToXLS(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet(sheetName); HSSFRow row = sheet1.createRow(0);/*www . j a va 2 s . c o m*/ HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell; for (int i = 0; i < heads.size(); i++) { cell = row.createCell(i); cell.setCellValue(heads.get(i)); cell.setCellStyle(style); } if (data != null && data.size() != 0) { for (int n = 0; n < data.size(); n++) { row = sheet1.createRow(n + 1); ExcelRow datarow = data.get(n); for (int m = 0; m < datarow.size(); m++) { cell = row.createCell(m); cell.setCellValue(datarow.get(m)); cell.setCellStyle(style); } } } wb.write(out); }
From source file:com.zhu.action.CarAction.java
public void exportpeople(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {/* ww w . j a va2s .co m*/ CarForm carForm = (CarForm) form; // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("?"); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? HSSFCell cell = row.createCell(0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("???"); cell.setCellStyle(style); // ? ?? OrderService orderService = new OrderService(); List<Orderinfo> list = orderService.getOrderCarDetail(carForm.getId()); System.out.println(list.size()); for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 1); Orderinfo order = (Orderinfo) list.get(i); // ? row.createCell(0).setCellValue(order.getN1()); row.createCell(1).setCellValue(order.getI1()); row = sheet.createRow(i + list.size() + 1); row.createCell(0).setCellValue(order.getN2()); row.createCell(1).setCellValue(order.getI2()); row = sheet.createRow(i + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN3()); row.createCell(1).setCellValue(order.getI3()); row = sheet.createRow(i + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN4()); row.createCell(1).setCellValue(order.getI4()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN5()); row.createCell(1).setCellValue(order.getI5()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN6()); row.createCell(1).setCellValue(order.getI6()); row = sheet.createRow( i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN7()); row.createCell(1).setCellValue(order.getI7()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN8()); row.createCell(1).setCellValue(order.getI8()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN9()); row.createCell(1).setCellValue(order.getI9()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN10()); row.createCell(1).setCellValue(order.getI10()); } // ? String filePath = ""; Date dt = new Date(); DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss"); String date = df.format(dt).toString(); filePath = "/Users/Nemo/Documents/carpeople" + date + ".xls"; File file = new File(filePath); try { // FileOutputStream fout = new FileOutputStream( // "/Users/Nemo/Documents/carpeople.xls"); // wb.write(fout); // fout.close(); OutputStream out = new FileOutputStream(file); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } int key = 0; int MaxRowNum = 0, MaxCellNum = 0; try { FileInputStream in = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(in); HSSFWorkbook workbook = new HSSFWorkbook(fs); FileOutputStream out = new FileOutputStream("/Users/Nemo/Documents/carpeople" + date + ".xls"); int number = workbook.getNumberOfSheets(); for (int i = 0; i < number; i++) { // ?sheet sheet = workbook.getSheetAt(i); // 14 System.out.println("" + sheet.getSheetName() + " ? " + (sheet.getLastRowNum() + 1)); MaxRowNum = 0; for (int k = 0; k <= sheet.getLastRowNum(); k++) { HSSFRow hRow = sheet.getRow(k); // System.out.println((k + 1) + ""); if (isBlankRow(hRow)) // { int m = 0; for (m = k + 1; m <= sheet.getLastRowNum(); m++) { HSSFRow nhRow = sheet.getRow(m); if (!isBlankRow(nhRow)) { // System.out.println("?" + (m + 1)); sheet.shiftRows(m, sheet.getLastRowNum(), k - m); break; } } if (m > sheet.getLastRowNum()) break; // ? } else { // ? MaxRowNum++; if (MaxCellNum < hRow.getLastCellNum()) MaxCellNum = hRow.getLastCellNum(); } } workbook.setPrintArea(i, 0, MaxCellNum, 0, MaxRowNum); System.out .println("?? " + sheet.getSheetName() + " " + MaxRowNum); } workbook.write(out); in.close(); out.close(); } catch (IOException e) { System.out.println(key + " " + e.getMessage() + " "); e.printStackTrace(); } System.out.println("??"); }
From source file:com.zhu.action.CarAction.java
public void export(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {// w w w . j av a 2s .co m // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("?"); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? HSSFCell cell = row.createCell(0); cell.setCellValue("id"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("?"); cell.setCellStyle(style); // ? ?? CarService carService = new CarService(); List<Car> list = carService.getCar(); for (int i = 0; i < list.size(); i++) { row = sheet.createRow((int) i + 1); Car car = (Car) list.get(i); // ? row.createCell(0).setCellValue(car.getId()); row.createCell(1).setCellValue(car.getCarid()); row.createCell(2).setCellValue(car.getLicense()); row.createCell(3).setCellValue(car.getNumber()); row.createCell(4).setCellValue(car.getPeople()); row.createCell(5).setCellValue(car.getRoute()); row.createCell(6).setCellValue(car.getStartdate()); row.createCell(7).setCellValue(car.getEnddate()); if (car.getStatus() == 0) { row.createCell(8).setCellValue(""); } else if (car.getStatus() == 1) { row.createCell(8).setCellValue(""); } else { row.createCell(8).setCellValue(""); } // cell = row.createCell((short) 3); } // ? String filePath = ""; Date dt = new Date(); DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss"); String date = df.format(dt).toString(); filePath = "/Users/Nemo/Documents/car" + date + ".xls"; File file = new File(filePath); try { FileOutputStream fout = new FileOutputStream(file); wb.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.zrx.authority.util.ObjectExcelView.java
@Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // TODO Auto-generated method stub Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet;/*from w ww .ja v a2 s . com*/ HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); HSSFCellStyle headerStyle = workbook.createCellStyle(); //? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = getCell(sheet, 0, i); cell.setCellStyle(headerStyle); setText(cell, title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<PageData> varList = (List<PageData>) model.get("varList"); int varCount = varList.size(); for (int i = 0; i < varCount; i++) { PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = getCell(sheet, i + 1, j); cell.setCellStyle(contentStyle); setText(cell, varstr); } } }
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 {/* www .j av a 2 s . co m*/ javax.swing.JFileChooser archivo = new javax.swing.JFileChooser(); archivo.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null; if (archivo.showSaveDialog(null) == archivo.APPROVE_OPTION) { ruta = archivo.getSelectedFile().getAbsolutePath(); if (ruta != null) { File archivoXLS = new File(ruta + ".xls"); File plantilla = new File("imagenes/plantillaConciliacion.xls"); Session session = HibernateUtil.getSessionFactory().openSession(); ArrayList datos = new ArrayList(); Query query = session.createSQLQuery( "select compania.nombre, orden.tipo_nombre, orden.modelo, orden.no_serie, clientes.nombre as nombres,orden.id_orden \n" + "from orden inner join compania on compania.id_compania=orden.id_compania inner join clientes on clientes.id_clientes=orden.id_cliente\n" + "where orden.id_orden=" + Integer.parseInt(orden) + ""); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); datos = (ArrayList) query.list(); // Path FROM = Paths.get("imagenes/plantillaConciliacion.xls"); Path TO = Paths.get(ruta + ".xls"); //sobreescribir el fichero de destino, si existe, y copiar // los atributos, incluyendo los permisos rwx CopyOption[] options = new CopyOption[] { StandardCopyOption.REPLACE_EXISTING, StandardCopyOption.COPY_ATTRIBUTES }; Files.copy(FROM, TO, options); FileInputStream miPlantilla = new FileInputStream(archivoXLS); POIFSFileSystem fsFileSystem = new POIFSFileSystem(miPlantilla); HSSFWorkbook libro = new HSSFWorkbook(fsFileSystem); libro.getSheet("Conciliacion").getRow(0).getCell(6) .setCellValue("CONCILIACIN PARA FACTURACIN"); for (int i = 0; i < datos.size(); i++) { java.util.HashMap map = (java.util.HashMap) datos.get(i); libro.getSheet("Conciliacion").getRow(1).getCell(2) .setCellValue(map.get("nombre").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(2) .setCellValue(map.get("tipo_nombre").toString()); libro.getSheet("Conciliacion").getRow(3).getCell(2) .setCellValue(map.get("modelo").toString()); libro.getSheet("Conciliacion").getRow(4).getCell(2) .setCellValue(map.get("no_serie").toString()); libro.getSheet("Conciliacion").getRow(5).getCell(2) .setCellValue(map.get("nombres").toString()); libro.getSheet("Conciliacion").getRow(2).getCell(12) .setCellValue(map.get("id_orden").toString()); } HSSFCellStyle borde_d = libro.createCellStyle(); borde_d.setBorderBottom(CellStyle.BORDER_THIN); borde_d.setBorderTop(CellStyle.BORDER_THIN); borde_d.setBorderRight(CellStyle.BORDER_THIN); borde_d.setBorderLeft(CellStyle.BORDER_THIN); borde_d.setAlignment(CellStyle.ALIGN_RIGHT); HSSFCellStyle borde_i = libro.createCellStyle(); borde_i.setBorderBottom(CellStyle.BORDER_THIN); borde_i.setBorderTop(CellStyle.BORDER_THIN); borde_i.setBorderRight(CellStyle.BORDER_THIN); borde_i.setBorderLeft(CellStyle.BORDER_THIN); borde_i.setAlignment(CellStyle.ALIGN_LEFT); HSSFCellStyle borde_c = libro.createCellStyle(); borde_c.setBorderBottom(CellStyle.BORDER_THIN); borde_c.setBorderTop(CellStyle.BORDER_THIN); borde_c.setBorderRight(CellStyle.BORDER_THIN); borde_c.setBorderLeft(CellStyle.BORDER_THIN); borde_c.setAlignment(CellStyle.ALIGN_CENTER); HSSFCellStyle borde_dr = libro.createCellStyle(); borde_dr.setBorderBottom(CellStyle.BORDER_THIN); borde_dr.setBorderTop(CellStyle.BORDER_THIN); borde_dr.setBorderRight(CellStyle.BORDER_THIN); borde_dr.setBorderLeft(CellStyle.BORDER_THIN); borde_dr.setAlignment(CellStyle.ALIGN_RIGHT); borde_dr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_dr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_dr.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_ir = libro.createCellStyle(); borde_ir.setBorderBottom(CellStyle.BORDER_THIN); borde_ir.setBorderTop(CellStyle.BORDER_THIN); borde_ir.setBorderRight(CellStyle.BORDER_THIN); borde_ir.setBorderLeft(CellStyle.BORDER_THIN); borde_ir.setAlignment(CellStyle.ALIGN_LEFT); borde_ir.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_ir.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_ir.setFillForegroundColor(HSSFColor.YELLOW.index); HSSFCellStyle borde_cr = libro.createCellStyle(); borde_cr.setBorderBottom(CellStyle.BORDER_THIN); borde_cr.setBorderTop(CellStyle.BORDER_THIN); borde_cr.setBorderRight(CellStyle.BORDER_THIN); borde_cr.setBorderLeft(CellStyle.BORDER_THIN); borde_cr.setAlignment(CellStyle.ALIGN_CENTER); borde_cr.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); borde_cr.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); borde_cr.setFillForegroundColor(HSSFColor.YELLOW.index); DecimalFormat formatoDecimal = new DecimalFormat("####0.0"); DecimalFormat formatoPorcentaje = new DecimalFormat("#,##0.00"); int miRenglon = 9; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == true || (boolean) t_datos.getValueAt(i, 4) == true) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue("V"); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } //font1.setColor(BaseColor.WHITE); libro.getSheet("Conciliacion").createRow(miRenglon); libro.getSheet("Conciliacion") .addMergedRegion(new CellRangeAddress(miRenglon, miRenglon, 0, 14)); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue("Faltante en Vales"); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0).setCellStyle(borde_c); miRenglon++; for (int i = 0; i < t_datos.getRowCount(); i++) { for (int j = 0; j < 4; j++) { int renglon = 0; switch (j) { case 0: renglon = 8; break; case 1: renglon = 10; break; case 2: renglon = 11; break; case 3: renglon = 12; break; } if ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && t_datos.getValueAt(i, 9).toString().compareTo("N") == 0) || ((Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) > 0 && renglon >= 10)) || (renglon == 8 && Double.parseDouble(t_datos.getValueAt(i, 10).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 11).toString()) <= 0 && Double.parseDouble(t_datos.getValueAt(i, 12).toString()) <= 0)) { if ((boolean) t_datos.getValueAt(i, 3) == false && (boolean) t_datos.getValueAt(i, 4) == false && t_datos.getValueAt(i, 5) != null) { libro.getSheet("Conciliacion").createRow(miRenglon); //columna0 if (t_datos.getValueAt(i, 5) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(0) .setCellValue(t_datos.getValueAt(i, 5).toString()); } //columna1 if (t_datos.getValueAt(i, 6) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(1) .setCellValue(t_datos.getValueAt(i, 6).toString()); } //columna2 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(2) .setCellValue(t_datos.getValueAt(i, renglon).toString()); //columna3 if (t_datos.getValueAt(i, 14) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(3) .setCellValue(t_datos.getValueAt(i, 14).toString()); } //columna4 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(4) .setCellValue(t_datos.getValueAt(i, 2).toString()); //columna5 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue(""); else { switch (renglon) { case 8: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("N"); break; case 10: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("D"); break; case 11: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("R"); break; case 12: libro.getSheet("Conciliacion").getRow(miRenglon).createCell(5) .setCellValue("M"); break; } } //columna6 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(6) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 15))); //columna7 $tot aut. double n; n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 15).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(7) .setCellValue(formatoPorcentaje.format(n)); //columna8 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(8) .setCellValue(formatoPorcentaje.format(t_datos.getValueAt(i, 16))); //columna9 $tot com n = BigDecimal .valueOf(Double.parseDouble(t_datos.getValueAt(i, renglon).toString()) * Double.parseDouble(t_datos.getValueAt(i, 16).toString())) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(9) .setCellValue(formatoPorcentaje.format(n)); //columna10 11 if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(""); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(""); } else { switch (renglon) { case 8: n = BigDecimal.valueOf( Double.parseDouble(t_datos.getValueAt(i, 16).toString()) / 0.9d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 10: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.72d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 11: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; case 12: n = BigDecimal .valueOf( Double.parseDouble(t_datos.getValueAt(i, 15).toString()) * 0.65d) .setScale(2, RoundingMode.UP).doubleValue(); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(10) .setCellValue(formatoPorcentaje.format(n * Double.parseDouble( t_datos.getValueAt(i, renglon).toString()))); libro.getSheet("Conciliacion").getRow(miRenglon).createCell(11) .setCellValue(formatoPorcentaje.format(n)); break; } } //columna12 if (t_datos.getValueAt(i, 18) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(12) .setCellValue(t_datos.getValueAt(i, 18).toString()); } //columna13 if (t_datos.getValueAt(i, 19) == null) { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(""); } else { libro.getSheet("Conciliacion").getRow(miRenglon).createCell(13) .setCellValue(t_datos.getValueAt(i, 19).toString()); } //columna14 libro.getSheet("Conciliacion").getRow(miRenglon).createCell(14) .setCellValue(""); if (renglon == 8 && t_datos.getValueAt(i, 9).toString().compareTo("-") == 0) { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_c); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_d); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_i); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_d); } else { libro.getSheet("Conciliacion").getRow(miRenglon).getCell(0) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(1) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(2) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(3) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(4) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(5) .setCellStyle(borde_cr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(6) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(7) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(8) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(9) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(10) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(11) .setCellStyle(borde_dr); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(12) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(13) .setCellStyle(borde_ir); libro.getSheet("Conciliacion").getRow(miRenglon).getCell(14) .setCellStyle(borde_dr); } miRenglon++; } } } } FileOutputStream archivo1 = new FileOutputStream(archivoXLS); libro.write(archivo1); archivo1.close(); Desktop.getDesktop().open(archivoXLS); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:corner.orm.tapestry.service.excel.ExcelService.java
License:Apache License
/** * ?titleStyle/* w ww .j a v a 2 s. 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:das.pf.io.IOExcel.java
License:Open Source License
private HSSFCellStyle getHSSFCellStyleValue(Workbook worbook, TypeValues typeValue, TypeUnits typeUnit) { HSSFCellStyle style = (HSSFCellStyle) worbook.createCellStyle(); HSSFFont font = (HSSFFont) worbook.createFont(); HSSFPalette palette = ((HSSFWorkbook) worbook).getCustomPalette(); font.setColor(HSSFColor.WHITE.index); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); switch (typeValue) { case VALUES:/*w w w.ja v a2s. c o m*/ switch (typeUnit) { case MTH: style.setFillForegroundColor(HSSFColor.DARK_BLUE.index); break; case QRT: style.setFillForegroundColor(HSSFColor.DARK_BLUE.index); palette.setColorAtIndex(HSSFColor.DARK_BLUE.index, (byte) 0, (byte) 0, (byte) 102); break; case YTD: style.setFillForegroundColor(HSSFColor.BLUE.index); break; case MAT: style.setFillForegroundColor(HSSFColor.AQUA.index); break; } break; case UNITS: switch (typeUnit) { case MTH: style.setFillForegroundColor(HSSFColor.RED.index); break; case QRT: style.setFillForegroundColor(HSSFColor.DARK_RED.index); break; case YTD: style.setFillForegroundColor(HSSFColor.ORANGE.index); break; case MAT: style.setFillForegroundColor(HSSFColor.YELLOW.index); break; } break; case U_E: switch (typeUnit) { case MTH: style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); break; case QRT: style.setFillForegroundColor(HSSFColor.DARK_GREEN.index); break; case YTD: style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); break; case MAT: style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); break; } break; } style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFont(font); return style; }
From source file:das.pf.io.IOExcel.java
License:Open Source License
private HSSFCellStyle getHSSFCellStyleHeaderData(Workbook worbook) { HSSFCellStyle style = (HSSFCellStyle) worbook.createCellStyle(); HSSFFont font = (HSSFFont) worbook.createFont(); font.setBoldweight((short) 2); font.setColor(HSSFColor.WHITE.index); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); return style; }
From source file:dataQuality.checkDuplicates.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//w w w . j av a 2 s . c o m 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(); }