数据导出 Excel 实战

2022-10-13
天津
本文字数：2008 字
阅读完需：约 7 分钟

每次都会有一些数据进行导出的操作，可以快速达供用户查看得到具体的图表显示，因为有些数据需要进行统计，导出的数据一般是 Excel 的类型，这里我们从接口设计到代码实战一条龙服务：

这里我们使用的是 hutool 的 POI 进行导出，我们直接上数据实战导出；

pom.xml 引用


<dependency>    <groupId>com.xiaoleilu</groupId>    <artifactId>hutool-all</artifactId>    <version>3.3.0</version></dependency>

复制代码

1. 控制层数据：

@ApiOperation("数据导出")@Log(operationType = Constants.LogOptEnum.EXPORT)@PostMapping("/exportData")public Result exportData(@Valid @RequestBody  Data param, HttpServletResponse response) {    List<DataVo> DataList = dataService.exportData(param);    boolean exportExcel = exportExcel(data, response);    if (exportExcel) {        return Result.ok("当前数据导出成功");    } else {        return Result.fail("当前数据导出失败");    }
}

复制代码

2.service 的实现

获取要导出的数据然后将数据导出

   /**     * 创建Excel     *     * @return     */    private boolean exportExcel(List<DataVo> dataList, HttpServletResponse response) {        ```BigExcelWriter writer = (BigExcelWriter) ExcelUtil.getBigWriter();//增加标题样式StyleSet style = writer.getStyleSet();CellStyle cellStyle = style.getHeadCellStyle();//-信息Map<String, List<DataVo>> collect = dataList.stream().collect(Collectors.groupingBy(DataVo::getName));boolean flag = true;Set<String> keySet = collect.keySet();List<String> collNameList = new ArrayList(keySet);for (int i = 0; i < collNameList.size(); i++) {    //创建的sheet表    String collName = collNameList.get(i);    List<DataVo> predictList = collect.get(collName);    //解决新增Excel表默认新增sheet1的工作表的问题    if (i == 0 && flag && StrUtil.isNotBlank(collName)) {        writer.renameSheet(collName);        flag = false;        for (DataVo n : dataList) {
            CollUtil.newArrayList(n);            writer.getStyleSet().setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER); //水平左对齐，垂直中间对齐            writer.setColumnWidth(0, 30); //第1列30px宽            writer.setColumnWidth(1, 30);            writer.setColumnWidth(2, 30);            writer.setColumnWidth(3, 30);            writer.setColumnWidth(4, 30);            writer.setColumnWidth(5, 30);            writer.setColumnWidth(6, 30);            writer.setColumnWidth(7, 30);            writer.setColumnWidth(8, 30);            writer.setColumnWidth(9, 30);
        }        //导出列名        extracted(writer);        writer.write(dataList, true);        continue;    }

    writer.setSheet(i - 1);    writer.setSheet(collName);    for (DataVo n : dataList) {        CollUtil.newArrayList(n);        writer.getStyleSet().setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER); //水平左对齐，垂直中间对齐        writer.setColumnWidth(0, 30); //第1列30px宽        writer.setColumnWidth(1, 30);        writer.setColumnWidth(2, 30);        writer.setColumnWidth(3, 30);        writer.setColumnWidth(4, 30);        writer.setColumnWidth(5, 30);        writer.setColumnWidth(6, 30);        writer.setColumnWidth(7, 30);        writer.setColumnWidth(8, 30);        writer.setColumnWidth(9, 30);    }    //导出列名    extracted(writer);    writer.write(dataList, true);
}

//out为OutputStream，需要写出到的目标流response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=test.xlsx");this.write(writer, response);return true;}

复制代码

列表导出

private void extracted(ExcelWriter writer) {    writer.addHeaderAlias("data", "日期");     、、、、、    writer.setOnlyAlias(true);}

复制代码

关闭数据流

//关闭流
@param write
@param response*/
private void write(ExcelWriter write, HttpServletResponse response) {OutputStream out = null;try {    out = response.getOutputStream();    write.flush(out);    write.close();    IoUtil.close(out);} catch (IOException e) {    log.error("数据导出异常", e.getMessage());} finally {    IoUtil.close(out);}