easypoi设置单元格样式
左边是 easypoi 导出的默认格式，右边是自定义格式
上述的转换其实都是在 "导出前一步" 进行完成的，所以开发基本流程为 先做好导出，在增加样式。
主要代码为：
//-------------------------------设置单元格样式开始------------------------------------- //获取sheetAt对象，这里一个sheetAt所以角标是0 Sheet sheetAt = workbook.getSheetAt(0); // 表格行数 int lastRowNum = sheetAt.getLastRowNum(); // 获取列数 int physicalNumberOfCells = sheetAt.getRow(0).getPhysicalNumberOfCells(); //开始遍历单元格并进行判断是否渲染 for (int i = 2 ; i <= lastRowNum ; i++) { //获取每行对象 Row row = sheetAt.getRow(i); for (int j = 1; j < physicalNumberOfCells; j++) { //获取单元格对象 Cell cell = row.getCell(j) ;
//获取单元格样式对象 CellStyle cellStyle = workbook.createCellStyle();
//获取单元格内容对象 Font font = workbook.createFont(); //一定要装入 样式中才会生效 cellStyle.setFont(font);
//获取当前单元格 中的value int score = Integer.valueOf(map.get(String.valueOf(i - 2)).get(j)); if (score > 90 && score < 95){ //设置单元格背景颜色 cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置单元格字体颜色 font.setColor(IndexedColors.YELLOW.getIndex()); } cell.setCellStyle(cellStyle); }
} //-------------------------------设置单元格样式完成-------------------------------------- |
实例
/** * 场景，假设现在要导出 学生成绩表的Excel文件 * 现在需要对其中 分数大于 90分 同时 小于95分 的同学 标记为 红色。 */ @GetMapping(value = "/api/jncsCarFlow/ExportStudentScore") @ApiOperation(value = "导出检测器监测记录", notes = "导出检测器监测记录") public void exportStudentScore(HttpServletResponse response) { try { Map<String, List<String>> map = getData(); //表头 List<ExcelExportEntity> headList = new ArrayList<>(); ExcelExportEntity excelExportEntity; for (int i = 0; i < map.get("headList").size(); i++) { excelExportEntity = new ExcelExportEntity(map.get("headList").get(i), map.get("keyHeadList").get(i)); headList.add(excelExportEntity); } //数据 List<Map<String, Object>> list = new ArrayList<>(); Map<String, Object> mapData; for (int i = 0; i < 9; i++) { mapData = new HashMap<>(); List<String> data = map.get(i + ""); mapData.put("name", data.get(0)); mapData.put("languageScore", Integer.valueOf(data.get(1))); mapData.put("mathScore", Integer.valueOf(data.get(2))); mapData.put("englishScore", Integer.valueOf(data.get(3))); list.add(mapData); }
//导出 String excelName = "StudentScoreName" + System.currentTimeMillis() + ".xls"; if (CollectionUtils.isNotEmpty(list)) { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8")); ExportParams params = new ExportParams("Excel的title", "sheet的Name"); Workbook workbook = ExcelExportUtil.exportExcel(params, headList, list); //-------------------------------设置单元格样式开始------------------------------------- //获取sheetAt对象，这里一个sheetAt所以角标是0 Sheet sheetAt = workbook.getSheetAt(0); // 表格行数 int lastRowNum = sheetAt.getLastRowNum(); // 获取列数 int physicalNumberOfCells = sheetAt.getRow(0).getPhysicalNumberOfCells(); //开始遍历单元格并进行判断是否渲染 for (int i = 2; i <= lastRowNum; i++) { //获取每行对象 Row row = sheetAt.getRow(i); for (int j = 1; j < physicalNumberOfCells; j++) { //获取单元格对象 Cell cell = row.getCell(j);
//获取单元格样式对象 CellStyle cellStyle = workbook.createCellStyle();
//获取单元格内容对象 Font font = workbook.createFont(); //一定要装入 样式中才会生效 cellStyle.setFont(font);
//获取当前单元格 中的value int score = Integer.valueOf(map.get(String.valueOf(i - 2)).get(j)); if (score > 90 && score < 95) { //设置单元格背景颜色 cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置单元格字体颜色 font.setColor(IndexedColors.YELLOW.getIndex()); } cell.setCellStyle(cellStyle); }
} //-------------------------------设置单元格样式完成------------------------------------- workbook.write(response.getOutputStream()); } } catch (Exception e) { e.printStackTrace(); } }
/* * 生成数据 * */ public Map<String, List<String>> getData() { Map<String, List<String>> map = new HashMap<>();
List<String> headList = Arrays.asList("姓名", "语文", "数学", "英语"); map.put("headList", headList); List<String> keyHeadList = Arrays.asList("name", "languageScore", "mathScore", "englishScore"); map.put("keyHeadList", keyHeadList);
Integer languageScore = 88; Integer mathScore = 87; Integer englishScore = 86;
for (int i = 0; i < 9; i++) { languageScore += i; mathScore += i; englishScore += i; List<String> data = Arrays.asList("小明" + i, languageScore + "", mathScore + "", englishScore + ""); map.put(i + "", data); } return map; } |
