java ee设置样式_JavaEE_POI导出Excel, 设置导出Excel 单元格样式

最近老大提了个新需求，需要将异常数据标红，网上搜索了一下如何对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{

Listlist = userClassDailyTotalMapper.selectBySelectiveNoPage(userClassDailyTotal);

//System.out.println(list.size());

class UserDailyStatExcel extends ExcelUtilVersionTwo{

@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);

}

//改

}