vue3+springboot 实现数据导出，多级表头，可设置表头宽度和数据内容单元格合并

项目场景：

数据导出或下载

主要实现逻辑

1、前端代码

1）数据导出按钮

2）ddd.js 文件

import https from '@/utils/https';/*** 下载数据* @param data* @returns {Promise | PromiseLike}*/
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依赖

com.alibabaeasyexcel2.2.0-beta2

2）control层

package com.controller.admin;import com.comm.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> headList= new ArrayList<>();  //headList 这是用来自定义表头内容headList.add(Lists.newArrayList( "姓名"));  //Lists.newArrayList( "性别")添加一个定义内容就是一级表头headList.add(Lists.newArrayList("性别", "男")); //Lists.newArrayList("性别", "男")添加两个定义内容就是有两级表头headList.add(Lists.newArrayList("性别", "女"));List 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> headList= new ArrayList<>();  //headList 这是动态定义掉头，拿到数据循环添加就行voS.forEach(v->{  //voS 是你根据请求获取的数据，然后循环添加到headList就可以了headList.add(Lists.newArrayList(v.getName(),v.getSex()));});List> 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 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 = s1.compareTo(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 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);}}}
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！