一、导入相关依赖
<!--excel导出-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
二、BaseEasyExcelExport工具类
BaseEasyExcelExport类为抽象父类位于ape-common-tool模块下，业务模块使用需要继承父类，并重写抽象方法实现定制化。
package com.ssm.tool.excel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.ssm.tool.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.*;
/**
* easyExcel数据导出模板
*/
@Slf4j
public abstract class BaseEasyExcelExport<T> {
/**
* 导出到excel，支持大数量的分批写入
* @param fileName 文件名称
* @param queryCondition 查询条件
*/
protected void exportExcel(String fileName, Map<String, Object> queryCondition) {
HttpServletResponse response = SpringContextUtil.getHttpServletResponse();
//根据条件查询总记录
Long totalCount = dataTotalCount(queryCondition);
//每一个Sheet存放的记录数
Long sheetDataRows = eachSheetTotalCount();
//每次写入的数据量
Long writeDataRows = eachTimesWriteSheetTotalCount();
if (totalCount < sheetDataRows) {
sheetDataRows = totalCount; //总记录小于sheet页容量，就把当前sheet页容量当做做那个记录数即可
}
if (sheetDataRows < writeDataRows) { //如果sheet页容量小于每次写的数量，那么每次编写的数量就为sheet页容量
writeDataRows = sheetDataRows;
}
doExport(response, fileName, queryCondition, totalCount, sheetDataRows, writeDataRows);
}
/**
* 导出到excel
* @param response
* @param fileName 文件名
* @param queryCondition 查询条件
* @param totalCount 总记录数
* @param sheetDataRows sheet页容量
* @param writeDataRows 每次编写的数量
*/
private void doExport(HttpServletResponse response, String fileName, Map<String, Object> queryCondition,
Long totalCount, Long sheetDataRows, Long writeDataRows) {
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
WriteWorkbook writeWorkbook = new WriteWorkbook();
writeWorkbook.setOutputStream(outputStream);
writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
ExcelWriter writer = new ExcelWriter(writeWorkbook);
WriteTable table = new WriteTable();
table.setHead(getExcelHead());
// 计算需要的Sheet数量
Long sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
// 计算一般情况下每一个Sheet需要写入的次数
Long oneSheetWriteCount = totalCount > sheetDataRows ? sheetDataRows / writeDataRows :
totalCount % writeDataRows > 0 ? totalCount / writeDataRows + 1 : totalCount / writeDataRows;
// 计算最后一个sheet需要写入的次数
Long lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount :
(totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) :
(totalCount / sheetDataRows / writeDataRows + 1));
// 分批查询分次写入
List<List<String>> dataList = new ArrayList<>();
for (int i = 0; i < sheetNum; i++) {
WriteSheet sheet = new WriteSheet();
sheet.setSheetNo(i);
sheet.setSheetName(sheetNum == 1 ? fileName : fileName + i);
for (int j = 0; j < (i != sheetNum - 1 || i == 0 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
dataList.clear();
buildDataList(dataList, queryCondition, j + 1 + oneSheetWriteCount * i, writeDataRows);
writer.write(dataList, sheet, table);
}
}
response.setHeader("Content-Disposition", "attachment;filename="
+ new String((fileName).getBytes("gb2312"),
"ISO-8859-1") + ".xlsx");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
writer.finish();
outputStream.flush();
} catch (Exception e) {
log.error("AbstractEasyExcelExport.exportWithBigData.error:{}", e.getMessage(), e);
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (Exception e) {
log.error("AbstractEasyExcelExport.exportWithBigData.close.error:{}", e.getMessage(), e);
}
}
}
}
/**
* 获取导出的表头
*/
protected abstract List<List<String>> getExcelHead();
/**
* 计算导出数据的总数
*/
protected abstract Long dataTotalCount(Map<String, Object> conditions);
/**
* 每一个sheet存放的数据总数
*/
protected abstract Long eachSheetTotalCount();
/**
* 每次写入sheet的总数
*/
protected abstract Long eachTimesWriteSheetTotalCount();
/**
* 构建每次写入的数据
*/
protected abstract void buildDataList(List<List<String>> resultList, Map<String, Object> queryCondition,
Long pageNo, Long pageSize);
}
三、业务模块继承工具类
package com.ssm.user.excel;
import com.ssm.entity.PageResult;
import com.ssm.tool.excel.BaseEasyExcelExport;
import com.ssm.user.convert.UserConvert;
import com.ssm.user.entity.po.UserPo;
import com.ssm.user.entity.req.UserPageReq;
import com.ssm.user.service.UserService;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import javax.annotation.Resource;
import java.util.*;
@Component
public class UserExcelExport extends BaseEasyExcelExport<UserPo> {
@Resource
private UserService userService;
/**
* 数据导出
*/
public void exportWithBigData(String fileName, Map<String, Object> conditions) {
this.exportExcel(fileName, conditions);
}
/**
* 获取导出的表头
*/
@Override
protected List<List<String>> getExcelHead() {
List<List<String>> head = new ArrayList<>();
//Collections.singletonList创建包含单个元素的不可变列表(不可修改的，即不能添加、删除或修改其中的元素)
head.add(Collections.singletonList("用户编号"));
head.add(Collections.singletonList("用户姓名"));
return head;
}
/**
* 计算导出数据的总数
*/
@Override
protected Long dataTotalCount(Map<String, Object> conditions) {
return userService.queryCount(conditions);
}
/**
* 每一个sheet存放的数据总数
*/
@Override
protected Long eachSheetTotalCount() {
return 5000L;
}
/**
* 每次写入sheet的总数
*/
@Override
protected Long eachTimesWriteSheetTotalCount() {
return 2000L;
}
/**
* 构建每次写入的数据
* resultList 空集合，存放数据(测试只存入id和name)
* queryCondition 查询条件
* pageNo 本次查询的页码
* pageSize 本次查询页容量
*/
@Override
protected void buildDataList(List<List<String>> resultList, Map<String, Object> queryCondition,
Long pageNo, Long pageSize) {
UserPageReq userPageReq = new UserPageReq();
//如果queryCondition不为空，则设置查询条件
userPageReq.setPageIndex(Integer.parseInt(pageNo.toString()));
userPageReq.setPageSize(Integer.parseInt(pageSize.toString()));
//将req转为dto
PageResult<UserPo> userPage = userService.getUserPage(UserConvert.INSTANCE.convertPageReqToPageDto(userPageReq));
//获取userpo对象集合
List<UserPo> records = userPage.getRecords();
if(!CollectionUtils.isEmpty(records)) {
records.forEach(user -> { //测试，只存入id，name
resultList.add(Arrays.asList(user.getId().toString(), user.getName()));
});
}
}
}
四、service层根据查询条件获取结果个数方法
@Override
public Long queryCount(Map<String, Object> conditions) {
UserPo userPo = new UserPo();
//可以将conditions内的元素，赋值到userpo对象上
return userMapper.count(userPo);
}
<select id="count" resultType="java.lang.Long" parameterType="com.ssm.user.entity.po.UserPo">
select count(1) from user
<where>
<if test="id != null">id = #{id}</if>
<if test="name != null and name != ''">and name = #{name}</if>
<if test="age != null">and age = #{age}</if>
<if test="createBy != null and createBy != ''">and create_by = #{createBy}</if>
<if test="createTime != null">and create_time = #{createTime}</if>
<if test="updateBy != null and updateBy != ''">and update_by = #{updateBy}</if>
<if test="updateTime != null">and update_time = #{updateTime}</if>
<if test="deleteFlag != null">and delete_flag = #{deleteFlag}</if>
<if test="version != null">and version = #{version}</if>
</where>
</select>
五、结果实现
package com.ssm.user.controller;
import com.ssm.user.excel.UserExcelExport;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.Map;
@RestController
@Api(value = "用户数据导出")
@RequestMapping("UserExport")
@Slf4j
public class UserExcelExportController {
@Resource
private UserExcelExport userExcelExport;
@ApiOperation(value = "导出excel")
@GetMapping("exportData")
public void exportData() {
//设置查询条件
Map<String, Object> map = new HashMap<>();
userExcelExport.exportWithBigData("用户列表", map);
}
}
