项目场景：
数据导出或下载
主要实现逻辑
1、前端代码
1）数据导出按钮
<template><div><el-button type="primary" icon="download" @click="clickDownload()" style="float: right">导出或下载</el-button></div></template><script setup>import {ElMessage} from 'element-plus'import {funDownloadData} from '@/api/ddd.js'//引入ddd.js//下载--反映问题类型统计const clickDownload= () =>{let d={}; //接口参数根据自己业务需求funDownloadData(d).then(res => {if (!res.success) {ElMessage.error(res.message)return;}ElMessage.success("下载成功");});}</script>
2）ddd.js 文件
import https from '@/utils/https';/*** 下载数据* @param data* @returns {Promise<any> | PromiseLike<any>}*/export function funDownloadData(data){return https({url: '/admin/statistical/downloadData', //输入后端请求数据的接口method: 'POST',responseType: 'blob',data}).then((res)=>{const fileName = "某某.xls"//自定义文件名const link = document.createElement('a')link.href = window.URL.createObjectURL(res);link.download = fileName;link.click();window.URL.revokeObjectURL(link.href);})}
2、后端代码
1）先引入easyExcel依赖
<!-- easyExcel依赖 --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.0-beta2</version></dependency>
2）control层
package com.controller.admin;import m.Result;import com.query.Query;import com.service.IDataService ;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.validation.annotation.Validated;import org.springframework.web.bind.annotation.*;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;/*** 统计控制层*/@RestController@RequestMapping("admin/statistical")public class StatisticalController {@PostMapping(value = "/downloadData",name = "下载统计数据")public Result downloadData(HttpServletRequest req, HttpServletResponse resp, @RequestBody @Validated Query query){ //Query 是用来接收参数，这是根据自己需求自定义的类return dataService.downloadData(req,resp,query);}@Autowiredprivate IDataService dataService;}
3）service层代码
@Overridepublic Result downloadData(HttpServletRequest req, HttpServletResponse response, Query query) {ServletOutputStream out = null;try {out = response.getOutputStream();response.setContentType("application/json;charset=utf-8");response.setCharacterEncoding("utf-8");String fileName = "某某";response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName,"UTF-8")+".xlsx");List<List<String>> headList= new ArrayList<>(); //headList 这是用来自定义表头内容headList.add(Lists.newArrayList( "姓名")); //Lists.newArrayList( "性别")添加一个定义内容就是一级表头headList.add(Lists.newArrayList("性别", "男")); //Lists.newArrayList("性别", "男")添加两个定义内容就是有两级表头headList.add(Lists.newArrayList("性别", "女"));List<ExcelVO> list = getData(query); //这个是请求获取的数据EasyExcel.write(out, ExcelVO.class).head(headList).sheet("某某").doWrite(list);out.flush();} catch (IOException e) {e.printStackTrace();// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");return Result.failure(ResultCode.EXCEL_ERROR.getCode(),ResultCode.EXCEL_ERROR.getMessage());}finally {if (out != null){try {out.close();} catch (IOException e) {e.printStackTrace();}}}return Result.success();}
4)ExcelVO 自定义类
package com.vo;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.HeadRowHeight;import com.alibaba.excel.metadata.BaseRowModel;import lombok.Data;@Data@HeadRowHeight(20) //这个是设置行高public class ExcelVO extends BaseRowModel {@ColumnWidth(20) //这个是单元格宽度@ExcelProperty(index = 0,value = "姓名")private String name;//姓名@ColumnWidth(20)@ExcelProperty(index = 1,value = "男")private String sexMan;//男@ColumnWidth(20)@ExcelProperty(index = 1,value = "女")private String sexWoman;//女}
5)上面的service 层还可以动态自定义表头，也不用自定义接收数据的类（如上面的ExcelVO）
@Overridepublic Result downloadData(HttpServletRequest req, HttpServletResponse response, Query query) {ServletOutputStream out = null;try {out = response.getOutputStream();response.setContentType("application/json;charset=utf-8");response.setCharacterEncoding("utf-8");String fileName = "某某";response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName,"UTF-8")+".xlsx");List<List<String>> headList= new ArrayList<>(); //headList 这是动态定义掉头，拿到数据循环添加就行voS.forEach(v->{ //voS 是你根据请求获取的数据，然后循环添加到headList就可以了headList.add(Lists.newArrayList(v.getName(),v.getSex()));});List<List<String>> list = getData(query); //这里请求获取的数据就要重新处理成List集合的数据，注意处理数据要对应动态表头EasyExcel.write(out).head(headList).registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)).registerWriteHandler(new SimpleRowHeightStyleStrategy((short)40,(short)15)).sheet("某某").doWrite(list); //registerWriteHandler 这个是可以设置表头样式，SimpleColumnWidthStyleStrategy 这个设置列宽，SimpleRowHeightStyleStrategy这个的第一参数是设置表头行高，第二个是设置内容数据的行高out.flush();} catch (IOException e) {e.printStackTrace();// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");return Result.failure(ResultCode.EXCEL_ERROR.getCode(),ResultCode.EXCEL_ERROR.getMessage());}finally {if (out != null){try {out.close();} catch (IOException e) {e.printStackTrace();}}}return Result.success();}
6) 还可以数据内容单元格合并
//需要合并的列int[] mergeColumeIndex = {0,1,2};// 从第二行后开始合并int mergeRowIndex = 2;EasyExcel.write(out,ExcelVO.class).head(headList).registerWriteHandler(new ExcelMergeHandler(mergeRowIndex, mergeColumeIndex)).sheet("某某").doWrite(list);//ExcelMergeHandler 自定义拦截处理器的类，主要用来处理合并数据
7）ExcelMergeHandler 类
package com.handler;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;public class ExcelMergeHandler implements CellWriteHandler {private int[] mergeColumnIndex;private int mergeRowIndex;public ExcelMergeHandler() {}public ExcelMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}/*** 当前单元格向上合并** @param writeSheetHolder* @param cell 当前单元格* @param curRowIndex当前行* @param curColIndex当前列*/private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();// 将当前单元格数据与上一个单元格数据比较Boolean dataBool = preData.equals(curData);//此处需要注意：因为我是按照工程名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是工程名称所在列的下标String s1 = cell.getRow().getCell(0).getStringCellValue();String s2 = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();/*BigDecimal d1 = new BigDecimal(cell.getRow().getCell(0).getNumericCellValue());BigDecimal d2 = new BigDecimal(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue());*/Boolean bool = pareTo(s2) == 0 ? true:false;// 原始的// Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());if (dataBool && bool) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}}
