List of usage examples for org.apache.poi.hssf.usermodel HSSFFont setFontHeightInPoints
public void setFontHeightInPoints(short height)
From source file:FILING.countyreport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// ww w .j av a 2s. c om * * @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/* w ww. ja va2 s. com*/ * @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:gov.guilin.ExcelView.java
License:Open Source License
/** * ?Excel// ww w. j av a2s . c o m * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
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(); font.setFontName(fontFamily);//from w w w .ja v a2 s .c o m 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:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java
License:Apache License
private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) { final HSSFCellStyle cellStyle = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font);/*from w w w . j a v a 2 s . c o m*/ cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setVerticalAlignment((short) 3); // styleEntete.setFillPattern(HSSFCellStyle.SPARSE_DOTS); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); // headerCellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); return cellStyle; }
From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java
License:Apache License
private static HSSFCellStyle createRowCellStyle(final HSSFWorkbook workbook, final boolean odd) { final HSSFCellStyle cellStyle = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font);/*from ww w .ja v a 2s. co m*/ cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); // cellStyle.setVerticalAlignment((short)3); // styleEntete.setFillPattern(HSSFCellStyle.SPARSE_DOTS); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(odd ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index); // headerCellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index); // cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // cellStyle.setWrapText(true); return cellStyle; }
From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java
License:Apache License
private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) { final HSSFCellStyle cellStyle = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); font.setBold(true);/*www .j av a 2 s .co m*/ cellStyle.setFont(font); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_40_PERCENT.getIndex()); cellStyle.setAlignment(HorizontalAlignment.CENTER); return cellStyle; }
From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java
License:Apache License
private static HSSFCellStyle createRowCellStyle(final HSSFWorkbook workbook, final boolean odd) { final HSSFCellStyle cellStyle = workbook.createCellStyle(); final HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); cellStyle.setFont(font);/*from w ww . j a v a 2 s.co m*/ cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillForegroundColor( odd ? HSSFColorPredefined.WHITE.getIndex() : HSSFColorPredefined.GREY_25_PERCENT.getIndex()); return cellStyle; }
From source file:is.idega.idegaweb.egov.accounting.business.AccountingEntryWriter.java
License:Open Source License
public MemoryFileBuffer writeXLS(IWContext iwc, Map paymentMethod, Map products) throws Exception { MemoryFileBuffer buffer = new MemoryFileBuffer(); MemoryOutputStream mos = new MemoryOutputStream(buffer); if (paymentMethod != null && products != null) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(StringHandler.shortenToLength( this.iwrb.getLocalizedString("accounting_statistics.statistics", "Statistics"), 30)); sheet.setColumnWidth((short) 0, (short) (30 * 256)); sheet.setColumnWidth((short) 1, (short) (14 * 256)); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = wb.createCellStyle(); style.setFont(font);/*ww w . j a v a 2 s . c om*/ int cellRow = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.item", "Item")); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.total", "Total")); cell.setCellStyle(style); Collection keys = paymentMethod.keySet(); Iterator iter = keys.iterator(); while (iter.hasNext()) { row = sheet.createRow(cellRow++); String key = (String) iter.next(); int count = ((Collection) paymentMethod.get(key)).size(); cell = row.createCell((short) 0); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.payment_method." + key, key)); cell = row.createCell((short) 1); cell.setCellValue(String.valueOf(count)); } keys = products.keySet(); iter = keys.iterator(); while (iter.hasNext()) { row = sheet.createRow(cellRow++); String key = (String) iter.next(); int count = ((Collection) products.get(key)).size(); cell = row.createCell((short) 0); cell.setCellValue( this.iwrb.getLocalizedString("accounting_statistics." + this.caseCode + "." + key, key)); cell = row.createCell((short) 1); cell.setCellValue(String.valueOf(count)); } keys = products.keySet(); iter = keys.iterator(); while (iter.hasNext()) { String key = (String) iter.next(); createNewSheet(iwc, wb, iwrb.getLocalizedString("accounting_statistics." + this.caseCode + "." + key, key), (List) products.get(key)); } wb.write(mos); } buffer.setMimeType(MimeTypeUtil.MIME_TYPE_EXCEL_2); return buffer; }
From source file:is.idega.idegaweb.egov.accounting.business.AccountingEntryWriter.java
License:Open Source License
private void createNewSheet(IWContext iwc, HSSFWorkbook workbook, String sheetName, List entries) { Collections.sort(entries, new AccountingEntryComparator(iwc.getCurrentLocale())); NumberFormat format = NumberFormat.getInstance(); format.setMaximumFractionDigits(0);//from w w w . ja v a 2 s .co m format.setMinimumFractionDigits(0); format.setGroupingUsed(false); HSSFSheet sheet = workbook.createSheet(StringHandler.shortenToLength(sheetName, 30)); sheet.setColumnWidth((short) 0, (short) (30 * 256)); sheet.setColumnWidth((short) 1, (short) (14 * 256)); sheet.setColumnWidth((short) 2, (short) (14 * 256)); sheet.setColumnWidth((short) 3, (short) (14 * 256)); sheet.setColumnWidth((short) 4, (short) (14 * 256)); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); int cellRow = 0; short cellColumn = 0; HSSFRow row = sheet.createRow(cellRow++); HSSFCell cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.name", "Name")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.personal_id", "Personal ID")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.valid_from", "Valid from")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.valid_to", "Valid to")); cell.setCellStyle(style); cell = row.createCell(cellColumn++); cell.setCellValue(this.iwrb.getLocalizedString("accounting_statistics.units", "Units")); cell.setCellStyle(style); Iterator iter = entries.iterator(); while (iter.hasNext()) { AccountingEntry entry = (AccountingEntry) iter.next(); row = sheet.createRow(cellRow++); cellColumn = 0; IWTimestamp startDate = new IWTimestamp(entry.getStartDate()); IWTimestamp endDate = null; if (entry.getEndDate() != null) { endDate = new IWTimestamp(entry.getEndDate()); } float units = 0; if (entry.getUnits() > 0) { units = entry.getUnits(); } else { units = entry.getAmount(); } cell = row.createCell(cellColumn++); cell.setCellValue(entry.getName()); cell = row.createCell(cellColumn++); cell.setCellValue(PersonalIDFormatter.format(entry.getPersonalId(), iwc.getCurrentLocale())); cell = row.createCell(cellColumn++); cell.setCellValue(startDate.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT)); cell = row.createCell(cellColumn++); if (endDate != null) { cell.setCellValue(endDate.getLocaleDate(iwc.getCurrentLocale(), IWTimestamp.SHORT)); } else { cell.setCellValue("-"); } cell = row.createCell(cellColumn++); cell.setCellValue(format.format(units)); } }