涉及依赖
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
参考链接：
单元格放置多图片按行排放方案