Java中EasyPoi導出復雜合並單元格的方法
前言:
上星期做瞭一個Excel的單元格合並,用的是EasyPoi,我之前合並單元格都是原生的,第一次使用EasyPoi合並也不太熟悉,看著網上自己套用,使用後發現比原生的方便些,貢獻一下,也給其他用到合並而且用的是EasyPoi的小夥伴節省下時間。
導出模板:
坐標:
版本號,自己來定,可以去官網查看:EasyPoi官網
<!-- easypoi 導入包 --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>4.0.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>4.0.0</version> </dependency>
實現代碼:
//表頭設置 List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>(); ExcelExportEntity colEntity = new ExcelExportEntity("經銷商", "distributorName"); colEntity.setNeedMerge(true); colEntity.setWidth(20); colList.add(colEntity); colEntity = new ExcelExportEntity("科室", "dept"); colEntity.setNeedMerge(true); colList.add(colEntity); colEntity = new ExcelExportEntity("部門", "region"); colEntity.setNeedMerge(true); colList.add(colEntity); colEntity = new ExcelExportEntity("省份", "province"); colEntity.setNeedMerge(true); colList.add(colEntity); colEntity = new ExcelExportEntity("門店數量", "storeNum"); colEntity.setNeedMerge(true); colEntity.setStatistics(true); colList.add(colEntity); Map<String, Integer> map = DateUtils.getLastDayOfMonthByStr(request.getMonthStr()); Integer dayNum = map.get("dayNum"); for (int i = 1; i <= dayNum; i++) { ExcelExportEntity group_1 = new ExcelExportEntity(i + "日", "day"); List<ExcelExportEntity> exportEntities = new ArrayList<>(); ExcelExportEntity appalyExcel = new ExcelExportEntity("申請數量", "applyNum" + i); appalyExcel.setStatistics(true); exportEntities.add(appalyExcel); ExcelExportEntity adoptExcel = new ExcelExportEntity("通過數量", "adoptNum" + i); adoptExcel.setStatistics(true); exportEntities.add(adoptExcel); group_1.setList(exportEntities); colList.add(group_1); } //文件數據 List<Map<String, Object>> list = new ArrayList<>(); List<StoreNewAddReportVO.DistributorStoreNewAddReportVO> disList = register.getStoreNewAddReportVO().getDistributorStoreNewAddReportVOList(); int size = disList.size(); for (int i = 0; i < size; i++) { StoreNewAddReportVO.DistributorStoreNewAddReportVO dis = disList.get(i); Map<String, Object> valMap = new HashMap<>(); valMap.put("distributorName", dis.getDistributorName()); valMap.put("dept", dis.getDept()); valMap.put("region", dis.getRegion()); valMap.put("province", dis.getProvince()); valMap.put("storeNum", dis.getStoreNum()); List<StoreNewAddReportVO.dayData> dayDataList = dis.getDayDataList(); Map<String, List<StoreNewAddReportVO.dayData>> collectMap = Maps.newHashMap(); if (CollectionUtils.isNotEmpty(dayDataList)) { collectMap = dayDataList.stream().collect(Collectors.groupingBy(StoreNewAddReportVO.dayData::getDayStr)); } List<Map<String, Object>> list_1 = new ArrayList<>(); Map<String, Object> valMap_1 = new HashMap<>(); for (int j = 1; j <= dayNum; j++) { List<StoreNewAddReportVO.dayData> dayData = collectMap.get(String.valueOf(j)); int applyflag = 0; int adoptflag = 0; if (CollectionUtils.isNotEmpty(dayData)) { applyflag = dayData.get(0).getApplyNum(); adoptflag = dayData.get(0).getAdoptNum(); } valMap_1.put("applyNum" + j, applyflag); valMap_1.put("adoptNum" + j, adoptflag); } list_1.add(valMap_1); valMap.put("day", list_1); list.add(valMap); } //導出 Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("【" + request.getMonthStr() + "】門店註冊日明細數據", "數據"), colList, list); Sheet sheet = workbook.getSheet("數據"); Row row = sheet.getRow(sheet.getLastRowNum()); Cell cell = row.getCell(0); cell.setCellValue("總計"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 Font font = workbook.createFont(); font.setFontHeightInPoints((short) 15); font.setFontName("Trebuchet MS"); cellStyle.setFont(font); cell.setCellStyle(cellStyle); CellRangeAddress range_0 = new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 3); sheet.addMergedRegion(range_0); File file = new File("D:\\".concat(UUID.randomUUID().toString().concat(".xls"))); FileOutputStream fileOutputStream = null; try { fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); } catch (Exception e) { log.error("門店註冊日workbook寫入到文件中失敗,錯誤信息:{}", ExceptionUtils.getStackTrace(e)); } finally { if (null != fileOutputStream) { try { fileOutputStream.close(); } catch (IOException e) { //skip } } }
具體的API細節就不介紹瞭可以去官網,關鍵在於ExcelExportEntity 這個類,它是以map形式展現的,創建的時候設置key,設置value的根據key進行設置,上面一些StoreNewAddReportVO還有其他是我的業務類, 到時候可以替換掉。
到此這篇關於Java中EasyPoi導出復雜合並單元格的方法的文章就介紹到這瞭,更多相關Java EasyPoi導出單元格內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Java中EasyPoi多sheet導出功能實現
- Java中Easypoi實現excel多sheet表導入導出功能
- Java操作Excel文件解析與讀寫方法詳解
- SpringBoot+EasyPoi實現excel導出功能
- SpringBoot+Hutool+thymeleaf完成導出Excel的實現方法