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

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 com.yymt.common.constants.CommonConstants;
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：2023/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如：2009年9月 也需要转成日期，默认是1日
            if (dateStr.contains("年") &amp;&amp; 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如：2023/1/16
            return 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);
        // 第一个sheet
        sheetMap.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 org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.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);
            //获取第一个sheet
            Sheet sheet = workBook.getSheetAt(0);
            //获取合并单元格信息的hashmap
            Map<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
@AllArgsConstructor
public 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);
                // 第一个sheet
                sheetMap.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);
                // 第三个sheet
                sheetMap.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);
                // 第四个sheet
                sheetMap.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);
                }
                // 第二个sheet
                sheetMap.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;
}

@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;
}

代码007普通

打赏 收藏 海报 链接