导出数据生成Excel表格
1. 导出实现类
通过export办法导出指定数据为Excel表格文件
/**导出指定数据为Excel表格参数1：HttpServletResponse response 必须；浏览器响应；参数2：QuestVo vo 非必须；申请条件：能够依据申请条件从数据库筛选出须要导出的数据；如果须要导出全副数据库数据，则能够去掉该参数；参数3：String fileName 必须；导出的Excel文件名称；*/ @Override public void export(HttpServletResponse response,QuestVo vo, String fileName) throws IOException { try{ /** （1）依据申请条件vo，从数据库内获取符合条件的数据， （2）再将其转换为须要导出的对象类型 以上两步操作能够封装在getData()办法中执行 ExportAuditInfo（例子）即为要导出的对象类型，从数据库 取得的数据都须要转换成该对象类型能力进行导出；该对象的 定义依据须要自定义 */ List<ExportAuditInfo> list = getData(vo); //设置浏览器响应的必要参数 ServletOutputStream out = response.getOutputStream(); response.setContentType("multipart/form-data"); response.setCharacterEncoding("utf-8"); String formFileName = new String(fileName.getBytes("utf-8"), "iso-8859-1"); response.setHeader("Content-Disposition", "attachment;filename=" + formFileName + ".xlsx"); //应用EasyExcel.write进行理论的导出操作 EasyExcel.write(out, ExportAuditInfo.class) //CustomCellWriteHandler()办法设置Excel文件内表格的宽度为自适应宽度 //createStyleStrategy()办法设置Excel文件的表格款式 .registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(this.createStyleStrategy()) .autoCloseStream(true) //sheet设置了导出的Excel表格的sheet名称 //doWrite办法负责将须要导出的List数据写入Excel中 .sheet("已审核报警数据").doWrite(list); out.flush(); }catch (Exception e){ LOGGER.error("{},{},{}", DefaultErrorCode.EXCEL_EXPORT_ERROR.getCode(), DefaultErrorCode.EXCEL_EXPORT_ERROR.getMessage(), DefaultErrorCode.EXCEL_EXPORT_ERROR.getContent(),e); } }/**定义CustomCellWriteHandler()办法设置Excel文件内表格的宽度为自适应宽度（可间接复用，无需更改）*/public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy { private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap<>(); CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Integer columnWidth = this.dataLength(cellDataList, cell, isHead); if (columnWidth >= 0) { if (columnWidth > 255) { columnWidth = 255; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } }/**createStyleStrategy()办法设置Excel文件的表格款式（可间接复用，无需更改）*/private HorizontalCellStyleStrategy createStyleStrategy(){ // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 背景设置为红色 headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)10); headWriteCellStyle.setWriteFont(headWriteFont); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); //底边框 contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); //左边框 contentWriteCellStyle.setBorderRight(BorderStyle.THIN); //左边框 contentWriteCellStyle.setBorderTop(BorderStyle.THIN); //顶边框 WriteFont contentWriteFont = new WriteFont(); // 字体大小 contentWriteFont.setFontHeightInPoints((short)10); contentWriteCellStyle.setWriteFont(contentWriteFont); // 这个策略是 头是头的款式 内容是内容的款式 其余的策略能够本人实现 HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle); return horizontalCellStyleStrategy; }2. 导出实体类ExportAuditInfo
注解@ExcelProperty("")
该注解用来指定Excel表格中的该属性对应的列名称