目录

环境设置

一、表头设计：

1、利用列表

2、注解形式(推荐)

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

三、单元格策略

只要相同的列就合并

合并判断

四、动态标题实现

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

实体类举例

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

参考：

环境设置

<code><!-- 阿里开源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);

  }

}

​

​

三、单元格策略

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

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

•       }

•   }

}

​

}

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

<code>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() 方法接受两个参数：

template 是包含占位符的原始字符串，即待替换的字符串。可以是 "点个赞吧${man}"其中man是动态标题

name 是要替换占位符 ${name} 的实际值。

String resolvedString = placeholderHelper.replacePlaceholders(template, name);

七、可选字段导出（补充，结果图没展示）

package com.example.procurementmanagement.util;

import com.alibaba.excel.metadata.Head;

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 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.Arrays;

import java.util.HashSet;

import java.util.List;

import java.util.Set;

public class ExcelFillCellMergeStrategyChooseField implements CellWriteHandler {

private int[] mergeColumnIndex;

private int mergeRowIndex;

// 需要导出的列名集合

private Set<String> exportColumnNames;

public ExcelFillCellMergeStrategyChooseField() {

}

public ExcelFillCellMergeStrategyChooseField(int mergeRowIndex, int[] mergeColumnIndex,List<String> exportColumnNames) {

this.mergeRowIndex = mergeRowIndex;

this.mergeColumnIndex = mergeColumnIndex;

this.exportColumnNames = new HashSet<>(exportColumnNames);

}

private boolean isColumnExported(String colName) {

return exportColumnNames.contains(colName);

}

@Override

public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

//当前行

int curRowIndex = cell.getRowIndex();

//当前列

int curColIndex = cell.getColumnIndex();

// 获取当前列的列名

String colName = head.getHeadNameList().get(0);

// 如果当前列不在导出列名集合中，隐藏该列

if (!isColumnExported(colName)) {

cell.getSheet().setColumnHidden(curColIndex, true);

return;

}

if (curRowIndex > mergeRowIndex) {

for (int columnIndex : mergeColumnIndex) {

if (curColIndex == columnIndex) {

mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);

break;

}

}

}

}

/**

* 当前单元格向上合并

*

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

// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行

// 比较 当前行第三列数据（项目编号） 与上一行是否相同 控制后续列与 第3列数据合并状态相同

Cell curCellCol3 = cell.getRow().getCell(0);

Object curCellCol3Data = new Object();

if (curCellCol3!=null){

curCellCol3Data = curCellCol3.getCellTypeEnum() == CellType.STRING ?

curCellCol3.getStringCellValue() : curCellCol3.getNumericCellValue();

}

Cell preCellCol3 = cell.getSheet().getRow(curRowIndex - 1).getCell(0);

Object preCellCol3Data = preCellCol3.getCellTypeEnum() == CellType.STRING ?

preCellCol3.getStringCellValue() : preCellCol3.getNumericCellValue();

if (curData.equals(preData)) {

// 第二列避开判断，第二列需要合并

// if (curColIndex==1){

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

// }

// }

if(curCellCol3Data.equals(preCellCol3Data)&&curCellCol3!=null){

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

}

}

}

}

@Override

public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

}

@Override

public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

}

}

ps：改动部分

新增一个字段

private Set<String> exportColumnNames;//传入表头

public ExcelFillCellMergeStrategyChooseField(int mergeRowIndex, int[] mergeColumnIndex,List<String> exportColumnNames) {

this.mergeRowIndex = mergeRowIndex;

this.mergeColumnIndex = mergeColumnIndex;

this.exportColumnNames = new HashSet<>(exportColumnNames);

}

@Override

public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

//当前行

int curRowIndex = cell.getRowIndex();

//当前列

int curColIndex = cell.getColumnIndex();

// 获取当前列的列名

String colName = head.getHeadNameList().get(0);

// 如果当前列不在导出列名集合中，隐藏该列

if (!isColumnExported(colName)) {

cell.getSheet().setColumnHidden(curColIndex, true);

return;

}

if (curRowIndex > mergeRowIndex) {

for (int columnIndex : mergeColumnIndex) {

if (curColIndex == columnIndex) {

mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);

break;

}

}

}

}

结果

本地

导出结果：

上传到web

excel结果：

参考：

表头设计参考

单元合并参考链接

导入推荐，感觉用起来不错

有问题可留言！