最近老大提了个新需求,需要将异常数据标红,网上搜索了一下如何对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>{
@Override
public 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);
}
//改
}