数据导出Excel实战

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第1天，点击查看活动详情

每次都会有一些数据进行导出的操作，可以快速达供用户查看得到具体的图表显示， 因为有些数据需要进行统计， 导出的数据一般是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);
    }


}
```
```