想要将数据使用excel表格导出，可以使用easyexcel
pom.xml
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency>
ExcelUtil
@Componentpublic class ExcelUtil {public static Map<Integer,String> addTop(String str) {Map<Integer,String> topMap = new HashMap<Integer,String>();topMap.put(0,str);return topMap;}public static Map<Integer,String> addStatistic(List<ExampleInfo> list) {Map<Integer,String> statisticMap = new HashMap<Integer,String>();statisticMap.put(0,null);for (int i =0; i<list.size(); i++) {statisticMap.put( i+1 , list.get(i).getOrgName());}return statisticMap;}public static Map<Integer,String> addNum(List<ExampleInfo> list) {Map<Integer,String> numMap = new HashMap<Integer,String>();numMap.put(0,null);for (int i =0; i<list.size(); i++) {numMap.put( i+1 , list.get(i).getCount().toString());}return numMap;}}
ExampleInfo
@Data@AllArgsConstructor@NoArgsConstructorpublic class ExampleInfo {private Integer id;private String orgName;private Integer count = 0;}
CustomCellWriteHandler
public class CustomCellWriteHandler implements CellWriteHandler {public void afterCellDispose(CellWriteHandlerContext context) {Cell cell = context.getCell();int rowIndex = cell.getRowIndex();int cellIndex = cell.getColumnIndex();// 自定义样式处理// 当前事件会在 数据设置到poi的cell里面才会回调// 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not trueif (BooleanUtils.isNotTrue(context.getHead())) {if (cell.getRowIndex() == 0 && StringUtils.isNotBlank(cell.getStringCellValue())) {// 拿到poi的workbookWorkbook workbook = context.getWriteWorkbookHolder().getWorkbook();// 这里千万记住 想办法能复用的地方把他缓存起来 一个表格最多创建6W个样式// 不同单元格尽量传同一个 cellStyleCellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setColor(Font.COLOR_RED);cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cell.setCellStyle(cellStyle);// 由于这里没有指定dataformat 最后展示的数据 格式可能会不太正确// 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到// cell里面去 会导致自己设置的不一样（很关键）context.getFirstCellData().setWriteCellStyle(null);} else if (cell.getRowIndex() == 1 && StringUtils.isNotBlank(cell.getStringCellValue())) {Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();//背景颜色cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUNDcellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setWrapText(true);//设置自动换行cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框cell.setCellStyle(cellStyle);Sheet sheet = cell.getSheet();for (int i = 0; i < cellIndex; i++) {sheet.setColumnWidth(i + 1,5000);}context.getFirstCellData().setWriteCellStyle(null);} else if (cell.getRowIndex() == 2 && StringUtils.isNotBlank(cell.getStringCellValue())) {Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setColor(Font.COLOR_RED);cellStyle.setFont(font);cellStyle.setWrapText(true);//设置自动换行cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框cell.setCellStyle(cellStyle);context.getFirstCellData().setWriteCellStyle(null);} else if (cell.getRowIndex() == 3 && StringUtils.isNotBlank(cell.getStringCellValue())) {Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setColor(Font.COLOR_RED);cellStyle.setFont(font);cellStyle.setWrapText(true);//设置自动换行cellStyle.setAlignment(HorizontalAlignment.LEFT);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cell.setCellStyle(cellStyle);context.getFirstCellData().setWriteCellStyle(null);} else if (cell.getRowIndex() == 4 && StringUtils.isNotBlank(cell.getStringCellValue())) {Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setWrapText(true);//设置自动换行cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框if (cell.getColumnIndex() == 1) {Font font = workbook.createFont();font.setColor(Font.COLOR_RED);cellStyle.setFont(font);}cell.setCellStyle(cellStyle);context.getFirstCellData().setWriteCellStyle(null);} else if (cell.getRowIndex() > 4 && cell.getColumnIndex() <= 11) {Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();cellStyle.setWrapText(true);//设置自动换行cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框if (cell.getColumnIndex() == 1) {Font font = workbook.createFont();font.setColor(Font.COLOR_RED);cellStyle.setFont(font);}cell.setCellStyle(cellStyle);context.getFirstCellData().setWriteCellStyle(null);}}}}
ExcelTestController
@RestController@Api(tags = "测试excel表格导出")@RequestMapping("/excel")@Slf4jpublic class ExcelTestController {@GetMapping("/export")public Object exportDataReport(HttpServletResponse response) {List<Map<Integer,String>> firstDataList = new ArrayList<Map<Integer,String>>();List<Map<Integer,String>> midDataList = new ArrayList<Map<Integer,String>>();List<Map<Integer,String>> finDataList = new ArrayList<Map<Integer,String>>();//日期转换格式SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日");Calendar yesterday=Calendar.getInstance();Calendar tomorrow=Calendar.getInstance();Calendar theDayAfterTomorrow=Calendar.getInstance();//昨天yesterday.add(Calendar.DATE, -1);//明天tomorrow.add(Calendar.DATE, 1);//后天theDayAfterTomorrow.add(Calendar.DATE, 2);String firstTop = simpleDateFormat.format(new Date()) + "测试名单1";String midTop = simpleDateFormat.format(new Date()) + "测试名单2";String finTop = simpleDateFormat.format(theDayAfterTomorrow.getTime()) + "测试名单3";firstDataList.add(ExcelUtil.addTop(firstTop));midDataList.add(ExcelUtil.addTop(midTop));finDataList.add(ExcelUtil.addTop(finTop));List<ExampleInfo> firstResult = new ArrayList<ExampleInfo>();firstResult.add(0,new ExampleInfo(1,"测试1",1));firstResult.add(1,new ExampleInfo(2,"测试2",2));firstResult.add(2,new ExampleInfo(3,"测试3",3));firstResult.add(3,new ExampleInfo(4,"测试4",4));firstResult.add(4,new ExampleInfo(5,"测试5",5));List<ExampleInfo> midResult = new ArrayList<ExampleInfo>();midResult.add(0,new ExampleInfo(1,"测试1",1));midResult.add(1,new ExampleInfo(2,"测试2",2));midResult.add(2,new ExampleInfo(3,"测试3",3));midResult.add(3,new ExampleInfo(4,"测试4",4));midResult.add(4,new ExampleInfo(5,"测试5",5));List<ExampleInfo> finResult = new ArrayList<ExampleInfo>();finResult.add(0,new ExampleInfo(1,"测试1",1));finResult.add(1,new ExampleInfo(2,"测试2",2));finResult.add(2,new ExampleInfo(3,"测试3",3));finResult.add(3,new ExampleInfo(4,"测试4",4));finResult.add(4,new ExampleInfo(5,"测试5",5));firstDataList.add(ExcelUtil.addStatistic(firstResult));midDataList.add(ExcelUtil.addStatistic(midResult));finDataList.add(ExcelUtil.addStatistic(finResult));firstDataList.add(ExcelUtil.addNum(firstResult));midDataList.add(ExcelUtil.addNum(midResult));finDataList.add(ExcelUtil.addNum(finResult));Map<Integer,String> titleMap = new HashMap<Integer,String>();titleMap.put(0,"详细数据:");firstDataList.add(titleMap);midDataList.add(titleMap);finDataList.add(titleMap);Map<Integer,String> headMap = new HashMap<Integer,String>();headMap.put(0,"标题1");headMap.put(1,"标题2");headMap.put(2,"标题3");headMap.put(3,"标题4");headMap.put(4,"标题5");headMap.put(5,"标题6");headMap.put(6,"标题7");headMap.put(7,"标题8");headMap.put(8,"标题9");headMap.put(9,"标题10");firstDataList.add(headMap);midDataList.add(headMap);finDataList.add(headMap);firstDataList = this.addlist(firstDataList);midDataList = this.addlist(midDataList);finDataList = this.addlist(finDataList);String fileName = String.format("D:\\testexcel\\%s","测试表格导出.xlsx");ExcelWriter excelWriter = EasyExcel.write(fileName).build(); // 本地//前端下载// ExcelWriter excelWriter = EasyWriteUtils.getExcelWriter(response,"excel表格.xlsx");OnceAbsoluteMergeStrategy firstOnceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(0, 0, 0, firstResult.size());OnceAbsoluteMergeStrategy midOnceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(0, 0, 0, midResult.size());OnceAbsoluteMergeStrategy finOnceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(0, 0, 0, finResult.size());OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy1 = new OnceAbsoluteMergeStrategy(3, 3, 0, 9);WriteCellStyle headWriteCellStyle = new WriteCellStyle();//内容策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置 自动换行contentWriteCellStyle.setWrapped(true);//设置 垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);WriteSheet sheet1 = EasyExcel.writerSheet(1, "模板1").registerWriteHandler(firstOnceAbsoluteMergeStrategy).registerWriteHandler(onceAbsoluteMergeStrategy1).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new CustomCellWriteHandler()).build();WriteSheet sheet2 = EasyExcel.writerSheet(2, "模板2").registerWriteHandler(midOnceAbsoluteMergeStrategy).registerWriteHandler(onceAbsoluteMergeStrategy1).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new CustomCellWriteHandler()).build();WriteSheet sheet3 = EasyExcel.writerSheet(3, "模板3").registerWriteHandler(finOnceAbsoluteMergeStrategy).registerWriteHandler(onceAbsoluteMergeStrategy1).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new CustomCellWriteHandler()).build();excelWriter.write(firstDataList,sheet1);excelWriter.write(midDataList,sheet2);excelWriter.write(finDataList,sheet3);excelWriter.finish();try {response.flushBuffer();} catch (Exception e) {e.printStackTrace();}return null;}public List<Map<Integer,String>> addlist(List<Map<Integer,String>> list) {for (int i = 0; i <= 10; i++) {Map<Integer,String> map = new HashMap<Integer,String>();map.put(0,"测试1");map.put(1,"测试2");map.put(2,"测试3");map.put(3,"测试4");map.put(4,"测试5");map.put(5,"测试6");map.put(6,"测试7");map.put(7,"测试8");map.put(8,"测试9");map.put(9,"测试10");list.add(map);}return list;}}
