JavaEE_POI导出Excel, 设置导出Excel 单元格样式

来源：互联网发布：mac系统怎么看文件大小编辑：程序博客网时间：2024/06/10 05:45

最近老大提了个新需求，需要将异常数据标红，网上搜索了一下如何对POI 导出的Excel 进行样式处理，在此mark 一下

代码：（设置Excel 导出Excel 样式的颜色）

提示：像 cellStyle, font 等对象只要创建一次就可以了，并不需要每个单元格去创建一个对象，否则话会样式太多，Excel 会报错。

因此我想到了用 HashMap 保存样式表单

某大神代码。

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));HSSFWorkbook resourceFile = new HSSFWorkbook(fs);HSSFSheet shOrg = resourceFile.getSheet("Sheet1");if (shOrg == null) {return;}HSSFRow curRow = shOrg.getRow(1); //取XSL文件Sheet1页上第2行HSSFCell curCell = curRow.getCell(0); //第1列HSSFCellStyle cellStyle = resourceFile.createCellStyle();cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格cellStyle.setFillForegroundColor(HSSFColor.DARK_RED.index);//填暗红色    Font font = resourceFile.createFont();    font.setFontHeightInPoints((short)24); //字体大小    font.setFontName("楷体");    font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体    font.setColor(HSSFColor.GREEN.index);//绿字    cellStyle.setFont(font);    curCell.setCellStyle(cellStyle);    // 另存文件String outputFileName = "resource1.xls";FileOutputStream stream;stream = new FileOutputStream(new File(outputFileName));resourceFile.write(stream);stream.close();

自己的代码：关于设置字体颜色部分：

//Excel导出@Transactional(propagation=Propagation.NOT_SUPPORTED)public void selectExportExcel(UserClassDailyTotal userClassDailyTotal, HttpServletResponse response) throws IOException{List<UserClassDailyTotal> list = userClassDailyTotalMapper.selectBySelectiveNoPage(userClassDailyTotal);//System.out.println(list.size());class UserDailyStatExcel extends ExcelUtilVersionTwo<UserClassDailyTotal>{@Overridepublic void selectedTableCells(HSSFRow tableRow,UserClassDailyTotal t) {DecimalFormat floatConvert = new DecimalFormat("##0.00");//设置截取两位小数的转换器SimpleDateFormat createDayConvert = new SimpleDateFormat("yyyy-MM-dd");//日期转换器HSSFCellStyle cellStyleRedFont = this.getExcelCellStyles().get("redFont");tableRow.createCell(0).setCellValue(t.getUserName());tableRow.createCell(1).setCellValue(Double.valueOf(floatConvert.format(t.getShouldHour())));tableRow.createCell(2).setCellValue(Double.valueOf(floatConvert.format(t.getActualHour())));tableRow.createCell(3).setCellValue(Double.valueOf(floatConvert.format(t.getOvertimeHour())));HSSFCell cell4 = tableRow.createCell(4);if(t.getIsDelay() == 1){cell4.setCellStyle(cellStyleRedFont);cell4.setCellValue("是");}else{cell4.setCellValue("否");}HSSFCell cell5 = tableRow.createCell(5);if(t.getIsEarlyLeave() == 1){cell5.setCellStyle(cellStyleRedFont);cell5.setCellValue("是");}else{cell5.setCellValue("否");}tableRow.createCell(6).setCellValue(createDayConvert.format(t.getUserClassDay()));}}UserDailyStatExcel userDailyStatExcel = new UserDailyStatExcel();//创建Excel单元格样式//0.得到Excel工作本HSSFWorkbook excelWorkBook =  userDailyStatExcel.getExcelWorkBook();//1.创建单元格样式HSSFCellStyle cellStyleRedFont = excelWorkBook.createCellStyle();HSSFFont font = excelWorkBook.createFont();font.setColor(HSSFColor.RED.index);    //红字cellStyleRedFont.setFont(font);//2.将样式加到HashMap中userDailyStatExcel.addHSSFCellStyle("redFont", cellStyleRedFont);SimpleDateFormat createDayConvert = new SimpleDateFormat("yyyyMMddHHmmssSSS");//日期转换器userDailyStatExcel.exportExcel(list, new String[]{"用户名","应工作时长(时)","实际工作时长(时)","加班时长(时)","迟到","早退","考勤日期"}, "个人每日考勤统计报表", createDayConvert.format(new Date())+ "个人每日考勤统计报表", response);}    //改}

0 0