基于hutoolt的ExcelWriter导出复杂表格

hutool的ExcelWriter导出复杂表格

1.效果图如下

平常导出请参考官方文档：https://hutool.cn/docs/#/poi/Excel生成-ExcelWriter

Page page = this.getList(new Page(1, 9999), orderQuery);
    List<OrderVO> orderVOList = page.getRecords();
    ExcelWriter writer = ExcelUtil.getWriter();
    List<List<Object>> rows = new LinkedList<>();
    //首先创建表头
    List<String> rowHead = CollUtil.newArrayList("订单编号", "订单类型", "买家名称", "卖家名称", "所属端", "订单总金额", "支付总金额", "订单状态", "订单创建时间"
            ,"商品名称","规格","品牌","商品价格","商品数量","明细商品总金额");
    //写入表头
    writer.writeHeadRow(rowHead);
    // 定义起始行
    AtomicInteger num = new AtomicInteger(1);
    orderVOList.stream().forEach(result->{
     if(result.getDetails().size() == 1 ){
                // 只有一条数据时不合并
                num.set(num.get()+1);
      }else(result.getDetails().size() > 1){
            // 创建合并规则    result.getDetails().size()-1 如果不减去1则会造成数据紊乱，下一条数据会合并到上一条中
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 0, 0, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 1, 1, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 2, 2, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 3, 3, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 4, 4, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 5, 5, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 6, 6, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 7, 7, null, true);
            writer.merge(num.get(), num.get() + result.getDetails().size()-1, 8, 8, null, true);
            // 明细占据的总行数
            num.set(num.get() + result.getDetails().size()+1);
        }
        result.getDetails().stream().forEach(item->{
            List<Object> rowA = CollUtil.newArrayList(
                    result.getOrderNo()
                    , result.getOrderType() == 1?"自费订单":"代付订单"
                    , result.getBuyer()
                    , result.getSeller()
                    , result.getBelongType() == 1 ? "客户端" : "平台端"
                    , result.getTotalAmount()
                    , result.getPayTotalAmount()
                    , OrderStatusEnum.findDescByCode(result.getOrderStatus())
                    , result.getCreateTime()
                    , item.getProductName()
                    , item.getSpecifications()
                    , item.getBrandName()
                    , item.getProductPrice()
                    , item.getProductNum()
                    , item.getTotalAmount()
            );
            rows.add(rowA);
        });
    });
    // 一次性写出内容，使用默认样式，强制输出标题
    writer.write(rows, true);
    //设置宽度自适应
    writer.setColumnWidth(-1, 30);
    //中文编码，没用到，使用中文还是乱码
    String fileName = null;
    try {
        fileName = URLEncoder.encode("客户订单" + new Date().toString(), "UTF-8");
    } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
    }
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
    ServletOutputStream out = null;
    try {
        out = response.getOutputStream();
        writer.flush(out, true);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        // 关闭writer，释放内存
        writer.close();
    }
    //关闭输出Servlet流
    IoUtil.close(out);
}
存
        writer.close();
    }
    //关闭输出Servlet流
    IoUtil.close(out);
}

vue前端

<el-col :span="6" style="margin-bottom:8px">
            <el-button @click="exportList()">导出</el-button>
 </el-col>



 exportList() {
      this.getNowDate()
      this.loading = true
      this.$axios({
        url: '你的后端url',
        method: 'post',
        params: {
          orderNo: this.form.orderNo,
          consumerCompanyName: this.form.consumerCompanyName,
          companyName: this.form.companyName,
          belongType: 1,
          orderType: 2,
          orderWay: -1,
        },
        responseType: 'blob',
      }).then((res) => {
        this.loading = false
        let blob = new Blob([res.data], {
          type: 'application/vnd.ms-excel;charset=utf-8;',
        })
        // 组装a标签
        let elink = document.createElement('a')
        elink.style.display = 'none'
        //下载文件名
        elink.download = '订单表' + this.nowDate
        elink.href = URL.createObjectURL(blob)
        document.body.appendChild(elink)
        elink.click()
        document.body.removeChild(elink)
      })
    },
    //获取年月日时间
    getNowDate() {
      var date = new Date()
      this.year = date.getFullYear() //年
      this.month = date.getMonth() + 1 //月
      this.strDate = date.getDate() //日
      if (this.month >= 1 && this.month <= 9) {
        this.month = '0' + this.month
      }
      if (this.strDate >= 0 && this.strDate <= 9) {
        this.strDate = '0' + this.strDate
      }
      this.nowDate =
        this.year +
        '-' +
        this.month +
        '-' +
        this.strDate +
        Math.floor(Math.random() * 100)
    },