VUE+java实现：Excel导出功能前后端代码

在很多时候，我们页面的表格需要我们导出，

1：前端代码

（1）：前端导出方法：

const fileDownload = {
  // 获取数据列表
  exportExcel(reqUrl, params, downloadFileName) {
    request({
      baseURL: baseUrl,
      url: reqUrl,
      method: 'get',
      responseType: 'arraybuffer', //必须要加上，转为blob类型
      params: params
    }).then(res => {
      const a = document.createElement('a')
      const blob = new Blob([res], {type: 'application/octet-stream'})
      const objectUrl = URL.createObjectURL(blob)
      a.setAttribute('href', objectUrl)
      a.setAttribute('download', downloadFileName)
      a.click()
    })
  }
}

export {fileDownload}

我们在这边写一个方法，等我们后面用的时候直接调用这个方法就可以了；

（2）：我们需要导出的时候，直接引用：

//导出Excel
      exportHandle() {
        let params = {
          startTime:this.dataForm.shiftData[0],
          endTime:this.dataForm.shiftData[1],
          groupCode: this.dataForm.groupCode,
          shiftCode:this.dataForm.shiftCode,
        }
        console.log(params)
        console.log(params.startTime);
        console.log(params.endTime);
        console.log(params.shiftCode);
        console.log(params.groupCode);
        fileDownload.exportExcel('/api/CostAuxiliarySavingsController/exportExcel', params, '原料日节超表.xls')
      },

（3）：表格：

 

 

2：后端代码：

@RequestMapping("/exportExcel")
    @ResponseBody
    public void exportExcel(@RequestParam Map<String,Object> params, HttpServletResponse response) {
        String startTime = MapUtil.getStr(params, "startTime");
        String endTime = MapUtil.getStr(params, "endTime");
        String groupCode = MapUtil.getStr(params, "groupCode");
        String shiftCode = MapUtil.getStr(params, "shiftCode");
        QueryWrapper<CostAuxiliarySavingEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge(!StringUtil.isEmpty(startTime),CostAuxiliarySavingEntity.COL_SHIFT_DATE,startTime);
        queryWrapper.le(!StringUtil.isEmpty(endTime),CostAuxiliarySavingEntity.COL_SHIFT_DATE,endTime);
        queryWrapper.eq(!StringUtil.isEmpty(groupCode),CostAuxiliarySavingEntity.COL_GROUP_CODE,groupCode);
        queryWrapper.eq(!StringUtil.isEmpty(shiftCode),CostAuxiliarySavingEntity.COL_SHIFT_CODE,shiftCode);
        List<CostAuxiliarySavingEntity> list = costAuxiliarySavingService.list(queryWrapper);
        ExcelWriter excelWriter = ExcelUtil.getWriter();
        List<List<Object>> lists = new ArrayList<>();


        List<Object> titleRow = new ArrayList<>();
        titleRow.add("日期");
        titleRow.add("班次");
        titleRow.add("产品全名");
        titleRow.add("线数");
        titleRow.add("物料种类");
        titleRow.add("入库包数");
        titleRow.add("入库件数");
        titleRow.add("包转吨折吨系数");
        titleRow.add("入库产量(T)");
        titleRow.add("单吨理论耗用量");
        titleRow.add("理论耗用量");
        titleRow.add("损耗率");
        titleRow.add("实际耗用量");
        titleRow.add("节超量");
        titleRow.add("单价(元)");
        titleRow.add("标准金额");
        titleRow.add("实际金额");
        titleRow.add("节超金额");

        excelWriter.writeRow(titleRow);
        excelWriter.merge(0, 1, 0, 0, "日期", false);
        excelWriter.merge(0, 1, 1, 1, "班次", false);
        excelWriter.merge(0, 1, 2, 2, "产品全名", false);
        excelWriter.merge(0, 1, 3, 3, "线数", false);
        excelWriter.merge(0, 1, 4, 4, "物料种类", false);
        excelWriter.merge(0, 1, 5, 5, "入库包数", false);
        excelWriter.merge(0, 1, 6, 6, "入库件数", false);
        excelWriter.merge(0, 1, 7, 7, "包转吨折吨系数", false);
        excelWriter.merge(0, 1, 8, 8, "入库产量(T)", false);
        excelWriter.merge(0, 1, 9, 9, "单吨理论耗用量", false);
        excelWriter.merge(0, 1, 10, 10, "理论耗用量", false);
        excelWriter.merge(0, 1, 11, 11, "损耗率", false);
        excelWriter.merge(0, 1, 12, 12, "实际耗用量", false);
        excelWriter.merge(0, 1, 13, 13, "节超量", false);
        excelWriter.merge(0, 1, 14, 14, "单价(元)", false);
        excelWriter.merge(0, 1, 15, 15, "标准金额", false);
        excelWriter.merge(0, 1, 16, 16, "实际金额", false);
        excelWriter.merge(0, 1, 17, 17, "节超金额", false);
        //将数据绑定。。。
        if (CollectionUtil.isNotEmpty(list)) {
            for (CostAuxiliarySavingEntity costMaterialDailyEntity : list) {
                List<Object> rows = new ArrayList<>();
                rows.add(costMaterialDailyEntity.getShiftDate());
                rows.add(costMaterialDailyEntity.getShiftGroupName());
                rows.add(costMaterialDailyEntity.getProductName());//
                rows.add(costMaterialDailyEntity.getLine());
                rows.add(costMaterialDailyEntity.getMaterialType());
                rows.add(costMaterialDailyEntity.getInboundPackages());//
                rows.add(costMaterialDailyEntity.getInboundPieces());
                rows.add(costMaterialDailyEntity.getTonnageFactor());
                rows.add(costMaterialDailyEntity.getOutput());
                rows.add(costMaterialDailyEntity.getTheorecticalPerTon());//封箱胶
                rows.add(costMaterialDailyEntity.getConsumption());
                rows.add(costMaterialDailyEntity.getLossRate());
                rows.add(costMaterialDailyEntity.getActualConsumption());
                rows.add(costMaterialDailyEntity.getSavingExcess());
                rows.add(costMaterialDailyEntity.getPrice());
                rows.add(costMaterialDailyEntity.getStandardAmount());
                rows.add(costMaterialDailyEntity.getActualAmount());
                rows.add(costMaterialDailyEntity.getExceeingAmount());
                /**
                 * 注释：我们在前面添加数据的时候，只需要把我们需要的数据添加到里面去，
                 * 我们不需要的数据我们就不需要添加进去了，
                 * 不然在后面是没有表头的，在后面只有数据，记录：
                 */
                lists.add(rows);
            }
        }
        excelWriter.write(lists);
        ServletOutputStream out;
        try {
            String filename = URLEncoder.encode("辅料日节超表", "utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
            out = response.getOutputStream();
            excelWriter.flush(out, true);
            // 关闭writer，释放内存
            excelWriter.close();
            IoUtil.close(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }