/** * 创建表格样式 * * @param wb 工作薄对象 * @return 样式列表 */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; }
private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; // Title Style Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(titleFont); style.setWrapText(true); styles.put("title", style); // Sub Title Style Font subTitleFont = wb.createFont(); subTitleFont.setFontHeightInPoints((short) 14); subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(subTitleFont); style.setWrapText(true); styles.put("subTitle", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 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(headerFont); 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); return styles; }
/** Create a library of cell styles */ 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 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; }
private HSSFCellStyle getHeaderStyle2( final HSSFWorkbook workbook, final boolean shouldRed, final boolean shouldBackground, final boolean shouldSmall, final boolean end, final boolean changeover, final Font font) { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 9); font.setColor(HSSFFont.COLOR_NORMAL); style.setFont(font); if (shouldRed) { font.setColor(HSSFFont.COLOR_RED); style.setFont(font); } if (shouldBackground) { if (changeover) { style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); } else { style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); } style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } if (shouldSmall) { font.setFontHeightInPoints((short) 7); style.setFont(font); } if (end) { style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); } return style; }
/** @Description: 初始化内容行字体 */ private static void initContentFont() { contentFont.setFontName("宋体"); contentFont.setFontHeightInPoints((short) 10); contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL); contentFont.setCharSet(Font.DEFAULT_CHARSET); contentFont.setColor(IndexedColors.BLUE_GREY.index); }
/** @Description: 初始化标题行字体 */ private static void initTitleFont() { titleFont.setFontName("宋体"); titleFont.setFontHeightInPoints((short) 20); // 设置字体大小 titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体 titleFont.setCharSet(Font.DEFAULT_CHARSET); titleFont.setColor(IndexedColors.BLUE_GREY.index); }
public CellStyle buildDimensionCellStyle(Sheet sheet) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER); String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR); logger.debug("Header background color : " + headerBGColor); short backgroundColorIndex = headerBGColor != null ? IndexedColors.valueOf(headerBGColor).getIndex() : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex(); cellStyle.setFillForegroundColor(backgroundColorIndex); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR); logger.debug("Header border color : " + bordeBorderColor); short borderColorIndex = bordeBorderColor != null ? IndexedColors.valueOf(bordeBorderColor).getIndex() : IndexedColors.valueOf(DEFAULT_HEADER_BORDER_COLOR).getIndex(); cellStyle.setLeftBorderColor(borderColorIndex); cellStyle.setRightBorderColor(borderColorIndex); cellStyle.setBottomBorderColor(borderColorIndex); cellStyle.setTopBorderColor(borderColorIndex); Font font = sheet.getWorkbook().createFont(); Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE); logger.debug("Header font size : " + headerFontSize); short headerFontSizeShort = headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE; font.setFontHeightInPoints(headerFontSizeShort); String fontName = (String) this.getProperty(PROPERTY_FONT_NAME); logger.debug("Font name : " + fontName); fontName = fontName != null ? fontName : DEFAULT_FONT_NAME; font.setFontName(fontName); String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR); logger.debug("Dimension color : " + color); short colorIndex = bordeBorderColor != null ? IndexedColors.valueOf(color).getIndex() : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex(); font.setColor(colorIndex); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setItalic(true); cellStyle.setFont(font); return cellStyle; }
public CellStyle buildDataCellStyle(Sheet sheet) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER); String cellBGColor = (String) this.getProperty(PROPERTY_CELL_BACKGROUND_COLOR); logger.debug("Cell background color : " + cellBGColor); short backgroundColorIndex = cellBGColor != null ? IndexedColors.valueOf(cellBGColor).getIndex() : IndexedColors.valueOf(DEFAULT_CELL_BACKGROUND_COLOR).getIndex(); cellStyle.setFillForegroundColor(backgroundColorIndex); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); String bordeBorderColor = (String) this.getProperty(PROPERTY_CELL_BORDER_COLOR); logger.debug("Cell border color : " + bordeBorderColor); short borderColorIndex = bordeBorderColor != null ? IndexedColors.valueOf(bordeBorderColor).getIndex() : IndexedColors.valueOf(DEFAULT_CELL_BORDER_COLOR).getIndex(); cellStyle.setLeftBorderColor(borderColorIndex); cellStyle.setRightBorderColor(borderColorIndex); cellStyle.setBottomBorderColor(borderColorIndex); cellStyle.setTopBorderColor(borderColorIndex); Font font = sheet.getWorkbook().createFont(); Short cellFontSize = (Short) this.getProperty(PROPERTY_CELL_FONT_SIZE); logger.debug("Cell font size : " + cellFontSize); short cellFontSizeShort = cellFontSize != null ? cellFontSize.shortValue() : DEFAULT_CELL_FONT_SIZE; font.setFontHeightInPoints(cellFontSizeShort); String fontName = (String) this.getProperty(PROPERTY_FONT_NAME); logger.debug("Font name : " + fontName); fontName = fontName != null ? fontName : DEFAULT_FONT_NAME; font.setFontName(fontName); String cellColor = (String) this.getProperty(PROPERTY_CELL_COLOR); logger.debug("Cell color : " + cellColor); short cellColorIndex = cellColor != null ? IndexedColors.valueOf(cellColor).getIndex() : IndexedColors.valueOf(DEFAULT_CELL_COLOR).getIndex(); font.setColor(cellColorIndex); cellStyle.setFont(font); return cellStyle; }
public CellStyle getHeaderStyle(XSSFWorkbook wb, Short fontSize) { CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); if (fontSize != null) { font.setFontHeightInPoints(fontSize); } else { font.setFontHeightInPoints((short) 10); } // font.setColor(color.getIndex()); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); // 自动换行 style.setBorderTop(CellStyle.BORDER_THIN); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); return style; }
/** * Método para gerar arquivo excel (xls); * * @author Alexandre Martins da Silva * @param documento - objeto para gerar o arquivo excel (xls) */ public void posProcessarXls(Object documento) { HSSFWorkbook planilha = (HSSFWorkbook) documento; HSSFSheet folha = planilha.getSheetAt(0); HSSFRow cabecalho = folha.getRow(0); HSSFCellStyle estiloDaCelula = planilha.createCellStyle(); Font fonteDoCabecalho = planilha.createFont(); fonteDoCabecalho.setColor(IndexedColors.WHITE.getIndex()); fonteDoCabecalho.setBold(true); fonteDoCabecalho.setFontHeightInPoints((short) 13); estiloDaCelula.setFont(fonteDoCabecalho); estiloDaCelula.setFillForegroundColor(IndexedColors.BLACK.getIndex()); estiloDaCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) { cabecalho.getCell(i).setCellStyle(estiloDaCelula); } }
/** * Discription:[设置文件头显示样式] * * @param wb * @return * @author:[代超] * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public CellStyle setRootSheetSysle(Workbook wb) { CellStyle style = wb.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); // 对齐方式:居中对齐 // 生成一个字体 Font font = wb.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); return style; }
private CellStyle getStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 设置单元格字体 Font headerFont = workbook.createFont(); // 字体 headerFont.setFontHeightInPoints((short) 14); headerFont.setColor(HSSFColor.BLACK.index); headerFont.setFontName("宋体"); style.setFont(headerFont); style.setWrapText(true); // 设置单元格边框及颜色 style.setBorderBottom((short) 1); style.setBorderLeft((short) 1); style.setBorderRight((short) 1); style.setBorderTop((short) 1); style.setWrapText(true); return style; }
private HSSFCellStyle getHeaderStyleChangeoverEnd(final HSSFWorkbook workbook, final Font font) { HSSFCellStyle style = workbook.createCellStyle(); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setWrapText(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 9); style.setFont(font); style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); return style; }
/** * 资产明细表 * * @param response * @param customer_2 * @param customer_0or1 * @throws IOException */ @RequestMapping("/report/frontequip/exportFrontEquipListReport_assetclean.do") public void exportFrontEquipListReport_assetclean( HttpServletResponse response, String customer_2, String customer_0or1) throws IOException { String customer_2_name = customerService.get(customer_2).getName(); String customer_0or1_name = customerService.get(customer_0or1).getName(); List<FrontEquipListReport> list = frontEquipReportRepository.queryFrontEquipListReport(customer_2, customer_0or1); List<FrontEquipListReport_subtype> list_subtype_prod = frontEquipReportRepository.queryFrontEquipListReport_header(customer_2, customer_0or1); XSSFWorkbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(); int rownum = 0; // 标题 Row title = sheet.createRow(rownum++); Cell title_cell = title.createCell(0); title_cell.setCellValue(customer_2_name + customer_0or1_name + "前端设备明细表"); CellStyle title_style = wb.createCellStyle(); Font title_font = wb.createFont(); title_font.setFontHeightInPoints((short) 16); // f.setColor(IndexedColors.RED.getIndex()); title_font.setBoldweight(Font.BOLDWEIGHT_BOLD); title_style.setFont(title_font); title_style.setAlignment(CellStyle.ALIGN_CENTER); title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); title_cell.setCellStyle(title_style); // 和并单元格 // sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 15)); // =========================================================================================== rownum = exportFrontEquipListReport_header_assetclean(list_subtype_prod, rownum, wb, sheet); // ============================================================================================= // 开始构建整个excel的文件 // 处理对应的品名在excel的哪一列 Map<String, Integer> prod_col_index_map = new HashMap<String, Integer>(); int cellIndex = 3; for (FrontEquipListReport_subtype subtype : list_subtype_prod) { for (FrontEquipListReport_prod prod : subtype.getProds()) { prod_col_index_map.put(prod.getProd_id(), cellIndex); cellIndex = cellIndex + 2; } } if (list != null && list.size() > 0) { int i = 1; for (FrontEquipListReport customer : list) { Row row = sheet.createRow(rownum++); Cell cell_prod_0 = row.createCell(0); cell_prod_0.setCellValue(i); // cell_prod_0.setCellStyle(style); Cell cell_prod_1 = row.createCell(1); cell_prod_1.setCellValue(customer.getPole_code()); Cell cell_prod_2 = row.createCell(2); cell_prod_2.setCellValue(customer.getPole_name()); for (FrontEquipListReport_prod prod : customer.getProdes()) { Cell cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id())); cell_prod.setCellValue(prod.getNum()); // cell_prod.setCellStyle(style); cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()) + 1); cell_prod.setCellValue(prod.getValue_net().doubleValue()); } i++; } // 对标题行 进行单元格合并 sheet.addMergedRegion( new CellRangeAddress(0, (short) 0, 0, (short) prod_col_index_map.size())); } sheet.createFreezePane(3, 3); // 添加总计一行 CellStyle style_sum = wb.createCellStyle(); Font style_sum_font = wb.createFont(); style_sum_font.setFontHeightInPoints((short) 12); // f.setColor(IndexedColors.RED.getIndex()); style_sum_font.setBoldweight(Font.BOLDWEIGHT_BOLD); style_sum.setFont(style_sum_font); style_sum.setAlignment(CellStyle.ALIGN_RIGHT); style_sum.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Row row_sum = sheet.createRow(rownum++); Cell cell_sum_2 = row_sum.createCell(2); cell_sum_2.setCellValue("小计:"); cell_sum_2.setCellStyle(style_sum); for (int i = 3; i < cellIndex; i++) { String col = CellReference.convertNumToColString(i); Cell cell_sum = row_sum.createCell(i); // =SUM(C4:C29) 从第4行开始到最后一样 // cell_sum.setCellValue("SUM("+col+"4:"+col+(rownum-1)+")"); cell_sum.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")"); cell_sum.setCellStyle(style_sum); Cell cell_net = row_sum.createCell(i); cell_net.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")"); cell_net.setCellStyle(style_sum); } String filename = customer_2_name + customer_0or1_name + "前端设备明细表-净资产.xlsx"; // FileOutputStream out = new FileOutputStream(filename); response.setHeader( "content-disposition", "attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1")); // response.setContentType("application/vnd.ms-excel;charset=uft-8"); response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=uft-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); }
@Test public void generateExcelPoiReport() throws IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Accounts"); int rowNum = 1; Row row = sheet.createRow(rowNum++); Font font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName(FONT_TYPE); font.setColor(FONT_COLOR_TITLE); /** * Header **** */ CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(BACKGROUND_COLOR); Cell cell = row.createCell((short) 1); cell.setCellValue("Account Report per Beneficiary"); cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 1, // first row (0-based) 2, // last row (0-based) 1, // first column (0-based) 16 // last column (0-based) )); /** * Body **** */ font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName(FONT_TYPE); font.setColor(FONT_COLOR); style = wb.createCellStyle(); style.setFont(font); rowNum = rowNum + 3; List<Account> accounts = accountManager.getAllAccounts(); for (Account account : accounts) { row = sheet.createRow(rowNum++); cell = row.createCell((short) 1); cell.setCellStyle(style); cell.setCellValue(account.getName()); cell = row.createCell((short) 2); cell.setCellValue(account.getNumber()); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue(account.getEntityId()); cell.setCellStyle(style); } sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls"); wb.write(fileOut); fileOut.close(); }
private static Map createStyles(Workbook wb) { Map styles = new HashMap(); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); CellStyle style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom((short) 7); 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((short) 1); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); 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((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment((short) 2); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_i", style); return styles; }
/** * 创建excel文档, * * @param list 数据 * @param keys list中map的key数组集合 * @param columnNames excel的列名 */ public static Workbook createWorkBook(List<Map<String, Object>> listMap, List<?> list) { // 创建excel工作簿 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet(页),并命名 Sheet sheet = wb.createSheet("数据"); // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。 /* for(int i=0;i<keys.length;i++){ sheet.setColumnWidth((short) i, (short) (35.7 * 150)); }*/ // 创建第一行 Row row = sheet.createRow((short) 0); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式(用于列名) f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建第二种字体样式(用于值) f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // 设置第一种单元格的样式(用于列名) cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // 设置第二种单元格的样式(用于值) cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); for (int i = 0; i < listMap.size(); i++) { if (Boolean.parseBoolean(listMap.get(i).get("hide") + "")) { listMap.remove(listMap.get(i)); } } // 设置列名 for (int i = 0; i < listMap.size(); i++) { Cell cell = row.createCell(i); cell.setCellValue(listMap.get(i).get("name") + ""); cell.setCellStyle(cs); } // 设置每行每列的值 for (short i = 0; i < list.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行,在页sheet上 Row row1 = sheet.createRow((short) i + 1); // 在row行上创建一个方格 for (int j = 0; j < listMap.size(); j++) { Cell cell = row1.createCell(j); Map<String, Object> map = (Map<String, Object>) list.get(i); cell.setCellValue( map.get(listMap.get(j).get("colkey")) == null ? " " : map.get(listMap.get(j).get("colkey")).toString()); cell.setCellStyle(cs2); } } return wb; }
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) { File excelFile = new File("export_parts.xls"); // Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet XSSFSheet sheet = workbook.createSheet("Parts Data"); String header = StringUtils.join(queryResult.getQuery().getSelects(), ";"); String[] columns = header.split(";"); Map<Integer, String[]> data = new HashMap<>(); String[] headerFormatted = createXLSHeaderRow(header, columns, locale); data.put(1, headerFormatted); Map<Integer, String[]> commentsData = new HashMap<>(); String[] headerComments = createXLSHeaderRowComments(header, columns); commentsData.put(1, headerComments); List<String> selects = queryResult.getQuery().getSelects(); int i = 1; for (QueryResultRow row : queryResult.getRows()) { i++; data.put(i, createXLSRow(selects, row, baseURL)); commentsData.put(i, createXLSRowComments(selects, row)); } // Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); String[] objArr = data.get(key); int cellnum = 0; for (String obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue(obj); } CreationHelper factory = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); String[] commentsObjArr = commentsData.get(key); cellnum = 0; for (String commentsObj : commentsObjArr) { if (commentsObj.length() > 0) { Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 1); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(commentsObj); comment.setString(str); // Assign the comment to the cell cell.setCellComment(comment); } cellnum++; } } // Define header style Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Courier New"); headerFont.setItalic(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Set header style for (int j = 0; j < columns.length; j++) { Cell cell = sheet.getRow(0).getCell(j); cell.setCellStyle(headerStyle); if (cell.getCellComment() != null) { String comment = cell.getCellComment().getString().toString(); if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER) || comment.equals(QueryField.PART_MASTER_NUMBER)) { for (int k = 0; k < queryResult.getRows().size(); k++) { Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j) : sheet.getRow(k + 1).createCell(j); grayCell.setCellStyle(headerStyle); } } } } try { // Write the workbook in file system FileOutputStream out = new FileOutputStream(excelFile); workbook.write(out); out.close(); } catch (Exception e) { LOGGER.log(Level.FINEST, null, e); } return excelFile; }
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; DataFormat format = wb.createDataFormat(); Font titleFont = wb.createFont(); titleFont.setFontName("Tahoma"); titleFont.setFontHeightInPoints((short) 12); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font headFont = wb.createFont(); headFont.setFontName("Tahoma"); headFont.setFontHeightInPoints((short) 11); headFont.setColor(IndexedColors.WHITE.getIndex()); headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 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(headFont); 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()); style.setWrapText(true); styles.put("header", style); Font groupFont = wb.createFont(); groupFont.setFontName("Tahoma"); groupFont.setFontHeightInPoints((short) 11); groupFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(groupFont); 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("groupleft", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(groupFont); 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("groupright", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); /* style.setFont(groupFont); */ 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("groupcenter", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setDataFormat(format.getFormat("#,##0")); /* style.setFont(groupFont); */ 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("groupnumber", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); 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("cellleft", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); 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("cellcenter", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); 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("cellright", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setDataFormat(format.getFormat("#,##0")); 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("cellnumber", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setDataFormat(format.getFormat("#,##0.00")); 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("cellnumber2", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); styles.put("celltop", style); return styles; }