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

单元格设置下拉值显示

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();    //设置隐藏sheet处理过多下拉值不显示问题    String sheetName = "hidden";    Sheet providerSheet = workbook.createSheet(sheetName);    workbook.setSheetHidden(1, true);    DataValidationHelper helper = sheet.getDataValidationHelper();    for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {      /***起始行、终止行、起始列、终止列**/      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);      /***处理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）为单位。
2、列宽：一个列宽单位等于“常规”样式中一个字符的宽度。对于比例字体，则使用字符“0”（零）的宽度。

POI设置单元格宽高

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

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

1cm = 360000EMUs、1px = 9525EMUs

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