/** * 创建表格样式 * * @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; }
@SuppressWarnings("rawtypes") private void generateHeaderRow( HSSFWorkbook workBook, Sheet sheet, JSONArray columnConfigJSONObject) { CellStyle center = workBook.createCellStyle(); center.setAlignment(HSSFCellStyle.ALIGN_CENTER); int columnType = Cell.CELL_TYPE_STRING; String col = null, text = null; short width = 15; sheet.setDefaultColumnWidth(width); Row headerRow = createRow(sheet, headerRowNum); for (short i = 0; i < columnConfigJSONObject.length(); i++) { JSONObject headerObj = columnConfigJSONObject.getJSONObject(i); for (Iterator iterator = headerObj.keys(); iterator.hasNext(); ) { String key = (String) iterator.next(); if ("columnType".equals(key)) { // if(isNumber(headerObj.getString(key))) { // columnType = Cell.CELL_TYPE_NUMERIC; // } } else { col = key; text = headerObj.getString(key); } } headerIdArray.add(col); Cell cell = createCell(headerRow, i, columnType); cell.setCellValue(new HSSFRichTextString(text)); cell.setCellStyle(center); } }
private CellStyle getDateFormatStyle(HSSFCell poiCell) { final HSSFWorkbook poiWorkbook = poiCell.getRow().getSheet().getWorkbook(); CellStyle dateFormatStyle = poiWorkbook.createCellStyle(); CreationHelper createHelper = poiWorkbook.getCreationHelper(); dateFormatStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:MM:ss")); dateFormatStyle.setAlignment(CellStyle.ALIGN_LEFT); return dateFormatStyle; }
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; }
private CellStyle getHeaderStyle(Workbook wb) { Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle headerStyle = wb.createCellStyle(); headerStyle.setFont(font); getAllAroundBorder(headerStyle); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setAlignment(CellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); return headerStyle; }
private static void setStyle(Workbook wb) { titleFont = wb.createFont(); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleStyle = wb.createCellStyle(); titleStyle.setBorderBottom(CellStyle.BORDER_THIN); titleStyle.setBorderLeft(CellStyle.BORDER_THIN); titleStyle.setBorderRight(CellStyle.BORDER_THIN); titleStyle.setBorderTop(CellStyle.BORDER_THIN); titleStyle.setAlignment(CellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); titleStyle.setFont(titleFont); contentStyle = wb.createCellStyle(); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderLeft(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); contentStyle.setBorderTop(CellStyle.BORDER_THIN); contentStyle.setAlignment(CellStyle.ALIGN_CENTER); contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); }
/** * This is used to get the cell style of the body cells. If you want a custom style for the cells * that say the attribute values of the entries in the list, customize this method. * * @param theWorkbook the Excel workbook we are working in * @return the cell style for the body */ public static CellStyle GetBodyCellStyle(Workbook theWorkbook) { CellStyle theStyle = theWorkbook.createCellStyle(); /*set the style to wrap the text and center vertical alignment * NOTE: This is the code block where customization should be done */ theStyle.setWrapText(true); theStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); theStyle.setAlignment(CellStyle.ALIGN_LEFT); return theStyle; }
private CellStyle buildCellStyle() { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); return cellStyle; }
/** @Description: 初始化表头行样式 */ private static void initHeadCellStyle() { headStyle.setAlignment(CellStyle.ALIGN_CENTER); headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headStyle.setFont(headFont); headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index); headStyle.setBorderTop(CellStyle.BORDER_MEDIUM); headStyle.setBorderBottom(CellStyle.BORDER_THIN); headStyle.setBorderLeft(CellStyle.BORDER_THIN); headStyle.setBorderRight(CellStyle.BORDER_THIN); headStyle.setTopBorderColor(IndexedColors.BLUE.index); headStyle.setBottomBorderColor(IndexedColors.BLUE.index); headStyle.setLeftBorderColor(IndexedColors.BLUE.index); headStyle.setRightBorderColor(IndexedColors.BLUE.index); }
/** @Description: 初始化内容行样式 */ private static void initContentCellStyle() { contentStyle.setAlignment(CellStyle.ALIGN_CENTER); contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); contentStyle.setFont(contentFont); contentStyle.setBorderTop(CellStyle.BORDER_THIN); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderLeft(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); contentStyle.setTopBorderColor(IndexedColors.BLUE.index); contentStyle.setBottomBorderColor(IndexedColors.BLUE.index); contentStyle.setLeftBorderColor(IndexedColors.BLUE.index); contentStyle.setRightBorderColor(IndexedColors.BLUE.index); contentStyle.setWrapText(true); // 字段换行 }
/** * Discription:[设置文件内容的显示样式] * * @param wb * @return * @author:[代超] * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public CellStyle setContentSheetSysle(Workbook wb) { CellStyle style = wb.createCellStyle(); // 生成并设置另一个样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成一个字体 Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style.setFont(font); return style; }
public void merge( int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex, boolean center) { if (startRowIndex <= endRowIndex && startColumnIndex <= endColumnIndex) { sheet.addMergedRegion( new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex)); if (center) { CellStyle cellStyle = createCellStyle(); HSSFCell cell = getCell(startRowIndex, startColumnIndex); cellStyle.cloneStyleFrom(cell.getCellStyle()); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cell.setCellStyle(cellStyle); } } }
/** * 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; }
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; }
/** * Creates a new instance. * * @param workbook target workbook * @throws IllegalArgumentException if some parameters were {@code null} */ WorkbookInfo(Workbook workbook) { if (workbook == null) { throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$ } this.workbook = workbook; Font font = workbook.createFont(); commonStyle = workbook.createCellStyle(); commonStyle.setFont(font); commonStyle.setBorderTop(BorderStyle.THIN); commonStyle.setBorderBottom(BorderStyle.THIN); commonStyle.setBorderLeft(BorderStyle.THIN); commonStyle.setBorderRight(BorderStyle.THIN); titleStyle = workbook.createCellStyle(); titleStyle.cloneStyleFrom(commonStyle); titleStyle.setLocked(true); titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); titleStyle.setAlignment(HorizontalAlignment.CENTER); CreationHelper helper = workbook.getCreationHelper(); DataFormat df = helper.createDataFormat(); dataStyle = workbook.createCellStyle(); dataStyle.cloneStyleFrom(commonStyle); dateDataStyle = workbook.createCellStyle(); dateDataStyle.cloneStyleFrom(commonStyle); dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$ timeDataStyle = workbook.createCellStyle(); timeDataStyle.cloneStyleFrom(commonStyle); timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$ datetimeDataStyle = workbook.createCellStyle(); datetimeDataStyle.cloneStyleFrom(commonStyle); datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$ }
/** @Description: 初始化日期行样式 */ private static void initDateCellStyle() { dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); dateStyle.setFont(dateFont); dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); }
private CellStyle getFirsetCellStyle(Workbook wb) { CellStyle cellStyle = wb.createCellStyle(); getAllAroundBorder(cellStyle); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); return cellStyle; }
public CellStyle buildNACellStyle(Sheet sheet) { CellStyle cellStyleForNA = this.buildDataCellStyle(sheet); cellStyleForNA.setAlignment(CellStyle.ALIGN_CENTER); return cellStyleForNA; }
@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(); }
public void exportarPlanilha(File arquivoDeSaida) throws FileNotFoundException, IOException { logger.info("Gerando relatório"); workbook = new HSSFWorkbook(); dataCellStyle = buildCellStyle(); headerCellStyle = buildCellStyle(); totalHeaderStyle = buildCellStyle(); totalHeaderStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); totalHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); totalCellStyle = buildCellStyle(); totalCellStyle.setAlignment(CellStyle.ALIGN_CENTER); totalCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); totalCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); totalRegistrosGravar = categorias.size() * medicos.size() * planos.size(); logger.debug("{} registros para gravar", totalRegistrosGravar); registrosGravados = 0; for (String categoria : categorias.keySet()) { Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(categoria)); Row rowPlanos = sheet.createRow(0); buildHeaderCell(rowPlanos, (short) 0); Map<String, AtomicInteger> totaisPlanos = new HashMap<String, AtomicInteger>(); for (String plano : planos.keySet()) { buildHeaderCell(rowPlanos, rowPlanos.getLastCellNum()).setCellValue(plano); totaisPlanos.put(plano, new AtomicInteger()); } Cell cellTotalHeader = buildTotalHeaderCell(rowPlanos, rowPlanos.getLastCellNum()); cellTotalHeader.setCellValue("TOTAL"); AtomicInteger totalGeral = new AtomicInteger(); for (String medico : medicos.keySet()) { Row row = sheet.createRow(sheet.getLastRowNum() + 1); Cell cellMedico = buildHeaderCell(row, (short) 0); cellMedico.setCellValue(medico); AtomicInteger totalMedico = new AtomicInteger(); for (String plano : planos.keySet()) { Chave chave = new Chave(plano, medico, categoria); AtomicInteger contador = contadores.get(chave); Cell cell = buildDataCell(row, row.getLastCellNum()); if (contador != null) { cell.setCellValue(contador.get()); totalMedico.addAndGet(contador.get()); totaisPlanos.get(plano).addAndGet(contador.get()); } else { cell.setCellValue("*"); } EventQueue.invokeLater( new Runnable() { @Override public void run() { int porcentagemConcluido = 50 + (int) ((++registrosGravados / (float) totalRegistrosGravar) * 50); progressBar.setValue(porcentagemConcluido); } }); } Cell cellTotal = buildTotalCell(row, row.getLastCellNum()); cellTotal.setCellValue(totalMedico.get()); totalGeral.addAndGet(totalMedico.get()); } Row rowTotais = sheet.createRow(sheet.getLastRowNum() + 1); Cell cellTotalPlanosHeader = buildTotalHeaderCell(rowTotais, (short) 0); cellTotalPlanosHeader.setCellValue("TOTAL"); for (String plano : planos.keySet()) { Cell cellTotal = buildTotalCell(rowTotais, rowTotais.getLastCellNum()); cellTotal.setCellValue(totaisPlanos.get(plano).get()); } Cell cellTotalGeral = buildTotalCell(rowTotais, rowTotais.getLastCellNum()); cellTotalGeral.setCellValue(totalGeral.get()); for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) { sheet.autoSizeColumn(i); } } FileOutputStream fileOut = new FileOutputStream(arquivoDeSaida); workbook.write(fileOut); fileOut.close(); logger.info("Relatório concluido"); }
/** * 创建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; }
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; }
/** * 资产明细表 * * @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(); }
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; }
/** @Description: 初始化标题行样式 */ private static void initTitleCellStyle() { titleStyle.setAlignment(CellStyle.ALIGN_CENTER); titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); titleStyle.setFont(titleFont); titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index); }
@Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Object> errorList = (List<Object>) model.get("errorList"); List<DonVi> donViError = (List<DonVi>) errorList.get(0); List<String> statusError = (List<String>) errorList.get(1); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet("Bộ phận sử dụng bị lỗi import"); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Times New Roman"); font.setFontHeight((short) 260); style.setFont(font); sheet.setDefaultRowHeight((short) 400); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 12000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 6000); sheet.setColumnWidth(5, 5000); sheet.setDefaultColumnStyle(0, style); sheet.setDefaultColumnStyle(1, style); sheet.setDefaultColumnStyle(2, style); sheet.setDefaultColumnStyle(3, style); sheet.setDefaultColumnStyle(4, style); sheet.setDefaultColumnStyle(5, style); // create header row CellStyle style2 = workbook.createCellStyle(); Font font2 = workbook.createFont(); font2.setFontName("Times New Roman"); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeight((short) 260); style2.setFont(font2); style2.setAlignment(CellStyle.ALIGN_CENTER); HSSFRow row2 = sheet.createRow(0); row2.createCell(0).setCellValue("Công ty điện lực thành phố Cần Thơ"); row2.getCell(0).setCellStyle(style); row2.createCell(1).setCellValue("Kho Công ty Điện Lực Cần Thơ"); row2.getCell(1).setCellStyle(style); // create header row HSSFRow header = sheet.createRow(1); response.setHeader("Content-Disposition", "inline; filename=" + "BophansudungError.xls"); header.createCell(0).setCellValue("Mã BPSD"); header.getCell(0).setCellStyle(style2); header.createCell(1).setCellValue("Tên BPSD"); header.getCell(1).setCellStyle(style2); header.createCell(2).setCellValue("Địa chỉ"); header.getCell(2).setCellStyle(style2); header.createCell(3).setCellValue("Email"); header.getCell(3).setCellStyle(style2); header.createCell(4).setCellValue("Số điện thoại"); header.getCell(4).setCellStyle(style2); header.createCell(5).setCellValue("Lỗi"); header.getCell(5).setCellStyle(style2); // create data rows int rowCount = 2; int i = 0; for (DonVi dv : donViError) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(dv.getDvMa()); aRow.createCell(1).setCellValue(dv.getDvTen()); aRow.createCell(2).setCellValue(dv.getDiaChi()); aRow.createCell(3).setCellValue(dv.getEmail()); aRow.createCell(4).setCellValue(dv.getSdt()); aRow.createCell(5).setCellValue(statusError.get(i++)); } }