List of usage examples for org.apache.poi.ss.usermodel CellStyle setVerticalAlignment
void setVerticalAlignment(VerticalAlignment align);
From source file:facturasdiferidas.frmFacturasDif.java
private void ExportarExcel(ResultSet rs) throws IOException, SQLException { JFileChooser file = new JFileChooser(); FileNameExtensionFilter filtro = new FileNameExtensionFilter("Excel(*.XLSX)", "xlsx"); file.setFileFilter(filtro);//from w w w. ja v a 2 s . c o m int seleccion = file.showSaveDialog(this); if (seleccion == JFileChooser.CANCEL_OPTION) { return; } File guarda = file.getSelectedFile(); //file.set if (guarda != null) { // file.setFileFilter(filtro); /*guardamos el archivo y le damos el formato directamente, * si queremos que se guarde en formato doc lo definimos como .doc*/ rutaArchivo = guarda.getAbsolutePath() + ".xlsx"; // JOptionPane.showMessageDialog(null, // "Se creo el archivo... Generando informacin", // "Informacin en\n"+rutaArchivo,JOptionPane.INFORMATION_MESSAGE); } else { // file.setFileFilter(filtro); rutaArchivo = System.getProperty("user.home") + "/Kardex.xlsx"; // JOptionPane.showMessageDialog(null, // "Se creo el archivo... Generando informacin", // "Informacin en\n"+rutaArchivo,JOptionPane.INFORMATION_MESSAGE); } /*Se crea el objeto de tipo File con la ruta del archivo*/ archivoXLS = new File(rutaArchivo); /*Si el archivo existe se elimina*/ if (archivoXLS.exists()) archivoXLS.delete(); // try { /*Se crea el archivo*/ //archivoXLS. archivoXLS.createNewFile(); libro = new XSSFWorkbook(); archivo = new FileOutputStream(archivoXLS); //archivo.close(); CreationHelper createhelper = libro.getCreationHelper(); cellStyle = libro.createCellStyle(); cellStyle.setDataFormat(createhelper.createDataFormat().getFormat("dd/mm/yyyy")); fuente = libro.createFont(); fuente.setFontHeightInPoints((short) 10); fuente.setFontName("Calibri"); fuente.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fuente.setBold(true); fuente3 = libro.createFont(); fuente3.setFontHeightInPoints((short) 8); fuente3.setFontName("Calibri"); fuente3.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); fuente2 = libro.createFont(); fuente2.setFontHeightInPoints((short) 9); fuente2.setFontName("Calibri"); //fuente.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); CellStyle cellStyle2 = libro.createCellStyle(); cellStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); cellStyle2.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle2.setFont(fuente); cellStyle2.setWrapText(true); CellStyle cellStyle3 = libro.createCellStyle(); cellStyle3.setAlignment(XSSFCellStyle.ALIGN_LEFT); cellStyle3.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle3.setFont(fuente); CellStyle cellStyle4 = libro.createCellStyle(); // cellStyle4.setAlignment(XSSFCellStyle. ALIGN_LEFT); // cellStyle4.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP); cellStyle4.setFont(fuente); hoja = libro.createSheet("FacturasDiferidas"); //hoja. hoja.setDefaultRowHeightInPoints(12); Row fila = hoja.createRow(0); Cell celda = fila.createCell(0); celda.setCellValue("TIENDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(1); celda.setCellValue("TIPO_DOC"); celda.setCellStyle(cellStyle4); celda = fila.createCell(2); celda.setCellValue("DOC_DIFERIDO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(3); celda.setCellValue("FECHA_EMI"); celda.setCellStyle(cellStyle4); celda = fila.createCell(4); celda.setCellValue("CLIENTE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(5); celda.setCellValue("NOMBRE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(6); celda.setCellValue("COD_PROD"); celda.setCellStyle(cellStyle4); celda = fila.createCell(7); celda.setCellValue("PRODUCTO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(8); celda.setCellValue("MONEDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(9); celda.setCellValue("PRECIO_UNITARIO"); celda.setCellStyle(cellStyle4); celda = fila.createCell(10); celda.setCellValue("TOTAL"); celda.setCellStyle(cellStyle4); celda = fila.createCell(11); celda.setCellValue("CANT_FACTURADA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(12); celda.setCellValue("SALDO_TOTAL_X_ATENDER"); celda.setCellStyle(cellStyle4); celda = fila.createCell(13); celda.setCellValue("CANTIDAD_ATENDIDA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(14); celda.setCellValue("TIPO_DOC_A"); celda.setCellStyle(cellStyle4); celda = fila.createCell(15); celda.setCellValue("DOCUMENTO_ATIENDE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(16); celda.setCellValue("FECHA_GUIA"); celda.setCellStyle(cellStyle4); celda = fila.createCell(17); celda.setCellValue("TIENDA_ATIENDE"); celda.setCellStyle(cellStyle4); celda = fila.createCell(18); celda.setCellValue("ALMACEN_ATIENDE"); celda.setCellStyle(cellStyle4); f = 1; reloj = new Timer(delay, new EjecutarTarea2()); reloj.start(); // // // while (rs.next()){ // // } }
From source file:FILING.cboreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// w w w.j a v a2s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String District[]; String Year = ""; District = request.getParameterValues("District"); Year = request.getParameter("Year"); String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String distval = ""; int activeOVC = 0; int activeHH = 0; float activeovc = 0; float activehh = 0; float percent = 0; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_CENTER); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("CBO FILING TRACKER REPORT" + "( " + Year + ")"); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 4500); shet1.setColumnWidth(1, 8500); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5000); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5000); shet1.setColumnWidth(17, 4000); shet1.setColumnWidth(18, 4000); shet1.setColumnWidth(19, 4200); shet1.setColumnWidth(20, 4200); shet1.setColumnWidth(21, 4200); shet1.setColumnWidth(22, 4200); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(40); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell4 = rw4.createCell(3); cell1.setCellValue("DISTRICT"); cell1.setCellStyle(style); cell2.setCellValue("CBO"); cell2.setCellStyle(style); cell6 = rw6.createCell(0); cell6.setCellValue("Status"); cell6.setCellStyle(style); cell7 = rw6.createCell(1); cell7.setCellValue(""); cell7.setCellStyle(style); shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); int rowcount = 3; int doccounter = 4; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; cell3 = rw4.createCell(2); cell3.setCellValue("ACTIVE OVC"); cell3.setCellStyle(style); cell3 = rw4.createCell(3); cell3.setCellValue("ACTIVE HH"); cell3.setCellStyle(style); cell6 = rw6.createCell(2); cell6.setCellValue(""); cell6.setCellStyle(stylex); cell6 = rw6.createCell(3); cell6.setCellValue(""); cell6.setCellStyle(stylex); ArrayList docidarray = new ArrayList(); String getdocname = "select * from ovcdocuments WHERE DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { docidarray.add(conn.rs3.getString(1)); System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); cell6 = rw6.createCell(doccounter1); cell6.setCellValue("Available"); cell6.setCellStyle(stylex); // cell5=rw6.createCell(doccounter1); // cell5.setCellValue("Not Available"); // cell5.setCellStyle(stylex); doccounter1++; System.out.println("mergecounter b4" + mergecounter); // shet1.addMergedRegion(new CellRangeAddress(2,2,2,3)); // shet1.addMergedRegion(new CellRangeAddress(2,2,4,5)); // shet1.addMergedRegion(new CellRangeAddress(2,2,6,7)); // shet1.addMergedRegion(new CellRangeAddress(2,2,8,9)); // shet1.addMergedRegion(new CellRangeAddress(2,2,10,11)); // shet1.addMergedRegion(new CellRangeAddress(2,2,12,13)); // shet1.addMergedRegion(new CellRangeAddress(2,2,14,15)); // shet1.addMergedRegion(new CellRangeAddress(2,2,16,17)); // shet1.addMergedRegion(new CellRangeAddress(2,2,18,19)); // shet1.addMergedRegion(new CellRangeAddress(2,2,20,21)); System.out.println("mergecounter after" + mergecounter); // mergecounter++; //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++)); } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; int countercopy = 4; HSSFRow rw5 = null; for (int j = 0; j < District.length; j++) { String getcboCOUNT = "select * from CBO where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs2 = conn.state2.executeQuery(getcboCOUNT); while (conn.rs2.next()) { counter++; rowcount++; rw5 = shet1.createRow(rowcount); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } cell2 = rw5.createCell(1); cell2.setCellValue(conn.rs2.getString(2)); cell2.setCellStyle(style_border); // cell5=rw5.createCell(rowcount); // cell6=rw5.createCell(rowcounter++); // cell5.setCellValue("x"); // cell6.setCellValue("y"); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); rw5.setHeightInPoints(25); // cell3=rw5.createCell(2); String getDistrict = "select * from District where DistrictID='" + District[j] + "' Order by DistrictID "; conn.rs4 = conn.state4.executeQuery(getDistrict); while (conn.rs4.next()) { districtname = conn.rs4.getString("District"); cell1 = rw5.createCell(0); cell1.setCellValue(districtname); cell1.setCellStyle(style_border1); } doccounter = 4; System.out.println("doccount " + doccounter); String getdocname1 = "select * from ovcdocuments"; // conn.rs3 = conn.state.executeQuery(getdocname1); // while(conn.rs3.next()){ String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, " + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0," + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District," + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))" + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + District[j] + "' and " + "Clientdetails.Cbo='" + conn.rs2.getString(1) + "' and Clientdetails.Exited='1' and " + " (Year='" + Year + "' OR Year='') AND Clientdetails.OVCID = ovcfiling.ovcid " + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District Order by ovcfiling.ovcdocid,District "; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); value0 = conn.rs.getInt(2); cboid = conn.rs.getString(3); doc = conn.rs.getString(4); Districtid = conn.rs.getString(5); activeOVC = conn.rs.getInt(6); activeHH = conn.rs.getInt(7); int a = 0; for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i) + " " + doc); if (docidarray.get(i).equals(doc)) { System.out.println(doc + "lll" + docidarray.get(i)); int cellcount = i + 2; activeovc = activeOVC; activehh = activeHH; if (doc.equals("8")) { percent = value1 / activehh * 100; } else { percent = value1 / activeovc * 100; } cell7 = rw5.createCell(2); cell8 = rw5.createCell(3); cell5 = rw5.createCell(doccounter); // cell6=rw5.createCell(doccounter); cell5.setCellValue(Math.round(percent) + "%"); // cell6.setCellValue(value0); //FOR ACTICE OVCs cell7.setCellValue(activeOVC); cell7.setCellStyle(style_border); //FOR ACTICE hhs cell8.setCellValue(activeHH); cell8.setCellStyle(style_border); cell5.setCellStyle(style_border); cell6.setCellStyle(style_border); System.out.println("****a " + doc + " " + doccounter); if (doc.equals("2")) { // System.out.println("****i "+doc +" "+doccounter); doccounter++; // doccounter++; System.out.println("am in2"); System.out.println("****b " + doc + " " + doccounter); } if (doc.equals("3")) { doccounter--; // doccounter--; // doccounter--; System.out.println("****f " + doc + " " + doccounter); cell5 = rw5.createCell(doccounter++); cell5.setCellStyle(style_border); cell5.setCellValue(Math.round(percent) + "%"); cell6 = rw5.createCell(doccounter); // cell6.setCellStyle(style_border); // cell6.setCellValue(value0); System.out.println("****b " + doc + " " + doccounter); doccounter--; } // else if(!doc.equals("4") && !docidarray.get(i).equals("4")){ // doccounter++; // doccounter++; // System.out.println("****c "+doc +" "+doccounter); // } doccounter++; } } } doccounter = 2; // String getcbo= "select * from CBO where cboid='"+cboid+"'"; // conn.rs2 = conn.state2.executeQuery(getcbo); // while(conn.rs2.next()){ // // cell2=rw5.createCell(1); // cell2.setCellValue(conn.rs2.getString(2)); // // System.out.println("rowcount "+rowcount ); // // } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); } //} // end of while loop if (distval.equals("")) { // totalvalue= countercopy+counter; System.out.println(countercopy + " counter " + counter + " " + rowcount); distval = districtname; System.out.println(countercopy + " nnnn " + counter + " " + rowcount); if (counter > countercopy) { shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0)); countercopy = rowcount; //cell1.setCellValue(districtname); } System.out.println(countercopy + " nnn " + counter + " " + rowcount + " " + distval); } // cell1.setCellValue(districtname); if (!distval.equals(districtname) && !distval.equals("")) { distval = districtname; // cell1.setCellValue(districtname); shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0)); countercopy = rowcount; // System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy); System.out.println(distval + "@@@@1 " + districtname); } System.out.println(counter + "@@@@ " + rowcount); //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0)); System.out.println(distval + "@@@@ " + districtname); } // end of for loop // int totalvalue=countercopy+counter; // System.out.println(counter+" hhhh "+countercopy); // if(counter>countercopy){ // shet1.addMergedRegion(new CellRangeAddress(countercopy,totalvalue-1,0,0)); // // countercopy=counter; // } // System.out.println("aaaaaannnn "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); // 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=CBO_FILING_TRACKER_REPORT_FOR_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }
From source file:FILING.childdetailsreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w ww.ja va2 s . co m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String District = ""; District = request.getParameter("District"); String Year = ""; Year = request.getParameter("Year"); String chw = ""; String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String chwval = ""; String docid = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); int cbocount = 3; //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_CENTER); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("OVC DOCUMENT DETAILS REPORT"); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 9000); shet1.setColumnWidth(1, 9000); shet1.setColumnWidth(2, 9000); shet1.setColumnWidth(3, 9000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(50); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell0, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell4 = rw4.createCell(3); cell0.setCellValue("CBO"); cell0.setCellStyle(style); cell1.setCellValue("CHW"); cell1.setCellStyle(style); cell2.setCellValue("OVCID"); cell2.setCellStyle(style); cell4.setCellValue("FULLNAME"); cell4.setCellStyle(style); ArrayList docidarray = new ArrayList(); int rowcount = 3; int doccounter = 3; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; String getdocname = "select * from ovcdocuments WHERE DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); docidarray.add(conn.rs3.getString(1)); doccounter1++; } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; int countercopy = 3; int countercopy1 = 3; HSSFRow rw5 = null; String getcboCOUNT = "select * from CBO where DistrictID='" + District + "' Order by DistrictID "; conn.rs2 = conn.state2.executeQuery(getcboCOUNT); while (conn.rs2.next()) { counter++; System.out.println("bb b " + conn.rs2.getString(2)); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); System.out.println("doccount " + doccounter); String getData = "select " + "ovcfiling.value,Clientdetails.FirstName, Middlename,SurName,ovcfiling.OVCID" + " , Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District,Clientdetails.Volunteerid " + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + District + "' and Clientdetails.Cbo='" + conn.rs2.getString(1) + "' " + "and Clientdetails.OVCID = ovcfiling.ovcid and (Year='" + Year + "' OR Year='') " + "group by Clientdetails.FirstName, Middlename,Surname,ovcfiling.OVCID,Clientdetails.Volunteerid,ovcfiling.value,ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District " + "Order by Clientdetails.Volunteerid,ovcfiling.OVCID,ovcfiling.ovcdocid"; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); FirstName = conn.rs.getString(2); MiddleName = conn.rs.getString(3); Surname = conn.rs.getString(4); ovcid1 = conn.rs.getString(5); // String getcbo= "select * from CBO where CBOID='"+cboid+"' "; // System.out.println("aaa "+getcbo); // conn.rs_1= conn.state4.executeQuery(getcbo); // while(conn.rs_1.next()){ //// // cbocount++; // cboname=conn.rs_1.getString("CBO"); // System.out.println("aaab "+cboname); // } cboname = conn.rs2.getString(2); cboid = conn.rs.getString(6); docid = conn.rs.getString(7); String getchw = "select * from CHW where CHWID='" + conn.rs.getString(9) + "' "; conn.rs3 = conn.state3.executeQuery(getchw); while (conn.rs3.next()) { chw = conn.rs3.getString("FirstName") + " " + conn.rs3.getString("MiddleName") + " " + conn.rs3.getString("Surname") + " " + conn.rs3.getString("CBOID"); } // fro holding ovc id // to create rows if (docid.equals("1")) { rw5 = shet1.createRow(rowcount); rw5.setHeightInPoints(25); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } rowcount++; cbocount++; } cell1 = rw5.createCell(0); cell1.setCellValue(cboname); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(1); cell1.setCellValue(chw); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(2); cell1.setCellValue(ovcid1); cell1.setCellStyle(style_border1); cell1 = rw5.createCell(3); cell1.setCellValue(FirstName + " " + MiddleName + " " + Surname); cell1.setCellStyle(style_border1); for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i)); if (rw5 == null) { rw5 = shet1.createRow(rowcount); } if (docidarray.get(i).equals(docid)) { int cellcount = i + 4; cell2 = rw5.createCell(cellcount); cell2.setCellValue(value1); cell2.setCellStyle(style_border1); // if( docid.equals("3") ) { // System.out.println("am in2"); // System.out.println("****a "+docid +" "+cellcount); // cellcount++; // } System.out.println("****b " + docid + " " + cellcount); } } // cell3=rw5.createCell(2); // cell3.setCellValue(docid); // cell3.setCellStyle(style_border1); System.out.println( FirstName + " " + ovcid1 + " " + value1 + "___" + doccounter + "_____" + rowcount); if (docid.equals("10")) { // rowcount++; doccounter = 2; } if (chwval.equals("")) { chwval = chw; System.out.println(countercopy + " nnnna " + rowcount); // shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0)); // countercopy=rowcount; System.out.println(countercopy + " nnnnb " + rowcount + " " + chwval); } if (!chwval.equals(chw) && !chwval.equals("")) { chwval = chw; System.out.println(countercopy + " nnna" + rowcount + " " + chwval); shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 2, 1, 1)); countercopy = rowcount - 1; System.out.println(countercopy + " nnnb " + rowcount + " " + chwval); } String cboval = ""; if (cboval.equals("")) { cboval = cboname; // shet1.addMergedRegion(new CellRangeAddress(countercopy1,cbocount-1,0,0)); // countercopy1=cbocount; } if (!cboval.equals(cboname) && !cboval.equals("")) { cboval = cboname; System.out.println(countercopy1 + " nnna" + cbocount + " " + chwval); shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 2, 0, 0)); countercopy1 = cbocount - 1; System.out.println(countercopy1 + " nnnb " + rowcount + " " + cboval); } // // if(monthval.equals("")){ // monthval= months; // System.out.println("88"+monthval +"___"+months); // System.out.println("88"+monthcopy1); // System.out.println("88"+counter1); // cell31.setCellValue(""+months+ " ("+conn.rs3.getInt(5)+")"); // shet2.addMergedRegion(new CellRangeAddress(monthcopy_1,counter1-1,1,1)); // monthcopy1=counter1; // // } // if(!monthval.equals("") && !monthval.equals(months)){ // monthval= months; // System.out.println("!!!"+monthval +"___"+months); // System.out.println("!!!"+monthcopy_1); // System.out.println("!!!!"+counter1); //// cell31.setCellValue(months); //// shet1.addMergedRegion(new CellRangeAddress(monthcopy,counter-1,1,1)); // monthcopy_1=counter1; // // } // if(rowcount>countercopy) { // // shet1.addMergedRegion(new CellRangeAddress(countercopy,rowcount-1,0,0)); // countercopy=rowcount; // } } if (rowcount > countercopy) { shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount - 1, 1, 1)); countercopy = rowcount; } if (cbocount > countercopy1) { shet1.addMergedRegion(new CellRangeAddress(countercopy1, cbocount - 1, 0, 0)); countercopy1 = cbocount; } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); } // // end of while loop 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=CBO_Details_Report_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }
From source file:FILING.countyreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w w w . j a v a 2 s . c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); // PrintWriter out = response.getWriter(); try { dbConn conn = new dbConn("1"); String County[]; County = request.getParameterValues("County"); String Year = ""; Year = request.getParameter("Year"); float percent = 0; ArrayList docidarray = new ArrayList(); System.out.println("countyv " + County); String FirstName = ""; String MiddleName = ""; String Surname = ""; String ovcid1 = ""; String ovcid2 = ""; int value0 = 0; int value1 = 0; String Districtid = ""; String cboid = ""; String doc = ""; String docname = ""; String cboname = ""; String districtname = ""; String countyval = ""; int countercopy = 4; int activeOVC = 0; int activeHH = 0; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% // style header with font color yello HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Arial Black"); font_header.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.YELLOW.index); style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_header.setAlignment(CellStyle.ALIGN_CENTER); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex with font color blue and backgound grey HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.PALE_BLUE.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(CellStyle.ALIGN_CENTER); // gold bg color -style HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // for border with no font color CellStyle style_border = wb.createCellStyle(); style_border.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFFont font1 = wb.createFont(); font1.setFontHeightInPoints((short) 18); font1.setFontName("Cambria"); font1.setBoldweight((short) 7); font1.setColor(HSSFColor.BLACK.index); CellStyle style_border1 = wb.createCellStyle(); style_border1.setBorderBottom(HSSFCellStyle.BORDER_THIN); style_border1.setBorderTop(HSSFCellStyle.BORDER_THIN); style_border1.setBorderRight(HSSFCellStyle.BORDER_THIN); style_border1.setBorderLeft(HSSFCellStyle.BORDER_THIN); style_border1.setFont(font); style_border1.setAlignment(CellStyle.ALIGN_JUSTIFY); style_border1.setVerticalAlignment(CellStyle.ALIGN_LEFT); HSSFRow rw1 = shet1.createRow(1); //cell; rw1.setHeightInPoints(30); for (int y = 0; y < 11; ++y) { HSSFCell cell = rw1.createCell(y); cell.setCellStyle(stylex); if (y == 0) { cell.setCellValue("COUNTY FILING TRACKER REPORT " + Year); } } shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 11)); shet1.setColumnWidth(0, 4500); shet1.setColumnWidth(1, 8500); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5000); shet1.setColumnWidth(4, 5000); shet1.setColumnWidth(5, 5000); shet1.setColumnWidth(6, 5000); shet1.setColumnWidth(7, 5000); shet1.setColumnWidth(8, 5000); shet1.setColumnWidth(9, 5000); shet1.setColumnWidth(10, 5000); shet1.setColumnWidth(11, 5000); shet1.setColumnWidth(12, 5000); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5000); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5000); shet1.setColumnWidth(17, 5000); shet1.setColumnWidth(18, 5000); shet1.setColumnWidth(19, 5000); shet1.setColumnWidth(20, 4500); shet1.setColumnWidth(21, 4500); shet1.setColumnWidth(22, 3500); // Merge the cells // shet1.addMergedRegion(new CellRangeAddress(1,1,1,3)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(40); HSSFRow rw6 = shet1.createRow(3); rw6.setHeightInPoints(25); // rw4.setRowStyle(style); // // // rw6.setRowStyle(style); // // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16; // cells fo row 2 cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell4 = rw4.createCell(3); cell1.setCellValue("COUNTY"); cell1.setCellStyle(style); cell2.setCellValue("DISTRICT"); cell2.setCellStyle(style); cell6 = rw6.createCell(0); cell6.setCellValue("Status"); cell6.setCellStyle(style); cell7 = rw6.createCell(1); cell7.setCellValue(""); cell7.setCellStyle(style_header); cell3 = rw4.createCell(2); cell3.setCellValue("ACTIVE OVC"); cell3.setCellStyle(style); cell3 = rw4.createCell(3); cell3.setCellValue("ACTIVE HH"); cell3.setCellStyle(style); shet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); int rowcount = 3; int doccounter = 4; int doccounter1 = 4; int columcounter = 3; String cboids = ""; int mergecounter = 2; cell6 = rw6.createCell(2); cell6.setCellValue(""); cell6.setCellStyle(stylex); cell6 = rw6.createCell(3); cell6.setCellValue(""); cell6.setCellStyle(stylex); String getdocname = "select * from ovcdocuments where DocumentName!=''"; conn.rs3 = conn.state3.executeQuery(getdocname); while (conn.rs3.next()) { System.out.println("mmm " + doccounter1); System.out.println(conn.rs3.getString(2)); docname = conn.rs3.getString(2); cell3 = rw4.createCell(doccounter1); cell3.setCellValue(docname); cell3.setCellStyle(style); docidarray.add(conn.rs3.getString(1)); cell6 = rw6.createCell(doccounter1++); cell6.setCellValue("Available"); cell6.setCellStyle(stylex); // cell5=rw6.createCell(doccounter1); // cell5.setCellValue("Not Available"); // cell5.setCellStyle(stylex); // doccounter1++; // System.out.println("mergecounter b4"+mergecounter); // System.out.println("mergecounter after"+mergecounter); // mergecounter++; //shet1.addMergedRegion(new CellRangeAddress(2,2,doccounter1,doccounter1++)); } System.out.println("lll " + doccounter1); int rowcounter = 4; int counter = 0; HSSFRow rw5 = null; String DistrictID = ""; for (int j = 0; j < County.length; j++) { String getDistrictCounts = "select * from District where Countyid='" + County[j] + "' order by District"; System.out.println("districtID " + getDistrictCounts); conn.rs2 = conn.state2.executeQuery(getDistrictCounts); while (conn.rs2.next()) { System.out.println("district1" + conn.rs2.getString("District")); counter++; rowcount++; rw5 = shet1.createRow(rowcount); for (int i = 2; i < doccounter1; i++) { System.out.println("mm " + i); cell5 = rw5.createCell(i); cell5.setCellValue(""); cell5.setCellStyle(style_border1); } cell2 = rw5.createCell(1); cell2.setCellValue(conn.rs2.getString(2)); cell2.setCellStyle(style_border); // cell5=rw5.createCell(rowcount); // cell6=rw5.createCell(rowcounter++); // cell5.setCellValue("x"); // cell6.setCellValue("y"); System.out.println("rowcount " + rowcount + " lll " + counter + " rowcounter " + rowcounter); rw5.setHeightInPoints(25); // cell3=rw5.createCell(2); String getDistrict = "select * from County where CountyID='" + County[j] + "' order by County"; System.out.println("dname " + getDistrict); conn.rs4 = conn.state4.executeQuery(getDistrict); while (conn.rs4.next()) { districtname = conn.rs4.getString("County"); System.out.println("district2" + districtname); cell1 = rw5.createCell(0); cell1.setCellValue(districtname); cell1.setCellStyle(style_border1); // to marge these values } System.out.println("doccount " + doccounter); doccounter = 4; String getData = "select " + "SUM(CASE WHEN ovcfiling.value='1' THEN 1 ELSE 0 END) AS COUNT1, " + "SUM(CASE WHEN ovcfiling.value='0' THEN 1 ELSE 0 END) AS COUNT0," + "Clientdetails.Cbo," + "ovcfiling.ovcdocid,Clientdetails.District, " + "count(Clientdetails.OVCID)," + "count(DISTINCT(Clientdetails.HouseHoldheadID))" + " from ovcfiling,Clientdetails " + "WHERE Clientdetails.District ='" + conn.rs2.getString("DistrictID") + "' and Clientdetails.OVCID = ovcfiling.ovcid and Clientdetails.Exited='1' and (Year='" + Year + "' OR Year='') " + "group by ovcfiling.ovcdocid,Clientdetails.Cbo,Clientdetails.District order by Clientdetails.District,ovcfiling.ovcdocid"; System.out.println("aaaaa " + getData); conn.rs = conn.state.executeQuery(getData); while (conn.rs.next()) { value1 = conn.rs.getInt(1); value0 = conn.rs.getInt(2); cboid = conn.rs.getString(3); doc = conn.rs.getString(4); Districtid = conn.rs.getString(5); activeOVC = conn.rs.getInt(6); activeHH = conn.rs.getInt(7); System.out.println("district3 " + Districtid); float activeovc = 0; float activehh = 0; for (int i = 0; i < docidarray.size(); i++) { System.out.println("hh " + docidarray.get(i) + " " + doc); // if(rw5==null){ // rw5=shet1.createRow(rowcount); // } if (docidarray.get(i).equals(doc)) { System.out.println(doc + "lll" + docidarray.get(i)); int cellcount = i + 2; // cell2=rw5.createCell(cellcount++); // cell2.setCellValue(value1); // cell2.setCellStyle(style_border1); // String getdocname1="select * from ovcdocuments"; // conn.rs3= conn.state3.executeQuery(getdocname1); // if(conn.rs3.next()){ cell7 = rw5.createCell(2); cell8 = rw5.createCell(3); cell5 = rw5.createCell(doccounter); // cell6=rw5.createCell(doccounter); activeovc = activeOVC; activehh = activeHH; if (doc.equals("8")) { percent = value1 / activehh * 100; } else { percent = value1 / activeovc * 100; } System.out.println("percenta " + percent + " " + value1 + " " + activeOVC + " act " + activeovc); cell5.setCellValue(Math.round(percent) + "%"); // cell6.setCellValue(value0); //FOR ACTICE OVCs cell7.setCellValue(activeOVC); cell7.setCellStyle(style_border); //FOR ACTICE hhs cell8.setCellValue(activeHH); cell8.setCellStyle(style_border); cell5.setCellStyle(style_border); cell6.setCellStyle(style_border); System.out.println("****a " + doc + " " + doccounter); if (doc.equals("2")) { // System.out.println("****i "+doc +" "+doccounter); doccounter++; // doccounter++; System.out.println("am in2"); System.out.println("****b " + doc + " " + doccounter); } if (doc.equals("3")) { percent = value1 / activeovc * 100; doccounter--; // doccounter--; // doccounter--; System.out.println("****f " + doc + " " + doccounter); cell5 = rw5.createCell(doccounter++); cell5.setCellStyle(style_border); cell5.setCellValue(percent); cell6 = rw5.createCell(doccounter); // cell6.setCellStyle(style_border); // cell6.setCellValue(value0); System.out.println("****b " + doc + " " + doccounter); doccounter--; } // else if(!doc.equals("4") && !docidarray.get(i).equals("4")){ // doccounter++; // doccounter++; // System.out.println("****c "+doc +" "+doccounter); // } doccounter++; } } } doccounter = 2; // String getcbo= "select * from CBO where cboid='"+cboid+"'"; // conn.rs2 = conn.state2.executeQuery(getcbo); // while(conn.rs2.next()){ // // cell2=rw5.createCell(1); // cell2.setCellValue(conn.rs2.getString(2)); // // System.out.println("rowcount "+rowcount ); // // } //} System.out.println("aaaaaa " + districtname + "__" + cboname + "____" + docname + "___" + value1 + "__" + value0); // shet1.addMergedRegion(new CellRangeAddress(countercopy,counter,0,0)); // countercopy=counter; // cell1.setCellValue(districtname); } if (countyval.equals("")) { // totalvalue= countercopy+counter; System.out.println(countercopy + " counter " + counter + " " + rowcount); countyval = districtname; System.out.println(countercopy + " nnnn " + counter + " " + rowcount); // if(counter>countercopy){ shet1.addMergedRegion(new CellRangeAddress(countercopy, rowcount, 0, 0)); countercopy = rowcount; //cell1.setCellValue(districtname); // } System.out.println(countercopy + " nnn " + counter + " " + rowcount + " " + countyval); } // cell1.setCellValue(districtname); if (!countyval.equals(districtname) && !countyval.equals("")) { countyval = districtname; // cell1.setCellValue(districtname); shet1.addMergedRegion(new CellRangeAddress(countercopy + 1, rowcount, 0, 0)); countercopy = rowcount; // System.out.println(counter + "@@@@1 " + rowcount + "__" + countercopy); System.out.println(countyval + "@@@@1 " + districtname); } System.out.println(counter + "@@@@ " + rowcount); //shet1.addMergedRegion(new CellRangeAddress(counter,rowcount,0,0)); System.out.println(countyval + "@@@@ " + districtname); } System.out.println(counter + "@@@@2 " + rowcount + " copy "); // System.out.println("aaaaaannnn "+districtname+"__"+ cboname +"____"+ doc +"___"+value1 +"__"+value0 ); // 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=County_Filing_Tracker_Report_" + Year + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } finally { // out.close(); } }
From source file:forseti.reportes.JReportesDlg.java
License:Open Source License
public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb) throws ServletException, IOException { JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet"); JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1"); JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1"); Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1"); Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2"); Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3"); Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1"); Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2"); Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3"); JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1"); JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2"); JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3"); JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1"); JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2"); JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3"); String fsi_filtro = (String) request.getAttribute("fsi_filtro"); Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report())); short nrow = 0; Row row = sheet.createRow(nrow++);/*from ww w . j a v a 2 s. c o m*/ Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.DARK_BLUE.index); Cell cell = row.createCell(0); cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); cellStyle.setFont(font); cell.setCellStyle(cellStyle); JRepCellStyles cellStyles = new JRepCellStyles(wb); /* Font fenc = wb.createFont(); fenc.setBoldweight(Font.BOLDWEIGHT_BOLD); fenc.setColor(HSSFColor.WHITE.index); Font fnorm = wb.createFont(); fnorm.setColor(HSSFColor.BLACK.index); /////////////////////////////////////////////////////////////////////////////////////////////////////// CellStyle cellStyle = wb.createCellStyle(); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); if(alin != null) { if(alin.equals("center")) cellStyle.setAlignment(CellStyle.ALIGN_CENTER); else if(alin.equals("right")) cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); else cellStyle.setAlignment(CellStyle.ALIGN_LEFT); } if(tipocel != null) { if(tipocel.equals("encabezado")) { cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle.setFillPattern(CellStyle.BIG_SPOTS); } else if(tipocel.equals("agregado")) { cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle.setFillPattern(CellStyle.BIG_SPOTS); } } /////////////////////////////////////////////////////////////////////////////////////////////////////// */ if (m_bSelectL1.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL1.getNumRows(); i++) { if (m_colL1.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName().toUpperCase(), "general", "STRING", m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL2.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL2.getNumRows(); i++) { if (m_colL2.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName().toUpperCase(), "general", "STRING", m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL3.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL3.getNumRows(); i++) { if (m_colL3.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName().toUpperCase(), "general", "STRING", m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL1.booleanValue()) { for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) { Row rowl1 = sheet.createRow(nrow++); for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) { if (m_colL1.getAbsRow(CL1).getWillShow()) JUtil.DatoXLS(cellStyles, rowl1, CL1, m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()), m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(), m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request); } // Nivel 2 if (m_bSelectL2.booleanValue()) { for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) { Row rowl2 = sheet.createRow(nrow++); for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) { if (m_colL2.getAbsRow(CL2).getWillShow()) JUtil.DatoXLS(cellStyles, rowl2, CL2, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2) .getSTS(m_colL2.getAbsRow(CL2).getColName()), m_colL2.getAbsRow(CL2).getFormat(), m_colL2.getAbsRow(CL2).getBindDataType(), m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request); } // Nivel 3 if (m_bSelectL3.booleanValue()) { for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getNumRows(); RL3++) { Row rowl3 = sheet.createRow(nrow++); for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) { if (m_colL3.getAbsRow(CL3).getWillShow()) JUtil.DatoXLS(cellStyles, rowl3, CL3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()), m_colL3.getAbsRow(CL3).getFormat(), m_colL3.getAbsRow(CL3).getBindDataType(), m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request); } } if (m_bComputeL3.booleanValue()) { for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getNumRows(); RC3++) { Row rowc3 = sheet.createRow(nrow++); for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) { if (m_colCL3.getAbsRow(CC3).getWillShow()) JUtil.DatoXLS(cellStyles, rowc3, CC3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getAbsRow(RC3) .getSTS(m_colCL3.getAbsRow(CC3).getColName()), m_colCL3.getAbsRow(CC3).getFormat(), m_colCL3.getAbsRow(CC3).getBindDataType(), m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL3 } // Fin SI L3 } if (m_bComputeL2.booleanValue()) { for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) { Row rowc2 = sheet.createRow(nrow++); for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) { if (m_colCL2.getAbsRow(CC2).getWillShow()) JUtil.DatoXLS(cellStyles, rowc2, CC2, m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2) .getSTS(m_colCL2.getAbsRow(CC2).getColName()), m_colCL2.getAbsRow(CC2).getFormat(), m_colCL2.getAbsRow(CC2).getBindDataType(), m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL2 } // Fin SI L2 } if (m_bComputeL1.booleanValue()) { for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) { Row rowc1 = sheet.createRow(nrow++); for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) { if (m_colCL1.getAbsRow(CC1).getWillShow()) JUtil.DatoXLS(cellStyles, rowc1, CC1, m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()), m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(), m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL1 } // Fin SI L1 int colsmer; if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL1.getNumRows() - 1; else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL2.getNumRows() - 1; else colsmer = m_colL3.getNumRows() - 1; sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer))); }
From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java
License:Apache License
/** * Initialisation des styles de cellule/* w w w . j av a2 s .c o m*/ */ protected void initStyles() { CellStyle defaultStyle = workbook.createCellStyle(); defaultStyle.setFont(getFont(FONT_NORMAL_NAME)); setStyleFillForegroundColor(defaultStyle, colorRegistry, HSSFColor.WHITE.index); defaultStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); defaultStyle.setBorderBottom(CellStyle.BORDER_THIN); setStyleBottomBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderLeft(CellStyle.BORDER_THIN); setStyleLeftBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderRight(CellStyle.BORDER_THIN); setStyleRightBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setBorderTop(CellStyle.BORDER_THIN); setStyleTopBorderColor(defaultStyle, colorRegistry, BORDER_COLOR_INDEX); defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultStyle.setWrapText(true); registerStyle(STYLE_DEFAULT_NAME, defaultStyle); CellStyle styleHeader = workbook.createCellStyle(); styleHeader.setAlignment(CellStyle.ALIGN_CENTER); styleHeader.setFont(getFont(FONT_HEADER_NAME)); setStyleFillForegroundColor(styleHeader, colorRegistry, HEADER_BACKGROUND_COLOR_INDEX); styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND); styleHeader.setBorderBottom(CellStyle.BORDER_THIN); setStyleBottomBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderLeft(CellStyle.BORDER_THIN); setStyleLeftBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderRight(CellStyle.BORDER_THIN); setStyleRightBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setBorderTop(CellStyle.BORDER_THIN); setStyleTopBorderColor(styleHeader, colorRegistry, BORDER_COLOR_INDEX); styleHeader.setDataFormat((short) 0); styleHeader.setWrapText(true); registerStyle(STYLE_HEADER_NAME, styleHeader); CellStyle styleOdd = cloneStyle(defaultStyle); registerStyle(STYLE_STANDARD_NAME + ROW_ODD_NAME, styleOdd); CellStyle styleEven = cloneStyle(styleOdd); setStyleFillForegroundColor(styleEven, colorRegistry, EVEN_ROW_BACKGROUND_COLOR_INDEX); registerStyle(STYLE_STANDARD_NAME + ROW_EVEN_NAME, styleEven); // styles pour les nombres entiers short integerFormatIndex = dataFormat.getFormat(integerDataFormat); CellStyle styleOddInteger = cloneStyle(styleOdd); styleOddInteger.setAlignment(CellStyle.ALIGN_RIGHT); styleOddInteger.setDataFormat(integerFormatIndex); registerStyle(STYLE_INTEGER_NAME + ROW_ODD_NAME, styleOddInteger); CellStyle styleEvenInteger = cloneStyle(styleEven); styleEvenInteger.setAlignment(CellStyle.ALIGN_RIGHT); styleEvenInteger.setDataFormat(integerFormatIndex); registerStyle(STYLE_INTEGER_NAME + ROW_EVEN_NAME, styleEvenInteger); // styles pour les nombres dcimaux short decimalFormatIndex = dataFormat.getFormat(decimalDataFormat); CellStyle styleOddDecimal = cloneStyle(styleOdd); styleOddDecimal.setAlignment(CellStyle.ALIGN_RIGHT); styleOddDecimal.setDataFormat(decimalFormatIndex); registerStyle(STYLE_DECIMAL_NAME + ROW_ODD_NAME, styleOddDecimal); CellStyle styleEvenDecimal = cloneStyle(styleEven); styleEvenDecimal.setAlignment(CellStyle.ALIGN_RIGHT); styleEvenDecimal.setDataFormat(decimalFormatIndex); registerStyle(STYLE_DECIMAL_NAME + ROW_EVEN_NAME, styleEvenDecimal); // styles pour les dates short dateFormatIndex = dataFormat.getFormat(dateDataFormat); CellStyle styleOddDate = cloneStyle(styleOdd); styleOddDate.setDataFormat(dateFormatIndex); registerStyle(STYLE_DATE_NAME + ROW_ODD_NAME, styleOddDate); CellStyle styleEvenDate = cloneStyle(styleEven); styleEvenDate.setDataFormat(dateFormatIndex); registerStyle(STYLE_DATE_NAME + ROW_EVEN_NAME, styleEvenDate); // styles pour les dates avec heure short dateTimeFormatIndex = dataFormat.getFormat(dateTimeDataFormat); CellStyle styleOddDateTime = cloneStyle(styleOdd); styleOddDateTime.setDataFormat(dateTimeFormatIndex); registerStyle(STYLE_DATE_TIME_NAME + ROW_ODD_NAME, styleOddDateTime); CellStyle styleEvenDateTime = cloneStyle(styleEven); styleEvenDateTime.setDataFormat(dateTimeFormatIndex); registerStyle(STYLE_DATE_TIME_NAME + ROW_EVEN_NAME, styleEvenDateTime); // styles pour les pourcentages short percentFormatIndex = dataFormat.getFormat(percentDataFormat); CellStyle styleOddPercent = cloneStyle(styleOdd); styleOddPercent.setDataFormat(percentFormatIndex); registerStyle(STYLE_PERCENT_NAME + ROW_ODD_NAME, styleOddPercent); CellStyle styleEvenPercent = cloneStyle(styleEven); styleEvenPercent.setDataFormat(percentFormatIndex); registerStyle(STYLE_PERCENT_NAME + ROW_EVEN_NAME, styleEvenPercent); short percentRelativeFormatIndex = dataFormat.getFormat(percentRelativeDataFormat); CellStyle styleOddPercentRelative = cloneStyle(styleOdd); styleOddPercentRelative.setDataFormat(percentRelativeFormatIndex); registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_ODD_NAME, styleOddPercentRelative); CellStyle styleEvenPercentRelative = cloneStyle(styleEven); styleEvenPercentRelative.setDataFormat(percentRelativeFormatIndex); registerStyle(STYLE_PERCENT_RELATIVE_NAME + ROW_EVEN_NAME, styleEvenPercentRelative); // styles pour les liens CellStyle styleOddLink = cloneStyle(styleOdd); styleOddLink.setFont(getFont(FONT_LINK_NAME)); registerStyle(STYLE_LINK_NAME + ROW_ODD_NAME, styleOddLink); CellStyle styleEvenLink = cloneStyle(styleEven); styleEvenLink.setFont(getFont(FONT_LINK_NAME)); registerStyle(STYLE_LINK_NAME + ROW_EVEN_NAME, styleEvenLink); // styles pour les tailles de fichiers short fileSizeFormatIndex = dataFormat.getFormat(fileSizeDataFormat); CellStyle styleOddFileSize = cloneStyle(styleOdd); styleOddFileSize.setDataFormat(fileSizeFormatIndex); registerStyle(STYLE_FILE_SIZE_NAME + ROW_ODD_NAME, styleOddFileSize); CellStyle styleEvenFileSize = cloneStyle(styleEven); styleEvenFileSize.setDataFormat(fileSizeFormatIndex); registerStyle(STYLE_FILE_SIZE_NAME + ROW_EVEN_NAME, styleEvenFileSize); }
From source file:fsi_admin.reportes.JReportesDlg.java
License:Open Source License
public void generarArchivoXLS(HttpServletRequest request, HttpServletResponse response, Workbook wb) throws ServletException, IOException { JReportesSet m_RepSet = (JReportesSet) request.getAttribute("m_RepSet"); JReportesLevel1 m_setL1 = (JReportesLevel1) request.getAttribute("m_setL1"); JReportesCompL1Set m_setCL1 = (JReportesCompL1Set) request.getAttribute("m_setCL1"); Boolean m_bSelectL1 = (Boolean) request.getAttribute("m_bSelectL1"); Boolean m_bSelectL2 = (Boolean) request.getAttribute("m_bSelectL2"); Boolean m_bSelectL3 = (Boolean) request.getAttribute("m_bSelectL3"); Boolean m_bComputeL1 = (Boolean) request.getAttribute("m_bComputeL1"); Boolean m_bComputeL2 = (Boolean) request.getAttribute("m_bComputeL2"); Boolean m_bComputeL3 = (Boolean) request.getAttribute("m_bComputeL3"); JReportesBind3Set m_colL1 = (JReportesBind3Set) request.getAttribute("m_colL1"); JReportesBind3Set m_colL2 = (JReportesBind3Set) request.getAttribute("m_colL2"); JReportesBind3Set m_colL3 = (JReportesBind3Set) request.getAttribute("m_colL3"); JReportesBind3Set m_colCL1 = (JReportesBind3Set) request.getAttribute("m_colCL1"); JReportesBind3Set m_colCL2 = (JReportesBind3Set) request.getAttribute("m_colCL2"); JReportesBind3Set m_colCL3 = (JReportesBind3Set) request.getAttribute("m_colCL3"); String fsi_filtro = (String) request.getAttribute("fsi_filtro"); Sheet sheet = wb.createSheet("reporte " + Integer.toString(m_RepSet.getAbsRow(0).getID_Report())); short nrow = 0; Row row = sheet.createRow(nrow++);/* www. j a v a 2 s. c o m*/ Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.DARK_BLUE.index); Cell cell = row.createCell(0); cell.setCellValue(m_RepSet.getAbsRow(0).getDescription() + " " + fsi_filtro); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); cellStyle.setFont(font); cell.setCellStyle(cellStyle); JRepCellStyles cellStyles = new JRepCellStyles(wb); if (m_bSelectL1.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL1.getNumRows(); i++) { if (m_colL1.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL1.getAbsRow(i).getColName(), "general", "STRING", m_colL1.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll1.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll1.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL2.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL2.getNumRows(); i++) { if (m_colL2.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL2.getAbsRow(i).getColName(), "general", "STRING", m_colL2.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll2.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll2.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL3.booleanValue()) { Row rowl = sheet.createRow(nrow++); for (int i = 0; i < m_colL3.getNumRows(); i++) { if (m_colL3.getAbsRow(i).getWillShow()) { JUtil.DatoXLS(cellStyles, rowl, i, m_colL3.getAbsRow(i).getColName(), "general", "STRING", m_colL3.getAbsRow(i).getAlinHor(), "encabezado", "fenc", request); //celll3.getCellStyle().setBorderTop(CellStyle.BORDER_THIN); //celll3.getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); } } } if (m_bSelectL1.booleanValue()) { for (int RL1 = 0; RL1 < m_setL1.getNumRows(); RL1++) { Row rowl1 = sheet.createRow(nrow++); for (int CL1 = 0; CL1 < m_colL1.getNumRows(); CL1++) { if (m_colL1.getAbsRow(CL1).getWillShow()) JUtil.DatoXLS(cellStyles, rowl1, CL1, m_setL1.getAbsRow(RL1).getSTS(m_colL1.getAbsRow(CL1).getColName()), m_colL1.getAbsRow(CL1).getFormat(), m_colL1.getAbsRow(CL1).getBindDataType(), m_colL1.getAbsRow(CL1).getAlinHor(), null, "fnorm", request); } // Nivel 2 if (m_bSelectL2.booleanValue()) { for (int RL2 = 0; RL2 < m_setL1.getAbsRow(RL1).getSetL2().getNumRows(); RL2++) { Row rowl2 = sheet.createRow(nrow++); for (int CL2 = 0; CL2 < m_colL2.getNumRows(); CL2++) { if (m_colL2.getAbsRow(CL2).getWillShow()) JUtil.DatoXLS(cellStyles, rowl2, CL2, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2) .getSTS(m_colL2.getAbsRow(CL2).getColName()), m_colL2.getAbsRow(CL2).getFormat(), m_colL2.getAbsRow(CL2).getBindDataType(), m_colL2.getAbsRow(CL2).getAlinHor(), null, "fnorm", request); } // Nivel 3 if (m_bSelectL3.booleanValue()) { for (int RL3 = 0; RL3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getNumRows(); RL3++) { Row rowl3 = sheet.createRow(nrow++); for (int CL3 = 0; CL3 < m_colL3.getNumRows(); CL3++) { if (m_colL3.getAbsRow(CL3).getWillShow()) JUtil.DatoXLS(cellStyles, rowl3, CL3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetL3() .getAbsRow(RL3).getSTS(m_colL3.getAbsRow(CL3).getColName()), m_colL3.getAbsRow(CL3).getFormat(), m_colL3.getAbsRow(CL3).getBindDataType(), m_colL3.getAbsRow(CL3).getAlinHor(), null, "fnorm", request); } } if (m_bComputeL3.booleanValue()) { for (int RC3 = 0; RC3 < m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getNumRows(); RC3++) { Row rowc3 = sheet.createRow(nrow++); for (int CC3 = 0; CC3 < m_colCL3.getNumRows(); CC3++) { if (m_colCL3.getAbsRow(CC3).getWillShow()) JUtil.DatoXLS(cellStyles, rowc3, CC3, m_setL1.getAbsRow(RL1).getSetL2().getAbsRow(RL2).getSetCL3() .getAbsRow(RC3) .getSTS(m_colCL3.getAbsRow(CC3).getColName()), m_colCL3.getAbsRow(CC3).getFormat(), m_colCL3.getAbsRow(CC3).getBindDataType(), m_colCL3.getAbsRow(CC3).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL3 } // Fin SI L3 } if (m_bComputeL2.booleanValue()) { for (int RC2 = 0; RC2 < m_setL1.getAbsRow(RL1).getSetCL2().getNumRows(); RC2++) { Row rowc2 = sheet.createRow(nrow++); for (int CC2 = 0; CC2 < m_colCL2.getNumRows(); CC2++) { if (m_colCL2.getAbsRow(CC2).getWillShow()) JUtil.DatoXLS(cellStyles, rowc2, CC2, m_setL1.getAbsRow(RL1).getSetCL2().getAbsRow(RC2) .getSTS(m_colCL2.getAbsRow(CC2).getColName()), m_colCL2.getAbsRow(CC2).getFormat(), m_colCL2.getAbsRow(CC2).getBindDataType(), m_colCL2.getAbsRow(CC2).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL2 } // Fin SI L2 } if (m_bComputeL1.booleanValue()) { for (int RC1 = 0; RC1 < m_setCL1.getNumRows(); RC1++) { Row rowc1 = sheet.createRow(nrow++); for (int CC1 = 0; CC1 < m_colCL1.getNumRows(); CC1++) { if (m_colCL1.getAbsRow(CC1).getWillShow()) JUtil.DatoXLS(cellStyles, rowc1, CC1, m_setCL1.getAbsRow(RC1).getSTS(m_colCL1.getAbsRow(CC1).getColName()), m_colCL1.getAbsRow(CC1).getFormat(), m_colCL1.getAbsRow(CC1).getBindDataType(), m_colCL1.getAbsRow(CC1).getAlinHor(), "agregado", "fenc", request); } } } // Fin SI CL1 } // Fin SI L1 int colsmer; if (m_colL1.getNumRows() > m_colL2.getNumRows() && m_colL1.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL1.getNumRows() - 1; else if (m_colL2.getNumRows() > m_colL1.getNumRows() && m_colL2.getNumRows() > m_colL3.getNumRows()) colsmer = m_colL2.getNumRows() - 1; else colsmer = m_colL3.getNumRows() - 1; sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (colsmer == -1 ? 0 : colsmer))); }
From source file:GapAnalysis.gapAnalysis.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); try {//w w w . j a v a 2 s . c o m ArrayList keyal = new ArrayList(); ArrayList countyal = new ArrayList(); ArrayList scountyal = new ArrayList(); ArrayList facilal = new ArrayList(); ArrayList yearmonthal = new ArrayList(); ArrayList monthal = new ArrayList(); ArrayList sectional = new ArrayList(); XSSFWorkbook wb; String periodname = ""; String allpath = getServletContext().getRealPath("/Gapanalysis.xlsm"); System.out.println(allpath); XSSFWorkbook workbook; String mydrive = allpath.substring(0, 1); // wb = new XSSFWorkbook( OPCPackage.open(allpath) ); Date da = new Date(); String dat2 = da.toString().replace(" ", "_"); dat2 = dat2.toString().replace(":", "_"); String np = mydrive + ":\\APHIAPLUS\\InternalSystem\\Gapanalysis" + dat2 + ".xlsm"; System.out.println("path:: " + np); // String desteepath1 = getServletContext().getRealPath("/Females 15to24.xlsm"); String sr = getServletContext().getRealPath("/Gapanalysis.xlsm"); //check if file exists //first time , it should create those folders that host the macro file File f = new File(np); if (!f.exists() && !f.isDirectory()) { /* do something */ copytemplates ct = new copytemplates(); ct.transfermacros(sr, np); //rem np is the destination file name System.out.println("Copying macro template first time .."); } else //copy the file alone { copytemplates ct = new copytemplates(); //copy the agebased file only ct.copymacros(sr, np); } String filepth = np; File allpathfile = new File(filepth); OPCPackage pkg = OPCPackage.open(allpathfile); pathtodelete = filepth; wb = new XSSFWorkbook(pkg); dbConn conn = new dbConn(); HashMap<String, String> rawdatahashmap = new HashMap<String, String>(); int year = 0; String yearval = ""; int prevyear = 0; String quarter = ""; String yearmonth = ""; String startyearmonth = ""; String endyearmonth = ""; yearval = request.getParameter("year").toString(); System.out.println("YEARVAL" + yearval); year = Integer.parseInt(yearval); prevyear = year - 1; quarter = request.getParameter("quarter"); periodname += yearval + "_"; if (quarter.equals("1")) { startyearmonth = prevyear + "10"; endyearmonth = prevyear + "12"; periodname = prevyear + "_(Oct_Dec)"; } else if (quarter.equals("2")) { startyearmonth = year + "01"; endyearmonth = year + "03"; periodname = yearval + "_(Jan-Mar)"; } else if (quarter.equals("3")) { startyearmonth = year + "04"; endyearmonth = year + "06"; periodname = yearval + "_(Apr_Jun)"; } else if (quarter.equals("4")) { startyearmonth = year + "07"; endyearmonth = year + "09"; periodname = yearval + "_(Jul_Sep)"; } int colsmerging = 6; String Sections[] = { "ART", "HTC", "PMTCT" }; String headers[] = { "County", "Sub-County", "Facility", "Year", "Month" }; String headergsn[] = { "County", "Sub-County", "Facility" }; //if one wants gaps for one service area if (request.getParameterValues("gapsection") != null) { Sections = request.getParameterValues("gapsection"); } //This is the loop that well use to create worksheets for each String period = " 1=1 and Annee=" + yearval + " and yearmonth between " + startyearmonth + " and " + endyearmonth + " "; String gsnperiod = " 1=1 "; //______________________________________________________________________________________ //______________________________________________________________________________________ Font font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle 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); CellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_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_CENTER); CellStyle stylex1 = wb.createCellStyle(); stylex1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex1.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex1.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex1.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex1.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex1.setAlignment(HSSFCellStyle.ALIGN_LEFT); CellStyle stylex2 = wb.createCellStyle(); stylex2.setFillForegroundColor(HSSFColor.SKY_BLUE.index); stylex2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex2.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex2.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex2.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex2.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex2.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex3 = wb.createCellStyle(); stylex3.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); stylex3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex3.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex3.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex3.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex3.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex3.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylex1.setFont(fontx); stylex1.setWrapText(true); stylex2.setFont(fontx); stylex2.setWrapText(true); //================================================== for (int a = 0; a < Sections.length; a++) { int column = 0; int Row = 3; Sheet shet = wb.createSheet(Sections[a]); Row rwx = shet.createRow(2); Row rw1 = null; Row rw2 = null; Row rw = shet.createRow(0); rw.setHeightInPoints(25); Cell cl0 = rw.createCell(0); cl0.setCellValue(Sections[a] + " GAP ANALYSIS"); cl0.setCellStyle(stylex1); //this will depend on the length of the number of elements being checked for (int b = 1; b <= colsmerging; b++) { Cell clx = rw.createCell(b); clx.setCellValue(""); clx.setCellStyle(stylex); } //now go to the database and do a query for each section int determinant = 2; String getqueries = " Select * from gap_analysis where active=1 and section='" + Sections[a] + "' "; conn.rs = conn.st.executeQuery(getqueries); while (conn.rs.next()) { //if an excel sheet exists, then get the row number 1 if (shet.getRow(1) != null) { rw1 = shet.getRow(1); } else { rw1 = shet.createRow(1); rw1.setHeightInPoints(25); } //print blanks before printing real header //for gsns, we only print three columns and no period if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw1.createCell(column + p); cl2.setCellValue(""); cl2.setCellStyle(stylex); shet.setColumnWidth(column + p, 5000); } } determinant++; if (determinant % 2 == 0) { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex3); } else { Cell cl1 = rw1.createCell(column); cl1.setCellValue(conn.rs.getString("rule")); cl1.setCellStyle(stylex2); } //Create the column header if (shet.getRow(2) != null) { rw2 = shet.getRow(2); } else { rw2 = shet.createRow(2); rw2.setHeightInPoints(25); } if (conn.rs.getString("id").equals("1")) { for (int p = 0; p < headergsn.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headergsn[p]); cl2.setCellStyle(stylex); } } else { for (int p = 0; p < headers.length; p++) { Cell cl2 = rw2.createCell(column + p); cl2.setCellValue(headers[p]); cl2.setCellStyle(stylex); } } String currentqry = conn.rs.getString("query"); //process each query as you //pass the necessary period parameters from the interface //rem each query ends with a 'and' if (conn.rs.getString("id").equals("1")) { currentqry += gsnperiod; } else { currentqry += period + " and subpartnera." + Sections[a] + "= 1 "; } System.out.println("" + currentqry); Row = 3; conn.rs1 = conn.st1.executeQuery(currentqry); while (conn.rs1.next()) { if (shet.getRow(Row) != null) { rwx = shet.getRow(Row); } else { rwx = shet.createRow(Row); rwx.setHeightInPoints(25); } Cell cly = rwx.createCell(column); cly.setCellValue(conn.rs1.getString("County")); cly.setCellStyle(style2); Cell cly2 = rwx.createCell(column + 1); cly2.setCellValue(conn.rs1.getString("DistrictNom")); cly2.setCellStyle(style2);//gsn sites do not have a yearmonth Cell cly1 = rwx.createCell(column + 2); cly1.setCellValue(conn.rs1.getString("SubPartnerNom")); cly1.setCellStyle(style2); //if the current list is not inclusive of GSNs if (!conn.rs.getString(1).equals("1")) { Cell cly3 = rwx.createCell(column + 3); cly3.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(0, 4))); cly3.setCellStyle(style2); //the month section Cell cly3x = rwx.createCell(column + 4); cly3x.setCellValue(new Integer(conn.rs1.getString("yearmonth").substring(4))); cly3x.setCellStyle(style2); //my key is a String mykey = Sections[a] + conn.rs1.getString("SubPartnerNom") + "_" + conn.rs1.getString("yearmonth") + "_"; //add all the facilities at this point //ignore the sites in ART since they are static if (!keyal.contains(mykey)) { keyal.add(mykey); countyal.add(conn.rs1.getString("County")); scountyal.add(conn.rs1.getString("DistrictNom")); facilal.add(conn.rs1.getString("SubPartnerNom")); sectional.add(Sections[a]); yearmonthal.add(conn.rs1.getString("yearmonth")); monthal.add(conn.rs1.getString("yearmonth").substring(4)); } } Row++; } if (conn.rs.getString(1).equals("1")) { column += 3; } else { column += 5; } if (conn.rs.getString("id").equals("1")) { shet.addMergedRegion(new CellRangeAddress(1, 1, 0, column - 1)); } else { shet.addMergedRegion(new CellRangeAddress(1, 1, column - 5, column - 1)); } } //end of all queries per section shet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1)); } // end of sheets loop //create a new sheet //county subcounty facility yearmonth section Sheet shet = wb.getSheet("Sheet1"); Row rw = shet.createRow(0); Cell cl0 = rw.createCell(0); cl0.setCellValue("county"); cl0.setCellStyle(stylex1); Cell cl1 = rw.createCell(1); cl1.setCellValue("subcounty"); cl1.setCellStyle(stylex1); Cell cl2 = rw.createCell(2); cl2.setCellValue("facility"); cl2.setCellStyle(stylex1); Cell cl3 = rw.createCell(3); cl3.setCellValue("year"); cl3.setCellStyle(stylex1); Cell cl4 = rw.createCell(4); cl4.setCellValue("month"); cl4.setCellStyle(stylex1); Cell cl5 = rw.createCell(5); cl5.setCellValue("section"); cl5.setCellStyle(stylex1); for (int q = 0; q < keyal.size(); q++) { Row rwx = shet.createRow(q + 1); Cell cl01 = rwx.createCell(0); cl01.setCellValue(countyal.get(q).toString()); cl01.setCellStyle(style2); Cell cl11 = rwx.createCell(1); cl11.setCellValue(scountyal.get(q).toString()); cl11.setCellStyle(style2); Cell cl21 = rwx.createCell(2); cl21.setCellValue(facilal.get(q).toString()); cl21.setCellStyle(style2); Cell cl31 = rwx.createCell(3); cl31.setCellValue(new Integer(yearmonthal.get(q).toString().substring(0, 4))); cl31.setCellStyle(style2); Cell cl41 = rwx.createCell(4); cl41.setCellValue(new Integer(monthal.get(q).toString())); cl41.setCellStyle(style2); Cell cl51 = rwx.createCell(5); cl51.setCellValue(sectional.get(q).toString()); cl51.setCellStyle(style2); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); 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=GapAnalysis_For" + periodname + "_Generatted_On_" + createdOn + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); outStream.close(); pkg.close(); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st != null) { conn.st.close(); } File file = new File(pathtodelete); System.out.println("path: 2" + pathtodelete); if (file.delete()) { System.out.println(file.getName() + " is deleted!"); } else { System.out.println("Delete operation failed."); } } catch (SQLException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(gapAnalysis.class.getName()).log(Level.SEVERE, null, ex); } finally { } }
From source file:guru.qas.martini.report.DefaultState.java
License:Apache License
@Override public void updateSuites(Sheet sheet) { int lastRowNum = sheet.getLastRowNum(); Row row = sheet.createRow(0 == lastRowNum ? 0 : lastRowNum + 1); row.createCell(0, CellType.STRING).setCellValue("ID"); row.createCell(1, CellType.STRING).setCellValue("Date"); row.createCell(2, CellType.STRING).setCellValue("Name"); row.createCell(3, CellType.STRING).setCellValue("Hostname"); row.createCell(4, CellType.STRING).setCellValue("IP"); row.createCell(5, CellType.STRING).setCellValue("Username"); row.createCell(6, CellType.STRING).setCellValue("Profiles"); row.createCell(7, CellType.STRING).setCellValue("Environment Variables"); for (Map.Entry<String, JsonObject> mapEntry : suites.entrySet()) { row = sheet.createRow(sheet.getLastRowNum() + 1); String id = mapEntry.getKey(); row.createCell(0, CellType.STRING).setCellValue(id); JsonObject suite = mapEntry.getValue(); JsonPrimitive primitive = suite.getAsJsonPrimitive("startTimestamp"); Long timestamp = null == primitive ? null : primitive.getAsLong(); Cell cell = row.createCell(1);/*from w w w . j a v a2s . c om*/ if (null != timestamp) { Workbook workbook = sheet.getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); CreationHelper creationHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm")); cellStyle.setVerticalAlignment(VerticalAlignment.TOP); cell.setCellValue(new Date(timestamp)); cell.setCellStyle(cellStyle); } cell = row.createCell(2); primitive = suite.getAsJsonPrimitive("name"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(3); JsonObject host = suite.getAsJsonObject("host"); primitive = null == host ? null : host.getAsJsonPrimitive("name"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(4); primitive = null == host ? null : host.getAsJsonPrimitive("ip"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(5); primitive = null == host ? null : host.getAsJsonPrimitive("username"); cell.setCellValue(null == primitive ? "" : primitive.getAsString()); cell = row.createCell(6); JsonArray array = suite.getAsJsonArray("profiles"); List<String> profiles = Lists.newArrayList(); if (null != array) { int size = array.size(); for (int i = 0; i < size; i++) { JsonElement element = array.get(i); String profile = null == element ? null : element.getAsString(); profiles.add(profile); } String profilesValue = Joiner.on('\n').skipNulls().join(profiles); cell.setCellValue(profilesValue); } cell = row.createCell(7); JsonObject environmentVariables = suite.getAsJsonObject("environment"); Map<String, String> index = new TreeMap<>(); if (null != environmentVariables) { Set<Map.Entry<String, JsonElement>> entries = environmentVariables.entrySet(); for (Map.Entry<String, JsonElement> environmentEntry : entries) { String key = environmentEntry.getKey(); JsonElement element = environmentEntry.getValue(); String value = null == element ? "" : element.getAsString(); index.put(key, value); } String variablesValue = Joiner.on('\n').withKeyValueSeparator('=').useForNull("").join(index); cell.setCellValue(variablesValue); } } for (int i = 0; i < 8; i++) { sheet.autoSizeColumn(i, false); } }
From source file:guru.qas.martini.report.DefaultTraceabilityMatrix.java
License:Apache License
protected void addResult(State state, Sheet sheet, JsonObject object) { int index = sheet.getLastRowNum(); Row row = sheet.createRow(index + 1); for (int i = 0; i < columns.size(); i++) { Cell cell = row.createCell(i);//from w w w .j av a 2 s. c o m CellStyle cellStyle = cell.getCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.TOP); TraceabilityColumn column = columns.get(i); column.addResult(state, cell, object); } }