0
0

easyexcel同时导出多个excel到web（zip压缩文件）

环境设置

<!-- 阿里开源easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>

一、表头设计：

1、利用列表

private static List<List<String>> head() {
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("序号");
        list.add(head0);
        Map<String, List<String>> map = getHeader();
        map.forEach((k, v) -> {
            String deviceCategory = k;
            List<String> ls = v;
            ls.forEach(e -> {
                List<String> head = new ArrayList<>();
                head.add(deviceCategory);
                head.add(e);
                list.add(head);
            });
​
•    });
•    List<String> head1 = new ArrayList<>();
•    head1.add("备注");
•    list.add(head1);
•    List<String> head2 = new ArrayList<>();
•    head2.add("埋深");
•    list.add(head2);
•    return list;
}
private static Map<String, List<String>> getHeader() {
    Map<String, List<String>> map = new HashMap<>();
    List<String> aList = new ArrayList<>();
    List<String> sList = new ArrayList<>();
    List<String> subList = new ArrayList<>();
    String column1 = "X";
    aList.add(column1);
    String column2 = "Y";
    aList.add(column2);
    String column3 = "B";
    sList.add(column3);
    String column4 = "L";
    sList.add(column4);
    String subColumn = "其它";
    subList.add(subColumn);
    subList.add("小计3");
    map.put("坐标", aList);
    map.put("经纬度", sList);
    return map;
}

2、注解形式(推荐)

@Data
@ApiModel("角色管理")
public class TSRoleVo extends ExcelModel {
​
@ExcelIgnore
@ApiModelProperty("id")
private String id;
​
•    
•    @ExcelProperty(value = {"角色表列表","导出人:${title}","角色名称"} , index = 0)
•    @ApiModelProperty(value = "角色名称")
•    @ColumnWidth(25)
•    private String roleName;//角色名称
•    
​
@ExcelProperty(value = {"角色表列表","导出人:${title}","角色编码"} , index = 1)
@ApiModelProperty(value = "角色编码")
@ColumnWidth(25)
private String roleCode;//角色编码
​
@ExcelProperty(value = {"角色表列表","导出人:${title}","部门权限组ID"} , index = 2)
@ApiModelProperty(value = "部门权限组ID")
@ColumnWidth(25)
private String departAgId;//组织机构ID  部门权限组ID
​
@Override
public boolean validation(Map<String, List<String>> validationArgs) {
    return false;
}
​
}

ps：
​
宽:@ColumnWidth(25)
​
内容的高:@ContentRowHeight(10)
​
表头的高：@HeadRowHeight(20)

二、导出zip（可实现任意个excel文件）

对于每个excel拥有两个sheet且 每个sheet实体类数据不同

public class ReceiveTaskUtils {
    public static void addFileToZip(File file, ZipOutputStream zos) throws IOException {
        InputStream fileInputStream = new FileInputStream(file);
        //zip中要放文件称为zipEntry
        ZipEntry zipEntry = new ZipEntry(file.getName());
        zos.putNextEntry(zipEntry);
        byte[] bytes = new byte[1024];
        int len;
        while ((len = fileInputStream.read(bytes)) != -1) {
            //读的内容会自动放到zip条目中，因此zipentry再输出流读完需要关闭
            zos.write(bytes, 0, len);
        }
        //要关闭这个 zos.closeEntry();
        zos.closeEntry();
        fileInputStream.close();
    }
​
    static void deleteFile(String path) {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
    }
​
    /**
     * 获取Date的月份
     */
    static int getMonth(Date Time) {
​
        LocalDate localDate = Time.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        int month = localDate.getMonthValue();
        return month;
​
    }
    private static String formatToDate(Date receiveCliDate) {
​
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
        String formattedDate = dateFormat.format(receiveCliDate);
        return formattedDate;
    }
    //写到本地{
    public static void export(ReceiveExportVo result, HttpServletResponse response, String type) throws Exception {
//设置输出流格式
        String name = (type.contains("已完成") ? "采购完成" : "领受任务");
        Date cliTime = (type.contains("已完成") ? CompleteCliDate : ReceiveCliDate);
        String month=String.valueOf(getMonth(cliTime));
        response.setContentType("application/zip");
        response.setCharacterEncoding("UTF-8");
        String fileName = name + System.currentTimeMillis() + ".zip";
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
//        创建excel文件
        String fileName1 = ".\\单一来源.xlsx";
        //合并策略：
        int[] mergeColumeIndex ={1,2,3,4,5,6,7,8,9,10,11,12};
        //从第二行后开始合并
        // 调用合并单元格工具类，此工具类是没有根据合并，数据相同就合并了
        String titleDate=formatToDate(ReceiveCliDate);
        ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(2,mergeColumeIndex,titleDate,month);
        //设置样式 标题和内容
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        log.info("创建excel之前");
        try (ExcelWriter excelWriter = EasyExcel.write(fileName1).build()) {
            WriteSheet writeSheet1 = EasyExcel.writerSheet(month + "月单一来源进行中项目统计")
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .head(SingReceiveExport1Vo.class).build();
            List<SingReceiveExport1Vo> singReceiveExport1VoList = result.getSingReceiveExport1VoList();
            excelWriter.write(singReceiveExport1VoList, writeSheet1);
            WriteSheet writeSheet2 = EasyExcel.writerSheet(month + "月已安排审价项目统计")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .head(SingReceiveExport2Vo.class).build();
            List<SingReceiveExport2Vo> singReceiveExport2VoList = result.getSingReceiveExport2VoList();
            excelWriter.write(singReceiveExport2VoList, writeSheet2);
        }
        String fileName2 = ".\\竞争型.xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName2).build()) {
            WriteSheet writeSheet3 = EasyExcel.writerSheet(month + "月正在进行中竞争性采购项目")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .head(CompeteReceiveExport1Vo.class).build();
            List<CompeteReceiveExport1Vo> competeReceiveExport1VoList = result.getCompeteReceiveExport1VoList();
            excelWriter.write(competeReceiveExport1VoList, writeSheet3);
            WriteSheet writeSheet4 = EasyExcel.writerSheet(month + "月已开标项目")
                    .registerWriteHandler(excelFillCellMergeStrategy)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .head(CompeteReceiveExport2Vo.class).build();
            List<CompeteReceiveExport2Vo> competeReceiveExport2VoList = result.getCompeteReceiveExport2VoList();
            excelWriter.write(competeReceiveExport2VoList, writeSheet4);
        }
        //压缩路径
//        String zipFilePath =".\\"+".zip";
        // 创建输出流
        FileOutputStream fos = new FileOutputStream(fileName);
        ZipOutputStream zos = new ZipOutputStream(fos);
        // 压缩文件
        File file1 = new File(fileName1);
        File file2 = new File(fileName2);
        addFileToZip(file1, zos);
        addFileToZip(file2, zos);
        zos.close();
        fos.close();
        //将压缩文件输入流传给response输出流
        InputStream fileInputStream = new FileInputStream(fileName);
        OutputStream outputStream = response.getOutputStream();
        byte[] bytes = new byte[1024 * 8];
        int len;
        while((len=fileInputStream.read(bytes))!=-1) {
            outputStream.write(bytes,0,len);
        }
        fileInputStream.close();
        outputStream.close();
//        删除文件
        deleteFile(fileName1);
        deleteFile(fileName2);
        deleteFile(fileName);
    }
}

​
​

三、单元格策略

只要相同的列就合并（图片仅供参考）

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
​
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
​
public ExcelFillCellMergeStrategy() {
}
​
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
}
​
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
​
}
​
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
   //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    int curColIndex = cell.getColumnIndex();
​
•    if (curRowIndex > mergeRowIndex) {
•        for (int i = 0; i < mergeColumnIndex.length; i++) {
•            if (curColIndex == mergeColumnIndex[i]) {
•                mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
•                break;
•            }
•        }
•    }
}
​
/**
​
- 当前单元格向上合并
  *
- @param writeSheetHolder
- @param cell             当前单元格
- @param curRowIndex      当前行
- @param curColIndex      当前列
  */
  private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
  //获取当前行的第一列的数据和上一行的第一列数据，通过第一行数据是否相同进行合并
​
//        Cell preCell_now = cell.getSheet().getRow(curRowIndex ).getCell(curColIndex);
//        Object curData = preCell_now.getCellTypeEnum() == CellType.STRING ? preCell_now.getStringCellValue() : preCell_now.getNumericCellValue();
//        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex - 1);
  //      Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
​
•    // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
•    //
•    if (curData.equals(preData)) {
•        Sheet sheet = writeSheetHolder.getSheet();
•        List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
•        boolean isMerged = false;
•        for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
•            CellRangeAddress cellRangeAddr = mergeRegions.get(i);
•            // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
•            if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
•                sheet.removeMergedRegion(i);
•                cellRangeAddr.setLastRow(curRowIndex);
•                sheet.addMergedRegion(cellRangeAddr);
•                isMerged = true;
•            }
•        }
•        // 若上一个单元格未被合并，则新增合并单元
•        if (!isMerged) {
•            CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
•            sheet.addMergedRegion(cellRangeAddress);
•        }
•    }
}
​
}

合并判断（图片仅供参考）

package com.ph.rfwg.util;
​
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
​
public class ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
​
public ExcelMergeUtil() {
}
​
public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
}
​
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
}
​
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
​
    //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    int curColIndex = cell.getColumnIndex();
​
    if (curRowIndex > mergeRowIndex) {
        for (int i = 0; i < mergeColumnIndex.length; i++) {
            if (curColIndex == mergeColumnIndex[i]) {
                mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                break;
            }
        }
    }
}
​
​
/**
 * 当前单元格向上合并
 *
 * @param writeSheetHolder
 * @param cell             当前单元格
 * @param curRowIndex      当前行
 * @param curColIndex      当前列
 */
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
    Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
    Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
    Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
    // 将当前单元格数据与上一个单元格数据比较
    Boolean dataBool = preData.equals(curData);
    //此处需要注意：因为我是按照工程名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是工程名称所在列的下标
    Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
    if (dataBool && bool) {
        Sheet sheet = writeSheetHolder.getSheet();
        List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
        boolean isMerged = false;
        for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
            CellRangeAddress cellRangeAddr = mergeRegions.get(i);
            // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
            if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                sheet.removeMergedRegion(i);
                cellRangeAddr.setLastRow(curRowIndex);
                sheet.addMergedRegion(cellRangeAddr);
                isMerged = true;
            }
        }
        // 若上一个单元格未被合并，则新增合并单元
        if (!isMerged) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
            sheet.addMergedRegion(cellRangeAddress);
        }
    }
}
}

四、动态标题实现

public class CustomerTitleHandler implements CellWriteHandler {
    private String title;
    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");
    public CustomerTitleHandler(String title) {
        this.title = title;
    }
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head！=null) {
        //获取一个标题列表
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                //需要多个动态变量直接在后面加，properties相当于map会自动映射
                properties.setProperty("title", title);
                headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));
            }
        }
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                Cell cell, Head head, Integer integer, Boolean aBoolean) {

}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                   CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                             List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

}

}

五、整合（动态标题+单元格合并）

实体类举例

/**
需求单位联系人/电话
/
@ColumnWidth(25)
@ExcelProperty(value={"${titleDate}","需求单位联系人/电话"})
// @ExcelProperty(value={"","需求单位联系人/电话"})
private String demandUnitPeople;
/
*单一来源承制单位
* plan
*/
@ColumnWidth(40)
@ExcelProperty(value={"单一来源正在进行中审价项目统计","单一来源承制单位"})
private String singleSourceProvider;

/**
  *承制单位联系人及电话
  */
 @ColumnWidth(40)
 @ExcelProperty(value={"单一来源正在进行中审价项目统计","承制单位联系人及电话"})
 private String manufacturerPhone;

package com.example.procurementmanagement.util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.PropertyPlaceholderHelper;

import java.util.List;
import java.util.Properties;

/**
 * author:lanjie
 * 单元格合并策略
 * describe：合并相同字段的列，但需要判断首列是否合并（条件合并）
 * ps:可复用
 */
@Slf4j
public class ExcelMergeUtil implements CellWriteHandler {
    //需要合并的列数组
    private int[] mergeColumnIndex;
    //合并起始行
    private int mergeRowIndex;

    private String titleDate;
    private String titleMonth;
    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex,String titleDate,String titleMonth) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.titleDate=titleDate;
        this.titleMonth=titleMonth;
    }

    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)){
                Properties properties = new Properties();
                properties.setProperty("titleDate", titleDate);
                properties.setProperty("titleMonth", titleMonth);
                headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));
                log.info("headNameList:{}",headNameList);
            }
        }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            log.info("定义单元格以后！");
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意：因为我是按照工程名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是工程名称所在列的下标
        Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
        if (dataBool && bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

六、感兴趣可看（封装函数说明）

1、 PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

PropertyPlaceholderHelper

的构造函数接受两个参数，分别是前缀和后缀。在你的例子中，

PropertyPlaceholderHelper("${", "}")

表示创建了一个 

PropertyPlaceholderHelper

实例，用于识别以 "${" 开头、以 "}" 结尾的占位符。

2、properties.setProperty("title", title);

上述代码将在属性文件中设置一个名为 "title" 的键，并将变量 

title

的值作为其对应的值。也就是说，

title

变量的值将被保存在属性文件中，以后可以通过该键来获取它。

题中 headNameList.set(0, placeholderHelper.replacePlaceholders(headNameList.get(0), properties));

是将properties（相当于map）中的键和你设置的动态标题进行匹配映射，因此可以直接往里面加任意个

对于为什么这里是0，因为标题分多级标题，headNameList获取的是该单元格所在列的所有标题放在一个字符串数组中，因此一级标题的索引是0，当然如果是改其他标题，你只需要更换索引进行映射即可。

3、

replacePlaceholders()

方法接受两个参数：

  1. template 是包含占位符的原始字符串，即待替换的字符串。可以是 "点个赞吧${man}"其中man是动态标题
  2. name 是要替换占位符 ${name} 的实际值。
String resolvedString = placeholderHelper.replacePlaceholders(template, name);

结果

本地

导出结果：

上传到web

excel结果：

参考：

表头设计参考

单元合并参考链接

有问题可留言！

标签： java 后端
本文转载自: https://blog.csdn.net/LanJieZhiFu/article/details/135587954
版权归原作者 菜鸟蹦迪 所有， 如有侵权，请联系我们删除。
登录后发布评论

“easyexcel同时导出多个excel到web（zip压缩文件）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航