失眠网 > easypoi 模板导入导出合并excel单元格功能

easypoi 模板导入导出合并excel单元格功能

时间：2020-11-21 03:21:34

easypoi 模板导入、导出合并单元格功能

参考：

hutool 导出复杂excel(动态合并行和列)

java使用poi读取跨行跨列excel

springboot集成easypoi并使用其模板导出功能和遇到的坑

Easypoi Excel模板功能简要说明

easypoi 模板导出兼容合并单元格功能

ExcelUtil

package com.yymt.utils;import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.TemplateExportParams;import cn.hutool.core.convert.Convert;import cn.hutool.core.io.FileUtil;import cn.hutool.core.util.ObjectUtil;import cn.hutool.core.util.StrUtil;import cn.hutool.core.util.ZipUtil;import monConstants;import com.yymt.exception.RRException;import com.yymt.exception.ResultEnum;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.text.NumberFormat;import java.text.SimpleDateFormat;import java.time.LocalDate;import java.time.LocalDateTime;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;/*** @Author：xielin* @Description: 导入（原始方式，支持读取合并）、导出（模板方式含合并）工具类* @Date：/1/11 14:33* @Version: 1.0*/public class ExcelUtil {private Logger logger = LoggerFactory.getLogger(this.getClass());/*** 创建工作簿** @param filePath 文件路径* @return 工作簿* @throws IOException IO异常*/public static Workbook createWorkBook(String filePath) throws IOException {Assert.isTrue(!FileUtil.exist(filePath), ResultEnum.FILE_NOT_FOUND.getMsg());//final Workbook workBook = new HSSFWorkbook(new FileInputStream(new File("test.xls")));if (filePath.toLowerCase().endsWith("xls")) {Workbook book = new HSSFWorkbook(new FileInputStream(new File(filePath)));return book;}if (filePath.toLowerCase().endsWith("xlsx")) {Workbook book = new XSSFWorkbook(new FileInputStream(new File(filePath)));return book;} else {throw new RRException("excel格式不正确");}}/*** 获取表格内容** @param sheet sheet对象* @param mergedRegionMap 合并单元格信息的Map* @param row 行对象* @param rowIndex 行索引* @param columnIndex列索引* @return 获取表格内容*/public static String getCellValue(Sheet sheet, Map<String, Integer[]> mergedRegionMap, Row row, int rowIndex, int columnIndex) {//将列对象的行号和列号+下划线组成key去hashmap中查询，不为空说明当前的cell是合并单元列String value = "";Integer[] firstRowNumberAndCellNumber = mergedRegionMap.get(rowIndex + "_" + columnIndex);//如果是合并单元列，就取合并单元格的首行和首列所在位置读数据，否则就是直接读数据if (firstRowNumberAndCellNumber != null) {Row rowTmp = sheet.getRow(firstRowNumberAndCellNumber[0]);Cell cellTmp = rowTmp.getCell(firstRowNumberAndCellNumber[1]);value = parseCell(cellTmp);} else {value = parseCell(row.getCell(columnIndex));}if ("/".equals(value)) {value = "";}return value;}/*** 将存在合并单元格的列记录入put进hashmap并返回** @param sheet sheet对象* @return*/public static Map<String, Integer[]> getMergedRegionMap(Sheet sheet) {Map<String, Integer[]> result = new HashMap<String, Integer[]>();//获取excel中的所有合并单元格信息int sheetMergeCount = sheet.getNumMergedRegions();//遍历处理for (int i = 0; i < sheetMergeCount; i++) {//拿到每个合并单元格，开始行，结束行，开始列，结束列CellRangeAddress range = sheet.getMergedRegion(i);int firstColumn = range.getFirstColumn();int lastColumn = range.getLastColumn();int firstRow = range.getFirstRow();int lastRow = range.getLastRow();//构造一个开始行和开始列组成的数组Integer[] firstRowNumberAndCellNumber = new Integer[]{firstRow, firstColumn};//遍历，将单元格中的所有行和所有列处理成由行号和下划线和列号组成的key，然后放在hashmap中for (int currentRowNumber = firstRow; currentRowNumber <= lastRow; currentRowNumber++) {for (int currentCellNumber = firstColumn; currentCellNumber <= lastColumn; currentCellNumber++) {result.put(currentRowNumber + "_" + currentCellNumber, firstRowNumberAndCellNumber);}}}return result;}/*** 解析表格的值** @param cell 单元格对象* @return 单元格的值*/public static String parseCell(Cell cell) {String temp = "";if (ObjectUtil.isEmpty(cell)) {return temp;}if (cell.getCellType() == CellType.NUMERIC) {short format = cell.getCellStyle().getDataFormat();if (HSSFDateUtil.isCellDateFormatted(cell)) {SimpleDateFormat sdf = null;if (format == 20 || format == 32) {sdf = new SimpleDateFormat("HH:mm");temp = sdf.format(cell.getDateCellValue());} else if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176) {// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)sdf = new SimpleDateFormat("yyyy-MM-dd");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);temp = sdf.format(date);} else {sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");temp = sdf.format(cell.getDateCellValue());}} else if (format == 57) {// HSSFDateUtil.isCellDateFormatted(cell) 存在误判SimpleDateFormat sdf = null;// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)sdf = new SimpleDateFormat("yyyy-MM-dd");double value = cell.getNumericCellValue();Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);temp = sdf.format(date);} else {temp = NumberFormat.getInstance().format(cell.getNumericCellValue());}} else if (cell.getCellType() == CellType.STRING) {temp = cell.getStringCellValue();} else if (cell.getCellType() == CellType.FORMULA) {temp = cell.getCellFormula();} else if (cell.getCellType() == CellType.BOOLEAN) {temp = String.valueOf(cell.getBooleanCellValue());}return StrUtil.trimToEmpty(temp);}/*** 根据dateStr转换成LocalDateTime* @param dateStr 日期格式字符串* @return LocalDateTime对象*/public static LocalDateTime getLocalDateTime(String dateStr) {if (StrUtil.isNotBlank(dateStr)) {// dateStr如：9月也需要转成日期，默认是1日if (dateStr.contains("年") && dateStr.contains("月")) {String year = dateStr.substring(0, dateStr.indexOf("年"));String month = dateStr.substring(dateStr.indexOf("年") + 1, dateStr.indexOf("月"));String day = "01";if (dateStr.contains("日")) {day = dateStr.substring(dateStr.indexOf("月") + 1, dateStr.indexOf("日"));}return LocalDate.of(Convert.toInt(year), Convert.toInt(month), Convert.toInt(day)).atStartOfDay();}// dateStr如：/1/16return LocalDate.parse(dateStr).atStartOfDay();}return null;}public static Integer formatYesOrNo(String str) {return "是".equals(str) ? 1 : 0;}/**** @param mapList 要导出的数据数据map集合* @param templateExcelName excel模板名称* @param sheetName sheet名称（默认是excel模板名称）* @param fileName 临时导出的文件名* @return 可访问的文件路径*/public static String handleExport(List<Map<String, Object>> mapList, String templateExcelName,String sheetName,String fileName) {// CommonConstants.TEMP_EXPORT_PATH = "/temp/export/"FileUtil.mkdir(CommonConstants.TEMP_EXPORT_PATH);Map<Integer, Map<String, Object>> sheetMap = new HashMap<>();Map<String, Object> dataMap = new HashMap<>();dataMap.put("mapList", mapList);// 第一个sheetsheetMap.put(0, dataMap);if (StrUtil.isBlank(sheetName)) {sheetName = templateExcelName;}TemplateExportParams params = new TemplateExportParams("static/template/" + templateExcelName + ".xlsx", sheetName);Workbook workbook = ExcelExportUtil.exportExcel(sheetMap, params);String fileAllPath = CommonConstants.TEMP_EXPORT_PATH + fileName;try (FileOutputStream fos = new FileOutputStream(fileAllPath);) {workbook.write(fos);} catch (IOException e) {throw new RRException(e.getLocalizedMessage());}// 压缩文件// compress(httpServletResponse);// File zip = ZipUtil.zip(FileUtil.file(CommonConstants.TEMP_EXPORT_PATH));// FileUtil.del(CommonConstants.TEMP_EXPORT_PATH);// String filePath = DownloadUtil.getFilePath(zip);// FileUtil.del(zip);String filePath = DownloadUtil.getFilePath(new File(fileAllPath));FileUtil.del(fileAllPath);return filePath;}}

DownloadUtil

package com.yymt.utils;import cn.hutool.core.io.IoUtil;import cn.hutool.extra.spring.SpringUtil;import com.yymt.exception.RRException;import com.yymt.modules.system.service.SysUploadFileService;import mons.fileupload.FileItem;import mons.fileupload.disk.DiskFileItemFactory;import org.springframework.http.MediaType;import org.springframework.monsMultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.*;import .URLEncoder;import java.nio.charset.StandardCharsets;import java.util.List;import java.util.Map;public class DownloadUtil {/*** 下载文件名重新编码** @param response 响应对象* @param realFileName 真实文件名* @return*/public static void setAttachmentResponseHeader(HttpServletResponse response, String realFileName) throws UnsupportedEncodingException {String percentEncodedFileName = percentEncode(realFileName);StringBuilder contentDispositionValue = new StringBuilder();contentDispositionValue.append("attachment; filename=").append(percentEncodedFileName).append(";").append("filename*=").append("utf-8''").append(percentEncodedFileName);response.addHeader("Access-Control-Allow-Origin", "*");response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");response.setHeader("Content-disposition", contentDispositionValue.toString());response.setHeader("download-filename", percentEncodedFileName);response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);response.setCharacterEncoding("utf-8");}/*** 百分号编码工具方法** @param s 需要百分号编码的字符串* @return 百分号编码后的字符串*/public static String percentEncode(String s) throws UnsupportedEncodingException {String encode = URLEncoder.encode(s, StandardCharsets.UTF_8.toString());return encode.replaceAll("\\+", "%20");}public static String getFilePath(File file) {FileItem fileItem = new DiskFileItemFactory().createItem("file",MediaType.MULTIPART_FORM_DATA_VALUE,true,file.getName());try (InputStream inputStream = new FileInputStream(file);OutputStream outputStream = fileItem.getOutputStream()) {// 流转换IoUtil.copy(inputStream, outputStream);} catch (Exception e) {throw new IllegalArgumentException("Invalid file:" + e, e);}CommonsMultipartFile multipartFile = new CommonsMultipartFile(fileItem);SysUploadFileService uploadFileService = SpringUtil.getBean(SysUploadFileService.class);R r = uploadFileService.uploadFile(multipartFile, "");if ( (Integer) r.get("code") != 0) {throw new RRException("文件下载失败");}Map<String,String> data = (Map<String,String>) r.get("data");return data.get("filePath");}}

导入的调用示例

@Transactional(rollbackFor = Exception.class)public void importBatch(FilePathParams filePathParams) {// 修改换成真实文件路径String filePath = webUploadBase + filePathParams.getFilePath();Workbook workBook = null;List<SchoolBuildingUseSaveParam> list = new ArrayList<>();// 上一次读取的序号（用户去除重复读取数据）Integer lastNo = null;// 序号是否相同的数据Boolean isSameData;try {workBook = ExcelUtil.createWorkBook(filePath);//获取第一个sheetSheet sheet = workBook.getSheetAt(0);//获取合并单元格信息的hashmapMap<String, Integer[]> mergedRegionMap = ExcelUtil.getMergedRegionMap(sheet);//从excel的第7行索行开始，遍历到最后一行（标题行，直接跳过不读取）for (int i = 6; i < sheet.getLastRowNum(); i++) {int j = 0;isSameData = Boolean.FALSE;// 拿到excel的行对象Row row = sheet.getRow(i);if (row == null) {break;}SchoolBuildingUseSaveParam entity = null;// 序号Integer no = Convert.toInt(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));if (ObjectUtil.isEmpty(no)) {break;}if (Objects.equals(no, lastNo)) {isSameData = Boolean.TRUE;} else {lastNo = no;}if (isSameData) {entity = list.get(list.size() - 1);} else {entity = new SchoolBuildingUseSaveParam();// 校区名称entity.setSchoolName(DicUtil.findCodeByTypeAndValue(DictTypeConstants.CAMPUS_NAME, ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++)));// 建筑物名称entity.setBuildingName(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));// 取得方式entity.setAcquisitionMethod(DicUtil.findCodeByTypeAndValue(DictTypeConstants.SCHOOL_BUILDING_USE_MANAGEMENT_ACQUISITION_METHOD, ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++)));// 取得日期entity.setAcquisitionDate(ExcelUtil.getLocalDateTime(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++)));// 权属人entity.setPropertyOwner(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));// 资产价值（万元）entity.setPropertyValue(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 地上面积（平方米）entity.setOvergroundArea(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 地下面积（平方米）entity.setUndergroundArea(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 地上层数entity.setOvergroundFloors(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 地下层数entity.setUndergroundFloors(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 房屋权属证明entity.setHouseOwnershipCertificate(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));// 发证日期entity.setReleaseDate(ExcelUtil.getLocalDateTime(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++)));// 房屋所有权证号entity.setHouseOwnershipNumber(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));// 权属面积（平方米）entity.setOwnershipArea(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 是否BOT模式(1:是,0：否)entity.setBotFlag(ExcelUtil.formatYesOrNo(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++)));// BOT模式期限entity.setBotDate(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));}// 使用情况的保存（从18列使用单位开始读取）j = 17;// 学校正在自用SchoolBuildingUsageSaveParam schoolBuildingUsageSaveParam1 = new SchoolBuildingUsageSaveParam();schoolBuildingUsageSaveParam1.setUsage(UsageEnum.SCHOOL_PRIVATE_USE.getCode());schoolBuildingUsageSaveParam1.setUseUnit(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));schoolBuildingUsageSaveParam1.setUsePeopleCount(Convert.toInt(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++)));schoolBuildingUsageSaveParam1.setUseArea(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 出租出借SchoolBuildingUsageSaveParam schoolBuildingUsageSaveParam2 = new SchoolBuildingUsageSaveParam();schoolBuildingUsageSaveParam2.setUsage(UsageEnum.LEND_HIRE.getCode());schoolBuildingUsageSaveParam2.setFloorRange(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));schoolBuildingUsageSaveParam2.setUseArea(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));// 暂未有效使用SchoolBuildingUsageSaveParam schoolBuildingUsageSaveParam3 = new SchoolBuildingUsageSaveParam();schoolBuildingUsageSaveParam3.setUsage(UsageEnum.YET_EFFECTIVE_USE.getCode());schoolBuildingUsageSaveParam3.setFloorRange(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));schoolBuildingUsageSaveParam3.setUseArea(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));List<SchoolBuildingUsageSaveParam> schoolBuildingUsageSaveParamList = entity.getSchoolBuildingUsageSaveParamList();if (CollUtil.isEmpty(schoolBuildingUsageSaveParamList)) {schoolBuildingUsageSaveParamList = new ArrayList<>();}if (schoolBuildingUsageSaveParam1.isValidOrNot()) {schoolBuildingUsageSaveParamList.add(schoolBuildingUsageSaveParam1);}if (schoolBuildingUsageSaveParam2.isValidOrNot()) {schoolBuildingUsageSaveParamList.add(schoolBuildingUsageSaveParam2);}if (schoolBuildingUsageSaveParam3.isValidOrNot()) {schoolBuildingUsageSaveParamList.add(schoolBuildingUsageSaveParam3);}entity.setSchoolBuildingUsageSaveParamList(schoolBuildingUsageSaveParamList);// 删除最后的或新增实体if (isSameData) {list.remove(list.size() - 1);} else {// 是否老旧危房entity.setOldHouseFlag(ExcelUtil.formatYesOrNo(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++)));// 备注entity.setRemark(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));}list.add(entity);}if (CollUtil.isNotEmpty(list)) {list.forEach(e -> insertOrUpdateSchoolBuildingUse(e));}} catch (Exception e) {log.error("error", e.getMessage());throw new RRException("数据导入异常");} finally {IoUtil.close(workBook);}}

导出的模板：

导出的调用示例1

@PostMapping("/exportBatch")@ApiOperation(value = "导出")public R exportBatch(@Validated @RequestBody SchoolBuildingUseExportParam schoolBuildingUseExportParam) {return R.ok().put("data", schoolBuildingUseService.exportBatch(schoolBuildingUseExportParam));}

@ApiModel(description = "校舍使用管理导出参数")@Data@NoArgsConstructor@AllArgsConstructorpublic class SchoolBuildingUseExportParam {@ApiModelProperty(value = "部分导出是不能为空")private List<String> idList;@ApiModelProperty(value = "导出类型，1：部分导出，2：全部导出")@NotNull(message = "导出类型不能为空")private Integer exportType;}

public String exportBatch(SchoolBuildingUseExportParam schoolBuildingUseExportParam) {List<String> idList = schoolBuildingUseExportParam.getIdList();List<Long> idLongList = new ArrayList<>();if (schoolBuildingUseExportParam.getExportType() == 1) {Assert.isTrue(ObjectUtil.isEmpty(idList), "请选择要导出的数据");idLongList = idList.stream().map(Long::parseLong).collect(Collectors.toList());}// 查询要导出的数据List<SchoolBuildingUseEntity> schoolBuildingUseEntityList = list(Wrappers.<SchoolBuildingUseEntity>lambdaQuery().in(ObjectUtil.isNotEmpty(idLongList), SchoolBuildingUseEntity::getId, idLongList).orderByDesc(SchoolBuildingUseEntity::getUpdateTime));// Assert.isTrue(CollUtil.isEmpty(schoolBuildingUseEntityList), "暂无数据导出");无数据可以用于下载模板(id传0)// 转换后的AtomicReference<Integer> no = new AtomicReference<>(1);List<Map<String, Object>> mapList = schoolBuildingUseEntityList.stream().map(e -> {SchoolBuildingUseExportVO schoolBuildingUseExportVO = new SchoolBuildingUseExportVO();BeanUtil.copyProperties(e, schoolBuildingUseExportVO);schoolBuildingUseExportVO.setNo(no.getAndSet(no.get() + 1));schoolBuildingUseExportVO.setBotFlagValue(BotFlagEnum.getValueByCode(e.getBotFlag()));schoolBuildingUseExportVO.setOldHouseFlagValue(OldHouseFlagEnum.getValueByCode(e.getOldHouseFlag()));schoolBuildingUseExportVO.setSchoolNameValue(DicUtil.findValueByTypeAndCode(DictTypeConstants.CAMPUS_NAME, e.getSchoolName()));schoolBuildingUseExportVO.setAcquisitionMethodValue(DicUtil.findValueByTypeAndCode(DictTypeConstants.SCHOOL_BUILDING_USE_MANAGEMENT_ACQUISITION_METHOD, e.getAcquisitionMethod()));// 查询使用情况Map<Integer, List<SchoolBuildingUsageEntity>> collectMap = querySchoolBuildingUsageEntityMapList(e.getId());if (ObjectUtil.isNotEmpty(collectMap)) {schoolBuildingUseExportVO.setSchoolPrivateUseList(BeanUtil.copyToList(collectMap.get(UsageEnum.SCHOOL_PRIVATE_USE.getCode()), SchoolBuildingUsageInfoVO.class));schoolBuildingUseExportVO.setLendHireList(BeanUtil.copyToList(collectMap.get(UsageEnum.LEND_HIRE.getCode()), SchoolBuildingUsageInfoVO.class));schoolBuildingUseExportVO.setYetEffectiveUseList(BeanUtil.copyToList(collectMap.get(UsageEnum.YET_EFFECTIVE_USE.getCode()), SchoolBuildingUsageInfoVO.class));// 设置使用情况集合generateSchoolBuildingUsageExportVOList(schoolBuildingUseExportVO);}return BeanUtil.beanToMap(schoolBuildingUseExportVO);}).collect(Collectors.toList());// 临时文件名String fileName = CommonConstants.SCHOOL_BUILDING_USE_TEMPLATE_EXCEL_NAME + StrUtil.UNDERLINE + System.currentTimeMillis() + ".xls";return ExcelUtil.handleExport(mapList, CommonConstants.SCHOOL_BUILDING_USE_TEMPLATE_EXCEL_NAME, null, fileName);}

导出的调用示例2

public String exportPropertyCheckCollect(List<Long> propertyCheckCollectIds,HttpServletResponse httpServletResponse) {TemplateExportParams params = new TemplateExportParams("static/template/资产清查固定资产清查表（含明细、汇总）空表.xls", true,"附1-" + LocalDate.now().getYear() + "年赣南医学院固定资产清查明细表","附2-" + LocalDate.now().getYear() + "年赣南医学院固定资产清查汇总表","附3-" + LocalDate.now().getYear() + "年赣南医学院固定资产清查盘盈明细表","附4-" + LocalDate.now().getYear() + "年赣南医学院固定资产清查盘亏明细表");List<PropertyCheckCollect> propertyCheckCollectList = baseMapper.selectBatchIds(propertyCheckCollectIds);Map<Long, List<PropertyCheckCollect>> listMap = propertyCheckCollectList.stream().collect(Collectors.groupingBy(PropertyCheckCollect::getDeptId));Map<Integer, Map<String, Object>> sheetMap = new HashMap<Integer, Map<String, Object>>();Map<String, Object> map = new HashMap<String, Object>();List<Map<String, Object>> maplist = null;for (Map.Entry<Long, List<PropertyCheckCollect>> listEntry : listMap.entrySet()) {List<PropertyCheckCollect> checkCollectList = listEntry.getValue();for (PropertyCheckCollect propertyCheckCollect : checkCollectList) {int num1 = 0;// 根据盘点id查询盘点资产关联表List<PropertyCheckCorr> checkCorrList = propertyCheckCorrMapper.selectList(Wrappers.<PropertyCheckCorr>lambdaQuery().eq(PropertyCheckCorr::getCheckId, propertyCheckCollect.getCheckId()));PropertyExportVO propertyExportVO = null;maplist = new ArrayList<Map<String, Object>>();for (PropertyCheckCorr propertyCheckCorr : checkCorrList) {map = new HashMap<>();map.put("year", LocalDateTime.now().getYear());num1++;propertyExportVO =propertyCheckCorrMapper.selectPropertyByPropertyId(propertyCheckCorr.getPropertyId(), propertyCheckCorr.getCheckId());map.put("deptName", propertyExportVO.getDeptName());// 获取资产的存放位置PropertyVO.PropertyUseInfo propertyUseInfo =propertyUseService.findPropertyUseInfo(propertyCheckCorr.getPropertyId());if (propertyUseInfo != null) {propertyExportVO.setPropertyArea(propertyUseInfo.getPropertyArea());}// 盘点数量propertyExportVO.setCheckNum(checkCorrList.size());propertyExportVO.setNum(num1);// 无盈亏if (CheckStatusEnum.HAVE_INVENTORY.getInventoryStatus() == propertyCheckCorr.getInventoryStatus()) {propertyExportVO.setNoProfitAndLoss("√");// 盘盈} else if (CheckStatusEnum.PROFIT.getInventoryStatus() == propertyCheckCorr.getInventoryStatus()) {propertyExportVO.setProfit("√");// 盘亏} else if (CheckStatusEnum.LOSS.getInventoryStatus() == propertyCheckCorr.getInventoryStatus()) {propertyExportVO.setLoss("√");}JSONObject parseObject = JSONObject.parseObject(JSON.toJSONString(propertyExportVO));maplist.add(parseObject);}map.put("maplist", maplist);// 第一个sheetsheetMap.put(0, map);List<PropertyCheckCorr> checkCorrProfitList = propertyCheckCorrMapper.selectList(Wrappers.<PropertyCheckCorr>lambdaQuery().eq(PropertyCheckCorr::getCheckId, propertyCheckCollect.getCheckId()).eq(PropertyCheckCorr::getInventoryStatus, CheckStatusEnum.PROFIT.getInventoryStatus()));map = new HashMap<>();maplist = new ArrayList<Map<String, Object>>();map.put("year", LocalDateTime.now().getYear());JSONObject parseObject = null;int num3 = 0;if (CollectionUtils.isNotEmpty(checkCorrProfitList)) {for (PropertyCheckCorr propertyCheckCorr : checkCorrProfitList) {propertyExportVO =propertyCheckCorrMapper.selectPropertyByPropertyId(propertyCheckCorr.getPropertyId(), propertyCheckCorr.getCheckId());map.put("deptName", propertyExportVO.getDeptName());// 获取资产的存放位置PropertyVO.PropertyUseInfo propertyUseInfo =propertyUseService.findPropertyUseInfo(propertyCheckCorr.getPropertyId());if (propertyUseInfo != null) {propertyExportVO.setPropertyArea(propertyUseInfo.getPropertyArea());}// 盘点数量propertyExportVO.setCheckNum(checkCorrList.size());num3++;propertyExportVO.setNum(num3);propertyExportVO.setProfit("√");parseObject = JSONObject.parseObject(JSON.toJSONString(propertyExportVO));maplist.add(parseObject);}}map.put("maplist", maplist);// 第三个sheetsheetMap.put(2, map);List<PropertyCheckCorr> checkCorrLossList = propertyCheckCorrMapper.selectList(Wrappers.<PropertyCheckCorr>lambdaQuery().eq(PropertyCheckCorr::getCheckId, propertyCheckCollect.getCheckId()).eq(PropertyCheckCorr::getInventoryStatus, CheckStatusEnum.LOSS.getInventoryStatus()));map = new HashMap<>();maplist = new ArrayList<Map<String, Object>>();map.put("year", LocalDateTime.now().getYear());int num4 = 0;if (CollectionUtils.isNotEmpty(checkCorrLossList)) {for (PropertyCheckCorr propertyCheckCorr : checkCorrLossList) {propertyExportVO =propertyCheckCorrMapper.selectPropertyByPropertyId(propertyCheckCorr.getPropertyId(), propertyCheckCorr.getCheckId());map.put("deptName", propertyExportVO.getDeptName());// 获取资产的存放位置PropertyVO.PropertyUseInfo propertyUseInfo =propertyUseService.findPropertyUseInfo(propertyCheckCorr.getPropertyId());if (propertyUseInfo != null) {propertyExportVO.setPropertyArea(propertyUseInfo.getPropertyArea());}// 盘点数量propertyExportVO.setCheckNum(checkCorrList.size());num4++;propertyExportVO.setNum(num4);propertyExportVO.setLoss("√");parseObject = JSONObject.parseObject(JSON.toJSONString(propertyExportVO));maplist.add(parseObject);}}map.put("maplist", maplist);// 第四个sheetsheetMap.put(3, map);// 组装第二个sheet数据map = new HashMap<>();map.put("year", LocalDate.now().getYear());map.put("deptName", propertyExportVO.getDeptName());map.put("checkNum", propertyExportVO.getCheckNum());if (CollectionUtils.isNotEmpty(checkCorrList)) {// 查询资产的总金额List<Long> propertyIdList = checkCorrList.stream().map(PropertyCheckCorr::getPropertyId).collect(Collectors.toList());BigDecimal totalMoney = propertyService.sumAllPropertyMoney(propertyIdList);map.put("totalMoney", totalMoney);PropertyCheck propertyCheck = propertyCheckService.getById(checkCorrList.get(0).getCheckId());map.put("checkFinishTime", propertyCheck.getCheckFinishTime());}// 查询无盈亏台数List<PropertyCheckCorr> checkCorrNoProfitAndLossList = propertyCheckCorrMapper.selectList(Wrappers.<PropertyCheckCorr>lambdaQuery().eq(PropertyCheckCorr::getCheckId, propertyCheckCollect.getCheckId()).eq(PropertyCheckCorr::getInventoryStatus, CheckStatusEnum.HAVE_INVENTORY.getInventoryStatus()));if (CollectionUtils.isNotEmpty(checkCorrNoProfitAndLossList)) {map.put("noProfitAndLoss", checkCorrNoProfitAndLossList.size());List<Long> propertyIds = checkCorrNoProfitAndLossList.stream().map(PropertyCheckCorr::getPropertyId).collect(Collectors.toList());BigDecimal noProfitAndLossMoney = propertyService.sumAllPropertyMoney(propertyIds);map.put("noProfitAndLossMoney", noProfitAndLossMoney);} else {map.put("noProfitAndLoss", 0);map.put("noProfitAndLossMoney", 0);}// 盘盈台数if (CollectionUtils.isNotEmpty(checkCorrProfitList)) {map.put("profit", checkCorrProfitList.size());} else {map.put("profit", 0);}// 盘亏台数和盘亏总金额if (CollectionUtils.isNotEmpty(checkCorrLossList)) {map.put("loss", checkCorrLossList.size());List<Long> propertyIds = checkCorrLossList.stream().map(PropertyCheckCorr::getPropertyId).collect(Collectors.toList());BigDecimal lossMoney = propertyService.sumAllPropertyMoney(propertyIds);map.put("lossMoney", lossMoney);} else {map.put("loss", 0);map.put("lossMoney", 0);}// 第二个sheetsheetMap.put(1, map);Workbook workbook = ExcelExportUtil.exportExcel(sheetMap, params);File savefile = new File("/tmp/excel/");if (!savefile.exists()) {savefile.mkdirs();}FileOutputStream fos = null;try {fos = new FileOutputStream("/tmp/excel/" + propertyCheckCollect.getCollectName()+ "资产清查固定资产清查表（含明细、汇总）表.xls");workbook.write(fos);} catch (IOException e) {log.error("导出盘点汇总报表异常: {}", e.getLocalizedMessage(), e);} finally {try {if (fos != null) {fos.close();}} catch (IOException e) {e.printStackTrace();}}}}// 压缩文件// compress(httpServletResponse);File zip = ZipUtil.zip(FileUtil.file("/tmp/excel/"));FileUtil.del("/tmp/excel/");String filePath = DownloadUtil.getFilePath(zip);FileUtil.del(zip);return filePath;}private void compress(HttpServletResponse httpServletResponse) {File zip = ZipUtil.zip(FileUtil.file("/tmp/excel/"));FileUtil.del("/tmp/excel/");FileInputStream fis = null;ServletOutputStream outputStream = null;try {DownloadUtil.setAttachmentResponseHeader(httpServletResponse,"资产清查固定资产清查表（含明细、汇总）表.zip");fis = new FileInputStream(zip);outputStream = httpServletResponse.getOutputStream();byte[] buffer = new byte[1024];int read = -1;while ((read = fis.read(buffer)) != -1) {outputStream.write(buffer, 0, read);}} catch (IOException e) {log.error("下载盘点汇总报表异常: {}", e.getLocalizedMessage(), e);} finally {try {if (fis != null) {fis.close();}if (outputStream != null) {outputStream.close();}FileUtil.del(zip);} catch (IOException e) {e.printStackTrace();}}}

扩展其他

不使用模板的导出，使用@ExcelCollection可以自动合并单元格

sheet.addMergedRegion(new Region(2,(short)0,3,(short )0));
//跨两行占一列 ce.setCellStyle(style); HSSFCell ce1=row.createCell(1);
//姓名 ce1.setCell

public static void main(String[] args) throws IOException {export1();}@GetMapping("/goods")public static void export1() throws IOException {List<UserGoodsDto> userGoodsDtos = new ArrayList<>();UserGoodsDto userGoodsDto = new UserGoodsDto();userGoodsDto.setUserName("name1");List<UserGoods> userGoodsList = new ArrayList<>();UserGoods userGoods = new UserGoods();userGoods.setId(1);userGoods.setGoodsName("name0");userGoods.setGoodsAddress("add");userGoods.setCreatedTime(new Date());UserGoods userGoods2 = new UserGoods();userGoods2.setId(2);userGoods2.setGoodsName("name1");userGoods2.setGoodsAddress("add2");userGoods2.setCreatedTime(new Date());userGoodsList.add(userGoods);userGoodsList.add(userGoods2);userGoodsDto.setUserGoodsList(userGoodsList);//***UserGoodsDto userGoodsDto2 = new UserGoodsDto();userGoodsDto2.setUserName("name1");List<UserGoods> userGoodsList2 = new ArrayList<>();userGoodsList2.add(new UserGoods().setId(3).setGoodsName("name2").setGoodsAddress("add2").setCreatedTime(new Date()));userGoodsList2.add(new UserGoods().setId(4).setGoodsName("name3").setGoodsAddress("add2").setCreatedTime(new Date()));userGoodsDto2.setUserGoodsList(userGoodsList2);List<UserGoodsTest> userGoodsTestList = new ArrayList<>();userGoodsTestList.add(new UserGoodsTest().setId(4).setGoodsName("name3Test").setGoodsAddress("add2").setCreatedTime(new Date()));userGoodsDto2.setUserGoodsTestList(userGoodsTestList);userGoodsDtos.add(userGoodsDto);userGoodsDtos.add(userGoodsDto2);System.out.println(userGoodsDtos);ExportParams exportParams = new ExportParams("双十一客户下单情况", null);Workbook workbook = ExcelExportUtil.exportExcel(exportParams, UserGoodsDto.class, userGoodsDtos);// ExcelExportEntity excelExportEntity = new ExcelExportEntity();// ExcelExportUtil.exportExcel(exportParams, , userGoodsDtos);String fileAllPath = CommonConstants.TEMP_EXPORT_PATH + "1.xls";try (FileOutputStream fos = new FileOutputStream(fileAllPath);) {workbook.write(fos);} catch (IOException e) {throw new RRException(e.getLocalizedMessage());}// ExcelExportUtil.exportExcel(userGoodsDtos,UserGoodsDto.class,"测试",exportParams,response);}@Data@Accessors(chain = true)class UserGoodsDto {@Excel(name = "用户名", needMerge = true)private String userName;@ExcelCollection(name = "商品")private List<UserGoods> userGoodsList;@ExcelCollection(name = "商品2")private List<UserGoodsTest> userGoodsTestList;}@Data@Accessors(chain = true)class UserGoods {private Integer id;@Excel(name = "商品名")private String goodsName;@Excel(name = "收货地址")private String goodsAddress;@Excel(name = "下单时间", exportFormat = "yyyy-MM-dd HH:mm:ss")private Date createdTime;}@Data@Accessors(chain = true)class UserGoodsTest {private Integer id;@Excel(name = "商品名test")private String goodsName;@Excel(name = "收货地址test")private String goodsAddress;@Excel(name = "下单时间test", exportFormat = "yyyy-MM-dd HH:mm:ss")private Date createdTime;}

oodsDto.class,“测试”,exportParams,response);

}

@Data

@Accessors(chain = true)

class UserGoodsDto {

@Excel(name = “用户名”, needMerge = true)

private String userName;

@ExcelCollection(name = “商品”)

private List userGoodsList;

@ExcelCollection(name = “商品2”)

private List userGoodsTestList;

}