java自定义Excel导出实现方案汇总

涉及依赖

com.alibaba.excel.EasyExcel
org.apache.poi
cn.hutool.core.io.resource

通用EasyExcel工具导出方法

public void exportFile(
      HttpServletResponse response,
      String fileName,
      List<List<String>> list,
      Map<Integer, String[]> mapDropDown,
      List<List<Object>> filterTableList) {
    try {
      response.setContentType("multipart/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      response.setCharacterEncoding("utf-8");
      String exportFileName = URLEncoder.encode(fileName, "UTF-8");

      response.setHeader("Content-disposition", "attachment;filename=" + exportFileName + ".xlsx");
      EasyExcel.write(response.getOutputStream())
      //设置自定义表头标题
          .head(list)
          // 设置自定义样式（例如下拉显示）
          .registerWriteHandler(new CustomSheetWriteHandler(mapDropDown))
          .sheet("sheet1")
          // 设置默认样式，单元格需要自定义样式这里需要设置为false
          .useDefaultStyle(true)
          //表格数据
          .doWrite(filterTableList);
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }

自定义单行表头内容

一、非固定表头标题属性名称类型可以保存在json文件，json文件获取属性名称，数据类型等自定义特殊处理。
例子：

{
  "fileName": "XXX表",
  "propertys": [
    {
      "headerName": "序号",
      "propertyName": "",
      "type": "INT",
      "isLov": false,
      "lovInfoName": ""
    },
    {
      "headerName": "所有人",
      "propertyName": "owner",
      "type": "STRING",
      "isLovInfo": false,
      "lovInfoName": ""
    }
   ]
  }

二、读取json获取表头

 List<String> mustPropertyList = new ArrayList<>();
   
 JSONObject dataProperty =
            JSONObject.parseObject(ResourceUtil.readUtf8Str("productProperty.json"));
 String fileName = dataProperty.getString("fileName");
 List<JSONObject> properties =
            dataProperty.getJSONArray("propertys").toJavaList(JSONObject.class);
        if (properties == null || properties.size() < 1) {
          return;
        }
  // 存表头
        for (int i = 0; i < properties.size(); i++) {
          JSONObject property = properties.get(i);
          mustPropertyList.add(property.getString("headerName"));
          }
     // 表头横向展示
        List<List<String>> list = new ArrayList<>();
        for (String headItem : mustPropertyList) {
          List<String> headList = new ArrayList<>();
          headList.add(headItem);
          list.add(headList);
        }     

注意表头list的保存方式，每一列在内层list内，合并单元格，每个单元格填入相同表头值，相同行或相同列会自动合并

自定义多级表头内容

其他处理同上

// 存表头
//三级表头标题（json文件保存）
      for (int i = 0; i < properties.size(); i++) {
        JSONObject property = properties.get(i);
        mustPropertyList.add(property.getString("headerName"));
      }
      // 设置标题
      Date now = new Date();
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
      String dateStr = sdf.format(now);
      // 首行含日期表头居中
      String bigTitle = "xxx           xxx表          " + dateStr;
      List<List<String>> headList = new ArrayList<>();
      List<Integer> imageColumnIndex = new ArrayList<>();
      for (int j = 0; j < secondTitle.size(); j++) {
        imageColumnIndex.add(j * mustPropertyList.size() + 1);
        for (int k = 0; k < mustPropertyList.size(); k++) {
          List<String> head = new ArrayList<>();
          //首行
          head.add(bigTitle);
          //第二行
          head.add(secondTitle.get(j));
          //第三行
          head.add(mustPropertyList.get(k));
          if (0 == k) {
          //合并上下行
            head.add("序号");
          } else {
            head.add(mustPropertyList.get(k));
          }
          headList.add(head);
        }
      }

导出表头样例

自定义表头样式

package xxx;

import com.alibaba.excel.enums.CellDataTypeEnum;
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 org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * @description 自定义单元格样式
 */
public class CustomImageCellWriteHandler implements CellWriteHandler {

  @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,
      WriteCellData<?> cellData,
      Cell cell,
      Head head,
      Integer relativeRowIndex,
      Boolean isHead) {
  }

  @Override
  public void afterCellDispose(
      WriteSheetHolder writeSheetHolder,
      WriteTableHolder writeTableHolder,
      List<WriteCellData<?>> cellDataList,
      Cell cell,
      Head head,
      Integer relativeRowIndex,
      Boolean isHead) {
    Sheet sheet = cell.getSheet();
    if (isHead) {
      Row row = cell.getRow();
      // 设置标题样式
      XSSFCellStyle cellStyle =
          (XSSFCellStyle) cell.getRow().getSheet().getWorkbook().createCellStyle();
      cellStyle.cloneStyleFrom(cell.getCellStyle());
      cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      //单元格背景色
     cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
      cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
      // 单元格宽度自适应
      cellStyle.setShrinkToFit(true);
      // 边框
      cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
      cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
      cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
      cellStyle.setBorderRight(BorderStyle.THIN); // 右边框
      // 单元格边框颜色
      cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
      cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
      cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
      cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
      // 设置字体
      XSSFFont font = (XSSFFont) cell.getRow().getSheet().getWorkbook().createFont();
      font.setFontName("微软雅黑");
      font.setBold(true);

      if (0 == cell.getRowIndex()) {
        font.setFontHeightInPoints((short) 20);
      } else if (1 == cell.getRowIndex()) {
        font.setFontHeightInPoints((short) 11);
      } else {
        font.setFontHeightInPoints((short) 8);
      }
      cellStyle.setFont(font);
      cell.setCellStyle(cellStyle);
    } else {
      XSSFCellStyle cellStyle =
          (XSSFCellStyle) cell.getRow().getSheet().getWorkbook().createCellStyle();
      cellStyle.cloneStyleFrom(cell.getCellStyle());
      cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
      cellStyle.setAlignment(HorizontalAlignment.CENTER);
      // 自动换行
      cellStyle.setWrapText(true);
      // 边框
      cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
      cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
      cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
      cellStyle.setBorderRight(BorderStyle.THIN); // 右边框
      // 设置字体
      XSSFFont font = (XSSFFont) cell.getRow().getSheet().getWorkbook().createFont();
      font.setFontName("微软雅黑");
      font.setFontHeightInPoints((short) 9);
      cellStyle.setFont(font);
      cell.setCellStyle(cellStyle);
    }
  }

  
}

自定义多sheet页的单元格样式

拦截器设置单元格样式

package xxx;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.fastjson.JSONObject;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.*;

/**
 * @Descpription 自定义单元格样式
 */
public class CellStyleSheetWriteHandler implements CellWriteHandler {

  /** map key：第i行 value：第二个map的key表示第i列，JSONObject保存样式，值（可以自行修改补充） */
  private Map<Integer, Map<Integer, JSONObject>> styleMap;

  /** 有参构造 */
  public CellStyleSheetWriteHandler(Map<Integer, Map<Integer, JSONObject>> styleMap) {
    this.styleMap = styleMap;
  }

  /** 无参构造 */
  public CellStyleSheetWriteHandler() {}

  /** 在创建单元格之前调用 */
  @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 afterCellDispose(CellWriteHandlerContext context) {
    WriteCellData<?> cellData = context.getFirstCellData();
    Cell cell = context.getCell();
    WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
    // 当head需要单独设置样式时，这里通过isHead判断区分
    Head head = context.getHeadData();
    Boolean isHead = context.getHead();
    // 获取当前样式/数据，设置后立即生效

    // 当前行的第i列
    int i = cell.getColumnIndex();
    // 第j行
    int j = cell.getRowIndex();
    if (styleMap.containsKey(j)) {
      Map<Integer, JSONObject> cStyleMap = styleMap.get(j);
      if (cStyleMap.containsKey(i)) {
        JSONObject styles = cStyleMap.get(i);
        if (styles != null && styles.keySet().size() > 0) {
          // 根据单元格获取workbook
          Workbook workbook = cell.getSheet().getWorkbook();
          // 单元格策略,获取（没有则新建）当前样式/数据，设置后立即生效
          WriteCellStyle contentWriteCellStyle = cellData.getOrCreateStyle();
          // 这种直接创建的方式不会直接生效
          //          WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

          // 设置单元格上下左右边框
          if (styles.containsKey("bottomBorderSize")) {
            contentWriteCellStyle.setBorderBottom(
                BorderStyle.valueOf(styles.getShort("bottomBorderSize")));
          }
          if (styles.containsKey("leftBorderSize")) {
            contentWriteCellStyle.setBorderLeft(
                BorderStyle.valueOf(styles.getShort("leftBorderSize")));
          }
          if (styles.containsKey("rightBorderSize")) {
            contentWriteCellStyle.setBorderRight(
                BorderStyle.valueOf(styles.getShort("rightBorderSize")));
          }
          if (styles.containsKey("topBorderSize")) {
            contentWriteCellStyle.setBorderTop(
                BorderStyle.valueOf(styles.getShort("topBorderSize")));
          }
          // 边框颜色
          if (styles.containsKey("bottomBorderColor")) {
            contentWriteCellStyle.setBottomBorderColor(styles.getShort("bottomBorderColor"));
          }
          if (styles.containsKey("leftBorderColor")) {
            contentWriteCellStyle.setLeftBorderColor(styles.getShort("leftBorderColor"));
          }
          if (styles.containsKey("rightBorderColor")) {
            contentWriteCellStyle.setRightBorderColor(styles.getShort("rightBorderColor"));
          }
          if (styles.containsKey("topBorderColor")) {
            contentWriteCellStyle.setTopBorderColor(styles.getShort("topBorderColor"));
          }

          // 设置背景颜色
          if (styles.containsKey("bgColor")) {
            // 设置默认填充模式为单色填充，否则颜色设置不生效；若设置了此项但不设置前景颜色，单元格会显示黑色，所以fillPattern务必与fillForeground同时设置
            contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            // 设置了前景色后网格线默认会隐藏
            contentWriteCellStyle.setFillForegroundColor(styles.getShort("bgColor"));
          }
          // 创建字体实例
          WriteFont cellWriteFont = new WriteFont();
          // 设置字体
          if (styles.containsKey("fontName")) {
            cellWriteFont.setFontName(styles.getString("fontName"));
          }
          // 设置字体大小
          if (styles.containsKey("fontSize")) {
            cellWriteFont.setFontHeightInPoints(styles.getShort("fontSize"));
          }
          // 设置字体粗细
          if (styles.containsKey("fontBold")) {
            cellWriteFont.setBold(styles.getBoolean("fontBold"));
          }
          // 设置字体颜色
          if (styles.containsKey("fontColor")) {
            cellWriteFont.setColor(styles.getShort("fontColor"));
          }
          contentWriteCellStyle.setWriteFont(cellWriteFont);
        }
      }
    }
  }
}

设置宽度自适应（粗略）

注意这个方法一定要在多个sheet页时调用时设置sheetNo，否则只有第一个sheet页生效

public class CellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

  /** 单元格的最大宽度 */
  private static final int MAX_COLUMN_WIDTH = 50;
  /** 缓存（第一个Map的键是sheet的index, 第二个Map的键是列的index, 值是数据长度） */
  private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

  /** 重写设置列宽的方法 */
  @Override
  protected void setColumnWidth(
      WriteSheetHolder writeSheetHolder,
      List<WriteCellData<?>> cellDataList,
      Cell cell,
      Head head,
      Integer relativeRowIndex,
      Boolean isHead) {
    boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
    // 当时表头或者单元格数据列表有数据时才进行处理
    if (needSetWidth) {
      Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());

      if (maxColumnWidthMap == null) {
        maxColumnWidthMap = new HashMap(16);
        CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
      }
      // 获取数据长度
      Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
      if (columnWidth >= 0) {
        if (columnWidth > MAX_COLUMN_WIDTH) {
          columnWidth = MAX_COLUMN_WIDTH;
        }
        // 确保一个列的列宽以表头为主，如果表头已经设置了列宽，单元格将会跟随表头的列宽
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());

        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
          maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
          // 如果使用EasyExcel默认表头，那么使用columnWidth * 512
          // 如果不使用EasyExcel默认表头，那么使用columnWidth * 256
          // 如果是自己定义的字体大小，可以再去测试这个参数常量
          writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
      }
    }
  }

  /**
   * 获取当前单元格的数据长度
   *
   * @param cellDataList
   * @param cell
   * @param isHead
   * @return
   */
  private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
    if (isHead) {
      return cell.getStringCellValue().getBytes().length;
    } else {
      WriteCellData cellData = cellDataList.get(0);
      CellDataTypeEnum type = cellData.getType();
      if (type == null) {
        return -1;
      } else {
        switch (type) {
          case STRING:
            return cellData.getStringValue().getBytes().length;
          case BOOLEAN:
            return cellData.getBooleanValue().toString().getBytes().length;
          case NUMBER:
            return cellData.getNumberValue().toString().getBytes().length;
          default:
            return -1;
        }
      }
    }
  }
}

设置根据文本换行符自适应行高

public class CellHeightStyleStrategy extends AbstractRowHeightStyleStrategy {
  /** 默认行高, 15pt=300twips(setHeight的单位) */
  private static final Integer DEFAULT_HEIGHT = 300;

  @Override
  protected void setHeadColumnHeight(Row row, int relativeRowIndex) {}

  @Override
  protected void setContentColumnHeight(Row row, int relativeRowIndex) {
    Iterator<Cell> cellIterator = row.cellIterator();
    if (!cellIterator.hasNext()) {
      return;
    }

    // 默认为 1行高度
    Integer maxHeight = 1;
    while (cellIterator.hasNext()) {
      Cell cell = cellIterator.next();
      switch (cell.getCellType()) {
        case STRING:
          if (cell.getStringCellValue().indexOf("\n") != -1) {
            int length = cell.getStringCellValue().split("\n").length;
            maxHeight = Math.max(maxHeight, length);
          }
          break;
        default:
          break;
      }
    }
    row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
  }
}

多sheet页导出，多sheet页独立样式设置示例

/**
   * @description 支持自定义单元格样式、多sheet页导出
   * @param response 文件导出响应
   * @param fileName 导出文件名
   * @param list 导出文件表头数据
   * @param multiStyleMap 包含每个sheet单元格样式数据，第一个map的key为sheet页名称，第二个map的key为第i行，第三个map的key为第j列
   * @param multiSheetMap 包含sheet名称和sheet数据，第一个map的key为sheet页名称
   * @throws
   * @return
   */
  public void exportFile(
      HttpServletResponse response,
      String fileName,
      List<List<String>> list,
      Map<String, Map<Integer, Map<Integer, JSONObject>>> multiStyleMap,
      Map<String, List<List<Object>>> multiSheetMap) {
    if (Optional.ofNullable(multiSheetMap).isEmpty() || multiSheetMap.keySet().size() < 1) {
      return;
    }
    try {
      response.setContentType("multipart/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      response.setCharacterEncoding("utf-8");
      String exportFileName = URLEncoder.encode(fileName, "UTF-8");
      response.setHeader("Content-disposition", "attachment;filename=" + exportFileName + ".xlsx");
	  // 这里使用ExcelWriter方便后续单独添加sheet页数据
      ExcelWriter excelWriter =
          EasyExcel.write(response.getOutputStream())
              .head(list)
              .useDefaultStyle(true)
              .build();
     //使用sheet名称作为key，value保存每个sheet页的数据
      multiSheetMap
          .keySet()
          .forEach(
              key -> {
              //获取每个sheet的独立样式数据
                Map<Integer, Map<Integer, JSONObject>> styleMap = multiStyleMap.get(key);
                CellStyleSheetWriteHandler cellStyleSheetWriteHandler =
                    new CellStyleSheetWriteHandler(styleMap);
                List<List<Object>> sheetData = multiSheetMap.get(key);
                WriteSheet sheet =
                    EasyExcel.writerSheet(key)
                        .registerWriteHandler(cellStyleSheetWriteHandler)
                        .build();
                excelWriter.write(sheetData, sheet);
              });
      //关闭流
      excelWriter.finish();
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }

单元格设置下拉值显示

package xxx;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.util.HashMap;
import java.util.Map;

/**
 * @Descpription 自定义sheet下拉
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {
  Map<Integer, String[]> mapDropDown = new HashMap<>();

  public CustomSheetWriteHandler(Map<Integer, String[]> mapDropDown) {
    this.mapDropDown = mapDropDown;
  }

  public CustomSheetWriteHandler() {}

  @Override
  public void beforeSheetCreate(
      WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}

  @Override
  public void afterSheetCreate(
      WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    Workbook workbook = writeWorkbookHolder.getWorkbook();
    Sheet sheet = writeSheetHolder.getSheet();
    DataValidationHelper helper = sheet.getDataValidationHelper();
    for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
     /***设置隐藏sheet处理下拉值过多不显示问题**/
      String sheetName = "hidden" + entry.getKey();
      Sheet providerSheet = workbook.createSheet(sheetName);
      Name categoryName = workbook.createName();
      categoryName.setNameName(sheetName);
      /***起始行、终止行、起始列、终止列**/
      CellRangeAddressList addressList =
          new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
      String[] values = entry.getValue();
      for (int i = 0, len = values.length; i < len; i++) {
        Row row = providerSheet.getRow(i);
        if (row == null) {
          row = providerSheet.createRow(i);
        }
        row.createCell(entry.getKey()).setCellValue(values[i]);
      }
      String excelLine = getExcelLine(entry.getKey());
      String refers =
          "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (values.length);
      /***设置下拉框数据**/
      DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
      DataValidation dataValidation = helper.createValidation(constraint, addressList);
      sheet.addValidationData(dataValidation);
      /** 设置列为隐藏 */
      int hiddenIndex = workbook.getSheetIndex(sheetName);
      if (!workbook.isSheetHidden(hiddenIndex)) {
        workbook.setSheetHidden(hiddenIndex, true);
      }
      /***处理Excel兼容性问题**/
      if (dataValidation instanceof XSSFDataValidation) {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
      } else {
        dataValidation.setSuppressDropDownArrow(false);
      }
    }
  }

  /**
   * @param num 列数
   * @return java.lang.String @Description 返回excel列标A-Z-AA-ZZ
   */
  public static String getExcelLine(int num) {
    String line = "";
    int first = num / 26;
    int second = num % 26;
    if (first > 0) {
      line = (char) ('A' + first - 1) + "";
    }
    line += (char) ('A' + second) + "";
    return line;
  }
}

单元格导出多图片（自定义单元格行、高）

package xxx;

import com.alibaba.excel.enums.CellDataTypeEnum;
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 org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * @description 自定义单个单元格多图片导出
 */
public class CustomImageCellWriteHandler implements CellWriteHandler {
  private List<String> repeats = new ArrayList<>();
  //图片需要放置的列号
  private List<Integer> imageColumnIndexs = new ArrayList<>();

  public CustomImageCellWriteHandler(
      List<Integer> imageColumnIndexs) {
    this.imageColumnIndexs = imageColumnIndexs;
  }

  public CustomImageCellWriteHandler() {}

  @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,
      WriteCellData<?> cellData,
      Cell cell,
      Head head,
      Integer relativeRowIndex,
      Boolean isHead) {
    if (isHead) {
      return;
    }
    // 将要插入图片的单元格的type设置为空,下面再填充图片
    if (imageColumnIndexs.contains(cell.getColumnIndex())) {
      cellData.setType(CellDataTypeEnum.EMPTY);
    }
  }

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

    if (isHead || cellDataList == null) {
      return;
    }
    if (!imageColumnIndexs.contains(cell.getColumnIndex())) {
      return;
    }
    String key = cell.getRowIndex() + "_" + cell.getColumnIndex();
    if (repeats.contains(key)) {
      return;
    }
    CellData cellData = cellDataList.get(0);
    String fieldids = cellData.getStringValue();
    if ("".equals(fieldids)) {
      return;
    }
    String[] fieldIdArr = fieldids.split(",");
    // 默认图片 1*2.52(cm)
    // 图片列 13（宽字符） * 60 (高pt)
    sheet.getRow(cell.getRowIndex()).setHeight((short) 1133);
    sheet.setColumnWidth(cell.getColumnIndex(), 256 * 13);
    for (int i = 0; i < fieldIdArr.length; i++) {
      try {
        this.insertImage(sheet, cell, fieldIdArr[i], i);
      } catch (IOException e) {
        throw new RuntimeException(e);
      }
    }
  }

  private void insertImage(Sheet sheet, Cell cell, String fieldid, int i) throws IOException {
    int index = sheet.getWorkbook().addPicture(getImage(fieldid), XSSFWorkbook.PICTURE_TYPE_PNG);
    Drawing drawing = sheet.getDrawingPatriarch();
    if (drawing == null) {
      drawing = sheet.createDrawingPatriarch();
    }
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    ClientAnchor anchor = helper.createClientAnchor();

    // 设置图片位置，图片放置在一个单元格，结束行列无需新增
    // 开始列数
    anchor.setCol1(cell.getColumnIndex());
    // 开始行
    anchor.setRow1(cell.getRowIndex());
    // 结束列数
    anchor.setCol2(cell.getColumnIndex());
    // 结束行
    anchor.setRow2(cell.getRowIndex());
    // 设置图片可以随着单元格移动
    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
    // 图片宽高 2.52*1（cm)
    int picWeight = 907200;
    int picHeight = 360000;
    // 按列排放
    // 左上边界相对左上角偏移量（单位EMU）
    anchor.setDx1(0);
    anchor.setDy1(picHeight * i);
    // 右下边界相对左上角偏移量
    anchor.setDx2(picWeight);
    anchor.setDy2(picHeight * i + picHeight);
    // 插入图片
    Picture pict = drawing.createPicture(anchor, index);
  }

  private byte[] getImage(String fieldid) {
    byte[] bytes = null;
    try {
    //......
      //需要根据业务场景，根据图片路径或图片流保存成byte[]
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return bytes;
  }
}

附：Excel、POI单元格宽高转化

excel行高单位

1、行高：以磅（pt）为单位，默认一行高度15pt=300twips
2、列宽：一个列宽单位等于“常规”样式中一个字符的宽度。对于比例字体，则使用字符“0”（零）的宽度。

POI设置单元格宽高

setColumnWidth（colindex, width）：设置第（colindex+1）列宽度为width个字符，一个字符宽度为256
setHeight 高度单位为twips
1英寸=72磅(pt)=25.4毫米=1440缇
1px = 0.75pt
1px = 15twips
1pt = 20twips

ClientAnchor设置dx,dy偏移量 单位为EMU

1cm = 360000EMUs、1px = 9525EMUs

参考链接：
单元格放置多图片按行排放方案