【EasyExcel】封裝一個(gè)分頁(yè)寫數(shù)據(jù)的通用方法
需求：通過(guò)elasticsearch查詢出來(lái)一次性寫，在大數(shù)據(jù)量時(shí)存在OOM的隱患分頁(yè)查詢、分批次寫數(shù)據(jù)，避免導(dǎo)出大數(shù)據(jù)量時(shí)內(nèi)存消耗陡增基于elasticsearch分頁(yè)查詢；mybatis-puls同理
前言
在上個(gè)博客中解決了線上導(dǎo)出字體依賴的問(wèn)題，由于涉及的導(dǎo)出模塊較多，因?yàn)榇蛩惴庋b一個(gè)方法做通用導(dǎo)出。
一、所需依賴
1、easyexcel mavn 依賴文件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
<optional>true</optional>
</dependency>
二、源碼實(shí)現(xiàn)
1、依賴的枚舉類:ExcelPageEnum(自定義)
ExcelPageEnum：文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-470113.html
/**
* @className: ExcelPageEnum
* @description: 存儲(chǔ) XLS與XLSX每次最大寫入與sheet最大Row的枚舉
* @author: 云野
* @date: 2022/8/12
*/
public enum ExcelPageEnum {
/**
*
* default export xlsx, page size 10000, sheet max row 1000000
* @author: 云野
* @date: 2022/8/12
*/
XLS(10_000, 60_000),
XLSX(10_000, 1_000_000);
private int pageSize;
private int sheetMaxRow;
ExcelPageEnum(int pageSize, int sheetMaxRow) {
this.pageSize = pageSize;
this.sheetMaxRow = sheetMaxRow;
}
public int getPageSize() {
return pageSize;
}
public int getSheetMaxRow() {
return sheetMaxRow;
}
}
2、依賴的核心接口類:PageQueryService(自定義)
/**
* @className: PageQueryService
* @description: 實(shí)現(xiàn)通用的核心接口
* @author: 云野
* @date: 2022/8/12
*/
@FunctionalInterface
public interface PageQueryService<E> {
/**
* 帶條件分頁(yè)查詢所有數(shù)據(jù)
*
* @return Result<List>
*/
List<E> findByPage(Integer current, Integer size);
}
3、最終調(diào)用工具類: EasyExcelUtils
/**
* 通用導(dǎo)出方法
*
* @className: EasyExcelUtils
* @author: 云野
* @date: 2022/8/12
*/
@Slf4j
@Component
public class EasyExcelUtils extends EasyExcel {
public static void pageWrite(
ExcelWriter excelWriter,
String sheetName,
Integer totalCount,
HttpServletResponse response,
PageQueryService pageQueryService) {
// default export xlsx, page size 10000, sheet max row 1000000
int pageSize = ExcelPageEnum.XLSX.getPageSize();
int sheetMaxRow = ExcelPageEnum.XLSX.getSheetMaxRow();
ExcelTypeEnum excelType = excelWriter.writeContext().writeWorkbookHolder().getExcelType();
boolean isXls = excelType != null && ExcelTypeEnum.XLS.getValue().equals(excelType.getValue());
if (isXls) {
pageSize = ExcelPageEnum.XLS.getPageSize();
sheetMaxRow = ExcelPageEnum.XLS.getSheetMaxRow();
}
try {
// 下方使用了用戶自己選擇文件保存路徑的方式，所以需要配請(qǐng)求參數(shù)，如果使用固定路徑可忽略此代碼
String filename = URLEncoder.encode(sheetName + ".xlsx", "UTF-8");
response.setCharacterEncoding("UTF-8");
// 設(shè)定輸出文件頭
response.setHeader("Content-disposition", "attachment; filename=" + filename);
// 定義輸出類型
response.setContentType("application/x-xls");
// 這里其實(shí)就是把上面的方法分開(kāi)寫，寫入同一個(gè)sheet
WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetName).build();
// compute page count, sheet count
long pageCount = (totalCount - 1) / pageSize + 1;
long sheetCount = (totalCount - 1) / sheetMaxRow + 1;
int currentPage = 0;
// page write data
WriteSheet sheet = null;
for (int i = 0; i < sheetCount; i++) {
Pageable pageable = new PageRequest(i, pageSize);
sheet = EasyExcelFactory.writerSheet(i, sheetName + i).build();
for (int j = 0; j < (sheetMaxRow / pageSize); j++) {
excelWriter.write(pageQueryService.findByPage(currentPage, pageSize), sheet);
currentPage++;
if (currentPage >= pageCount) {
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
}
三、代碼調(diào)用方式
1、Controller層代碼
@Slf4j
@Api(tags = "后臺(tái)--貨源服務(wù)API")
@RestController
@RequestMapping("/api/admin")
public class AdminCargoOwnerSupplyResource {
@Autowired private HttpServletResponse response;
@Autowired private CargoOwnerSupplyService cargoOwnerSupplyService;
/**
* @author: 云野 @Description: 導(dǎo)出貨源Excel
* @date: 2022/8/12
* @param: [queryRequest]查詢條件對(duì)象
*/
@ApiOperation(value = "導(dǎo)出貨源", httpMethod = "GET")
@GetMapping(value = "/url")
public void exportExcel(AdminCargoOwnerSupplyQueryRequest queryRequest) {
ServletOutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
} catch (IOException e) {
}
// build excel writer ， CargoOwnerSupplyExportExcel 為自定義的導(dǎo)出類
// excelType：導(dǎo)出類型，我這邊導(dǎo)出的是XLSX文件
ExcelWriter excelWriter =
EasyExcelFactory.write(outputStream, CargoOwnerSupplyExportExcel.class)
.excelType(ExcelTypeEnum.XLSX)
.build();
// page write ； “CargoOwnerSupply”為寫出的文件名字
// cargoOwnerSupplyService.countOfQueryRequest(queryRequest) 是調(diào)用的方法，查詢需要導(dǎo)出多少數(shù)據(jù)
EasyExcelUtils.pageWrite(
excelWriter,
"CargoOwnerSupply",
cargoOwnerSupplyService.countOfQueryRequest(queryRequest),
response,
(currentPage, pageSize) ->
cargoOwnerSupplyService.exportExcel(
queryRequest, new PageRequest(currentPage, pageSize)));
}
}
2、ExportExcel導(dǎo)出層實(shí)體(自定義，也可以是直接的entity實(shí)體)
/**
* @Author 云野 @Description: 導(dǎo)出Excel后臺(tái)貨源 @Date 2022/8/12
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class CargoOwnerSupplyExportExcel {
/** 賬號(hào) */
@ExcelProperty(value = "賬號(hào)", index = 0)
private String account;
/** 聯(lián)系人 */
@ExcelProperty(value = "聯(lián)系人", index = 1)
private String contactPerson;
/** 發(fā)布時(shí)間 */
@ExcelProperty(value = "發(fā)布時(shí)間", index = 2)
private String createTime;
/** 貨物名稱 */
@ExcelProperty(value = "貨物名稱", index = 4)
private String goodsName;
/** 發(fā)貨量 */
@ExcelProperty(value = "發(fā)貨量", index = 5)
private Integer deliveryMount;
}
3、Service接口層
/** @Author 云野 @Description: @Date 2022/8/12 */
public interface CargoOwnerSupplyService {
/** 根據(jù)條件查詢貨源數(shù)量 */
Integer countOfQueryRequest(AdminCargoOwnerSupplyQueryRequest request);
/**
* 導(dǎo)出Excel
*
* @param request
* @param pageable
*/
List<CargoOwnerSupplyExportExcel> exportExcel(
AdminCargoOwnerSupplyQueryRequest request, Pageable pageable);
}
4、Service接口實(shí)現(xiàn)層
/** @Author 云野 @Description: @Date 2022/8/12 */
public interface CargoOwnerSupplyServiceImpl {
/** 根據(jù)條件查詢貨源數(shù)量 */
@Override
public Integer countOfQueryRequest(AdminCargoOwnerSupplyQueryRequest request) {
// 這是是我Elasticsearch的查詢數(shù)量的方法，如果是sql的話，用自己的api
return Integer.valueOf(
String.valueOf(
elasticsearchTemplate.count(
new NativeSearchQueryBuilder().withQuery(queryConditions(request)).build(),
CargoOwnerSupplyDTO.class)));
}
/**
*
* 因?yàn)槲覜](méi)有直接使用數(shù)據(jù)庫(kù)查詢出來(lái)的entity實(shí)體對(duì)象，而是再封裝定義了一層ExportExcel實(shí)體對(duì)象，因此我這邊需要準(zhǔn)備這個(gè)賦值的方式，如果看官們直接用的數(shù)據(jù)庫(kù)查詢出來(lái)的 entity實(shí)體，那么就不需要賦值的過(guò)程
* @author: 云野
* @Description: 導(dǎo)出Excel
* @date: 2022/8/12
* @param: [request(查詢條件), pageable(分頁(yè)查詢)]
* @return: void
*/
@Override
public List<CargoOwnerSupplyExportExcel> exportExcel(
AdminCargoOwnerSupplyQueryRequest request, Pageable pageable) {
// 通過(guò)條件從數(shù)據(jù)庫(kù)查詢所有，返回一個(gè)List
List<CargoOwnerSupplyVo> cargoOwnerSupplyList =
cargoOwnerSupplyWebMapper.dtos2vos(adminFindAll(request, pageable).getContent());
// 初始化List
ArrayList<CargoOwnerSupplyExportExcel> list = new ArrayList<>();
// 遍歷查詢出來(lái)的 List<CargoOwnerSupplyVo> , 然后進(jìn)行賦值到 ArrayList<CargoOwnerSupplyExportExcel>的操作
cargoOwnerSupplyList.stream()
.forEach(
vo -> {
// 基于@Builder注解進(jìn)行賦值
CargoOwnerSupplyExportExcel excel = CargoOwnerSupplyExportExcel
.builder()
.account(vo.getContactPhone())
.contactPerson(vo.getContactPerson())
.createTime(vo.getCreateTime())
.goodsName(vo.getGoodsName())
.deliveryMount(vo.getDeliveryMount()).build();
list.add(excel);
});
return list;
}
}
三、總結(jié)
看過(guò)我以前博客的小伙伴應(yīng)該知道，我這邊所有的案例都是開(kāi)發(fā)時(shí)碰到的，同時(shí)我這邊的Data層用的是Elasticsearch+Spring Data Jpa ，因?yàn)楣居玫木褪沁@個(gè)呀，所以案例中出現(xiàn) 的查詢方案案例，如果使用Sql的小伙伴不需要進(jìn)行深究，只需要明白一點(diǎn)就行，這種時(shí)候可以直接使用Sql中同樣的查詢即可。看到這里如果幫助到了你，給個(gè)點(diǎn)贊，謝謝啦！文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-470113.html
到了這里，關(guān)于【EasyExcel】封裝一個(gè)分頁(yè)寫數(shù)據(jù)的通用方法(保姆級(jí))，繼上一篇easyExcel導(dǎo)出上線后的優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
