基于Hutool和POI的Excel导出
前言
最近有一个需求实现导出excel,我使用的EaxyExcel,相比以前使用过的POI那代码量可简单多了,但是由于公司项目引入了Hutool的工具类库,发现hutool中有这么个模块“hutool-poi”,这个模块对POI中Excel和Word进行了封装,于是…
hutool-poi 针对POI中Excel和Word的封装
pom
hutool-4.x的poi-ooxml 版本需高于 3.17(注意:使用这个版本,自行引入POI库,Hutool默认不引入。)
hutool-5.x的poi-ooxml 版本需高于 4.1.2
<!-- hutool工具类依赖-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.4.3</version>
</dependency>
<!--POI依赖,对office进行操作-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
controller层
@ApiOperation(value = "业务查询(导出)")
@GetMapping("/exportExcel")
public void export(ProjectInfoQuery projectInfoQuery, HttpServletResponse httpServletResponse) throws IOException {
projectInfoQuery.setSize(1);
IPage<AgencyProjectDto> myProjectInfoDtoIPage = projectInfoService.queryProjectPage(projectInfoQuery);
long total = myProjectInfoDtoIPage.getTotal();
if (total == 0) {
throw new GeneralException("查询无数据");
}
int page = 1;
//通过hutool工具创建的excel的writer,默认为xls格式
ExcelWriter writer = ExcelUtil.getWriter();
//自定义excel标题和列名
writer.addHeaderAlias("projectId","办件id");
writer.addHeaderAlias("projectNo","办件编号");
writer.addHeaderAlias("projectName","办件名称");
writer.addHeaderAlias("projectName","办件名称");
do {
projectInfoQuery.setIndex(page);
List<AgencyProjectDto> records = projectInfoService.queryProjectPage(projectInfoQuery).getRecords();
//写出内容,使用默认样式,强制输出标题
writer.write(records,true);
page += 1;
} while ((page - 1) * 1000 < total);
httpServletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
//name是下载对话框的名称,不支持中文,想用中文名称需要进行utf8编码
String excelName = "用户基本信息表";
excelName = URLEncoder.encode(excelName, "utf-8");
httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + excelName +".xls");
//设置返回excel的格式为xlsx
//httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//httpServletResponse.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode("用户信息表","utf-8") + ".xlsx");
ServletOutputStream excelOut = null;
//将excel文件信息写入输出流,返回给调用者
try {
excelOut = httpServletResponse.getOutputStream();
writer.flush(excelOut,true);
} catch (IOException e) {
e.printStackTrace();
}finally {
writer.close();
}
IoUtil.close(excelOut);
}
数据返回实体
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class AgencyProjectDto {
/**
* 办件id
*/
@ApiModelProperty("办件id")
private Long projectId;
/**
* 办件编号
*/
@ApiModelProperty("办件编号")
private String projectNo;
/**
* 办件名称
*/
@ApiModelProperty("办件名称")
private String projectName;
/**
* 到期时间
*/
@ApiModelProperty("过期时间")
private LocalDateTime expireTime;
}
这里附上另一种方案使用EasyExcel分页导出数据