List of usage examples for org.apache.poi.hssf.usermodel HSSFFont setColor
public void setColor(short color)
From source file:HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from w w w. j a va2 s. c om */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); 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 = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 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 FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); }
From source file:br.com.nfsconsultoria.azcontrole.bean.VendaBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/*ww w . ja v a 2 s. c o m*/ HSSFRow header = sheet.getRow(0); HSSFFont font = wb.createFont(); font.setBold(true); font.setColor(HSSFColor.WHITE.index); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setWrapText(true); cellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); cellStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:br.com.pontocontrol.controleponto.controller.impl.ExportadorXLSController.java
private void formatHeaderRow(HSSFWorkbook workbook, HSSFRow row) { HSSFFont headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Arial"); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setBoldweight((short) 800); headerFont.setItalic(false);/*from w w w.j a v a 2s .com*/ HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex()); headerStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex()); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); row.cellIterator().forEachRemaining((cell) -> { cell.setCellStyle(headerStyle); }); }
From source file:cn.mario256.blog.ExcelView.java
License:Open Source License
/** * ?Excel//from w w w.ja va2s . com * * @param model * ? * @param workbook * HSSFWorkbook * @param request * HttpServletRequest * @param response * HttpServletResponse */ 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:com.abacus.reports.ExcelBuilder.java
@Override protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List headerlist = (List) map.get("header"); List<Object[]> data = (List) map.get("data"); String reportname = String.valueOf(map.get("report_name")); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet(reportname); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment; filename=" + reportname + ".xls"); OutputStream outStream = response.getOutputStream(); sheet.setDefaultColumnWidth(30);//from w w w . ja va 2s . c om // create style for header cells CellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); HSSFPalette palette = workbook.getCustomPalette(); HSSFColor color = palette.findSimilarColor(152, 35, 17); short paindex = color.getIndex(); font.setFontName("Trebuchet MS"); style.setFillForegroundColor(paindex); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create header row HSSFRow header = sheet.createRow(0); int row = 0; for (Object headerlist1 : headerlist) { header.createCell(row).setCellValue(String.valueOf(headerlist1)); header.getCell(row).setCellStyle(style); row++; } CellStyle style2 = workbook.createCellStyle(); HSSFFont font2 = workbook.createFont(); font2.setFontName("Trebuchet MS"); style2.setFont(font2); System.out.println("data.size(): " + data.size()); int rownum = 1; // create data rows for (int rowCount = 0; rowCount < data.size(); rowCount++) { HSSFRow aRow = sheet.createRow(rownum); Object[] value = data.get(rowCount); int col = 0; for (Object value1 : value) { HSSFCell cell = aRow.createCell(col++); cell.setCellStyle(style2); if (value1 instanceof java.lang.String) cell.setCellValue(String.valueOf(value1)); if (value1 instanceof java.lang.Integer) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Boolean) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Double) cell.setCellValue(Double.parseDouble(String.valueOf(value1))); if (value1 instanceof java.lang.Float) cell.setCellValue(Float.parseFloat(String.valueOf(value1))); } rownum++; } workbook.write(outStream); outStream.close(); }
From source file:com.ah.ui.actions.admin.LicenseMgrAction.java
License:Open Source License
private void exportEntitleKeyInfo() { try {//from w w w .ja v a2s . c o m // create a new file FileOutputStream out = new FileOutputStream(ORDERKEYINFO_EXPORT_FILE_PATH); // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet s = wb.createSheet("Sheet1"); // declare a row object reference HSSFRow r = null; // declare a cell object reference HSSFCell c = null; // row index int rowNum = 0; // cell count int cellcount = 0; if (NmsUtil.isHMForOEM()) { cellcount = 7; } else if (getIsInHomeDomain()) { cellcount = 8; } else if (NmsUtil.isHostedHMApplication()) { cellcount = 9; } if (cellcount == 0) { return; } else { for (int i = 0; i < cellcount; i++) { s.setColumnWidth(i, getColumnWidthByIndex(i) * 256); } } // create cell style HSSFCellStyle cs = wb.createCellStyle(); // create font object HSSFFont f = wb.createFont(); //set font to 12 point type f.setFontHeightInPoints((short) 12); f.setFontName("Calibri"); //set cell stlye cs.setFont(f); cs.setAlignment(CellStyle.ALIGN_CENTER); cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs1 = wb.createCellStyle(); // create font object HSSFFont f1 = wb.createFont(); //set font to 12 point type f1.setFontHeightInPoints((short) 12); f1.setFontName("Calibri"); f1.setBoldweight(Font.BOLDWEIGHT_BOLD); //set cell stlye cs1.setFont(f1); cs1.setAlignment(CellStyle.ALIGN_RIGHT); cs1.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setFont(f1); cs2.setAlignment(CellStyle.ALIGN_CENTER); cs2.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs3 = wb.createCellStyle(); cs3.setFont(f); cs3.setAlignment(CellStyle.ALIGN_RIGHT); cs3.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs4 = wb.createCellStyle(); cs4.setFont(f1); cs4.setAlignment(CellStyle.ALIGN_LEFT); cs4.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs5 = wb.createCellStyle(); // create font object HSSFFont f2 = wb.createFont(); //set font to 12 point type f2.setFontHeightInPoints((short) 12); f2.setFontName("Calibri"); f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setColor(Font.COLOR_RED); cs5.setFont(f2); cs5.setAlignment(CellStyle.ALIGN_LEFT); cs5.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create cell style HSSFCellStyle cs6 = wb.createCellStyle(); cs6.setFont(f); cs6.setAlignment(CellStyle.ALIGN_LEFT); cs6.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); // create a row // row 1 s.addMergedRegion(new CellRangeAddress(0, 0, 0, cellcount - 1)); r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.title")); c.setCellStyle(cs); // row 2 String sysInfo = ""; if (getIsInHomeDomain()) { sysInfo = "System ID: " + getSystemId(); // HiveManager Online user } else if (NmsUtil.isHostedHMApplication()) { sysInfo = "VHM ID: " + getDomain().getVhmID(); } s.addMergedRegion(new CellRangeAddress(1, 1, 0, cellcount - 1)); r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(sysInfo); c.setCellStyle(cs); // row 3 s.addMergedRegion(new CellRangeAddress(2, 2, 0, cellcount - 1)); r = s.createRow(rowNum++); c = r.createCell(0); List<?> userInfo = QueryUtil.executeQuery( "SELECT email, company FROM " + UserRegInfoForLs.class.getSimpleName(), null, new FilterParams("owner.domainName", getDomain().getDomainName())); if (!userInfo.isEmpty()) { Object[] userInfos = (Object[]) userInfo.get(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.email.company", new String[] { (String) userInfos[0], (String) userInfos[1] })); } c.setCellStyle(cs); // row 4 r = s.createRow(rowNum++); // row 5 r = s.createRow(rowNum++); // row 6 cell 1 c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.licensed")); c.setCellStyle(cs1); // row 6 cell 2 c = r.createCell(1); c.setCellValue(licenseInfo.getHiveAps()); c.setCellStyle(cs2); // device management info Map<String, Integer> apcount = HiveAPInfoFromeDatabase .getManagedDeviceTypeAndNumber(getDomain().getDomainName()); int vpnCount = 0; int totalCount = 0; if (null != apcount) { Object[] typeNames = apcount.keySet().toArray(); Arrays.sort(typeNames); for (Object typeName : typeNames) { // VPN Gateway VA does not belong to device if (MgrUtil.getEnumString("enum.hiveAp.model.10").equals(typeName)) { vpnCount = apcount.get(typeName); } else { r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue((String) typeName); c.setCellStyle(cs3); c = r.createCell(1); c.setCellValue(apcount.get(typeName)); c.setCellStyle(cs); totalCount += apcount.get(typeName); } } } // managed device total number cell1 r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.total")); c.setCellStyle(cs1); // managed device total number cell2 c = r.createCell(1); c.setCellValue(totalCount); c.setCellStyle(cs2); // blank row r = s.createRow(rowNum++); // licensed VPN Gateway VA cell1 r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.licensed")); c.setCellStyle(cs1); // licensed VPN Gateway VA cell2 c = r.createCell(1); c.setCellValue(licenseInfo.getCvgNumber()); c.setCellStyle(cs2); // managed VPN Gateway VA cell1 r = s.createRow(rowNum++); c = r.createCell(0); c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.total")); c.setCellStyle(cs1); // managed VPN Gateway VA cell 2 c = r.createCell(1); c.setCellValue(vpnCount); c.setCellStyle(cs2); // entitlement key information preparePage(); if (null != page && !page.isEmpty()) { r = s.createRow(rowNum++); List<OrderHistoryInfo> normalKey = new ArrayList<>(); List<OrderHistoryInfo> invalidKey = new ArrayList<>(); List<OrderHistoryInfo> expiredKey = new ArrayList<>(); for (Object obj : page) { OrderHistoryInfo orderInfo = (OrderHistoryInfo) obj; if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL && orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL) { normalKey.add(orderInfo); } else if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE || orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE) { invalidKey.add(orderInfo); } else { expiredKey.add(orderInfo); } } if (!normalKey.isEmpty()) { // normal entitle key title r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs4, null, r, null, MgrUtil.getUserMessage("order.key")); // normal entitle key info for (OrderHistoryInfo keyInfo : normalKey) { r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null); } } if (!invalidKey.isEmpty()) { if (!normalKey.isEmpty()) { r = s.createRow(rowNum++); r = s.createRow(rowNum++); } // invalid entitle key title r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs5, null, r, null, MgrUtil.getUserMessage("admin.license.orderkey.export.invalidkey.title")); // invalid entitle key info for (OrderHistoryInfo keyInfo : invalidKey) { r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null); } } if (!expiredKey.isEmpty()) { if (!normalKey.isEmpty() || !invalidKey.isEmpty()) { r = s.createRow(rowNum++); r = s.createRow(rowNum++); } // expired entitle key title r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs5, null, r, null, MgrUtil.getUserMessage("admin.license.orderkey.export.expiredkey.title")); // expired entitle key info for (OrderHistoryInfo keyInfo : expiredKey) { r = s.createRow(rowNum++); setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null); } } } // write the workbook to the output stream // close our file (don't blow out our file handles) wb.write(out); out.close(); generateAuditLog(HmAuditLog.STATUS_SUCCESS, MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key")); } catch (Exception ex) { generateAuditLog(HmAuditLog.STATUS_FAILURE, MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key")); log.error("exportEntitleKeyInfo()", ex.getMessage()); } }
From source file:com.app.ExcelView.java
License:Open Source License
/** * ?Excel/*w w w . j ava 2s . c om*/ * * @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" + " " + "A" + "P" + "P" + "T" + "E" + "A" + "M")); 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:com.bdx.rainbow.spsy.service.impl.EnterpriseLicenseServiceImpl.java
/** * * @param title ??//from w w w. j a v a 2 s. c o m * @param headers ?? * @param resultLists ? * @param pattern ?,"yyyy-MM-dd" */ private HSSFWorkbook exportExcel(String title, String[] headers, List<DubboEnterpriseLicense> resultLists, String pattern) { if (resultLists == null) { return null; } if (pattern == null && StringUtils.isEmpty(pattern)) { pattern = "yyyy-MM-dd"; } SimpleDateFormat sdf = new SimpleDateFormat(pattern); // HSSFWorkbook workbook = new HSSFWorkbook(); // ? HSSFSheet sheet = workbook.createSheet(title); // 15 sheet.setDefaultColumnWidth((short) 15); // ?? HSSFCellStyle style = workbook.createCellStyle(); // ? style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // ?? style.setFont(font); // ??? HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // ?? HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // ?? style2.setFont(font2); // HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // ????? for (int i = 0; i < resultLists.size(); i++) { row = sheet.createRow(i + 1); int index = 0; // HSSFCell cell = row.createCell(j); // row.createCell(index++).setCellStyle(style2); DubboEnterpriseLicense info = resultLists.get(i); row.createCell(index++).setCellValue(info.getEnterpriseName()); row.createCell(index++).setCellValue(info.getOrganizationCode()); row.createCell(index++).setCellValue(info.getLegalPerson()); row.createCell(index++).setCellValue(info.getLegalPersonPhone()); String type = info.getLicenseType(); if ("001".equals(type)) { row.createCell(index++).setCellValue(""); } else if ("002".equals(type)) { row.createCell(index++).setCellValue("??"); } else if ("003".equals(type)) { row.createCell(index++).setCellValue(""); } else if ("004".equals(type)) { row.createCell(index++).setCellValue("?"); } else { row.createCell(index++).setCellValue(type); } row.createCell(index++).setCellValue(info.getLicenseCode()); if (info.getLicenseTime() != null) { row.createCell(index++).setCellValue(sdf.format(info.getLicenseTime())); } else { row.createCell(index++).setCellValue(""); } if (info.getInvalidDate() != null) { Long time = (System.currentTimeMillis() - info.getInvalidDate().getTime()) / (1000 * 3600 * 24); if (time > 0) { row.createCell(index++).setCellValue(""); } else if (time <= 0) { row.createCell(index++).setCellValue(""); } else { row.createCell(index++).setCellValue("1"); } } else { row.createCell(index++).setCellValue(""); } } return workbook; }
From source file:com.commander4j.util.JExcel.java
License:Open Source License
public void exportToExcel(String filename, ResultSet rs) { try {/*from w w w . j av a 2s . com*/ ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int columnType = 0; String columnTypeName = ""; int recordNumber = 0; int passwordCol = -1; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFCellStyle cellStyle_varchar = workbook.createCellStyle(); cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle(); cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle(); cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_title = workbook.createCellStyle(); cellStyle_title.setAlignment(HorizontalAlignment.CENTER); HSSFCellStyle cellStyle_char = workbook.createCellStyle(); cellStyle_char.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_date = workbook.createCellStyle(); cellStyle_date.setAlignment(HorizontalAlignment.CENTER); cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle(); cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER); cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_decimal = workbook.createCellStyle(); cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT); HSSFFont font_title = workbook.createFont(); font_title.setColor((short) 0xc); font_title.setBold(true); ; font_title.setItalic(true); font_title.setUnderline(HSSFFont.U_DOUBLE); cellStyle_title.setFont(font_title); HSSFCell cell; HSSFRow row; // rs.beforeFirst(); while (rs.next()) { recordNumber++; if (recordNumber == 1) { row = sheet.createRow((int) 0); for (int column = 1; column <= numberOfColumns; column++) { cell = row.createCell((int) (column - 1)); String columnName = rsmd.getColumnLabel(column); columnName = columnName.replace("_", " "); columnName = JUtility.capitalize(columnName); cell.setCellStyle(cellStyle_title); cell.setCellValue(columnName); if (columnName.equals("Password")) { passwordCol = column; } } } row = sheet.createRow((int) recordNumber); for (int column = 1; column <= numberOfColumns; column++) { columnType = rsmd.getColumnType(column); columnTypeName = rsmd.getColumnTypeName(column); cell = row.createCell((int) (column - 1)); try { switch (columnType) { case java.sql.Types.NVARCHAR: HSSFRichTextString rtf_nvarchar; if (column == passwordCol) { rtf_nvarchar = new HSSFRichTextString("*****"); } else { rtf_nvarchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_nvarchar); cell.setCellValue(rtf_nvarchar); break; case java.sql.Types.VARCHAR: HSSFRichTextString rtf_varchar; if (column == passwordCol) { rtf_varchar = new HSSFRichTextString("*****"); } else { rtf_varchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_varchar); break; case java.sql.Types.CHAR: HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column)); cell.setCellStyle(cellStyle_char); cell.setCellValue(rtf_char); break; case java.sql.Types.DATE: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_date); } catch (Exception ex) { } break; case java.sql.Types.TIMESTAMP: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_timestamp); } catch (Exception ex) { } break; case java.sql.Types.DECIMAL: HSSFRichTextString rtf_decimal = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimal); break; case java.sql.Types.NUMERIC: HSSFRichTextString rtf_decimaln = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimaln); break; case java.sql.Types.BIGINT: HSSFRichTextString rtf_bigint = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_bigint); break; case java.sql.Types.INTEGER: HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_int); break; case java.sql.Types.FLOAT: HSSFRichTextString rtf_float = new HSSFRichTextString( String.valueOf(rs.getFloat(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_float); break; case java.sql.Types.DOUBLE: HSSFRichTextString rtf_double = new HSSFRichTextString( String.valueOf(rs.getDouble(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_double); break; default: cell.setCellValue(new HSSFRichTextString(columnTypeName)); break; } } catch (Exception ex) { String errormessage = ex.getLocalizedMessage(); HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage); cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_exception); break; } } if (recordNumber == 65535) { break; } } for (int column = 1; column <= numberOfColumns; column++) { sheet.autoSizeColumn((int) (column - 1)); } if (recordNumber > 0) { try { FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase()); workbook.write(fileOut); fileOut.close(); } catch (Exception ex) { setErrorMessage(ex.getMessage()); } } try { workbook.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (SQLException e) { setErrorMessage(e.getMessage()); } }
From source file:com.creativity.controller.PesquisaFichasBean.java
public void posProcessarXls(Object documento) { HSSFWorkbook planilha = (HSSFWorkbook) documento; HSSFSheet folha = planilha.getSheetAt(0); HSSFRow cabecalho = folha.getRow(0); HSSFCellStyle estiloCelula = planilha.createCellStyle(); HSSFFont fonteCabecalho = planilha.createFont(); fonteCabecalho.setColor(IndexedColors.BLACK.getIndex()); fonteCabecalho.setBold(true);/* w w w. j a va2 s. c o m*/ fonteCabecalho.setFontHeightInPoints((short) 10); estiloCelula.setFont(fonteCabecalho); estiloCelula.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); estiloCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) { cabecalho.getCell(i).setCellStyle(estiloCelula); } }