vue+Java 实现前后端Excel导出

1. 按钮设置

使用avue的导出功能可以查看官网
https://www.avuejs.com/default/export.html


我这里使用的是自定义的按钮，并且自己实现了Excel的导出

2、Java后端代码

（1）定义Excel工具类

这个工具类是根据ExcelWriter 这个类进行创建的

public class ExcelExportUtils {
    public static void exportExcelByResponse(ExcelWriter excelWriter, HttpServletResponse response, String fileName, HttpServletRequest request) throws IOException {
        String userAgent = request.getHeader("USER-AGENT");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Trident") || StringUtils.contains(userAgent, "Edge")) {//IE 浏览器
            fileName = URLEncoder.encode(fileName, "UTF8");
        } else {//火狐，google等其他浏览器
            fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
        }
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        //客户端不缓存
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        Workbook workbook = excelWriter.getWorkbook();
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        //关闭缓存
        workbook.close();
        outputStream.close();
    }
}

（2）创建枚举类来指定Excel表格中头标题的别名

/**
 * Excel表格别名定义
 * @author fanxiaoxuan
 * @version 1.0
 * @since 2021/12/15 10:42
 */
public enum SplitExportEnum {

    PLAT_NO("platNo","平台编号"),
    BIZ_ORDER_NO("bizOrderNo","业务单号"),
    ORDER_NO("orderNo","支付单号"),
    BUSINESS_TYPE("businessType","业务类型"),
    BIZ_SPLIT_NO("bizSplitNo","业务分账单号"),
    SPLIT_NO("splitNo","支付分账单号"),
    TRANS_TIME("transTime","分账时间"),
    MEMBER_NO("memberNo","会员号"),
    MEMBER_NAME("memberName","会员名称"),
    SPLIT_TYPE("splitType","分账类型"),
    ORDER_AMT("orderAmt","订单金额"),
    SPLIT_AMT("splitAmt","分账金额"),
    CLEAR_TIME("clearTime","清算时间"),
    SETTLE_TIME("settleTime","结算时间"),
    CHANNEL_FLOW_NO("channelFlowNo","渠道流水号");


    private String name;  //字段名称
    private String alias; //字段对应的别名
    private SplitExportEnum(String name,String alias){
        this.name = name;
        this.alias = alias;
    }

    public String getName(){
        return name;
    }
    public String getAlias(){
        return alias;
    }
}

（3）创建业务代码

/**
 * 导出分账明细表
 *
 * @author fanxiaoxuan
 * @version 1.0
 * @since 2021/12/14 15:25
 */
@Slf4j
@RestController
@RequestMapping("splitExport")
public class GatePabSplitDetailExportController {

    @Autowired
    private OrderSplitDetailManager orderSplitDetailManager;

    @GetMapping("/list")
    public Result<String> getDetailList(Page<OrderSplitDetailEntity> page, OrderSplitDetailEntity orderSplitDetailEntity, HttpServletResponse resp, HttpServletRequest request) {
        // 实例excel模板
        log.info("进入导出应用");
        //获取搜索条件里面的值
        Page<OrderSplitDetailEntity> pages = orderSplitDetailManager.findByPage(page, orderSplitDetailEntity);
        //首先先判断数量是否超限制 (根据自己的业务需求)
        if (pages.getTotal() > 1000) {
            log.error("数据量过大，total={}", pages.getTotal());
            return Result.failed("数据量过大，最多只能下载1000条，请缩小搜索范围!");
        }
        //初始化Excel
        ExcelWriter writer = ExcelUtil.getBigWriter();
        try {
            // 变更excel样式
            StyleSet style = writer.getStyleSet();
            CellStyle cell = style.getHeadCellStyle();
            cell.setVerticalAlignment(VerticalAlignment.CENTER);
            // 新增列的数据模板 （设置别名）
            writer.addHeaderAlias(SplitExportEnum.PLAT_NO.getName(), SplitExportEnum.PLAT_NO.getAlias());
            writer.addHeaderAlias(SplitExportEnum.BIZ_ORDER_NO.getName(), SplitExportEnum.BIZ_ORDER_NO.getAlias());
            writer.addHeaderAlias(SplitExportEnum.ORDER_NO.getName(), SplitExportEnum.ORDER_NO.getAlias());
            writer.addHeaderAlias(SplitExportEnum.BUSINESS_TYPE.getName(), SplitExportEnum.BUSINESS_TYPE.getAlias());
            writer.addHeaderAlias(SplitExportEnum.BIZ_SPLIT_NO.getName(), SplitExportEnum.BIZ_SPLIT_NO.getAlias());
            writer.addHeaderAlias(SplitExportEnum.SPLIT_NO.getName(), SplitExportEnum.SPLIT_NO.getAlias());
            writer.addHeaderAlias(SplitExportEnum.TRANS_TIME.getName(), SplitExportEnum.TRANS_TIME.getAlias());
            writer.addHeaderAlias(SplitExportEnum.MEMBER_NO.getName(), SplitExportEnum.MEMBER_NO.getAlias());
            writer.addHeaderAlias(SplitExportEnum.MEMBER_NAME.getName(), SplitExportEnum.MEMBER_NAME.getAlias());
            writer.addHeaderAlias(SplitExportEnum.SPLIT_TYPE.getName(), SplitExportEnum.SPLIT_TYPE.getAlias());
            writer.addHeaderAlias(SplitExportEnum.ORDER_AMT.getName(), SplitExportEnum.ORDER_AMT.getAlias());
            writer.addHeaderAlias(SplitExportEnum.SPLIT_AMT.getName(), SplitExportEnum.SPLIT_AMT.getAlias());
            writer.addHeaderAlias(SplitExportEnum.CLEAR_TIME.getName(), SplitExportEnum.CLEAR_TIME.getAlias());
            writer.addHeaderAlias(SplitExportEnum.SETTLE_TIME.getName(), SplitExportEnum.SETTLE_TIME.getAlias());
            writer.addHeaderAlias(SplitExportEnum.CHANNEL_FLOW_NO.getName(), SplitExportEnum.CHANNEL_FLOW_NO.getAlias());
            writer.getSheet().setDefaultColumnWidth(20); //设置当前sheet页列宽
            //获取集合值
            List<OrderSplitDetailEntity> records = pages.getRecords();  
            //将需要展示的字段放到集合中，SplitDetailExportVo是需要展示出来的字段（自己创建对象）
            List<SplitDetailExportVo> splitDetailLists = new ArrayList<>();
            //遍历值放到SplitDetailExportVo中用于展示，
            for (OrderSplitDetailEntity order : records) {
                SplitDetailExportVo splitDetailExportVo = new SplitDetailExportVo();
                splitDetailExportVo.setPlatNo(order.getPlatNo());
                splitDetailExportVo.setBizOrderNo(order.getBizOrderNo());
                splitDetailExportVo.setOrderNo(order.getOrderNo());
                splitDetailExportVo.setBusinessType(TypeUtils.businessTypeTrans(order.getBusinessType()));
                splitDetailExportVo.setBizSplitNo(order.getBizSplitNo());
                splitDetailExportVo.setSplitNo(order.getSplitNo());
                splitDetailExportVo.setTransTime(order.getTransTime());
                splitDetailExportVo.setMemberNo(order.getMemberNo());
                splitDetailExportVo.setMemberName(order.getMemberName());
                splitDetailExportVo.setSplitType(TypeUtils.splitTypeTrans(order.getSplitType()));
                splitDetailExportVo.setOrderAmt(new BigDecimal(order.getOrderAmt()).divide(PmsConst.ONE_HUNDRED).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                splitDetailExportVo.setSplitAmt(new BigDecimal(order.getSplitAmt()).divide(PmsConst.ONE_HUNDRED).setScale(2, BigDecimal.ROUND_HALF_UP).toString());
                splitDetailExportVo.setClearTime(order.getClearTime());
                splitDetailExportVo.setSettleTime(order.getSettleTime());
                splitDetailExportVo.setChannelFlowNo(order.getChannelFlowNo());
                splitDetailLists.add(splitDetailExportVo);
            }
            //将数据写到excel中
            writer.write(splitDetailLists);
        } catch (Exception e) {
            log.error("导出excel异常", e);
            resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            return Result.failed("导出excel系统异常!");
        }
        try {
            ExcelExportUtils.exportExcelByResponse(writer, resp, "分帐明细单", request);
        } catch (IOException e) {
            log.error("导出失败", e);
            return Result.failed("导出excel系统异常!");
        }
        return Result.ok("导出成功!");
    }
}

2、avue前端代码

//导出Excel
            handleExcel(page,params) {
                //debugger
                if (params == undefined) {
                    params = this.searchParam;
                }
                this.listLoading = false;
                const type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';  //这里和后端工具类中指定的type保持一致。
                exportDetail(Object.assign({
                    current: page.currentPage,
                    size: page.pageSize
                }, params)).then(res => {
                    //根据传输过来的数据的type类型来判断是否是成功的excel表格，如果没有成功是application/json格式的type
                    //如果成功就将后端传过来的表格进行下载
                    if(res.data.type == type){
                        const fileName = '分账明细单.xls';
                        if ('download' in document.createElement('a')) { // 非IE下载
                            const blob = new Blob([res.data], {type: type});
                            const elink = document.createElement('a');
                            elink.download = fileName;
                            elink.style.display = 'none';
                            elink.href = URL.createObjectURL(blob);
                            document.body.appendChild(elink);
                            elink.click();
                            URL.revokeObjectURL(elink.href); // 释放URL 对象
                            document.body.removeChild(elink);
                        }
                        //如果不是excel的type类型就是json格式的，也就是导出失败了，这时候再将之前设置好的blob格式转换成json格式，并输出错误信息
                    }else {
                            let reader = new FileReader();
                            reader.onload = e =>{
                                let readerres = reader.result;
                                var parseObj = JSON.parse(readerres);
                                console.log(parseObj); //将后端传过来的错误信息进行提示
                                this.$message({
                                    type: 'error',
                                    message: parseObj.message
                                })
                            }
                            reader.readAsText(res.data, 'utf-8')
                    }
                }).catch(() =>{
                        this.listLoading = false;
                        this.$message({
                            message: '导出失败',
                            type: "error",
                            center: true,
                            duration: "2000",
                        });
                });
                this.listLoading=false;  //是否刷新页面

            },

注意：

1. 后端是Excel类型，传到前端进行下载的时候，前端需要将后端方法设置为blob类型，否则就会乱码

export function exportDetail(query){
    return request({
        url: '/tope-pay-web/splitExport/list',
        method: 'get',
        params:query,
        responseType: 'blob',  //这里一定要设置blob类型，否则会乱码
    })
}

2. 如果后端传过来的数据类型不是application/vnd.openxmlformats-officedocument.spreadsheetml.sheet类型，（这个类型需要和后端保持一致），那就证明上传Excel表格没有成功，就会使application/json格式的，这时候避免将错误信息放到Excel继续进行下载，就需要将类型从blob类型转成json类型。

let reader = new FileReader();
                            reader.onload = e =>{
                                let readerres = reader.result;
                                var parseObj = JSON.parse(readerres);
                                console.log(parseObj); //将后端传过来的错误信息进行提示
                                this.$message({
                                    type: 'error',
                                    message: parseObj.message
                                })
                            }
                            reader.readAsText(res.data, 'utf-8')