这篇文章会给大家实操一个关于列表导出成excel表格的功能，相信大家在日常工作中也会遇到列表导出的需求，看完本篇文章那么你就可以轻松的去整合列表导出的功能。
本次使用的导出工具是阿里巴巴开源的EasyExcel，关于EasyExcel我这里简单的介绍一下：
我们知道Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。
ok，下面我们就开始实操整合一下EasyExcel，实现导出功能。
第一步：我们先创建Maven项目，整合成SpringBoot的项目。
- 加入依赖，创建测试接口，确保项目能够运行。
- pom.xml加入依赖
<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.1.5.RELEASE</version><relativePath/></parent><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.8</version></dependency></dependencies>
- 定义主类
/**
* @author lixiang
* @date 2023/5/26 16:02
*/@SpringBootApplicationpublicclassExcelApplication{publicstaticvoidmain(String[] args){SpringApplication.run(ExcelApplication.class, args);}}
- 创建控制器
/**
* @author lixiang
* @date 2023/5/26 16:00
*/@RestController@RequestMapping("/excel")publicclassExcelController{@GetMapping("/create")publicStringcreate(){String msg ="SUCCESS";return msg;}}
- 测试运行
第二步：引入EasyExcel依赖，封装工具类
<dependency><groupId>com.pig4cloud.excel</groupId><artifactId>excel-spring-boot-starter</artifactId><version>0.5.0</version></dependency>
packagecom.lixiang.util;importcom.alibaba.excel.EasyExcel;importcom.alibaba.excel.write.builder.ExcelWriterSheetBuilder;importcom.alibaba.excel.write.handler.RowWriteHandler;importcom.alibaba.excel.write.metadata.holder.WriteSheetHolder;importcom.alibaba.excel.write.metadata.holder.WriteTableHolder;importcom.alibaba.excel.write.metadata.style.WriteCellStyle;importcom.alibaba.excel.write.style.HorizontalCellStyleStrategy;importlombok.AllArgsConstructor;importlombok.Data;importlombok.NoArgsConstructor;importorg.apache.poi.ss.usermodel.*;importorg.apache.poi.ss.util.CellRangeAddress;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.net.URLEncoder;importjava.util.ArrayList;importjava.util.List;/**
* 导出excel 表格 工具类
* @author lixiang
* @date 2023/5/26 10:18
*/publicclassEasyExcelUtilimplementsRowWriteHandler{privateint mergeRowIndex;//从哪一行开始合并privateint[] mergeColumnIndex;//excel合并的列privateint[] signNum;//合并的唯一标识privateint total;//总行数privateint lastRow;privateint firstCol;privateint lastCol;privateint firstRow;privateint mergeCount =1;privateEasyExcelUtil(){}privateEasyExcelUtil(int mergeRowIndex,int[] mergeColumnIndex,int[] signNum,int total){this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;this.signNum = signNum;this.total = total;}/**
* 导出excel
* @param response
* @param fileName 文件名称
* @param exportList 导出数据
* @param clazz 导出实体bean class对象
* @param <T>
* @throws IOException
*/publicstatic<T>voidcreateExcel(HttpServletResponse response,String fileName,List<T> exportList,Class<T> clazz)throwsIOException{createExcel(response,fileName,exportList,clazz,null);}/**
* 导出excel
* @param response
* @param fileName 文件名称
* @param exportList 导出数据
* @param clazz 导出实体bean class对象
* @param cellMerge 单元格合并规则
* @param <T>
* @throws IOException
*/publicstatic<T>voidcreateExcel(HttpServletResponse response,String fileName,List<T> exportList,Class<T> clazz,CellMerge cellMerge)throwsIOException{// 设置下载信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
fileName =URLEncoder.encode(fileName,"UTF-8");
response.setHeader("Content-disposition","attachment;filename="+ fileName +".xlsx");//定义ExcelWriterSheetBuilderExcelWriterSheetBuilder excelWriterSheetBuilder =EasyExcel.write(response.getOutputStream(), clazz).sheet(fileName);//合并单元格if(cellMerge !=null){// 从那一行开始合并int mergeRowIndex =1;EasyExcelUtil
excelMergeRowByRowStrategy =newEasyExcelUtil(mergeRowIndex, cellMerge.getMergeColumIndex(),
cellMerge.getMergeRuleColumIndex(), exportList.size());
excelWriterSheetBuilder.registerWriteHandler(excelMergeRowByRowStrategy);}//设置头样式WriteCellStyle headWriteCellStyle =newWriteCellStyle();//设置内容格式WriteCellStyle contentWriteCellStyle =newWriteCellStyle();HorizontalCellStyleStrategy horizontalCellStyleStrategy =newHorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);//设计内容居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置内容自动换行
contentWriteCellStyle.setWrapped(true);
excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy);//调用doWrite方法
excelWriterSheetBuilder.doWrite(exportList);}@OverridepublicvoidbeforeRowCreate(WriteSheetHolder writeSheetHolder,WriteTableHolder writeTableHolder,Integer integer,Integer integer1,Boolean aBoolean){}@OverridepublicvoidafterRowCreate(WriteSheetHolder writeSheetHolder,WriteTableHolder writeTableHolder,Row row,Integer integer,Boolean aBoolean){}@OverridepublicvoidafterRowDispose(WriteSheetHolder writeSheetHolder,WriteTableHolder writeTableHolder,Row row,Integer relativeRowIndex,Boolean isHead){//当前行int curRowIndex = row.getRowNum();//每一行的最大列数short lastCellNum = row.getLastCellNum();if(curRowIndex ==1){//赋初值 第一行
firstRow = curRowIndex;}//开始合并位置if(curRowIndex > mergeRowIndex &&!row.getCell(0).getStringCellValue().equals("")){for(int i =0; i < lastCellNum; i++){if(i == mergeColumnIndex[i]){//当前行号 当前行对象 合并的标识位mergeWithPrevAnyRow(writeSheetHolder.getSheet(), curRowIndex, row, signNum);break;//已经进入到合并单元格操作里面了，执行一次就行}}}}publicvoidmergeWithPrevAnyRow(Sheet sheet,int curRowIndex,Row row,int[] signNum){Row preRow = row.getSheet().getRow(curRowIndex -1);List<String> rowDataList =newArrayList<>();List<String> preDataList =newArrayList<>();for(int i : signNum){Object currentData =
row.getCell(i).getCellTypeEnum()==CellType.STRING ? row.getCell(i).getStringCellValue():
row.getCell(i).getNumericCellValue();Object preData =
preRow.getCell(i).getCellTypeEnum()==CellType.STRING ? preRow.getCell(i).getStringCellValue():
preRow.getCell(i).getNumericCellValue();
rowDataList.add(String.valueOf(currentData));
preDataList.add(String.valueOf(preData));}String rowDataStr =String.join(",", rowDataList);String preDataStr =String.join(",", preDataList);//判断是否合并单元格boolean curEqualsPre = rowDataStr.equals(preDataStr);//判断前一个和后一个相同 并且 标识位相同if(curEqualsPre){
lastRow = curRowIndex;
mergeCount++;}//excel过程中合并if(!curEqualsPre && mergeCount >1){mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
mergeCount =1;}//excel结尾处合并if(mergeCount >1&& total == curRowIndex){mergeSheet(firstRow, lastRow, mergeColumnIndex, sheet);
mergeCount =1;}if(!curEqualsPre){
firstRow = curRowIndex;}}privatevoidmergeSheet(int firstRow,int lastRow,int[] mergeColumnIndex,Sheet sheet){for(int colNum : mergeColumnIndex){
firstCol = colNum;
lastCol = colNum;CellRangeAddress cellRangeAddress =newCellRangeAddress(firstRow, lastRow, firstCol, lastCol);
sheet.addMergedRegion(cellRangeAddress);}}/**
* 单元格合并类
*/@Data@AllArgsConstructor@NoArgsConstructorpublicclassCellMerge{privateint[] mergeColumIndex;privateint[] mergeRuleColumIndex;}/**
* 设置单元格合并规则
* @param mergeColumIndex
* @param mergeRuleColumIndex
* @return
*/publicstaticCellMergesetCellMerge(int[] mergeColumIndex,int[] mergeRuleColumIndex){EasyExcelUtil excelUtil =newEasyExcelUtil();CellMerge cellMerge = excelUtil.newCellMerge();
cellMerge.setMergeColumIndex(mergeColumIndex);
cellMerge.setMergeRuleColumIndex(mergeRuleColumIndex);return cellMerge;}}
第三步：模拟测试数据
- 创建学生实体类
/**
* @ColumnWidth(20) 这个是设置单元格长度的
* @ExcelProperty("") 这个是设置表格头部的
* @author lixiang
* @date 2023/5/26 16:30
*/@Data@AllArgsConstructor@NoArgsConstructor@ColumnWidth(20)publicclassStudent{@ExcelProperty("年级")privateString gradeName;@ExcelProperty("班级")privateString className;@ExcelProperty("姓名")privateString name;@ExcelProperty("年龄")privateInteger age;@ExcelProperty("性别")privateString sex;}
- 模拟学生数据
/**
* @author lixiang
* @date 2023/5/26 16:13
*/@ServicepublicclassStudentService{List<String> classNames;List<String> gradeNames;{
classNames =newArrayList<>();
classNames.add("一班");
classNames.add("二班");
classNames.add("三班");
gradeNames =newArrayList<>();
gradeNames.add("2017级");
gradeNames.add("2018级");
gradeNames.add("2019级");}publicList<Student>getStudentData(){List<Student> list =newArrayList<>();for(int i =1; i <20; i++){Student student =newStudent();
student.setName("李祥"+i);
student.setClassName(getClassName());
student.setGradeName(getGradeName());
student.setSex("男");
student.setAge(18);
list.add(student);}return list;}/**
* 获取班级
* @return
*/privateStringgetClassName(){Random rand =newRandom();return classNames.get(rand.nextInt(classNames.size()));}/**
* 获取年级
* @return
*/privateStringgetGradeName(){Random rand =newRandom();return gradeNames.get(rand.nextInt(gradeNames.size()));}}
/**
* @author lixiang
* @date 2023/5/26 16:00
*/@RestController@RequestMapping("/excel")publicclassExcelController{@AutowiredprivateStudentService studentService;@GetMapping("/create")publicList<Student>create(){List<Student> studentData = studentService.getStudentData();return studentData;}}
第四步：基于这个数据我们进行导出成excel
/**
* @author lixiang
* @date 2023/5/26 16:00
*/@RestController@RequestMapping("/excel")publicclassExcelController{@AutowiredprivateStudentService studentService;@GetMapping("/create")publicvoidcreate(HttpServletResponse response)throwsException{List<Student> studentData = studentService.getStudentData();String fileName ="学生列表";/**
* 第一个参数：HttpServletResponse
* 第二个参数：文件名称
* 第三个参数：数据集
* 第四个参数：数据集实体class对象
*/EasyExcelUtil.createExcel(response,fileName,studentData,Student.class);}}
Ok，那么现在 我们想要合并单元格，将同年级的同班级的单元格进行合并。
@GetMapping("/create")publicvoidcreate(HttpServletResponse response)throwsException{List<Student> studentData = studentService.getStudentData();String fileName ="学生列表";/**
* 第一个参数：HttpServletResponse
* 第二个参数：文件名称
* 第三个参数：数据集
* 第四个参数：数据集实体class对象
* 第五个参数：合并单元格的规则
* EasyExcelUtil.setCellMerge(meargeColl, meargeColl);
* 第一个参数 是 我们要合并的那些列，第二个是合并的规则。
*/int[] meargeColl =newint[]{0,1};EasyExcelUtil.CellMerge cellMerge =EasyExcelUtil.setCellMerge(meargeColl, meargeColl);EasyExcelUtil.createExcel(response,fileName,studentData,Student.class,cellMerge);}
注意合并的时候，需要将数据进行排序，确保年级，班级相同的数据都在一起。现在我们先进行数据排序。
- 定义中文排序工具
/**
* @author lixiang
* @date 2023/5/26 17:14
*/publicclassChineseNumberUtil{publicstaticintchineseNumber2Int(String chineseNumber){int result =0;int temp =1;//存放一个单位的数字如：十万int count =0;//判断是否有chArrchar[] cnArr =newchar[]{'一','二','三','四','五','六','七','八','九'};char[] chArr =newchar[]{'十','百','千','万','亿'};for(int i =0; i < chineseNumber.length(); i++){boolean b =true;//判断是否是chArrchar c = chineseNumber.charAt(i);for(int j =0; j < cnArr.length; j++){//非单位，即数字if(c == cnArr[j]){if(0!= count){//添加下一个单位之前，先把上一个单位值添加到结果中
result += temp;
temp =1;
count =0;}// 下标+1，就是对应的值
temp = j +1;
b =false;break;}}if(b){//单位{'十','百','千','万','亿'}for(int j =0; j < chArr.length; j++){if(c == chArr[j]){switch(j){case0:
temp *=10;break;case1:
temp *=100;break;case2:
temp *=1000;break;case3:
temp *=10000;break;case4:
temp *=100000000;break;default:break;}
count++;}}}if(i == chineseNumber.length()-1){//遍历到最后一个字符
result += temp;}}return result;}}
- service进行排序
publicList<Student>getStudentData(){List<Student> list =newArrayList<>();for(int i =1; i <20; i++){Student student =newStudent();
student.setName("李祥"+i);
student.setClassName(getClassName());
student.setGradeName(getGradeName());
student.setSex("男");
student.setAge(18);
list.add(student);}//排序逻辑
list.sort((s1,s2)->{Integer flag =Integer.parseInt(s1.getGradeName().substring(0,4))-Integer.parseInt(s2.getGradeName().substring(0,4));if(flag ==0){returnInteger.compare(ChineseNumberUtil.chineseNumber2Int(s1.getClassName().substring(0,2)),ChineseNumberUtil.chineseNumber2Int(s2.getClassName().substring(0,2)));}return flag;});return list;}
OK，我们排序完成就可以进行合并啦。
int[] meargeColl =newint[]{0,1};EasyExcelUtil.CellMerge cellMerge =EasyExcelUtil.setCellMerge(meargeColl, meargeColl);EasyExcelUtil.createExcel(response,fileName,studentData,Student.class,cellMerge);
我们看到相同的行已经被合并啦，ok，excel整合我们就完成啦。
本文转载自: https://blog.csdn.net/weixin_47533244/article/details/130891333
版权归原作者 互联网小阿祥 所有， 如有侵权，请联系我们删除。
版权归原作者 互联网小阿祥 所有， 如有侵权，请联系我们删除。