excel模板类
/** * Created by wang on 2018/9/11. */ public class ScoreCertExcelUtil { /** * 打印表格 */ public static HSSFWorkbook print(List<Map<String, Object>> resultList) throws Exception { //通用变量 int rowNo = 0, cellNo = 0; Row nRow; Cell nCell; //1.创建工作簿 HSSFWorkbook wb = new HSSFWorkbook(); //2.创建工作表 Sheet sheet = wb.createSheet(); //设置列宽 sheet.setColumnWidth(cellNo, 29 * 256); cellNo = 0;//重置 //3.创建行对象 //=======================================小标题==================================== String titles[] = {"编号"}; //创建小标题的行对象 nRow = sheet.createRow(rowNo++); nRow.setHeightInPoints(26.25f);//设置行高 //创建单元格对象,并设置内容 ,并设置样式 CellStyle titleCellStyle = title(wb); for (String title : titles) { nCell = nRow.createCell(cellNo++);//创建单元格对象 nCell.setCellValue(title);//设置内容 nCell.setCellStyle(titleCellStyle);//设置样式 } //=======================================数据输出================================= CellStyle textCellStyle = text(wb); for (int i = 0; i < resultList.size(); i++) { nRow = sheet.createRow(rowNo++);//产生数据行 nRow.setHeightInPoints(24);//设置行高 cellNo = 0; nCell = nRow.createCell(cellNo); nCell.setCellValue((String) resultList.get(i).get("scoreCertCode")); nCell.setCellStyle(textCellStyle); } return wb; } //小标题的样式 private static CellStyle title(Workbook wb) { CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 12); font.setBold(true); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setBorderTop(BorderStyle.THIN); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); //背景色 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex()); return style; } //文字样式 private static CellStyle text(Workbook wb) { CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setFontName("Times New Roman"); font.setFontHeightInPoints((short) 10); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setBorderTop(BorderStyle.THIN); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); return style; } }
serviceImpl层代码块(service层这里就不写了)
@Override public ResultDO<List<Map<String, Object>>> downloadExcel() { List<Map<String, Object>> resultList = new ArrayList<>(); Map<String, Object> rMap; for (int i=0;i<10;i++) { rMap = new HashMap<>(); rMap.put("scoreCertCode",i); resultList.add(rMap); } return new ResultDO<>(resultList); }
controller层代码
/** * 导出表格 */ @ResponseBody @RequestMapping("/downloadExcel.ajax") public Map<String, Object> downloadExcel(HttpServletResponse response) { Map<String, Object> resultMap = new HashMap<>(); String fileName ="111.xls"; response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment; filename=" + fileName); OutputStream outputStream; ResultDO<List<Map<String, Object>>> workbookResultDO = certBindLogService.downloadExcel(); if (workbookResultDO.success()) { try { List<Map<String, Object>> resultList = workbookResultDO.getObj(); HSSFWorkbook wb = null; try { wb = DownloadExcelUtil.print(resultList); } catch (Exception e) { MedChatLogger.error(e); } outputStream = response.getOutputStream(); wb.write(outputStream); outputStream.flush(); outputStream.close(); } catch (IOException e) { e.printStackTrace(); } resultMap.put(MESSAGE, "导出成功"); this.setSuccess(resultMap); } else { this.setFailure(resultMap, workbookResultDO.getErrorMsg()); } return resultMap; }
之前写的时候样式没有提到外面 每次表格样式赋值的时候都要调用一次样式的方法,
数据量大的时候就会报这个错(The maximum number of cell styles was exceeded. You can define up to 4000 styles)
同事帮找到的解决方案的例子,这里把解决方案地址附上(https://blog.csdn.net/johnstrive/article/details/8568113)
我这写的例子是一列数据的例子,如果需要多列可以在ScoreCertExcelUtil里的小标题和数据输出位置追加列和行,代码可能不够优化,欢迎指点,共同交流。