List of usage examples for org.apache.poi.ss.usermodel CellStyle setFillPattern
void setFillPattern(FillPatternType fp);
From source file:reports.resultspercbo.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// w w w .j av a2s . 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 { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; startdate = request.getParameter("startdate"); enddate = request.getParameter("enddate"); // year=request.getParameter("year"); //site=request.getParameter("sitecbo"); //period=request.getParameter("period"); //cbo=request.getParameter("staffcbo"); String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible 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_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.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.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=========================ROW STYLE=============================== HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setWrapText(true); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle orangestyle = wb.createCellStyle(); orangestyle.setFont(bolfont); orangestyle.setWrapText(true); orangestyle.setAlignment(orangestyle.ALIGN_CENTER); orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index); orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.WHITE.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet("LIP_REPORT"); shet2.setColumnWidth(0, 8000); shet2.setColumnWidth(1, 5000); shet2.setColumnWidth(2, 5000); shet2.setColumnWidth(3, 5000); shet2.setColumnWidth(4, 5000); shet2.setColumnWidth(5, 5000); shet2.setColumnWidth(6, 5000); shet2.setColumnWidth(7, 5000); shet2.setColumnWidth(8, 5000); shet2.setColumnWidth(9, 5000); shet2.setColumnWidth(10, 5000); shet2.setColumnWidth(11, 5000); shet2.setColumnWidth(12, 5000); shet2.setColumnWidth(13, 5000); //create header one HSSFRow header = shet2.createRow(0); header.setHeightInPoints(30); HSSFCell cel1 = header.createCell(0); cel1.setCellValue("Results on LIP Initial conducted from " + startdate + " to " + enddate); cel1.setCellStyle(style); for (int b = 1; b < 14; b++) { cel1 = header.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 13)); //create header two HSSFRow header2 = shet2.createRow(1); header2.setHeightInPoints(28); HSSFCell cel2 = null; for (int b = 1; b < 14; b++) { cel2 = header2.createCell(b); cel2.setCellValue(""); cel2.setCellStyle(style); } cel2 = header2.createCell(0); cel2.setCellValue("Percent Scores Per Domain"); cel2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(1, 1, 0, 13)); //cbo name //create header three //==========DATE OF VISIT String mywhere = " ass_date between '" + startdate + "' and '" + enddate + "' "; //if the current countyid is 0, then the where code should not specify the county name //=============================================================================================== //=============================================================================================== String getdomains = "SELECT domain_id,domain_name,section_name FROM domains join sections on domains.section_id=sections.section_id"; conn.rs = conn.st.executeQuery(getdomains); ArrayList domainids = new ArrayList(); int r = 2; HSSFRow theader = shet2.createRow(r); HSSFRow domainshd = shet2.createRow(r + 1); //create a row with title lip HSSFCell tce = domainshd.createCell(0); tce.setCellValue("LIP"); tce.setCellStyle(Y); int cnt = 1; while (conn.rs.next()) { HSSFCell tcel = theader.createCell(cnt); tcel.setCellValue(conn.rs.getString("section_name")); tcel.setCellStyle(Y); theader.setHeightInPoints(24); HSSFCell tcel1 = domainshd.createCell(cnt); tcel1.setCellValue(conn.rs.getString("domain_name")); tcel1.setCellStyle(Y); domainids.add(conn.rs.getString("domain_id")); cnt++; } //create avarage header HSSFCell tcel = domainshd.createCell(cnt); tcel.setCellValue("Avarage"); tcel.setCellStyle(Y); shet2.addMergedRegion(new CellRangeAddress(r, r, 1, 5)); shet2.addMergedRegion(new CellRangeAddress(r, r, 6, 12)); cnt++; // shet2.addMergedRegion(new CellRangeAddress(7,7,0,5)); // String gettables = "SELECT domain_name,domains.domain_id as domainid,section_name,domains.section_id as secid,value as domainvalue,aggregate_sum,period,year,site FROM domains join sections on domains.section_id=sections.section_id join domain_totals on domains.domain_id=domain_totals.domainid where "+mywhere+" order by domainid"; String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id where date between '" + startdate + "' and '" + enddate + "' group by cbo.cboid,domainid order by cbo,domainid"; //if its the first county, themn skip the county part System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 4; HSSFRow rwx = null; HSSFCell celx = null; String sectioncopy = ""; int rowcopy = 8; while (conn.rs.next()) { //if the section has changed String domainid = conn.rs.getString("domainid"); float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; //BigDecimal bd=new BigDecimal(domainvalue).setScale(0,RoundingMode.HALF_EVEN); //domainval=bd.doubleValue(); domainvalue = Math.round(domainvalue); float totalsum = conn.rs.getFloat("aggregate_sum"); // totalsum=totalsum*100; int dmn = (int) domainvalue; totalsum = Math.round(totalsum); //determine the cell to print data on int ttlsm = (int) totalsum; if (domainid.equals("1")) { rwx = shet2.createRow(rwcount); rwx.setHeightInPoints(22); HSSFCell celx2 = rwx.createCell(0); celx2.setCellValue("" + conn.rs.getString("cbo")); celx2.setCellStyle(dnamestyle); rwcount++; } for (int t = 0; t < domainids.size(); t++) { //if row is blank create it if (rwx == null) { rwx = shet2.createRow(rwcount); } if (domainids.get(t).equals(domainid)) { int ct = t + 1; HSSFCell celx1 = rwx.createCell(ct); celx1.setCellValue("" + dmn); celx1.setCellStyle(dnamestyle); rwx.setHeightInPoints(22); //System.out.println("worked in row ============="+rwcount+"__col "+(ct)+"_"+domainvalue); } } //incement if the column is the last if (domainid.equals("12")) { //create an avarage // System.out.println("XXXXX LAST LOOP"); if (rwx == null) { rwx = shet2.createRow(rwcount); } celx = rwx.createCell(13); rwx.setHeightInPoints(23); celx.setCellValue("" + ttlsm); celx.setCellStyle(dnamestyle); // rwcount++; } } ///=========================end of while loop //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=OVC_LIP_COUNTY_REPORT_" + startdate + "_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
/** * create a library of cell styles// www . ja v a 2 s . co m */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); DataFormat df = wb.createDataFormat(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); style.setDataFormat(df.getFormat("d-mmm")); styles.put("header_date", style); Font font1 = wb.createFont(); font1.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font1); styles.put("cell_b", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(font1); styles.put("cell_b_centered", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_b_date", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_g", style); Font font2 = wb.createFont(); font2.setColor(IndexedColors.BLUE.getIndex()); font2.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font2); styles.put("cell_bb", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(font1); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_bg", style); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 14); font3.setColor(IndexedColors.DARK_BLUE.getIndex()); font3.setBold(true); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(font3); style.setWrapText(true); styles.put("cell_h", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setWrapText(true); styles.put("cell_normal", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.CENTER); style.setWrapText(true); styles.put("cell_normal_centered", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.RIGHT); style.setWrapText(true); style.setDataFormat(df.getFormat("d-mmm")); styles.put("cell_normal_date", style); style = createBorderedStyle(wb); style.setAlignment(HorizontalAlignment.LEFT); style.setIndention((short) 1); style.setWrapText(true); styles.put("cell_indented", style); style = createBorderedStyle(wb); style.setFillForegroundColor(IndexedColors.BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("cell_blue", style); return styles; }
From source file:ro.dabuno.office.integration.LoadCalculator.java
/** * cell styles used for formatting calendar sheets *///from ww w .j a v a 2 s . c om private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); styles.put("title", style); Font itemFont = wb.createFont(); itemFont.setFontHeightInPoints((short) 9); itemFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.LEFT); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat( wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); styles.put("input_$", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.RIGHT); style.setFont(itemFont); style.setBorderRight(BorderStyle.DOTTED); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft(BorderStyle.DOTTED); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop(BorderStyle.DOTTED); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom(BorderStyle.DOTTED); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("formula_i", style); return styles; }
From source file:ru.wmbdiff.ExportIntoExcel.java
License:Apache License
public void export(File file, WMBDiffNoRootTreeTableModel model) { logger.info("export begin"); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("WMBDiff"); int rowNum = 0; //Create Header CellStyle style; Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontHeightInPoints((short) 10); style = workbook.createCellStyle();/*from w ww . jav a 2 s. c o m*/ style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); Row row = sheet.createRow(rowNum++); Cell cell; cell = row.createCell(0); cell.setCellValue("Result"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("Broker"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("Execution Group"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("Name"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("Type"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("Last Modification"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("Deployment Date"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("Bar File"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("Result Description"); cell.setCellStyle(style); sheet.createFreezePane(0, 1); List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList(); ListIterator<DiffExecutionGroup> litr = dEG.listIterator(); while (litr.hasNext()) { DiffExecutionGroup element = litr.next(); element.getDiffResultList(); ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator(); while (litr2.hasNext()) { DiffDeployedObjectResult res = litr2.next(); switch (res.getResult()) { case ONLY_IN_A: createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc()); break; case ONLY_IN_B: createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc()); break; case EQUAL: createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; case DIFF: createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; } ; } ; } ; //Adjust column width to fit the contents for (int i = 0; i < 9; i++) sheet.autoSizeColumn(i); //set Filter sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8)); try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); workbook.close(); out.close(); } catch (Exception e) { logger.error("export", e); } logger.info("export end"); }
From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java
License:Apache License
/** * {@inheritDoc}//from w w w .java 2s. c o m */ @Override @SuppressWarnings("all") public CellStyle getCellStyle(final ExcelElement el, final Workbook workbook) { // Check sanity Validate.notNull(workbook, "workbook"); Validate.notNull(el, "el"); // Acquire the el and Font as expected final CellStyle toReturn = workbook.createCellStyle(); final Font theFont = workbook.createFont(); switch (el) { case TITLE: theFont.setFontHeightInPoints((short) 18); theFont.setBold(true); theFont.setColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); break; case HEADER: theFont.setFontHeightInPoints((short) 11); theFont.setColor(IndexedColors.WHITE.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setWrapText(true); break; case CELL: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(true); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case NON_WRAPPING: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(false); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(GREY_25_PERCENT); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; case ALT_FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; default: throw new IllegalArgumentException( "Style [" + el.name() + "] was not defined. " + "Blame the programmer."); } // All done. toReturn.setFont(theFont); return toReturn; }
From source file:servlet.exportScoreSheet.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle();/*from w w w .j a v a 2 s.com*/ style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:test.poi.MyExcelDemo.java
License:Apache License
/** * Create a library of cell styles/*from ww w. j a v a 2 s. c om*/ */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.BLACK.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); // style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:Tools.CompareDrotVSRoster.java
private void foregroundColorSetUpV1(Row row, XSSFWorkbook myWorkBook) { if (row != null) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); //DataFormatter df = new DataFormatter(); // String lastFour = df.formatCellValue(ssnCell); //return ***-**-**** DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // String lastFour = df.formatCellValue(ssnCell); String lastFour = GlobalVar.last4Generator(df.formatCellValue(ssnCell)); Cell lastNameCell = row.getCell(GlobalVar.LAST_NAME_CELL_INDEX_V1); String lastName = null;/*from ww w. jav a 2s . co m*/ if (lastNameCell != null) { lastName = lastNameCell.getStringCellValue(); } //System.out.println(lastName); String ssn = DB.getSSN(lastName, lastFour); if (ssn != null) { Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); String ctrlNumString = ctrlNumCell.getStringCellValue(); if (proclibrary.containsKey(ssn)) { // Map< SSN, Map<ctrlNum, signOutdate>> List<String> leaves = proclibrary.get(ssn); if (leaves.contains(ctrlNumString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } else if (rejlibrary.containsKey(ssn)) { List<String> leaves = rejlibrary.get(ssn); if (leaves.contains(ctrlNumString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } else if (recylibrary.containsKey(ssn)) { List<String> leaves = recylibrary.get(ssn); if (leaves.contains(ctrlNumString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } } }
From source file:Tools.PostProcessing.java
private void colorDeletedLeaves(String ssn, Cell ctrlNumCell, XSSFWorkbook myWorkBook, Map<String, Integer> statusMap) { String ctrlNumString = null;/*from w w w .j a va2 s.c o m*/ if (ctrlNumCell != null && ctrlNumCell.getStringCellValue() != null) { ctrlNumString = ctrlNumCell.getStringCellValue().trim(); // color valid leaves CellStyle style = ctrlNumCell.getCellStyle(); String key = ssn + ctrlNumString; if (style.getFillForegroundColor() == IndexedColors.GREEN.getIndex()) { statusMap.put(key, GlobalVar.NO_ERR); if (!LEGIT_LV_MAP_FOR_COLOR_LV_LOG.containsKey(ssn)) { // Map< SSN, Map<ctrlNum, signOutdate>> style.setFillForegroundColor(GlobalVar.AUDITOR_DELETED_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); statusMap.put(key, GlobalVar.AUDIT_DELETE_ERR); } else { // contains SSN Map<String, String> list = LEGIT_LV_MAP_FOR_COLOR_LV_LOG.get(ssn); if (!list.keySet().contains(ctrlNumString)) { style.setFillForegroundColor(GlobalVar.AUDITOR_DELETED_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); statusMap.put(key, GlobalVar.AUDIT_DELETE_ERR); } } } else if (style.getFillForegroundColor() == GlobalVar.BAD_STATUS_COLOR) { statusMap.put(key, GlobalVar.BAD_STATUS_ERR); } else if (style.getFillForegroundColor() == GlobalVar.ETS_STATUS_COLOR) { statusMap.put(key, GlobalVar.ETS_ERR); } else if (style.getFillForegroundColor() == GlobalVar.DUPLICATE_LV_COLOR) { statusMap.put(key, GlobalVar.DUPLICATE_LV_ERR); } else if (style.getFillForegroundColor() == GlobalVar.DUPLICATE_CTRL_NUM_COLOR) { statusMap.put(key, GlobalVar.DUPLICATE_CTRL_NUM_ERR); } else if (style.getFillForegroundColor() == GlobalVar.AFTER_PCS_COLOR) { statusMap.put(key, GlobalVar.AFTER_PCS_ERR); } else if (style.getFillForegroundColor() == GlobalVar.INPROCESSING_COLOR) { statusMap.put(key, GlobalVar.INPROCESSING_ERR); } else if (style.getFillForegroundColor() == GlobalVar.WRONG_SSN_COLOR) { statusMap.put(key, GlobalVar.WRONG_SSN_ERR); } else if (style.getFillForegroundColor() == GlobalVar.OVERLAP_LV_COLOR) { statusMap.put(key, GlobalVar.OVERLAP_LV_ERR); } else if (style.getFillForegroundColor() == GlobalVar.AUDITOR_DELETED_COLOR) { statusMap.put(key, GlobalVar.AUDIT_DELETE_ERR); } else { statusMap.put(key, GlobalVar.IN_CYCLE_DUPLICATION_INVALID_FIRST_FIVE_ERR); } } }
From source file:Utilities.BatchInDJMSHelper.java
private void colorLeaves(String ssn, Cell ctrlNumCell, String soDate, XSSFWorkbook myWorkBook, Map<String, Map<String, String>> legitLvMap) { String ctrlNumString = null;//w ww. j a va2 s. c o m if (ctrlNumCell != null) { ctrlNumString = ctrlNumCell.getStringCellValue().trim(); } //String ctrlNumString = // color valid leaves if (legitLvMap != null && legitLvMap.containsKey(ssn)) { // Map< SSN, Map<ctrlNum, signOutdate>> Map<String, String> value = legitLvMap.get(ssn); if (value.containsKey(ctrlNumString) && value.get(ctrlNumString).equals(soDate)) { CellStyle style = GlobalVar.createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); value.remove(ctrlNumString); return; // colored the cell } } // end of coloring valid leaves // color invalid leaves if (INVALID_LV_MAP.containsKey(ssn)) { String msg = INVALID_LV_MAP.get(ssn); colorInvalidLeave(msg, myWorkBook, ctrlNumCell); // might contain more than one bad leaves because of ssn } else if (INVALID_LV_MAP.containsKey(ctrlNumString)) { String msg = INVALID_LV_MAP.get(ctrlNumString); //INVALID_LV_MAP.remove(ctrlNumString); colorInvalidLeave(msg, myWorkBook, ctrlNumCell); } }