List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:excel.FileExcel.java
public File excel_mutation_report(ArrayList<MutationReportData> datamutation, KodeNamaKonter konterinfo, Date Start, Date End, Date CurrentDate) { if (datamutation.size() != 0) { DateFormat time = new SimpleDateFormat("hhmm"); String fileName = "Mutation_" + konterinfo.nama_konter + "_" + fmt.format(CurrentDate) + "_" + time.format(CurrentDate) + ".xls"; File ExcelMutation = new File(fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); // set page sheet.getPrintSetup().setLandscape(false); //Set Header Information Header headerPage = sheet.getHeader(); headerPage.setCenter(HeaderFooter.page()); headerPage.setRight(fileName);//from w w w . ja va2 s .co m //Set Footer Information with Page Numbers Footer footerPage = sheet.getFooter(); footerPage.setCenter("Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages()); // prepare variable to edit the xls HSSFRow header; HSSFCell cell; HSSFCellStyle titlestyle = workbook.createCellStyle(); HSSFCellStyle headerstyle = workbook.createCellStyle(); HSSFCellStyle datastyle = workbook.createCellStyle(); HSSFFont boldfont = workbook.createFont(); HSSFFont normalfont = workbook.createFont(); // create the title header = sheet.createRow(1); cell = header.createCell(1); boldfont.setBoldweight(Font.BOLDWEIGHT_BOLD); titlestyle.setFont(boldfont); titlestyle.setAlignment(CellStyle.ALIGN_CENTER); titlestyle.setBorderTop(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); titlestyle.setBorderRight(HSSFCellStyle.BORDER_NONE); cell.setCellStyle(titlestyle); cell.setCellValue("TABEL MUTATION REPORT"); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3)); // create file info // create file info header = sheet.createRow(3); cell = header.createCell(2); cell.setCellValue("Konter : "); cell = header.createCell(3); cell.setCellValue(konterinfo.nama_konter); header = sheet.createRow(4); cell = header.createCell(2); cell.setCellValue("Tanggal : "); cell = header.createCell(3); cell.setCellValue(fmt.format(Start)); cell = header.createCell(4); cell.setCellValue("-"); cell = header.createCell(5); cell.setCellValue(fmt.format(End)); // create the header headerstyle.setFont(boldfont); headerstyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); headerstyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); header = sheet.createRow(7); cell = header.createCell(1); cell.setCellStyle(headerstyle); cell.setCellValue("Tanggal"); cell = header.createCell(2); cell.setCellStyle(headerstyle); cell.setCellValue("Barang Masuk"); cell = header.createCell(3); cell.setCellStyle(headerstyle); cell.setCellValue("Setoran Masuk"); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); normalfont.setBoldweight(Font.BOLDWEIGHT_NORMAL); datastyle.setFont(normalfont); datastyle.setAlignment(CellStyle.ALIGN_RIGHT); datastyle.setBorderTop(HSSFCellStyle.BORDER_THIN); datastyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); datastyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); datastyle.setBorderRight(HSSFCellStyle.BORDER_THIN); int i; // fill the data for (i = 0; i < datamutation.size(); i++) { header = sheet.createRow(8 + i); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue(fmt.format(datamutation.get(i).tanggal_mutasi)); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(datamutation.get(i).barang_keluar); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(datamutation.get(i).setoran); } datastyle.setFont(boldfont); header = sheet.createRow(i + 10); cell = header.createCell(1); cell.setCellStyle(datastyle); cell.setCellValue("Hutang Konter"); cell = header.createCell(2); cell.setCellStyle(datastyle); cell.setCellValue(fmt.format(CurrentDate)); cell = header.createCell(3); cell.setCellStyle(datastyle); cell.setCellValue(konterinfo.hutang_konter); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); try { FileOutputStream out = new FileOutputStream(ExcelMutation); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); ExcelMutation = null; } catch (IOException e) { e.printStackTrace(); ExcelMutation = null; } return ExcelMutation; } else { return null; } }
From source file:FILING.cboreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from ww w. ja 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./*ww w .j ava 2 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./*ww w . ja 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:gda.hrpd.data.HSSF.java
License:Apache License
/** * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells. * * @param filename//from w w w . j a va2 s .c o m * @param write * @exception IOException */ public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException { short rownum = 0; FileOutputStream out = new FileOutputStream(filename); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = (short) 0; rownum < 300; rownum++) { r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } // r.setRowNum(( short ) rownum); for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString("TEST")); s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } // 50 characters divided by 1/20th of a point } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (short cellnum = (short) 0; cellnum < 50; cellnum++) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK); // c.setCellValue(0); c.setCellStyle(cs3); } s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3)); s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet wb.write(out); out.close(); }
From source file:hr.restart.swing.raExtendedTable.java
License:Apache License
public void exportToXLS(File output) { String fname = output.getName(); if (!fname.endsWith("xls") && fname.indexOf('.') < 0) output = new File(output.getParentFile(), fname + ".xls"); System.out.println("exporting to XLS"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat df = wb.createDataFormat(); String fontFamily = frmParam.getParam("sisfun", "excelFont", "Arial", "Font za export u Excel", true); if (fontFamily == null || fontFamily.length() == 0) fontFamily = "Arial"; int fontSize = 10; String fontSizeTx = frmParam.getParam("sisfun", "excelFontSize", "10", "Veliina fonta za export u Excel, u tokama", true); if (fontSizeTx != null && Aus.getNumber(fontSizeTx) >= 6 && Aus.getNumber(fontSizeTx) <= 72) fontSize = Aus.getNumber(fontSizeTx); HSSFFont font = wb.createFont();//from w w w.ja v a2 s . c o m font.setFontName(fontFamily); font.setFontHeightInPoints((short) fontSize); HSSFFont fontTitle = wb.createFont(); fontTitle.setFontName(fontFamily); fontTitle.setFontHeightInPoints((short) (fontSize * 1.8)); HSSFFont fontSubtitle = wb.createFont(); fontSubtitle.setFontName(fontFamily); fontSubtitle.setFontHeightInPoints((short) (fontSize * 1.5)); HSSFCellStyle csHeader = wb.createCellStyle(); csHeader.setFont(font); csHeader.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); csHeader.setDataFormat(df.getFormat("text")); HSSFCellStyle csFooter = wb.createCellStyle(); csFooter.setFont(font); csFooter.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooter.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooter.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooter.setAlignment(HSSFCellStyle.ALIGN_LEFT); csFooter.setDataFormat(df.getFormat("text")); HSSFCellStyle csFooterNum2 = wb.createCellStyle(); csFooterNum2.setFont(font); csFooterNum2.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooterNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csFooterNum2.setDataFormat(df.getFormat("#,##0.00")); HSSFCellStyle csFooterNum = wb.createCellStyle(); csFooterNum.setFont(font); csFooterNum.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); csFooterNum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csFooterNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csFooterNum.setDataFormat(df.getFormat("#")); HSSFCellStyle csDate = wb.createCellStyle(); csDate.setFont(font); csDate.setAlignment(HSSFCellStyle.ALIGN_CENTER); csDate.setDataFormat(df.getFormat("dd.mm.yyyy")); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setFont(fontTitle); csTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); csTitle.setDataFormat(df.getFormat("text")); HSSFCellStyle csSubtitle = wb.createCellStyle(); csSubtitle.setFont(fontSubtitle); csSubtitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); csSubtitle.setDataFormat(df.getFormat("text")); HSSFCellStyle csNum2 = wb.createCellStyle(); csNum2.setFont(font); csNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum2.setDataFormat(df.getFormat("#,##0.00")); HSSFCellStyle csNum3 = wb.createCellStyle(); csNum3.setFont(font); csNum3.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum3.setDataFormat(df.getFormat("#,##0.000")); HSSFCellStyle csNum = wb.createCellStyle(); csNum.setFont(font); csNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csNum.setDataFormat(df.getFormat("#.#")); HSSFCellStyle csInt = wb.createCellStyle(); csInt.setFont(font); csInt.setAlignment(HSSFCellStyle.ALIGN_RIGHT); csInt.setDataFormat(df.getFormat("#")); HSSFCellStyle csText = wb.createCellStyle(); csText.setFont(font); csText.setAlignment(HSSFCellStyle.ALIGN_LEFT); csText.setDataFormat(df.getFormat("text")); repDynamicProvider dp = repDynamicProvider.getInstance(); boolean sums = dp.hasSumRow(); int cols = getColumnModel().getColumnCount(); int rows = getRowCount() - (sums ? 1 : 0); HSSFSheet sheet = wb.createSheet(); HSSFRow row; HSSFCell cell; short cRow = 0; // header and title row = sheet.createRow(cRow = 0); cell = row.createCell((short) 0); cell.setCellStyle(csText); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(repMemo.getrepMemo().getOneLine()); row = sheet.createRow(++cRow); cell = row.createCell((short) 0); cell.setCellStyle(csTitle); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); if (dp.getTitle().length() > 0) cell.setCellValue(dp.getTitle().substring(1)); else cell.setCellValue(""); if (dp.getSubtitle().length() > 0) { row = sheet.createRow(++cRow); cell = row.createCell((short) 0); cell.setCellStyle(csSubtitle); if (cols > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1))); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(dp.getSubtitle().substring(1)); } for (short c = 0; c < cols; c++) sheet.setColumnWidth(c, (short) (getColumnModel().getColumn(c).getWidth() * 40)); // sections row = sheet.createRow(++cRow); int secRow = 0, firstRow = 0; for (int r = 0; r < rows; r++) { if (r == 0) { row = sheet.createRow(++cRow); for (short c = 0; c < cols; c++) { cell = row.createCell(c); cell.setCellStyle(csHeader); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(getColumnModel().getColumn(c).getHeaderValue().toString()); } if (firstRow == 0) firstRow = cRow; secRow = cRow; } row = sheet.createRow(++cRow); for (short c = 0; c < cols; c++) { cell = row.createCell(c); Object o = getValueAt(r, c); if (o instanceof Number) { if (o instanceof BigDecimal) { BigDecimal bd = (BigDecimal) o; if (bd.scale() == 2) cell.setCellStyle(csNum2); else if (bd.scale() == 3) cell.setCellStyle(csNum3); else cell.setCellStyle(csNum); cell.setCellValue(bd.doubleValue()); } else { String t = dp.getValueAt(r, c); if (Aus.isDigit(t)) { cell.setCellStyle(csInt); cell.setCellValue(((Number) o).doubleValue()); } else { cell.setCellStyle(csText); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(t); } } } else if (o instanceof Date) { cell.setCellStyle(csDate); cell.setCellValue((Date) o); } else { cell.setCellStyle(csText); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(dp.getValueAt(r, c)); } } } System.out.println("sums " + sums); if (sums) { int non = 0; while (non < cols && dp.getValueAt(getRowCount() - 1, non).trim().length() == 0) ++non; if (non < cols) { System.out.println("creating row " + non); row = sheet.createRow(++cRow); if (non > 0) { cell = row.createCell((short) 0); cell.setCellStyle(csFooter); cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue("U K U P N O"); if (non > 1) sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (non - 1))); } for (short c = (short) non; c < cols; c++) { cell = row.createCell(c); Object o = getValueAt(rows - 1, c); if ((o instanceof BigDecimal) && ((BigDecimal) o).scale() == 2) cell.setCellStyle(csFooterNum2); else cell.setCellStyle(csFooterNum); if (dp.getValueAt(getRowCount() - 1, c).trim().length() != 0) cell.setCellFormula("SUBTOTAL(9;" + xlsRange(firstRow + 1, cRow, c) + ")"); else cell.setCellValue(""); } } } FileOutputStream out = null; try { out = new FileOutputStream(output); wb.write(out); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (out != null) try { out.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
@Override public void generateXSL(String user, String startDate, String endDate) { try {/*from ww w . ja 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 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:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
public static void cover(HSSFWorkbook wb) { LogMaker.makelog("Making Cover sheet"); HSSFSheet sheet = wb.createSheet("Cover");// ??????? for (int iRowNum = 0; iRowNum < 1; iRowNum++) {///////1?? HSSFRow row = sheet.createRow(iRowNum); int iCol = 2; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Date "); row.getCell(iCol).setCellStyle(Style.dateCellStyle); sheet.autoSizeColumn((short) iCol, true); iCol++;/*w ww .j av a 2 s . co m*/ row.createCell(iCol); row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(" "); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Doc. No. "); row.getCell(iCol).setCellStyle(Style.componentCellStyle); sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Version"); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol); row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(" "); row.getCell(iCol).setCellStyle(Style.leftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); } for (int iRowNum = 1; iRowNum < 2; iRowNum++) {//2?? HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(" EXAMPLE "); row.getCell(iCol).setCellStyle(Style.exampleCellStyle); sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(" "); row.getCell(iCol).setCellStyle(Style.exbCellStyle); iCol++; row.createCell(iCol); row.getCell(iCol).setCellStyle(Style.dateCellStyle); iCol++; row.createCell(iCol); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); iCol++; row.createCell(iCol); row.getCell(iCol).setCellStyle(Style.componentCellStyle); iCol++; row.createCell(iCol); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); iCol++; row.createCell(iCol); row.getCell(iCol).setCellStyle(Style.leftOpenMediumRightCellStyle); iCol++; } for (int iRowNum = 2; iRowNum < 3; iRowNum++) {//3?? HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Organization"); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Author"); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Reviewer"); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Approver"); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 3; iRowNum < 4; iRowNum++) {//4th row HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); row.setHeight((short) 500); iCol++; row.createCell(iCol); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 4; iRowNum < 5; iRowNum++) {//5th row HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Project"); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Title"); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.LROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.LROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.LROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 5; iRowNum < 6; iRowNum++) {//6th row HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BRightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); row.setHeight((short) 500); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLeftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BRightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLeftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } //////////////////////////example end for (int iRowNum = 7; iRowNum < 8; iRowNum++) {//8th row HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Project Information"); row.getCell(iCol).setCellStyle(Style.projectInformationCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 8; iRowNum < 9; iRowNum++) {//9th row HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.TLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.TLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); row.setHeight((short) 400); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.TLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.TLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.TleftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.TrightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.TleftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 9; iRowNum < 18; iRowNum++) {//10-18th row HSSFRow row = sheet.createRow(iRowNum); row.setHeight((short) 350); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.LROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.LROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.LROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.LROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.rightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.leftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 18; iRowNum < 19; iRowNum++) {//19th row HSSFRow row = sheet.createRow(iRowNum); row.setHeight((short) 350); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLROpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLeftOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BRightOpenCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.BLeftOpenMediumRightCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 20; iRowNum < 21; iRowNum++) {//21th row HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Change History"); row.getCell(iCol).setCellStyle(Style.CHCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 21; iRowNum < 22; iRowNum++) {//22th row HSSFRow row = sheet.createRow(iRowNum); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Version"); row.getCell(iCol).setCellStyle(Style.greyCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Date"); row.getCell(iCol).setCellStyle(Style.greyCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Description"); row.getCell(iCol).setCellStyle(Style.greyCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.greyCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.greyCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("Author"); row.getCell(iCol).setCellStyle(Style.greyCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue("OSS Notice Version"); row.getCell(iCol).setCellStyle(Style.greyCellStyle); sheet.autoSizeColumn((short) iCol, true); iCol++; } for (int iRowNum = 22; iRowNum < 44; iRowNum++) {//23-44th row HSSFRow row = sheet.createRow(iRowNum); row.setHeight((short) 400); int iCol = 0; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; row.createCell(iCol, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row.getCell(iCol).setCellStyle(Style.componentCellStyle); //sheet.autoSizeColumn((short) iCol, true); iCol++; } //////////merge cell //?????? sheet.AddMergedRegion(int ????row, short ????col,int ????row, short ????col); //example sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 1)); sheet.addMergedRegion(new Region(0, (short) 2, 0, (short) 3)); sheet.addMergedRegion(new Region(0, (short) 5, 0, (short) 6)); sheet.addMergedRegion(new Region(1, (short) 2, 1, (short) 3)); sheet.addMergedRegion(new Region(1, (short) 5, 1, (short) 6)); sheet.addMergedRegion(new Region(2, (short) 0, 2, (short) 1)); sheet.addMergedRegion(new Region(2, (short) 2, 2, (short) 3)); sheet.addMergedRegion(new Region(2, (short) 5, 2, (short) 6)); sheet.addMergedRegion(new Region(3, (short) 5, 3, (short) 6)); sheet.addMergedRegion(new Region(3, (short) 0, 3, (short) 1)); sheet.addMergedRegion(new Region(3, (short) 2, 3, (short) 3)); sheet.addMergedRegion(new Region(4, (short) 0, 4, (short) 1)); sheet.addMergedRegion(new Region(4, (short) 2, 4, (short) 6)); sheet.addMergedRegion(new Region(5, (short) 0, 5, (short) 1)); sheet.addMergedRegion(new Region(5, (short) 2, 5, (short) 6)); //project information sheet.addMergedRegion(new Region(8, (short) 0, 8, (short) 4)); sheet.addMergedRegion(new Region(8, (short) 5, 8, (short) 6)); sheet.addMergedRegion(new Region(9, (short) 0, 9, (short) 4)); sheet.addMergedRegion(new Region(9, (short) 5, 9, (short) 6)); sheet.addMergedRegion(new Region(10, (short) 0, 10, (short) 4)); sheet.addMergedRegion(new Region(10, (short) 5, 10, (short) 6)); sheet.addMergedRegion(new Region(11, (short) 0, 11, (short) 4)); sheet.addMergedRegion(new Region(11, (short) 5, 11, (short) 6)); sheet.addMergedRegion(new Region(12, (short) 0, 12, (short) 4)); sheet.addMergedRegion(new Region(12, (short) 5, 12, (short) 6)); sheet.addMergedRegion(new Region(13, (short) 0, 13, (short) 4)); sheet.addMergedRegion(new Region(13, (short) 5, 13, (short) 6)); sheet.addMergedRegion(new Region(14, (short) 0, 14, (short) 4)); sheet.addMergedRegion(new Region(14, (short) 5, 14, (short) 6)); sheet.addMergedRegion(new Region(15, (short) 0, 15, (short) 4)); sheet.addMergedRegion(new Region(15, (short) 5, 15, (short) 6)); sheet.addMergedRegion(new Region(16, (short) 0, 16, (short) 4)); sheet.addMergedRegion(new Region(16, (short) 5, 16, (short) 6)); sheet.addMergedRegion(new Region(17, (short) 0, 17, (short) 4)); sheet.addMergedRegion(new Region(17, (short) 5, 17, (short) 6)); sheet.addMergedRegion(new Region(18, (short) 0, 18, (short) 4)); sheet.addMergedRegion(new Region(18, (short) 5, 18, (short) 6)); //change history for (int i = 21; i < 44; i++) { sheet.addMergedRegion(new Region(i, (short) 2, i, (short) 4)); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "src_Project_Name" /*ww w .j a va2 s . c o m*/ * */ public static void src(HSSFWorkbook wb, String projectName) { LogMaker.makelog("Making sheet for Project ID Files"); HSSFSheet sheet; projectName = SCharRemover.StringReplace(projectName, false); int pCount = 0; sheet = wb.createSheet("SRC_" + projectName); Count.sheet = sheet; ArrayList<String> Header = new ArrayList<String>(); for (int rowNum = 0; rowNum < 1; rowNum++) { HSSFRow row = sheet.createRow(0); row.setHeight((short) 500); int i = 0; row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue("Analysis Description"); row.getCell(i).setCellStyle(Style.anaDesCellStyle); sheet.autoSizeColumn((short) i, true); i++; row.createCell(14, HSSFCell.CELL_TYPE_STRING).setCellValue("1st Review (Development Team)"); row.getCell(14).setCellStyle(Style.fReviewCellStyle); i++; row.createCell(19, HSSFCell.CELL_TYPE_STRING).setCellValue("Final Review"); row.getCell(19).setCellStyle(Style.finalReviewCellStyle); i++; } sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 13)); sheet.addMergedRegion(new Region(0, (short) 14, 0, (short) 18)); sheet.addMergedRegion(new Region(0, (short) 19, 0, (short) 20)); Header.clear(); Header.add("Category"); Header.add("File Path"); Header.add("Identification Type"); Header.add("Component"); Header.add("License"); Header.add("Version"); Header.add("OSS: Matched Files"); Header.add("%"); Header.add("OSS: Matched File Line"); Header.add("Dev.: Matched First Line"); Header.add("Dev.: Matched Total Line"); Header.add("Identified Date"); Header.add("Identifier"); Header.add("Comments"); HSSFRow row = sheet.createRow(1); row.setHeight((short) 500); src2ndrow(sheet, Header, 0, row, 14, 19); Header.clear(); Header.add(" Person In Charge "); Header.add(" Linkage Type "); Header.add(" A or B "); Header.add(" Reviewer's Comments "); Header.add(" Counterplan "); src2ndrow(sheet, Header, 14, row, 14, 19);// 0?? ???? ??????? Header.clear(); Header.add(" Final Review Date "); Header.add(" Reviewer's Comments "); src2ndrow(sheet, Header, 19, row, 14, 19); for (int i = 14; i < 21; i++) { sheet.autoSizeColumn(i, true); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * "Build Image Analysis" /*w w w.j a v a 2 s. co m*/ * * */ public static void BIA(HSSFWorkbook wb) { LogMaker.makelog("Making BIA sheet"); HSSFSheet sheetBIA = wb.createSheet("Build Image Analysis");// ??????? ArrayList<String> Header = new ArrayList<String>(); Header.add("Analysis Description"); Header.add("1st Reviewer (Development Team)"); Header.add("Final Revewer"); lineHeader(sheetBIA, 0, Header);// 0?? ???? ??????? sheetBIA.addMergedRegion(new Region(0, (short) 0, 0, (short) 5)); sheetBIA.addMergedRegion(new Region(0, (short) 6, 0, (short) 8)); sheetBIA.addMergedRegion(new Region(0, (short) 9, 0, (short) 10)); Header.clear(); HSSFRow row = sheetBIA.createRow(1); //row ???? row.setHeight((short) 500); Header.add("Binary File Path"); Header.add(" "); Header.add("Component"); Header.add("License");//drop down Header.add("Analyzer"); Header.add("Analyzer's Comments"); src2ndrow(sheetBIA, Header, 0, row, 6, 9); Header.clear(); sheetBIA.addMergedRegion(new Region(1, (short) 0, 1, (short) 1)); Header.add("Person In Charge"); Header.add("A or B");//drop down Header.add("Reviewer's Ccomments"); src2ndrow(sheetBIA, Header, 6, row, 6, 9);// 0?? ???? ??????? sheetBIA.autoSizeColumn((short) 6, true); sheetBIA.autoSizeColumn((short) 8, true); Header.clear(); Header.add("Review Date"); Header.add("Reviewer's Comments"); src2ndrow(sheetBIA, Header, 9, row, 6, 9);// 0?? ???? ??????? Header.clear(); for (int i = 2; i < 200; i++) { HSSFRow row2 = sheetBIA.createRow(i); row2.setHeight((short) 500); for (int j = 0; j < 11; j++) { row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue(" "); row2.getCell(j).setCellStyle(Style.componentCellStyle); if (j == 3) { CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheetBIA.addValidationData(dataValidation); } if (j == 7) { CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheetBIA.addValidationData(dataValidation); } } } }