List of usage examples for org.apache.poi.hssf.usermodel HSSFRow setHeightInPoints
@Override public void setHeightInPoints(float height)
From source file:FILING.cboreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//w w w.java 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 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 w w .j a va2s. 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 = ""; 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.// ww 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:gov.nih.nci.evs.reportwriter.formatter.AsciiToExcelFormatter.java
License:BSD License
public Boolean convert(String textfile, String delimiter, String outfile) throws Exception { Vector<String> headings = getColumnHeadings(textfile, delimiter); Vector<Integer> maxChars = getColumnMaxChars(textfile, delimiter); // Note: Special Case for CDISC STDM Terminology report. int extensible_col = -1; if (_specialCases_CDISC) extensible_col = findColumnIndicator(headings, "Extensible"); int heading_height_multiplier = 1; for (int i = 0; i < maxChars.size(); i++) { String heading = (String) headings.elementAt(i); int maxCellLen = maxChars.elementAt(i); int maxTokenLen = getMaxTokenLength(heading); if (maxTokenLen > maxCellLen) { maxCellLen = maxTokenLen;//from w ww.j av a2 s . co m maxChars.setElementAt(new Integer(maxCellLen), i); } if (maxCellLen < MAX_CODE_WIDTH) { Vector<String> tokens = parseData(heading, " "); if (tokens.size() > heading_height_multiplier) heading_height_multiplier = tokens.size(); } } Boolean[] a = findWrappedColumns(textfile, delimiter, MAX_WIDTH); // Note: The max column number allowed in an Excel spreadsheet is 256 int[] b = new int[255]; for (int i = 0; i < 255; i++) { b[i] = 0; } File file = new File(textfile); String absolutePath = file.getAbsolutePath(); _logger.debug("Absolute Path: " + absolutePath); String filename = file.getName(); _logger.debug("filename: " + filename); int m = filename.indexOf("."); String workSheetLabel = filename.substring(0, m); int n = workSheetLabel.indexOf("__"); workSheetLabel = workSheetLabel.substring(0, n); _logger.debug("workSheetLabel: " + workSheetLabel); if (workSheetLabel.compareTo("") == 0) return Boolean.FALSE; String pathName = file.getPath(); _logger.debug("Path: " + pathName); BufferedReader br = getBufferReader(textfile); FileOutputStream fout = new FileOutputStream(outfile); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet ws = wb.createSheet(workSheetLabel); HSSFCellStyle toprow = wb.createCellStyle(); HSSFCellStyle highlightedrow = wb.createCellStyle(); HSSFCellStyle cs = wb.createCellStyle(); // Note: GF20673 shade top row HSSFFont font = wb.createFont(); font.setColor(HSSFColor.BLACK.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); toprow.setFont(font); if (extensible_col == -1) { toprow.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); } else { //toprow.setFillForegroundColor(HSSFColor.YELLOW.index); toprow.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); } toprow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); toprow.setAlignment(HSSFCellStyle.VERTICAL_CENTER); toprow.setWrapText(true); highlightedrow.setFont(font); //highlightedrow.setFillForegroundColor(HSSFColor.SKY_BLUE.index); //highlightedrow.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); highlightedrow.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); highlightedrow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); highlightedrow.setAlignment(HSSFCellStyle.VERTICAL_CENTER); // highlightedrow.setWrapText(true); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); cs.setAlignment(HSSFCellStyle.VERTICAL_TOP); HSSFRow wr = null; int rownum = 0; // int baseline_height = 15; int baseline_height = 12; while (true) { String line = br.readLine(); if (line == null) break; // line = line.trim(); Note: 090512 first value could be empty if (line.length() <= 0) continue; Vector<String> v = parseData(line, delimiter); wr = ws.createRow(rownum); // wr.setHeightInPoints(60); if (rownum == 0) { wr.setHeightInPoints(baseline_height * heading_height_multiplier); } else { wr.setHeightInPoints(baseline_height); if (ADJUST_HEIGHT) { int num_lines = getHeightInPoints(v, ADJUST_HEIGHT, MAX_CELL_WIDTH); wr.setHeightInPoints(baseline_height * num_lines); } } // Note: Special Case for CDISC STDM Terminology report. boolean highlight_row = false; if (_specialCases_CDISC) highlight_row = extensible_col != -1 && v.elementAt(extensible_col).trim().length() > 0; for (int i = 0; i < v.size(); i++) { HSSFCell wc = wr.createCell(i); if (rownum == 0) { wc.setCellStyle(toprow); } else if (a[i].equals(Boolean.TRUE)) { wc.setCellStyle(cs); wc.setCellType(HSSFCell.CELL_TYPE_STRING); if (highlight_row) wc.setCellStyle(highlightedrow); } else { if (highlight_row) wc.setCellStyle(highlightedrow); } String s = (String) v.elementAt(i); s = s.trim(); if (s.length() > b[i]) { b[i] = s.length(); } if (s.equals("")) { s = null; } wc.setCellValue(s); if (_ncitCodeColumns.contains(i) && rownum > 0 && s != null && s.length() > 0) { try { wc.setCellFormula("HYPERLINK(\"" + getNCItCodeUrl(s) + "\", \"" + s + "\")"); } catch (Exception e) { ExceptionUtils.print(_logger, e, "The following string is too large to be a " + "valid NCIt code (" + filename + "): " + s); } } } rownum++; } br.close(); for (int i = 0; i < 255; i++) { if (b[i] != 0) { int multiplier = b[i]; if (i < headings.size()) { Integer int_obj = (Integer) maxChars.elementAt(i); multiplier = int_obj.intValue(); } // Note(GF20673): 315 is the magic number for this font and size int colWidth = multiplier * 315; // Fields like definition run long, some sanity required if (colWidth > 20000) { colWidth = 20000; } // _logger.debug("Calculated column width " + i + ": " + // colWidth); ws.setColumnWidth(i, colWidth); } } // Note(GF20673): Freeze top row ws.createFreezePane(0, 1, 0, 1); wb.write(fout); fout.close(); return Boolean.TRUE; }
From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
@Override public void generateXSL(String user, String startDate, String endDate) { try {/*from www . ja v a 2 s .com*/ HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet("Users_Sheet1"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("SMS OUT REPORT"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); String[] titles = { "Mobile", "Source Address", "Message", "Time Spent", "Last Update", "User", "Status", "No. of SMS" }; HSSFRow row = sheet.createRow(1); row.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = row.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } List<SMSOut> exportSMSOutReport = (List) userSMSOutReport(user, startDate, endDate).get("result"); int rowNum = 2; for (SMSOut anSMS : exportSMSOutReport) { row = sheet.createRow(rowNum); row.createCell(0).setCellValue(anSMS.getDestinationAddr()); row.createCell(1).setCellValue(anSMS.getSourceAddr()); row.createCell(2).setCellValue(anSMS.getMessagePayload()); row.createCell(3).setCellValue(anSMS.getTimeSubmitted()); row.createCell(4).setCellValue(anSMS.getTimeProcessed()); row.createCell(5).setCellValue(anSMS.getUser()); row.createCell(6).setCellValue(anSMS.getRealStatus()); row.createCell(7).setCellValue(anSMS.getSmsCount()); rowNum++; } sheet.setColumnWidth(0, 20 * 256); //30 characters wide sheet.setColumnWidth(1, 15 * 256); for (int i = 2; i < 5; i++) { sheet.setColumnWidth(i, 20 * 256); //6 characters wide } sheet.setColumnWidth(5, 10 * 256); sheet.setColumnWidth(6, 20 * 256); sheet.setColumnWidth(7, 10 * 256); //10 characters wide FacesContext context = FacesContext.getCurrentInstance(); HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse(); res.setContentType("application/vnd.ms-excel"); res.setHeader("Content-disposition", "attachment;filename=mydata.xlsx"); ServletOutputStream out = res.getOutputStream(); wb.write(out); out.flush(); out.close(); FacesContext.getCurrentInstance().responseComplete(); } catch (Exception e) { e.printStackTrace(); } }
From source file:model.Reports.java
public void generateXSL(List<SmsOutUserBean> smsOutUserBeans, int count) { try {//from w w w . j a va 2 s . c o m HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet("Users_Sheet1"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("SMS OUT REPORT"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); String[] titles = { "Mobile", "Source Address", "Message", "Time Sent", "Last Update", "User", "Status", "Number of SMS", }; HSSFRow row = sheet.createRow(1); row.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = row.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rowNum = 2; for (SmsOutUserBean bean : smsOutUserBeans) { row = sheet.createRow(rowNum); row.createCell(0).setCellValue(bean.getSmsOutModel().getDestinationAddress()); row.createCell(1).setCellValue(bean.getSmsOutModel().getSourceAddress()); row.createCell(2).setCellValue(bean.getSmsOutModel().getMessagePayload()); row.createCell(3) .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeSubmitted())); row.createCell(4) .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeProcessed())); row.createCell(5).setCellValue(bean.getUserBean().getUsername()); row.createCell(6).setCellValue(bean.getSmsOutModel().getRealStatus()); row.createCell(7).setCellValue(bean.getSmsOutModel().getSmsCount()); rowNum++; } sheet.setColumnWidth(0, 20 * 256); //30 characters wide sheet.setColumnWidth(1, 15 * 256); for (int i = 2; i < 5; i++) { sheet.setColumnWidth(i, 20 * 256); //6 characters wide } sheet.setColumnWidth(5, 10 * 256); sheet.setColumnWidth(6, 20 * 256); sheet.setColumnWidth(7, 10 * 256); //10 characters wide FacesContext context = FacesContext.getCurrentInstance(); HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse(); res.setContentType("application/vnd.ms-excel"); res.setHeader("Content-disposition", "attachment;filename=mydata.xls"); ServletOutputStream out = res.getOutputStream(); wb.write(out); out.flush(); out.close(); FacesContext.getCurrentInstance().responseComplete(); } catch (Exception e) { e.printStackTrace(); } }
From source file:net.sf.jasperreports.engine.export.JRXlsMetadataExporter.java
License:Open Source License
protected void setRowHeight(HSSFRow row) { Integer rowHeight = (Integer) currentRow.get(CURRENT_ROW_HEIGHT); if (row != null && rowHeight != null && rowHeight < Integer.MAX_VALUE) { row.setHeightInPoints((Integer) currentRow.get(CURRENT_ROW_HEIGHT)); }//from w ww.jav a 2s . c o m }
From source file:org.fourfive.engine2.util.ReportServletWorker.java
License:Mozilla Public License
public void writeExcelToOut(ReportData data, ServletOutputStream out, ArrayList paramList, EasyReport component, HttpServletRequest req) throws Exception { HttpSession session = req.getSession(); HSSFWorkbook wb = new HSSFWorkbook(); ExcelUtil excelUtil = new ExcelUtil(wb); excelUtil.setSession(session);/*from w ww . j a v a 2s .c o m*/ // Murali C HashMap dataformat = null; ArrayList crosstabColumnIds = null; // // ArrayList nonCurrItems = ReportDataUtil.getNotCurrency(data,component); Columns columns = component.getDefinition().getSelect().getColumns(); Hashtable ht = com.manthan.promax.report.ReportUtil.getLabels(session); String reportType = ReportDataUtil.getReportType(component); //component.getTitle().getValue() //excelUtil.getSheetTitle(component.getTitle().getValue()) //String title = (String) com.manthan.promax.report.ReportUtil.getLabels(session).get(component.getTitle().getValue()); String title = component.getTitle().getValue(); HSSFSheet sheet1 = wb.createSheet(excelUtil.getSheetTitle(title)); excelUtil.setSheetStyle(); excelUtil.isExpanded(true); excelUtil.getLogo(session.getServletContext()); int rowStart = 4; int colStart = 0; HSSFRow titleRow = sheet1.createRow((short) (rowStart)); HSSFCell titleCell = titleRow.createCell((short) (colStart)); titleCell.setCellStyle(excelUtil.getTitleStyle()); titleCell.setCellValue(new HSSFRichTextString(data.getTitle())); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); rowStart = rowStart + 2; HSSFRow selectionRow = sheet1.createRow((short) rowStart); selectionRow.setHeightInPoints(40); HSSFCell selectionCell = selectionRow.createCell((short) colStart); selectionCell.setCellStyle(excelUtil.getSelectionStyle()); selectionCell.setCellValue(new HSSFRichTextString(excelUtil.getCurrentSelection(req).toString())); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); rowStart = rowStart + 1; int colWidth = 10; excelUtil.setPrintAndPageSettings(rowStart + 1, rowStart + 2, title, ((ArrayList) data.getRows().get(0)).size()); excelUtil.setStartWidthColumn(rowStart + 2); ArrayList row = (ArrayList) data.getRows().get(0); ReportCell rcell = null; ArrayList nonVisibleCol = new ArrayList(); for (int j = 0; j < row.size(); j++) { rcell = (ReportCell) row.get(j); if (!rcell.getVisible()) { nonVisibleCol.add(new Integer(j)); } } HSSFCell excel_cell = null; HSSFRow excelRow = sheet1.createRow((short) rowStart); HashMap crossTabDetails = data.getCrossTabReportDetails(); if (reportType != null && reportType.equalsIgnoreCase(DefaultKernel.CROSSTAB_REPORT_TYPE)) { ArrayList columnList = (ArrayList) crossTabDetails.get("COLUMN_LIST"); ArrayList growingColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_DYNAMIC); ArrayList staticColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_STATIC); // Murali C if (crossTabDetails.get("COLUMN_IDS_LIST") != null) { crosstabColumnIds = (ArrayList) crossTabDetails.get("COLUMN_IDS_LIST"); } if (crossTabDetails.get("CROSSTABDATAFORMAT") != null) { dataformat = (HashMap) crossTabDetails.get("CROSSTABDATAFORMAT"); } // int noGrowingColumns = growingColumns.size(); int noStaticColumns = staticColumns.size(); int staticColSpan = noStaticColumns; int dynamicColSpan = noGrowingColumns; for (int colCount = 0; colCount < staticColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount))) { staticColSpan--; } } for (int colCount = 0; colCount < growingColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount + noStaticColumns))) { dynamicColSpan--; } } for (int i = 0; i < columnList.size(); i++) { short cellIndex = (short) ((staticColSpan) + (dynamicColSpan * i)); excel_cell = excelRow.createCell(cellIndex); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); excel_cell.setCellValue(new HSSFRichTextString((String) columnList.get(i))); for (short index = (short) (cellIndex + 1); index < cellIndex + dynamicColSpan; index++) { excel_cell = excelRow.createCell(index); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); } sheet1.addMergedRegion(new Region(rowStart, (short) (cellIndex), rowStart, (short) (cellIndex + dynamicColSpan - 1))); } // we are getting first row for column headers or column titles } rowStart++; sheet1.createFreezePane(2, 10); for (int i = 0; i < data.getRows().size(); i++) { row = (ArrayList) data.getRows().get(i); excelRow = sheet1.createRow((short) (i + rowStart)); if (i == 0) excelRow.setHeightInPoints(45); else excelRow.setHeightInPoints(20); int incr = 0; //String dataInString = null; for (int j = 0; j < row.size(); j++) { ReportCell cell = (ReportCell) row.get(j); if (!cell.getName().startsWith("<img") && cell.getVisible()) { String columnTitle = (String) cell.getName(); String columnTitleFromProp = ""; String datatype = ""; try { // Murali C if (null != component.getDefinition().getReportType() && component.getDefinition().getReportType().equalsIgnoreCase("crosstab")) { datatype = dataformat.get(crosstabColumnIds.get(j).toString()).toString(); } else { datatype = ((Column) component.getDefinition().getSelect().getColumns().getColumn(j)) .getDataFormat(); } double val = Double.parseDouble(cell.getName()); HSSFCell dataCell = excelRow.createCell((short) (j + colStart + incr)); dataCell.setCellValue(val); if (i == 1) { if (datatype.equalsIgnoreCase("currency")) { dataCell.setCellStyle(excelUtil.getTotalStyle(true)); } else if (datatype.equalsIgnoreCase("number")) { dataCell.setCellStyle(excelUtil.getTotalStyle(false)); } } else { if (datatype.equalsIgnoreCase("currency")) { dataCell.setCellStyle(excelUtil.getDataStyleFormat()); } else if (datatype.equalsIgnoreCase("number")) { dataCell.setCellStyle(excelUtil.getDataStyleFormatLess()); } else { dataCell.setCellValue(new HSSFRichTextString(cell.getName())); dataCell.setCellStyle(excelUtil.firstColStyle()); } } //dataInString = ""+val; } catch (NumberFormatException ee) { /*if (i==0) { if (reportType == null || !reportType.equalsIgnoreCase(DefaultKernel.CROSSTAB_REPORT_TYPE)) { if( columns.getColumn(j).getId() != null && null != ht.get(columns.getColumn(j).getId().toLowerCase())) { columnTitleFromProp = (String) (ht.get(columns.getColumn(j).getId().toLowerCase())); }else{ columnTitleFromProp = columnTitle; } }else{ ArrayList idsList = (ArrayList) crossTabDetails.get("COLUMN_IDS_LIST"); if(idsList.get(j)!=null && ht.get(((String)idsList.get(j)).toLowerCase()) != null){ columnTitleFromProp = (String)ht.get(((String)idsList.get(j)).toLowerCase()); }else{ columnTitleFromProp = columnTitle; } } } else { columnTitleFromProp = columnTitle; }*/ columnTitleFromProp = columnTitle; HSSFCell headerCell = excelRow.createCell((short) (j + colStart + incr)); if (i == 1) { headerCell.setCellStyle(excelUtil.getTotalStyle(false)); } else { if (i == 0) headerCell.setCellStyle(excelUtil.getHeaderStyle()); else headerCell.setCellStyle(excelUtil.getDataTextStyle()); } headerCell.setCellValue(new HSSFRichTextString(columnTitleFromProp)); //dataInString = ""+columnTitleFromProp; } } else { incr = incr - 1; } /*if(dataInString.length()>colWidth) { colWidth = dataInString.length(); }*/ } } excelUtil.setWidth(); //sheet1.setDefaultColumnWidth((short)(10)); try { wb.write(out); out.flush(); out.close(); } catch (IOException e) { } }
From source file:org.fourfive.servlets.ArcTreeServlet.java
License:Mozilla Public License
private void writeExcelToOut(ArcTreeReport root, ServletOutputStream out, HttpSession session, HttpServletRequest req) throws MarshalException, ValidationException { EasyReport component = root.getEasyReport(); ArrayList rows = (ArrayList) root.getTreeRows(); TreeRow row = (TreeRow) rows.get(0); ReportCell cell = null;// w ww. j ava2 s . c om ArrayList rowCells = row.getRowCells(); ArrayList nonVisibleCol = new ArrayList(); for (int j = 0; j < rowCells.size(); j++) { cell = (ReportCell) rowCells.get(j); if (!cell.getVisible()) { nonVisibleCol.add(new Integer(j)); } } nonCurrItems = root.getNonCurrencyColumns(); HSSFWorkbook wb = new HSSFWorkbook(); // title of the report Hashtable ht = (Hashtable) com.manthan.promax.report.ReportUtil.getLabels(session); String repGenTime = "Report Generated Time : "; if (ht != null && ht.get("export.reportgenerated.time") != null && ht.get("export.reportgenerated.time").toString().trim().length() > 0) { repGenTime = (String) ht.get("export.reportgenerated.time"); } String nameKey = (String) session.getAttribute("NAME_KEY"); int rowStart = 4; int colStart = 0; int colWidth = 10; //String dataInString = null; ExcelUtil excelUtil = new ExcelUtil(wb); excelUtil.setSession(session); excelUtil.getCurrentSelection(req); HSSFSheet sheet1 = wb.createSheet(excelUtil.getSheetTitle(nameKey)); excelUtil.setSheetStyle(); HSSFRow showTimeRow = sheet1.createRow((short) (rowStart)); HSSFCell showTimeCell = showTimeRow.createCell((short) (colStart)); showTimeCell.setCellStyle(excelUtil.getSelectionStyle()); showTimeCell.setCellValue(new HSSFRichTextString(repGenTime + new Date().toString())); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 7))); rowStart = rowStart + 2; HSSFRow titleRow = sheet1.createRow((short) rowStart); HSSFCell titleCell = titleRow.createCell((short) colStart); titleCell.setCellStyle(excelUtil.getTitleStyle()); titleCell.setCellValue(new HSSFRichTextString(nameKey)); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); rowStart = rowStart + 2; HSSFRow selectionRow = sheet1.createRow((short) rowStart); //selectionRow.setHeightInPoints(40); HSSFCell selectionCell = selectionRow.createCell((short) colStart); selectionCell.setCellStyle(excelUtil.getSelectionStyle()); java.util.ArrayList summaryData = (java.util.ArrayList) session.getAttribute("FILTER_SUMMARY_DATA"); selectionCell.setCellValue(new HSSFRichTextString((String) ht.get("arc.current.selection"))); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 5))); int filterSize = 1; if (summaryData != null) { for (int sumCnt = 0; sumCnt < summaryData.size(); sumCnt++) { java.util.ArrayList sumdata = (java.util.ArrayList) summaryData.get(sumCnt); rowStart++; selectionRow = sheet1.createRow((short) rowStart); selectionCell = selectionRow.createCell((short) (colStart)); selectionCell.setCellStyle(excelUtil.getSelectionStyle()); String value = (String) sumdata.get(1); value = value.replaceAll("<span>", ""); value = value.replaceAll("</span>", ""); selectionCell .setCellValue(new HSSFRichTextString(" " + (String) sumdata.get(0) + " -- " + value)); sheet1.addMergedRegion(new Region(rowStart, (short) (colStart), rowStart, (short) (colStart + 10))); } //sheet1.addMergedRegion(new Region(rowStart-summaryData.size(),(short)(colStart),rowStart,(short)(colStart))); filterSize = summaryData.size(); } rowStart++; excelUtil.setPrintAndPageSettings(rowStart + 1, rowStart + 2, (String) ReportUtil.getLabels(session).get(nameKey), rowCells.size() + 3); excelUtil.setStartWidthColumn(rowStart + 1); HSSFCell excel_cell = null; HSSFRow excelRow = sheet1.createRow((short) rowStart); String reportType = ReportDataUtil.getReportType(component); HashMap crossTabDetails = root.getCrossTabReportDetails(); //----- for displaying unit measure by Gowri ----------------- Columns columns = component.getDefinition().getSelect().getColumns(); ArrayList crosstabColumnIds = new ArrayList(); for (int colCnt = 0; colCnt < columns.getColumnCount(); colCnt++) { Column column = columns.getColumn(colCnt); crosstabColumnIds.add(column.getId()); } //------------------------------------------------------------- if (reportType != null && reportType.equalsIgnoreCase(DefaultKernel.CROSSTAB_REPORT_TYPE)) { crosstabColumnIds = (ArrayList) crossTabDetails.get("COLUMN_IDS_LIST"); ArrayList columnList = (ArrayList) crossTabDetails.get("COLUMN_LIST"); ArrayList growingColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_DYNAMIC); ArrayList staticColumns = (ArrayList) crossTabDetails.get(DefaultKernel.XTAB_STATIC); int noGrowingColumns = growingColumns.size(); int noStaticColumns = staticColumns.size(); int staticColSpan = noStaticColumns; int dynamicColSpan = noGrowingColumns; for (int colCount = 0; colCount < staticColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount))) { staticColSpan--; } } for (int colCount = 0; colCount < growingColumns.size(); colCount++) { if (nonVisibleCol.contains(new Integer(colCount + noStaticColumns))) { dynamicColSpan--; } } for (int i = 0; i < columnList.size(); i++) { short cellIndex = (short) (1 + (staticColSpan - 1) + (dynamicColSpan * i)); excel_cell = excelRow.createCell(cellIndex); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); excel_cell.setCellValue(new HSSFRichTextString((String) columnList.get(i))); for (short index = (short) (cellIndex + 1); index < cellIndex + dynamicColSpan; index++) { excel_cell = excelRow.createCell(index); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); } sheet1.addMergedRegion(new Region(rowStart, (short) (cellIndex), rowStart, (short) (cellIndex + dynamicColSpan - 1))); } } rowStart++; sheet1.createFreezePane(2, 12 + filterSize); excelRow = sheet1.createRow((short) rowStart); HashMap hierarchyLevels = root.getHierarchyLevels(); row = (TreeRow) root.getTreeRows().get(0); int colCout = colStart; //Columns columns = component.getDefinition().getSelect().getColumns(); Hashtable measures = com.manthan.promax.db.ApplicationConfig.getAllMeasures(); for (int cellCount = 0; cellCount < row.getRowCells().size(); cellCount++) { ReportCell rCell = (ReportCell) row.getRowCells().get(cellCount); String colId = crosstabColumnIds.get(cellCount).toString(); if (rCell.getVisible()) { String cellValue = rCell.getName(); //----- for displaying unit measure by Gowri ----------------- String unitMeasures = ""; HashMap measure = (HashMap) measures.get(colId); if (null != measure && null != measure.get("COLUMN_TYPE") && measure.get("COLUMN_TYPE").toString().equalsIgnoreCase("currency") && ((measure.get("RULE") != null && !measure.get("RULE").toString().trim().equals("") && measure.get("RULE").toString().indexOf("100") == -1) || (measure.get("DB_COLUMN_NAME") != null && !measure.get("DB_COLUMN_NAME").toString().trim().equals("")) || (measure.get("MEASURE_EXPRESSION") != null && !measure.get("MEASURE_EXPRESSION").toString().trim().equals("")))) { unitMeasures = (session.getAttribute("unitMeasureInDigits") != null && !session.getAttribute("unitMeasureInDigits").toString().equals("1")) ? "[in " + ((String) session.getAttribute("unitMeasureInDigits")).substring(1) + "]" : ""; } //------------------------------------------------------------- excel_cell = excelRow.createCell((short) (colCout)); excel_cell.setCellStyle(excelUtil.getHeaderStyle()); excel_cell.setCellValue(new HSSFRichTextString(cellValue + "\n" + unitMeasures)); colCout++; } } rowStart++; excelRow = sheet1.createRow((short) rowStart); excelRow.setHeightInPoints(20); row = (TreeRow) root.getTreeRows().get(1); HSSFCell totCell = excelRow.createCell((short) colStart); totCell.setCellStyle(excelUtil.getTotalStyle(excelUtil.format(nonCurrItems, 0))); totCell.setCellValue(new HSSFRichTextString(((ReportCell) row.getRowCells().get(0)).getName())); cell = null; colCout = colStart + 1; for (int cellCount = 1; cellCount < row.getRowCells().size(); cellCount++) { cell = (ReportCell) row.getRowCells().get(cellCount); if (cell.getVisible()) { String cellValue = cell.getName(); excel_cell = excelRow.createCell((short) (colCout)); try { double val = Double.parseDouble(cellValue); excel_cell.setCellStyle(excelUtil.getTotalStyle(excelUtil.format(nonCurrItems, cellCount))); excel_cell.setCellValue(val); } catch (NumberFormatException e) { excel_cell.setCellStyle(excelUtil.getTotalStyle(excelUtil.format(nonCurrItems, cellCount))); excel_cell.setCellValue(new HSSFRichTextString(cellValue)); } colCout++; } } count_row = rowStart + 1; writeArcTreeElementToExcel(root, sheet1, excelUtil); excelUtil.getLogo(session.getServletContext()); excelUtil.setWidth(); excelUtil.removeCell(sheet1); HSSFRow compConfidentialRow = sheet1.createRow((short) (count_row + 1)); HSSFCell compConfidentialCell = compConfidentialRow.createCell((short) (0)); compConfidentialCell.setCellStyle(excelUtil.getSelectionStyle()); compConfidentialCell.setCellValue(new HSSFRichTextString((String) ht.get("arc.company.confidential"))); sheet1.addMergedRegion(new Region(count_row + 1, (short) (0), rowStart, (short) (5))); try { wb.write(out); out.flush(); out.close(); } catch (IOException e) { } }
From source file:org.fourfive.servlets.ArcTreeServlet.java
License:Mozilla Public License
public void writeArcTreeElementToExcel(ArcTreeReport node, HSSFSheet sheet1, ExcelUtil excelUtil) { EasyReport component = node.getEasyReport(); String reportType = ReportDataUtil.getReportType(component); HashMap dataformat = null;/*from www. j av a 2 s . c o m*/ ArrayList crosstabColumnIds = null; HashMap crossTabDetails = node.getCrossTabReportDetails(); if (reportType != null && reportType.equalsIgnoreCase(DefaultKernel.CROSSTAB_REPORT_TYPE)) { if (crossTabDetails.get("COLUMN_IDS_LIST") != null) { crosstabColumnIds = (ArrayList) crossTabDetails.get("COLUMN_IDS_LIST"); } if (crossTabDetails.get("CROSSTABDATAFORMAT") != null) { dataformat = (HashMap) crossTabDetails.get("CROSSTABDATAFORMAT"); } } ArrayList treeRows = (ArrayList) node.getTreeRows(); TreeRow row = null; ReportCell cell = null; String cellValue = null; int level = 0; int colStart = 0; for (int i = 2; i < treeRows.size(); i++) { row = (TreeRow) treeRows.get(i); level = row.getLevel(); HSSFRow excelRow = sheet1.createRow((short) count_row); count_row = count_row + 1; excelRow.setHeightInPoints(20); HSSFCell dcsCell = excelRow.createCell((short) (colStart)); String space = ""; dcsCell.setCellStyle(excelUtil.getDataTextStyle()); for (int k = 1; k < level; k++) { if (level - 1 == 0) space = ""; else space = space + " "; } cell = (ReportCell) (row.getRowCells()).get(0); String cellVal = space + cell.getName(); dcsCell.setCellValue(new HSSFRichTextString(cellVal)); int count = colStart + 1; for (int j = 1; j < row.getRowCells().size(); j++) { cell = (ReportCell) (row.getRowCells()).get(j); if (cell.getVisible()) { cellValue = cell.getName(); HSSFCell excel_cell = excelRow.createCell((short) (count)); String datatype = ""; try { if (null != component.getDefinition().getReportType() && component.getDefinition().getReportType().equalsIgnoreCase("crosstab")) { datatype = dataformat.get(crosstabColumnIds.get(j).toString()).toString(); } else { datatype = ((Column) component.getDefinition().getSelect().getColumns().getColumn(j)) .getDataFormat(); } double val = Double.parseDouble(cellValue); excel_cell.setCellValue(val); if (datatype.equalsIgnoreCase("currency")) { excel_cell.setCellStyle(excelUtil.getDataStyleFormat()); } else if (datatype.equalsIgnoreCase("number")) { excel_cell.setCellStyle(excelUtil.getDataStyleFormatLess()); } else { excel_cell.setCellValue(new HSSFRichTextString(cellValue)); excel_cell.setCellStyle(excelUtil.firstColStyle()); } } catch (NumberFormatException ee) { excel_cell.setCellStyle(excelUtil.getDataTextStyle()); excel_cell.setCellValue(new HSSFRichTextString(cellValue)); } count++; } } if (row.getChild() != null && row.getState().equalsIgnoreCase(GlobalConstant.EXPANDED)) writeArcTreeElementToExcel(row.getChild(), sheet1, excelUtil); } }