easypoi 模板導入、導出合并單元格功能

參考：

hutool 導出復雜excel(動態(tài)合并行和列)

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());

    /**
     * 創(chuàng)建工作簿
     *
     * @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);
        //如果是合并單元列，就取合并單元格的首行和首列所在位置讀數(shù)據，否則就是直接讀數(shù)據
        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();

            //構造一個開始行和開始列組成的數(shù)組
            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("年") && 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 要導出的數(shù)據數(shù)據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<>();
        // 上一次讀取的序號（用戶去除重復讀取數(shù)據）
        Integer lastNo = null;
        // 序號是否相同的數(shù)據
        Boolean isSameData;

        try {
            workBook = ExcelUtil.createWorkBook(filePath);
            //獲取第一個sheet
            Sheet sheet = workBook.getSheetAt(0);
            //獲取合并單元格信息的hashmap
            Map<String, Integer[]> mergedRegionMap = ExcelUtil.getMergedRegionMap(sheet);
            //從excel的第7行索行開始，遍歷到最后一行（標題行，直接跳過不讀?。?/span>
            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();
                    // 校區(qū)名稱
                    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));
                    // 地上層數(shù)
                    entity.setOvergroundFloors(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));
                    // 地下層數(shù)
                    entity.setUndergroundFloors(Convert.toDouble(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++), 0D));
                    // 房屋權屬證明
                    entity.setHouseOwnershipCertificate(ExcelUtil.getCellValue(sheet, mergedRegionMap, row, i, j++));
                    // 發(fā)證日期
                    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列使用單位開始讀?。?/span>
                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("數(shù)據導入異常");
        } 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 = "校舍使用管理導出參數(shù)")
@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), "請選擇要導出的數(shù)據");
        idLongList = idList.stream().map(Long::parseLong).collect(Collectors.toList());
    }

    // 查詢要導出的數(shù)據
    List<SchoolBuildingUseEntity> schoolBuildingUseEntityList = list(Wrappers.<SchoolBuildingUseEntity>lambdaQuery()
            .in(ObjectUtil.isNotEmpty(idLongList), SchoolBuildingUseEntity::getId, idLongList)
            .orderByDesc(SchoolBuildingUseEntity::getUpdateTime));
    // Assert.isTrue(CollUtil.isEmpty(schoolBuildingUseEntityList), "暫無數(shù)據導出");無數(shù)據可以用于下載模板(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() + "年贛南醫(yī)學院固定資產清查明細表",
                "附2-" + LocalDate.now().getYear() + "年贛南醫(yī)學院固定資產清查匯總表",
                "附3-" + LocalDate.now().getYear() + "年贛南醫(yī)學院固定資產清查盤盈明細表",
                "附4-" + LocalDate.now().getYear() + "年贛南醫(yī)學院固定資產清查盤虧明細表");

        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查詢盤點資產關聯(lián)表
                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());
                    }
                    // 盤點數(shù)量
                    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());
                        }
                        // 盤點數(shù)量
                        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());
                        }
                        // 盤點數(shù)量
                        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數(shù)據
                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());
                }

                // 查詢無盈虧臺數(shù)
                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);
                }

                // 盤盈臺數(shù)
                if (CollectionUtils.isNotEmpty(checkCorrProfitList)) {
                    map.put("profit", checkCorrProfitList.size());
                } else {
                    map.put("profit", 0);
                }

                // 盤虧臺數(shù) 和盤虧總金額
                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;
}文章來源地址http://www.zghlxwxcb.cn/news/detail-421555.html

到了這里，關于easypoi 模板導入、導出合并excel單元格功能的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！