Java使用EasyExcel生成動(dòng)態(tài)表頭和多Sheet數(shù)據(jù)的Excel實(shí)例
Java使用EasyExcel生成動(dòng)態(tài)表頭和多Sheet數(shù)據(jù)的Excel
由于業(yè)務(wù)需求,Excel的表頭需要固定表頭+動(dòng)態(tài)表頭,不同Sheet使用不同的動(dòng)態(tài)表頭進(jìn)行Excel導(dǎo)出,這里還是使用EasyExcel進(jìn)行數(shù)據(jù)導(dǎo)出,對(duì)關(guān)鍵部分代碼會(huì)進(jìn)行釋義:
1、動(dòng)態(tài)表頭+數(shù)據(jù)Excel
String fileName = "設(shè)備臺(tái)賬" + DateUtil.format(new Date(), DatePattern.PURE_DATE_FORMAT); //根據(jù)模版id進(jìn)行分組,不同模板下的數(shù)據(jù),導(dǎo)出為不同Sheet的數(shù)據(jù) Map<Long, DeviceLedgerTemplateEntity> templateMap = deviceTemplateList.stream().collect(Collectors.toMap(DeviceLedgerTemplateEntity::getLedgerTemplateId, v -> v)); int i = 0; response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); fileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build(); for (Map.Entry<Long, List<DeviceLedgerImportVO>> deviceMap : deviceTemplateMap.entrySet()) { Long templateId = deviceMap.getKey(); List<List<String>> headList = new ArrayList<>(); List<List<String>> dataList = new ArrayList<>(); if (templateMap.containsKey(templateId)) { DeviceLedgerTemplateEntity templateEntity = templateMap.get(templateId); List<FormDTO> formList = JSONArray.parseArray(templateEntity.getFormItems(), FormDTO.class); //動(dòng)態(tài)生成多個(gè)Sheet的表頭 getHeadList(headList, formList); List<DeviceLedgerImportVO> deviceList = deviceTemplateMap.get(templateId); //動(dòng)態(tài)填充多個(gè)Sheet的數(shù)據(jù) getDataList(dataList, deviceList); //循環(huán)輸出sheet和表頭以及表格內(nèi)容 WriteSheet sheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(headList).build(); excelWriter.write(dataList, sheet); } i++; } excelWriter.finish();
- getHeadList 動(dòng)態(tài)生成表頭
private void getHeadList(List<List<String>> headList, List<FormDTO> formList) { //生成Excel中表頭 //固定表頭 Field[] fields = DeviceLedgerImportVO.class.getDeclaredFields(); for (Field field : fields) { boolean bool = field.isAnnotationPresent(ExcelProperty.class); if (bool) { String value = Arrays.stream(field.getAnnotation(ExcelProperty.class).value()).findFirst().get(); List<String> filedList = new ArrayList<>(); filedList.add(value); headList.add(filedList); } } //動(dòng)態(tài)表頭 formList.stream().forEach(form -> { List<String> filedList = new ArrayList<>(); filedList.add(form.getLabel()); headList.add(filedList); }); }
- getDataList 動(dòng)態(tài)生出數(shù)據(jù)
private void getDataList(List<List<String>> dataList, List<DeviceLedgerImportVO> deviceList) { //生成Excel中值 deviceList.stream().forEach(device -> { //固定表頭下的數(shù)據(jù) List<String> valueList = new ArrayList<>(); valueList.add(device.getDeviceSn()); valueList.add(device.getDeviceName()); valueList.add(device.getDeviceTypeName()); valueList.add(device.getLocation()); valueList.add(device.getStatus()); //動(dòng)態(tài)表頭下的數(shù)據(jù) List<FormDTO> formList = JSONArray.parseArray(device.getFormItems(), FormDTO.class); if (CollectionUtil.isNotEmpty(formList)) { formList.stream().forEach(form -> valueList.add(form.getValue())); } dataList.add(valueList); }); }
- Sheet1數(shù)據(jù):
- Shee2數(shù)據(jù):
也可以將Excel導(dǎo)出那部分寫到工具類,懶的抽出來了,也懶得寫Demo了,大概邏輯差不多,需要修改headList和dataList也根據(jù)索引導(dǎo)出每個(gè)Sheet的數(shù)據(jù),在此,提供工具類:
注意:需要修改headList和dataList,不可直接使用上面的headList和dataList方法
public static void writeDynamicBatchExcel(HttpServletResponse response, String fileName, String sheetName, List<List<String>> headList, List<List<Object>> dataList) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); fileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build(); //循環(huán)輸出sheet和表頭以及表格內(nèi)容 for (int i = 0; i < dataList.size(); i++) { WriteSheet sheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(Collections.singletonList(headList.get(i))).build(); excelWriter.write(dataList.get(i), sheet); } excelWriter.finish(); }
2、動(dòng)態(tài)表頭Excel
如果只生成動(dòng)態(tài)表頭模版,不需要數(shù)據(jù)的話,getHeadList()不變,僅供參考
public void downloadDeviceLedger(DeviceLedgerSearchDTO param, HttpServletResponse response) throws IOException { //查詢模版 DeviceLedgerTemplateEntity templateEntity = deviceLedgerTemplateService.queryDeviceLedgerTemplate(param.getLedgerTemplateId()); List<FormDTO> formList = JSONArray.parseArray(templateEntity.getFormItems(), FormDTO.class); String fileName = "設(shè)備臺(tái)賬" + DateUtil.format(new Date(), DatePattern.PURE_DATE_FORMAT); List<List<String>> resultList = new ArrayList<>(); getHeadList(resultList, formList); EasyExcelUtils.writeDynamicExcel(response, fileName, "設(shè)備臺(tái)賬", resultList, Collections.EMPTY_LIST); }
EasyExcelUtils.writeDynamicExcel工具類:
public static void writeDynamicExcel(HttpServletResponse response, String fileName, String sheetName, List<List<String>> headList, List<List<Object>> dataList) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); fileName = URLEncoder.encode(fileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream()) // 設(shè)置動(dòng)態(tài)頭 .head(headList) .sheet(sheetName) .doWrite(dataList); }
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
解決HttpServletResponse和HttpServletRequest取值的2個(gè)坑
這篇文章主要介紹了解決HttpServletResponse和HttpServletRequest取值的2個(gè)坑問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12通過圖例了解IDEA引入JQuery實(shí)現(xiàn)步驟
這篇文章主要介紹了IDEA引入JQuery實(shí)現(xiàn)步驟圖解,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-09-09Okhttp在SpringBoot中的應(yīng)用實(shí)戰(zhàn)記錄(太強(qiáng)了)
這篇文章主要給大家介紹了關(guān)于Okhttp在SpringBoot中應(yīng)用實(shí)戰(zhàn)的相關(guān)資料,在Spring Boot中使用OkHttp主要是為了發(fā)送HTTP請(qǐng)求和處理響應(yīng),OkHttp是一個(gè)高效、易用的HTTP客戶端庫,它具有簡潔的API和強(qiáng)大的功能,需要的朋友可以參考下2023-12-12Java?超詳細(xì)講解類的定義方式和對(duì)象的實(shí)例化
Java是一門純面向?qū)ο蟮恼Z言(Object?Oriented?Program,繼承OOP),在面對(duì)對(duì)象的世界里面,一切皆為對(duì)象。面向?qū)ο笫墙鉀Q問題的一種思想,主要依靠對(duì)象之間的交互完成一件事情2022-03-03