List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setVerticalAlignment
@Override public void setVerticalAlignment(VerticalAlignment align)
From source file:com.cms.utils.CommonUtils.java
/** * * @param workbook//from w ww .j a va 2 s . c o m * @return HSSFCellStyle */ public static HSSFCellStyle styleCenter(HSSFWorkbook workbook) { //giua HSSFCellStyle cellStyleCenter = workbook.createCellStyle(); cellStyleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleCenter.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyleCenter.setWrapText(false); return cellStyleCenter; }
From source file:com.cms.utils.CommonUtils.java
/** * * @param workbook//from w w w. j a v a 2s. co m * @return HSSFCellStyle */ public static HSSFCellStyle styleCell(HSSFWorkbook workbook) { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setWrapText(false); return cellStyle; }
From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java
License:Open Source License
public void postProcessXls(Object document) { logger.trace("postProcessXls start document " + document); if (document != null) { HSSFWorkbook workBook = (HSSFWorkbook) document; HSSFSheet sheet = workBook.getSheetAt(0); HSSFRow headerRow = sheet.getRow(0); for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { sheet.setColumnWidth(i, 30 * 265); // width for 40 characters }//from ww w . j ava2 s . c o m sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n // by 1 to get space // for header sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3")); HSSFFont headerFont = workBook.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell headerCell = headerRow.createCell(0); headerCell.setCellStyle(headerCellStyle); headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date())); HSSFCellStyle metaDataCellStyle = workBook.createCellStyle(); metaDataCellStyle.setFont(headerFont); HSSFRow metaDataRow = sheet.getRow(3); if (metaDataRow == null) { metaDataRow = sheet.createRow(3); } HSSFCell metaDataKey = metaDataRow.createCell(0); metaDataKey.setCellStyle(metaDataCellStyle); metaDataKey.setCellValue("CATS Instance"); HSSFCell metaDataValue = metaDataRow.createCell(1); metaDataValue.setCellStyle(metaDataCellStyle); metaDataValue.setCellValue(AuthController.getHostAddress()); HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle(); datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); datatTableHeaderCellStyle.setFont(headerFont); HSSFRow actualDataTableHeaderRow = sheet.getRow(5); for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) { HSSFCell cell = actualDataTableHeaderRow.getCell(i); if (cell != null) { String cellValue = cell.getStringCellValue(); cellValue = cellValue.replace("<br/> ", ""); // replace // any line // breaks cell.setCellValue(cellValue); cell.setCellStyle(datatTableHeaderCellStyle); } } } logger.trace("postProcessXls end"); }
From source file:com.demo.common.extreme.view.XlsView.java
License:Apache License
private Map initStyles(HSSFWorkbook wb, short fontHeight) { Map result = new HashMap(); HSSFCellStyle titleStyle = wb.createCellStyle(); HSSFCellStyle textStyle = wb.createCellStyle(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFCellStyle numericStyle = wb.createCellStyle(); HSSFCellStyle numericStyleBold = wb.createCellStyle(); HSSFCellStyle moneyStyle = wb.createCellStyle(); HSSFCellStyle moneyStyleBold = wb.createCellStyle(); HSSFCellStyle percentStyle = wb.createCellStyle(); HSSFCellStyle percentStyleBold = wb.createCellStyle(); // Add to export totals HSSFCellStyle moneyStyle_Totals = wb.createCellStyle(); HSSFCellStyle naStyle_Totals = wb.createCellStyle(); HSSFCellStyle numericStyle_Totals = wb.createCellStyle(); HSSFCellStyle percentStyle_Totals = wb.createCellStyle(); HSSFCellStyle textStyle_Totals = wb.createCellStyle(); result.put("titleStyle", titleStyle); result.put("textStyle", textStyle); result.put("boldStyle", boldStyle); result.put("numericStyle", numericStyle); result.put("numericStyleBold", numericStyleBold); result.put("moneyStyle", moneyStyle); result.put("moneyStyleBold", moneyStyleBold); result.put("percentStyle", percentStyle); result.put("percentStyleBold", percentStyleBold); // Add to export totals result.put("moneyStyle_Totals", moneyStyle_Totals); result.put("naStyle_Totals", naStyle_Totals); result.put("numericStyle_Totals", numericStyle_Totals); result.put("percentStyle_Totals", percentStyle_Totals); result.put("textStyle_Totals", textStyle_Totals); HSSFDataFormat format = wb.createDataFormat(); // Global fonts HSSFFont font = wb.createFont();/* ww w .j av a2s . c o m*/ font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font.setColor(HSSFColor.BLACK.index); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints(fontHeight); HSSFFont fontBold = wb.createFont(); fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fontBold.setColor(HSSFColor.BLACK.index); fontBold.setFontName(HSSFFont.FONT_ARIAL); fontBold.setFontHeightInPoints(fontHeight); // Money Style moneyStyle.setFont(font); moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyle.setDataFormat(format.getFormat(moneyFormat)); // Money Style Bold moneyStyleBold.setFont(fontBold); moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyleBold.setDataFormat(format.getFormat(moneyFormat)); // Percent Style percentStyle.setFont(font); percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyle.setDataFormat(format.getFormat(percentFormat)); // Percent Style Bold percentStyleBold.setFont(fontBold); percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyleBold.setDataFormat(format.getFormat(percentFormat)); // Standard Numeric Style numericStyle.setFont(font); numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Standard Numeric Style Bold numericStyleBold.setFont(fontBold); numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // Title Style titleStyle.setFont(font); titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleStyle.setBottomBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titleStyle.setLeftBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleStyle.setRightBorderColor(HSSFColor.BLACK.index); titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleStyle.setTopBorderColor(HSSFColor.BLACK.index); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Standard Text Style textStyle.setFont(font); textStyle.setWrapText(true); // Standard Text Style boldStyle.setFont(fontBold); boldStyle.setWrapText(true); // Money Style Total moneyStyle_Totals.setFont(fontBold); moneyStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); moneyStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); moneyStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); moneyStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); moneyStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); moneyStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); moneyStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); moneyStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); moneyStyle_Totals.setDataFormat(format.getFormat(moneyFormat)); // n/a Style Total naStyle_Totals.setFont(fontBold); naStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); naStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); naStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); naStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); naStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); naStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); naStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); naStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Numeric Style Total numericStyle_Totals.setFont(fontBold); numericStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); numericStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); numericStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); numericStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); numericStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); numericStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); numericStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); numericStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // Percent Style Total percentStyle_Totals.setFont(fontBold); percentStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); percentStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); percentStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); percentStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); percentStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); percentStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); percentStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_RIGHT); percentStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); percentStyle_Totals.setDataFormat(format.getFormat(percentFormat)); // Text Style Total textStyle_Totals.setFont(fontBold); textStyle_Totals.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); textStyle_Totals.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); textStyle_Totals.setBorderBottom(HSSFCellStyle.BORDER_THIN); textStyle_Totals.setBottomBorderColor(HSSFColor.BLACK.index); textStyle_Totals.setBorderTop(HSSFCellStyle.BORDER_THIN); textStyle_Totals.setTopBorderColor(HSSFColor.BLACK.index); textStyle_Totals.setAlignment(HSSFCellStyle.ALIGN_LEFT); textStyle_Totals.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return result; }
From source file:com.develog.utils.report.engine.export.JRXlsExporter.java
License:Open Source License
/** * *//*w w w. j av a 2 s . c om*/ protected HSSFCellStyle getLoadedCellStyle(short mode, short backcolor, short horizontalAlignment, short verticalAlignment, short rotation, HSSFFont font) { HSSFCellStyle cellStyle = null; if (loadedCellStyles != null && loadedCellStyles.size() > 0) { HSSFCellStyle cs = null; for (int i = 0; i < loadedCellStyles.size(); i++) { cs = (HSSFCellStyle) loadedCellStyles.get(i); if (cs.getFillPattern() == mode && cs.getFillForegroundColor() == backcolor && cs.getAlignment() == horizontalAlignment && cs.getVerticalAlignment() == verticalAlignment && cs.getRotation() == rotation && cs.getFontIndex() == font.getIndex()) { cellStyle = cs; break; } } } if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(backcolor); cellStyle.setFillPattern(mode); cellStyle.setAlignment(horizontalAlignment); cellStyle.setVerticalAlignment(verticalAlignment); cellStyle.setRotation(rotation); cellStyle.setFont(font); cellStyle.setWrapText(true); loadedCellStyles.add(cellStyle); } return cellStyle; }
From source file:com.dp2345.ExcelView.java
License:Open Source License
/** * ?Excel//w w w. j a v a 2 s. co 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" + " " + "D" + "P" + "2" + "3" + "4" + "5")); 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.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb, HSSFCellStyle src) {//from w ww .j a v a 2 s . co m if (src == null || dest == null) return; dest.setAlignment(src.getAlignment()); dest.setBorderBottom(src.getBorderBottom()); dest.setBorderLeft(src.getBorderLeft()); dest.setBorderRight(src.getBorderRight()); dest.setBorderTop(src.getBorderTop()); dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb)); dest.setDataFormat( destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat()))); dest.setFillPattern(src.getFillPattern()); dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb)); dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb)); dest.setHidden(src.getHidden()); dest.setIndention(src.getIndention()); dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb)); dest.setLocked(src.getLocked()); dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb)); dest.setRotation(src.getRotation()); dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb)); dest.setVerticalAlignment(src.getVerticalAlignment()); dest.setWrapText(src.getWrapText()); HSSFFont f = srcwb.getFontAt(src.getFontIndex()); HSSFFont nf = findFont(f, srcwb, destwb); if (nf == null) { nf = destwb.createFont(); nf.setBoldweight(f.getBoldweight()); nf.setCharSet(f.getCharSet()); nf.setColor(findColor(f.getColor(), srcwb, destwb)); nf.setFontHeight(f.getFontHeight()); nf.setFontHeightInPoints(f.getFontHeightInPoints()); nf.setFontName(f.getFontName()); nf.setItalic(f.getItalic()); nf.setStrikeout(f.getStrikeout()); nf.setTypeOffset(f.getTypeOffset()); nf.setUnderline(f.getUnderline()); } dest.setFont(nf); }
From source file:com.esd.cs.common.PoiCreateExcel.java
License:Open Source License
/** * /*from ww w . ja va 2 s . c o m*/ * * @param FilePath * @param companyList * @return */ public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow0 = sheet.createRow(0); HSSFCell headCell = headRow0.createCell(0); // ?? sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ??? headCell = headRow0.createCell(0); // headCell.setCellValue(model.getTitle()); // ? HSSFCellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(HSSFColor.GREEN.index); style.setAlignment(CellStyle.ALIGN_CENTER);// style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// // HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // style.setFont(font); headCell.setCellStyle(style); // ? ?? HSSFRow RowTow = sheet.createRow(1); HSSFCell CellTow = headRow0.createCell(1); // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ??? CellTow = RowTow.createCell(0); // CellTow.setCellValue(model.getCreateCompany()); // ? // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ??? CellTow = RowTow.createCell(6); HSSFCellStyle style1 = wb.createCellStyle(); style1.setFillBackgroundColor(HSSFColor.GREEN.index); style1.setAlignment(CellStyle.ALIGN_RIGHT);// ? CellTow.setCellStyle(style1); // CellTow.setCellValue(model.getCreateData()); // ? HSSFRow headRow = sheet.createRow(2); HSSFCell headell = headRow.createCell(2); // ??? headell = headRow.createCell(0); headell.setCellValue(model.getType()); headell = headRow.createCell(1); headell.setCellValue("??"); headell = headRow.createCell(2); headell.setCellValue("??"); sheet.setColumnWidth(2, 3000); // headell = headRow.createCell(3); headell.setCellValue("???"); sheet.setColumnWidth(3, 3000); // headell = headRow.createCell(4); headell.setCellValue("???"); sheet.setColumnWidth(4, 4000); // headell = headRow.createCell(5); headell.setCellValue("???"); sheet.setColumnWidth(5, 4000); // headell = headRow.createCell(6); headell.setCellValue("???"); sheet.setColumnWidth(6, 4500); // headell = headRow.createCell(7); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(8); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(9); headell.setCellValue(""); sheet.setColumnWidth(9, 4000); headell = headRow.createCell(10); headell.setCellValue("?"); sheet.setColumnWidth(10, 4000); headell = headRow.createCell(11); headell.setCellValue("???"); headell = headRow.createCell(12); headell.setCellValue("?"); headell = headRow.createCell(13); headell.setCellValue("?"); for (int i = 0; i < companyList.size(); i++) { ReportViewModel company = companyList.get(i); // Excel? HSSFRow row = sheet.createRow(i + 3); HSSFCell cell = row.createCell(i + 3); // ??? // ???? cell = row.createCell(0); cell.setCellValue(company.getReportName()); // ?? cell = row.createCell(1); cell.setCellValue(company.getUnitNum()); // ?? cell = row.createCell(2); cell.setCellValue(company.getEmpTotal()); // ??? cell = row.createCell(3); cell.setCellValue(company.getUnAudit()); // ?, ??? cell = row.createCell(4); cell.setCellValue(company.getUnReAudit()); // ?, ?? cell = row.createCell(5); cell.setCellValue(company.getAuditOk()); // ?, ?? cell = row.createCell(6); cell.setCellValue(company.getUnauditOk()); // cell = row.createCell(7); cell.setCellValue(company.getShouldTotal().toString()); // ? cell = row.createCell(8); cell.setCellValue(company.getAlreadyTotal().toString()); // cell = row.createCell(9); cell.setCellValue(company.getLessTotal().toString()); // ? cell = row.createCell(10); cell.setCellValue(company.getAmountPayable().toString()); // ??? cell = row.createCell(11); cell.setCellValue(company.getReductionAmount().toString()); // ? cell = row.createCell(12); cell.setCellValue(company.getActualAmount().toString()); // ? cell = row.createCell(13); cell.setCellValue(company.getAlreadyAmount().toString()); } // ? HSSFRow row = sheet.createRow(companyList.size() + 3); HSSFCell cell = row.createCell(companyList.size() + 3); // ??? // ???? sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ??? cell = row.createCell(0); // ? HSSFCellStyle styleFoot = wb.createCellStyle(); styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ? cell.setCellStyle(styleFoot); // cell.setCellValue(model.getCreatePeople()); try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); companyList.clear(); companyList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; }
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
private static HSSFCellStyle getHeadCellStyle(HSSFWorkbook wb, HSSFFont font) {// HSSFCellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font);//from www . j ava 2 s . c o m headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headCellStyle.setWrapText(false); headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return headCellStyle; }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell templateCell = dataObject.templateCell; HSSFCell resultCell = dataObject.resultCell; BandData bandData = dataObject.bandData; HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook(); HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook(); String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); String styleName = (String) bandData.getParameterValue(paramName); if (styleName == null) continue; HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName); if (cellStyle == null) continue; HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle); if (resultStyle == null) { HSSFCellStyle newStyle = resultWorkbook.createCellStyle(); // color newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor()); newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor()); newStyle.setFillPattern(cellStyle.getFillPattern()); // borders newStyle.setBorderLeft(cellStyle.getBorderLeft()); newStyle.setBorderRight(cellStyle.getBorderRight()); newStyle.setBorderTop(cellStyle.getBorderTop()); newStyle.setBorderBottom(cellStyle.getBorderBottom()); // border colors newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor()); newStyle.setRightBorderColor(cellStyle.getRightBorderColor()); newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor()); newStyle.setTopBorderColor(cellStyle.getTopBorderColor()); // alignment newStyle.setAlignment(cellStyle.getAlignment()); newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment()); // misc DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat(); newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString())); newStyle.setHidden(cellStyle.getHidden()); newStyle.setLocked(cellStyle.getLocked()); newStyle.setIndention(cellStyle.getIndention()); newStyle.setRotation(cellStyle.getRotation()); newStyle.setWrapText(cellStyle.getWrapText()); // font HSSFFont cellFont = cellStyle.getFont(templateWorkbook); HSSFFont newFont = fontCache.getFontByTemplate(cellFont); if (newFont == null) { newFont = resultWorkbook.createFont(); newFont.setFontName(cellFont.getFontName()); newFont.setItalic(cellFont.getItalic()); newFont.setStrikeout(cellFont.getStrikeout()); newFont.setTypeOffset(cellFont.getTypeOffset()); newFont.setBoldweight(cellFont.getBoldweight()); newFont.setCharSet(cellFont.getCharSet()); newFont.setColor(cellFont.getColor()); newFont.setUnderline(cellFont.getUnderline()); newFont.setFontHeight(cellFont.getFontHeight()); newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints()); fontCache.addCachedFont(cellFont, newFont); }/*from w ww .j a v a2 s . co m*/ newStyle.setFont(newFont); resultStyle = newStyle; styleCache.addCachedNamedStyle(cellStyle, resultStyle); } fixNeighbourCellBorders(cellStyle, resultCell); resultCell.setCellStyle(resultStyle); Sheet sheet = resultCell.getSheet(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int firstCol = mergedRegion.getFirstColumn(); int lastCol = mergedRegion.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) for (int col = firstCol; col <= lastCol; col++) sheet.getRow(row).getCell(col).setCellStyle(resultStyle); // cell includes only in one merged region break; } } } } }