Java 实现前端数据的导出操作

前端

class="export" type="primary" icon="el-icon-download" @click="exportData()">导出
 
 
    exportData() {
        //data 操作data 变成后端需要的格式
        let data = {
            capacityVos: resultVo
          }
        this.$confirm("是否确认导出曲线信息？", "警告", {
        confirmButtonText: "确定",
        cancelButtonText: "取消",
        type: "warning",
      }).then(response => {
        exportExcelData(data).then(res => {
          if (res.code == 200) {
            this.jnpf.downloadFile(res.data.url)  
          }
        })
      })
    }

前端跳后端

// 导出
export function exportExcelData(data) {
  return request({
    url: define.api + '/user/export',
    method: 'post',
    data:data,
  });
}

模板excel

后端接口

    //xAxixAndData 导出的数据
    @ApiOperation("导出Excel")
    @PostMapping("/export")
    public ActionResult export(@RequestBody XAxixAndData xAxixAndData){
        Map map = new HashMap<>();
        List> vos = hisCapacityService.getByOne(xAxixAndData);
        List> names = hisCapacityService.getDataName(xAxixAndData);
        map.put("data", vos);
        map.put("names", names);
 
        //1.获取导出模板地址Curve
        ClassPathResource classPathResource = new ClassPathResource("static/CurveModel.xlsx");
        String path = classPathResource.getPath();
        TemplateExportParams templateExportParams = new TemplateExportParams(path);
        // 2.执行excel导出
        Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams, map);
        //3.获取到第0个sheet
        Sheet sheet = workbook.getSheetAt(0);
        //4.创建标题行 数据有几列--标题合并居中有几列
        List> names1 = (List) map.get("names");
        int num = names1.size();
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        if(ObjectUtil.isNotNull(xAxixAndData.getVos())){
            //风电
            titleCell.setCellValue("风电出力曲线");
        }else if(ObjectUtil.isNotNull(xAxixAndData.getCapacityVos())){
            //光伏
            titleCell.setCellValue("光伏出力曲线");
        }else if(ObjectUtil.isNotNull(xAxixAndData.getCurveVos())){
            //曲线目录
            titleCell.setCellValue(xAxixAndData.getCurveVos().get(0).getName());
        }
        CellRangeAddress range = new CellRangeAddress(
                0, // first row (0-based)
                0, // last row
                0, // first column (0-based)
                num  // last column
        );
        sheet.addMergedRegion(range);
        Font font = workbook.createFont();
        Font font2 = workbook.createFont();
        font.setFontHeightInPoints((short) 14); // 设置字体大小
        font.setBold(true); // 设置加粗
        font2.setBold(true);
        CellStyle style = workbook.createCellStyle();
        CellStyle style2 = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 设置单元格格式为居中
        style2.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);
        style2.setFont(font2);
        titleCell.setCellStyle(style);
        //5.创建列名所在的行
        Row columnRow = sheet.createRow(1);
        Cell columnRowCell = columnRow.createCell(0);
        columnRowCell.setCellValue("时刻");
        columnRowCell.setCellStyle(style2);
        for (int i = 1; i <= names1.size(); i++) {
            Cell columnRowCell1 = columnRow.createCell(i);
            columnRowCell1.setCellValue(names1.get(i-1).get("name"));
            columnRowCell1.setCellStyle(style2);
        }
 
        //6.循环row设置行高自适应
        for(int i = 0;i <= sheet.getLastRowNum();i ++){
            XSSFRow xssfRow = (XSSFRow) sheet.getRow(i);
            CTRow ctRow = xssfRow.getCTRow();
            ctRow.setCustomHeight(false);
        }
        //7.设置列宽
        List> data = (List) map.get("data");
        for (int i = 1; i < data.size(); i++) {
            sheet.setColumnWidth(i,10 * 256);
        }
        //8.重命名第0个sheet名称
        workbook.setSheetName(0, "曲线数据");
        DownloadVO vo = this.creatModelExcel(fileApi.getPath(FileTypeEnum.TEMPORARY), userInfo, workbook);
        return ActionResult.success(vo);
    }

后端--导出表格

    /***
     * 导出表格
     * @Author lixi
     * @date 2023-09-14
     * @return jnpf.base.vo.DownloadVO
     **/
    public DownloadVO creatModelExcel(String path, UserInfo userInfo, Workbook workbook) {
        DownloadVO vo = DownloadVO.builder().build();
        String sheetName =  "名字";
        ExportParams exportParams = new ExportParams(null, sheetName);
        exportParams.setType(ExcelType.XSSF);
        try {
            String name = sheetName + ".xlsx";
            @Cleanup ByteArrayOutputStream output = new ByteArrayOutputStream();
            workbook.write(output);
            byte[] barray = output.toByteArray();
            //将Excel文件二进制转为inputstream对象
            @Cleanup InputStream ins = new ByteArrayInputStream(barray);
            //上传文件
            GetStrategy.getInstance(fileCoreProperties.getDefaultPlatform()).uploadFile(path.toLowerCase(), FileTypeEnum.TEMPORARY, name, ins);
            vo.setName(name);
            vo.setUrl(UploaderUtil.uploaderFile(userInfo.getId() + "#" + name + "#" + "Temporary"));
        } catch (Exception e) {
            log.error("信息导出Excel错误:{}", e);
        }
        return vo;
    }

前端将导出的结果显示在网页上

 exportExcelData(data).then(res => {
          if (res.code == 200) {
//调用this.jnpf.downloadFile方法即可
            this.jnpf.downloadFile(res.data.url)  
          }
 })