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);
    }
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

自定义单行表头内容

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

{
  "fileName": "XXX表",
  "propertys": [
    {
      "headerName": "序号",
      "propertyName": "",
      "type": "INT",
      "isLov": false,
      "lovInfoName": ""
    },
    {
      "headerName": "所有人",
      "propertyName": "owner",
      "type": "STRING",
      "isLovInfo": false,
      "lovInfoName": ""
    }
   ]
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

二、读取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);
        }     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

注意表头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);
        }
      }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

导出表头样例

自定义表头样式

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

  
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

自定义多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);
        }
      }
    }
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143

设置宽度自适应（粗略）

注意这个方法一定要在多个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;
        }
      }
    }
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

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

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));
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

多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);
    }
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

单元格设置下拉值显示

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159

附：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

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