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

编程（变成）小辣鸡

已于 2023-06-09 14:48:48 修改

阅读量1.9k

点赞数

文章标签： spring boot vue.js elementui

于 2022-09-07 10:31:21 首次发布

本文链接：https://blog.csdn.net/weixin_52803316/article/details/126738252

版权

项目场景：

数据导出或下载

主要实现逻辑

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 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);
    }

    @Autowired
    private IDataService dataService;
}

3）service层代码

    @Override
    public 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();
            // 重置response
            response.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）

    @Override
    public 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();
            // 重置response
            response.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;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public 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 = 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<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);
            }
        }
    }
}