JAVA导出Excel通用工具——第一篇：详细介绍POI 导出excel的多种复杂情况，包括动态设置筛选、动态合并横向（纵向）单元格等多种复杂情况——保姆级别，真的不能再详细了，封装通用工具类，代码拿来即用）

前言① 关于EasyExcel1. POI1.0 效果展示1.1 第一篇->基础篇1.1.1 基础入门1.1.2 基础优化（筛选）1.1.3 基础优化（合并单元格）1.1.3.1 合并单元格例子1.1.3.2 纵向合并单元格问题1.1.3.3 附本类完整代码：1.1.4 基础优化（下拉）1.2 第二篇->进阶版（动态导出）1.2.1 动态导出（入门->抛出问题）1.2.1.1 假装模拟动态查询1.2.1.2 抛出问题（怎么实现动态设置）1.2.2 动态导出 （优化->动态设置筛选、横向合并行等）1.2.2.1 功能介绍1.2.2.2 看效果1.2.2.3 附代码1.2.3 动态导出 （优化->实现动态处理纵向合并）1.2.3.1 功能介绍1.2.3.2 实现效果1.2.3.3 附核心代码1.3 第三篇->投入使用（封装通用工具类）1.4 第四篇->同时导出多个sheet页1.4.1 先看效果1.4.2 核心代码1.5 第五篇->多对象数据导出到同一sheet页(多table写入)1.5.1 先看效果图1.5.2 核心代码1.6 第六篇->特殊问题篇（解决其他各种问题）1.6.1 属性是枚举问题1.6.2 非枚举映射问题1.6.3 导出注解使用的其他问题1.7 web中导出使用1.7.1 核心代码1.7.2 提供接口1.7.3 测试接口 + 查看数据效果1.7.4 附导出工具类完整代码1.8 代码中需要优化的1.9 附上官网地址：2. 附项目代码

前言

① 关于EasyExcel

想了解 EasyExcel 的，可移步下面这篇文章：

JAVA导出Excel通用工具——第二篇：使用EasyExcel导出excel的多种情况的例子介绍

1. POI

1.0 效果展示

先给出导出的效果，看效果往下选看自己想要实现该效果的例子，效果图如下：

1.1 第一篇->基础篇

1.1.1 基础入门

首先，主要依赖

<!-- POI --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>

效果如下：

代码如下（入门级别，先看怎么实现）：

package com.liu.susu.excel.poi.example.export.example1;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import java.io.FileOutputStream;/*** description** @author susu**/public class TestExport1 {public static void main(String[] args) throws Exception{//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表String sheetName = "测试1";HSSFSheet sheet = workbook.createSheet(sheetName);//3.创建行（表头）Row headRow = sheet.createRow(0);//3-1 创建表头单元格Cell headCell1 = headRow.createCell(0);Cell headCell2 = headRow.createCell(1);//4.创建行（数据行）Row dataRow = sheet.createRow(1);//4-1 创建数据单元格Cell dataCell1 = dataRow.createCell(0);Cell dataCell2 = dataRow.createCell(1);//5. 给单元格赋值headCell1.setCellValue("姓名");headCell2.setCellValue("性别");dataCell1.setCellValue("麦兜");dataCell2.setCellValue("女");//6. 设置单元格的样式//6-1 表头样式HSSFCellStyle headCellStyle = workbook.createCellStyle();headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中headCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中//设置边框headCellStyle.setBorderTop(BorderStyle.THIN);headCellStyle.setBorderBottom(BorderStyle.THIN);headCellStyle.setBorderLeft(BorderStyle.THIN);headCellStyle.setBorderRight(BorderStyle.THIN);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short)12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);headCell1.setCellStyle(headCellStyle);headCell2.setCellStyle(headCellStyle);//6-2 数据样式HSSFCellStyle dataCellStyle = workbook.createCellStyle();dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);dataCellStyle.setBorderTop(BorderStyle.THIN);dataCellStyle.setBorderBottom(BorderStyle.THIN);dataCellStyle.setBorderLeft(BorderStyle.THIN);dataCellStyle.setBorderRight(BorderStyle.THIN);dataCell1.setCellStyle(dataCellStyle);dataCell2.setCellStyle(dataCellStyle);//7.创建问卷输出String filePathAndName = "C:\\Users\\Administrator\\Desktop\\导出excel_1.xlsx";FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}}

1.1.2 基础优化（筛选）

先看实现的效果

核心代码（两种方式都可）

//5.设置筛选//5.1 设置筛选 （方式1）// CellRangeAddress rangeAddress = new CellRangeAddress(0,0,1,2);// sheet.setAutoFilter(rangeAddress);//5.1 设置筛选 （方式2）CellRangeAddress rangeAddress_2 = CellRangeAddress.valueOf("D1:E1");sheet.setAutoFilter(rangeAddress_2);

本测试类代码

package com.liu.susu.excel.poi.example.export.example1;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import java.io.FileOutputStream;import java.util.HashMap;import java.util.Map;/*** description 筛选** @author susu**/public class TestExport2 {public static void main(String[] args) throws Exception{//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表String sheetName = "测试2-筛选";HSSFSheet sheet = workbook.createSheet(sheetName);//3.创建行（表头）Row headRow = sheet.createRow(0);fillInHeadAndSetStyle(headRow,workbook);//填充表头数据 + 设置表头样式//4.创建行（数据行） + 填充表格数据 + 设置表格数据样式fillInDataAndSetStyle(sheet,workbook);//5.设置筛选//5.1 设置筛选 （方式1）// CellRangeAddress rangeAddress = new CellRangeAddress(0,0,1,2);// sheet.setAutoFilter(rangeAddress);//5.1 设置筛选 （方式2）CellRangeAddress rangeAddress_2 = CellRangeAddress.valueOf("D1:E1");sheet.setAutoFilter(rangeAddress_2);//6.创建问卷输出String filePathAndName = "C:\\Users\\Administrator\\Desktop\\筛选-导出excel_2.xlsx";FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :处理表头样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealHeadStyle(HSSFWorkbook workbook){//6-1 表头样式HSSFCellStyle headCellStyle = dealPublicStyle(workbook);headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short)12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);return headCellStyle;}/*** description :处理表格数据样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealDataStyle(HSSFWorkbook workbook){//6-2 数据样式HSSFCellStyle dataCellStyle = dealPublicStyle(workbook);dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return dataCellStyle;}/*** description :表头和表格数据的公共样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealPublicStyle(HSSFWorkbook workbook){HSSFCellStyle publicCellStyle = workbook.createCellStyle();//设置边框publicCellStyle.setBorderTop(BorderStyle.THIN);publicCellStyle.setBorderBottom(BorderStyle.THIN);publicCellStyle.setBorderLeft(BorderStyle.THIN);publicCellStyle.setBorderRight(BorderStyle.THIN);publicCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中publicCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中return publicCellStyle;}/*** description :设置表头数据* @param headRow* @return void* @author susu*/public static void fillInHeadAndSetStyle(Row headRow,HSSFWorkbook workbook){//先获取表头样式HSSFCellStyle headStyle = dealHeadStyle(workbook);for (int i = 0; i < 3; i++) {headRow.createCell(0).setCellValue("姓名");headRow.createCell(1).setCellValue("性别");headRow.createCell(2).setCellValue("品种");headRow.createCell(3).setCellValue("年龄");headRow.createCell(4).setCellValue("颜色");headRow.setRowStyle(headStyle);//设置单元格样式for (Cell cell : headRow){cell.setCellStyle(headStyle);}}}/*** description :填充表格数据* @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static void fillInDataAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);Map<Integer, Map<String, String>> dataMap = getRowData();dataMap.forEach((k,v)->{Row dataRow = sheet.createRow(k);dataRow.createCell(0).setCellValue(v.get("name"));dataRow.createCell(1).setCellValue(v.get("sex"));dataRow.createCell(2).setCellValue(v.get("kind"));dataRow.createCell(3).setCellValue(v.get("age"));dataRow.createCell(4).setCellValue(v.get("colour"));for (Cell cell : dataRow){cell.setCellStyle(dataStyle);}});}/*** description :造数据* @param* @return java.util.Map<java.lang.Integer,java.util.Map<java.lang.String,java.lang.String>>* @author susu*/public static Map<Integer, Map<String,String>> getRowData(){Map<Integer, Map<String, String>> dataMap = new HashMap<>();Map<String, String> dog1 = new HashMap<>();dog1.put("name", "麦兜");dog1.put("sex", "女");dog1.put("kind", "边牧");dog1.put("age", "2");dog1.put("colour", "黑白");Map<String, String> dog2 = new HashMap<>();dog2.put("name", "贝塔");dog2.put("sex", "女");dog2.put("kind", "陨石边牧");dog2.put("age", "2");dog2.put("colour", "棕白");Map<String, String> dog3 = new HashMap<>();dog3.put("name", "小H");dog3.put("sex", "男");dog3.put("kind", "柴犬");dataMap.put(1,dog1);dataMap.put(2,dog2);dataMap.put(3,dog3);dog3.put("age", "6");dog3.put("colour", "土黄");return dataMap;}}

1.1.3 基础优化（合并单元格）

1.1.3.1 合并单元格例子

来，先看官网例子：

一行代码搞定，见名知意，那就比葫芦画瓢吧应用实例 横向合并核心代码：

//横向合并单元格Cell cell_5 = headRow.createCell(5);cell_5.setCellValue("狗狗的两个最好朋友");sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 6));

纵向合并核心代码：

sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 2, 1, 1));

其实上面一行就行，多放出些吧，方便理解：

/*** description :填充表格数据* @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static void fillInDataAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);Map<Integer, Map<String, String>> dataMap = getRowData();dataMap.forEach((k,v)->{Row dataRow = sheet.createRow(k);dataRow.createCell(0).setCellValue(v.get("name"));dataRow.createCell(1).setCellValue(v.get("sex"));dataRow.createCell(2).setCellValue(v.get("kind"));dataRow.createCell(3).setCellValue(v.get("age"));dataRow.createCell(4).setCellValue(v.get("colour"));dataRow.createCell(5).setCellValue(v.get("friend_1"));dataRow.createCell(6).setCellValue(v.get("friend_2"));/*** 第一种方式：使用 addMergedRegionUnsafe* 第二种方式：addMergedRegion* 使用 addMergedRegion 方法纵向合并（纵向合并前，先取消某个范围的合并，然后再次合并，不然报错）*///第一种方式sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 2, 1, 1));//第二种方式// removeMergedRegions(sheet,1,2,1,1);//取消合并// sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));//重新合并for (Cell cell : dataRow){cell.setCellStyle(dataStyle);}});}

效果如下：

只有横向合并的：

横向和纵向合并都有的：

1.1.3.2 纵向合并单元格问题

报错代码写法：

sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));//重新合并

问题：提示单元格已合并，无法合并已经被合并的单元格

如下：

Cannot add merged region B2:B3 to sheet because it overlaps with an existing merged region (B2:B3).

解决问题：先取消某个范围的合并，然后再次合并第一种解决方法：使用addMergedRegionUnsafe方法即可解决

sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 2, 1, 1));

第二种解决方法：先取消某个范围的合并，然后再次合并

/*** description :取消多个合并单元格* @return void* @author susu*/public static void removeMergedRegions(Sheet sheet, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {firstRow = firstRow == null ? sheet.getFirstRowNum() : firstRow;lastRow = lastRow == null ? sheet.getLastRowNum() : lastRow;//查询合并的区域块数int mergeCount = sheet.getNumMergedRegions();//用于保存要移除的那个合并区域List<Integer> removeIndexList = new ArrayList<>();for (int i = 0; i < mergeCount; i++) {//获取第i个合并区域CellRangeAddress rangeAddresses = sheet.getMergedRegion(i);if (firstRow <= rangeAddresses.getFirstRow() && lastRow >= rangeAddresses.getLastRow()&& firstCol <= rangeAddresses.getFirstColumn() && lastCol >= rangeAddresses.getLastColumn()) {removeIndexList.add(i);}}sheet.removeMergedRegions(removeIndexList);}

1.1.3.3 附本类完整代码：

如下：

package com.liu.susu.excel.poi.example.export.example1;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import java.io.FileOutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** description 合并单元格** @author susu**/public class TestExport3 {public static void main(String[] args) throws Exception{//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表String sheetName = "测试3-合并";HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//列宽//3.创建行（表头）fillInHeadAndSetStyle(sheet,workbook);//填充表头数据 + 设置表头样式//4.创建行（数据行） + 填充表格数据 + 设置表格数据样式fillInDataAndSetStyle(sheet,workbook);//5.设置筛选//5.1 设置筛选 （方式1）// CellRangeAddress rangeAddress = new CellRangeAddress(0,0,1,2);// sheet.setAutoFilter(rangeAddress);//5.1 设置筛选 （方式2）CellRangeAddress rangeAddress_2 = CellRangeAddress.valueOf("D1:E1");sheet.setAutoFilter(rangeAddress_2);//6.创建问卷输出String filePathAndName = "C:\\Users\\Administrator\\Desktop\\合并-导出excel_3.xlsx";FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :处理表头样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealHeadStyle(HSSFWorkbook workbook){//6-1 表头样式HSSFCellStyle headCellStyle = dealPublicStyle(workbook);headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short)12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);return headCellStyle;}/*** description :处理表格数据样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealDataStyle(HSSFWorkbook workbook){//6-2 数据样式HSSFCellStyle dataCellStyle = dealPublicStyle(workbook);dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font dataFont = workbook.createFont();//表头字体样式dataFont.setFontName("宋体");dataFont.setFontHeightInPoints((short)11);//字体大小dataCellStyle.setFont(dataFont);return dataCellStyle;}/*** description :表头和表格数据的公共样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealPublicStyle(HSSFWorkbook workbook){HSSFCellStyle publicCellStyle = workbook.createCellStyle();//设置边框publicCellStyle.setBorderTop(BorderStyle.THIN);publicCellStyle.setBorderBottom(BorderStyle.THIN);publicCellStyle.setBorderLeft(BorderStyle.THIN);publicCellStyle.setBorderRight(BorderStyle.THIN);publicCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中publicCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中return publicCellStyle;}/*** description :设置表头数据* @param sheet* @return void* @author susu*/public static void fillInHeadAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表头样式HSSFCellStyle headStyle = dealHeadStyle(workbook);Row headRow = sheet.createRow(0);headRow.createCell(0).setCellValue("姓名");headRow.createCell(1).setCellValue("性别");headRow.createCell(2).setCellValue("品种");headRow.createCell(3).setCellValue("年龄");headRow.createCell(4).setCellValue("颜色");//横向合并单元格Cell cell_5 = headRow.createCell(5);cell_5.setCellValue("狗狗的两个最好朋友");sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 6));headRow.setRowStyle(headStyle);//设置单元格样式for (Cell cell : headRow){cell.setCellStyle(headStyle);}}/*** description :填充表格数据* @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static void fillInDataAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);Map<Integer, Map<String, String>> dataMap = getRowData();dataMap.forEach((k,v)->{Row dataRow = sheet.createRow(k);dataRow.createCell(0).setCellValue(v.get("name"));dataRow.createCell(1).setCellValue(v.get("sex"));dataRow.createCell(2).setCellValue(v.get("kind"));dataRow.createCell(3).setCellValue(v.get("age"));dataRow.createCell(4).setCellValue(v.get("colour"));dataRow.createCell(5).setCellValue(v.get("friend_1"));dataRow.createCell(6).setCellValue(v.get("friend_2"));/*** 第一种方式：使用 addMergedRegionUnsafe* 第二种方式：addMergedRegion* 使用 addMergedRegion 方法纵向合并（纵向合并前，先取消某个范围的合并，然后再次合并，不然报错）*///第一种方式sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 2, 1, 1));//第二种方式// removeMergedRegions(sheet,1,2,1,1);//取消合并// sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));//重新合并for (Cell cell : dataRow){cell.setCellStyle(dataStyle);}});}/*** description :造数据* @param* @return java.util.Map<java.lang.Integer,java.util.Map<java.lang.String,java.lang.String>>* @author susu*/public static Map<Integer, Map<String,String>> getRowData(){Map<Integer, Map<String, String>> dataMap = new HashMap<>();Map<String, String> dog1 = new HashMap<>();dog1.put("name", "麦兜");dog1.put("sex", "女");dog1.put("kind", "边牧");dog1.put("age", "2");dog1.put("colour", "黑白");dog1.put("friend_1", "酥妮");dog1.put("friend_2", "黑豆");Map<String, String> dog2 = new HashMap<>();dog2.put("name", "贝塔");dog2.put("sex", "女");dog2.put("kind", "陨石边牧");dog2.put("age", "2");dog2.put("colour", "棕白");dog2.put("friend_1", "麦兜");dog2.put("friend_2", "大豆");Map<String, String> dog3 = new HashMap<>();dog3.put("name", "小H");dog3.put("sex", "男");dog3.put("kind", "柴犬");dog3.put("age", "6");dog3.put("colour", "土黄");dog3.put("friend_1", "apple");dog3.put("friend_2", "iphone");dataMap.put(1,dog1);dataMap.put(2,dog2);dataMap.put(3,dog3);return dataMap;}/*** description :取消多个合并单元格* @return void* @author susu*/public static void removeMergedRegions(Sheet sheet, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {firstRow = firstRow == null ? sheet.getFirstRowNum() : firstRow;lastRow = lastRow == null ? sheet.getLastRowNum() : lastRow;//查询合并的区域块数int mergeCount = sheet.getNumMergedRegions();//用于保存要移除的那个合并区域List<Integer> removeIndexList = new ArrayList<>();for (int i = 0; i < mergeCount; i++) {//获取第i个合并区域CellRangeAddress rangeAddresses = sheet.getMergedRegion(i);if (firstRow <= rangeAddresses.getFirstRow() && lastRow >= rangeAddresses.getLastRow()&& firstCol <= rangeAddresses.getFirstColumn() && lastCol >= rangeAddresses.getLastColumn()) {removeIndexList.add(i);}}sheet.removeMergedRegions(removeIndexList);}}

1.1.4 基础优化（下拉）

下拉 核心代码

在上面合并单元格TestExport3.java的基础上，加了一段代码，其他没改，所以只附上这段代码即可，整个类就不重复粘贴了

/*** description :填充表格数据* @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static void fillInDataAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);Map<Integer, Map<String, String>> dataMap = getRowData();dataMap.forEach((k,v)->{Row dataRow = sheet.createRow(k);dataRow.createCell(0).setCellValue(v.get("name"));dataRow.createCell(1).setCellValue(v.get("sex"));dataRow.createCell(2).setCellValue(v.get("kind"));dataRow.createCell(3).setCellValue(v.get("age"));dataRow.createCell(4).setCellValue(v.get("colour"));dataRow.createCell(5).setCellValue(v.get("friend_1"));dataRow.createCell(6).setCellValue(v.get("friend_2"));/*** 第一种方式：使用 addMergedRegionUnsafe* 第二种方式：addMergedRegion* 使用 addMergedRegion 方法纵向合并（纵向合并前，先取消某个范围的合并，然后再次合并，不然报错）*///第一种方式sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 2, 1, 1));//第二种方式// removeMergedRegions(sheet,1,2,1,1);//取消合并// sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));//重新合并//设置下拉列表CellRangeAddressList addressList = new CellRangeAddressList(1, 10, 2, 2);String [] selectData = {"边牧","陨石边牧","柴犬","拉布拉多"};DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectData);DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);dataValidation.setSuppressDropDownArrow(false);sheet.addValidationData(dataValidation);for (Cell cell : dataRow){cell.setCellStyle(dataStyle);}});}

下拉 效果如图：

1.2 第二篇->进阶版（动态导出）

1.2.1 动态导出（入门->抛出问题）

1.2.1.1 假装模拟动态查询

模拟动态查询数据库 先来个实体

构造数据，小学生级别代码，截个简单图看看就行

导出类 DynamicExport1.java ，这个是在上面 TestExport4.java 的基础上做了简单修改，简单看看吧，主要是看抛出的问题，根据问题怎么真正实现动态设置

package com.liu.susu.excel.poi.example.export.example2;import com.liu.susu.excel.poi.example.export.example2.data.DogEntity;import com.liu.susu.excel.poi.example.export.example2.data.MakeDogData;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import java.io.FileOutputStream;import java.util.List;/*** description 动态获取数据导出** @author susu**/public class DynamicExport1 {public static void main(String[] args) throws Exception{//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表String sheetName = "动态测试1-简单";HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//列宽//3.创建行（表头）fillInHeadAndSetStyle(sheet,workbook);//填充表头数据 + 设置表头样式//4.创建行（数据行） + 填充表格数据 + 设置表格数据样式fillInDataAndSetStyle(sheet,workbook);//5.设置筛选//5.1 设置筛选 （方式1）CellRangeAddress rangeAddress = new CellRangeAddress(0,0,1,4);sheet.setAutoFilter(rangeAddress);//6.创建问卷输出String filePathAndName = "C:\\Users\\Administrator\\Desktop\\excel导出\\动态-导出excel_1.xlsx";FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :处理表头样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealHeadStyle(HSSFWorkbook workbook){//6-1 表头样式HSSFCellStyle headCellStyle = dealPublicStyle(workbook);headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short)12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);return headCellStyle;}/*** description :处理表格数据样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealDataStyle(HSSFWorkbook workbook){//6-2 数据样式HSSFCellStyle dataCellStyle = dealPublicStyle(workbook);dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font dataFont = workbook.createFont();//表头字体样式dataFont.setFontName("宋体");dataFont.setFontHeightInPoints((short)11);//字体大小dataCellStyle.setFont(dataFont);return dataCellStyle;}/*** description :表头和表格数据的公共样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealPublicStyle(HSSFWorkbook workbook){HSSFCellStyle publicCellStyle = workbook.createCellStyle();//设置边框publicCellStyle.setBorderTop(BorderStyle.THIN);publicCellStyle.setBorderBottom(BorderStyle.THIN);publicCellStyle.setBorderLeft(BorderStyle.THIN);publicCellStyle.setBorderRight(BorderStyle.THIN);publicCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中publicCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中return publicCellStyle;}/*** description :设置表头数据* @param sheet* @return void* @author susu*/public static void fillInHeadAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表头样式HSSFCellStyle headStyle = dealHeadStyle(workbook);Row headRow = sheet.createRow(0);//问题1：如果动态获取 需要知道表头的列数 以及获取动态获取每列的内容headRow.createCell(0).setCellValue("姓名");headRow.createCell(1).setCellValue("性别");headRow.createCell(2).setCellValue("品种");headRow.createCell(3).setCellValue("年龄");headRow.createCell(4).setCellValue("颜色");//问题2：横向合并单元格（如果动态设置：需要知道合并单元格所在的行数 要合并的起始列 和 结束列）Cell cell_5 = headRow.createCell(5);cell_5.setCellValue("狗狗的两个最好朋友");sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 6));headRow.setRowStyle(headStyle);//设置单元格样式for (Cell cell : headRow){cell.setCellStyle(headStyle);}}/*** description :填充表格数据* @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static void fillInDataAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);List<DogEntity> dataList = MakeDogData.getDogs(); if (dataList!=null && dataList.size()>0){for (int i = 0; i < dataList.size(); i++) {Row dataRow = sheet.createRow(i+1);//问题3：如果动态获取 需要知道列数 以及获取动态获取每列的内容dataRow.createCell(0).setCellValue(dataList.get(i).getName());dataRow.createCell(1).setCellValue(dataList.get(i).getSex());dataRow.createCell(2).setCellValue(dataList.get(i).getKind());dataRow.createCell(3).setCellValue(dataList.get(i).getAge());dataRow.createCell(4).setCellValue(dataList.get(i).getColour());dataRow.createCell(5).setCellValue(dataList.get(i).getFriendOne());dataRow.createCell(6).setCellValue(dataList.get(i).getFriendTwo());//问题4：纵向合并（如果是动态，首先，1.哪一列 2.此列分组查询 3.相同内容则合并 4.相同内容的起始行和结束行）sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 2, 1, 1));//问题5：设置下拉（如果动态：主要是知道设置的哪一列 以及设置多少行）setSelectData(sheet,1, 1000, 2, 2);//设置单元格样式for (Cell cell : dataRow){cell.setCellStyle(dataStyle);}}}}/*** description :设置下拉（如果动态：主要是知道设置的哪一列 以及设置多少行）* @param sheet* @param firstRow* @param lastRow* @param firstCol* @param lastCol* @return void* @author susu*/public static void setSelectData(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){//设置下拉列表CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);String [] selectData = {"边牧","陨石边牧","柴犬","拉布拉多"};DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectData);DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);dataValidation.setSuppressDropDownArrow(false);sheet.addValidationData(dataValidation);}}

1.2.1.2 抛出问题（怎么实现动态设置）

在上面的代码里，抛出的问题代码，如下：

在实际开发种，看到上面代码的心情：

三字，一坨屎！！，上面的几个问题如果不解决，真的是每写一次导出就会难受一次，写一次吐槽一次，不想写，怎么解决，往下看吧

1.2.2 动态导出 （优化->动态设置筛选、横向合并行等）

1.2.2.1 功能介绍

本节主要实现：

动态获取表头 + 动态获取数据 + 动态设置筛选 + 动态设置横向合并+ 动态设置下拉数据实现方法：

通过自定义注解 + 放射

1.2.2.2 看效果

如下：

1.2.2.3 附代码

核心代码 + 附实现功能代码： 两个注解ExcelExportField.java和ExcelExportCellRange.java

package com.liu.susu.excel.poi.annotation;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/*** description 导出excel** @author susu**/@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelExportCellRange {/*** 横向合并标志*/boolean horizontalMergerFlag() default false;/*** 横向合并的列数*/int horizontalMergerColNum() default 2;}

package com.liu.susu.excel.poi.annotation;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/*** description 导出excel** @author susu**/@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelExportField {/*** 字段名称*/String value() default "";/*** 表头筛选标志*/boolean autoFilterFlag() default false;/*** 设置下拉选项的值*/String [] selectData() default {""};/*** 设置横向合并参数*/ExcelExportCellRange dealHorizontalMerger() default @ExcelExportCellRange();}

实体类（加注解的实体）

实体字段属性设置参数类ExcelClassField.java

package com.liu.susu.excel.poi.fields;import lombok.Data;/*** description 实体字段属性设置参数类** @author susu**/@Datapublic class ExcelClassField {/*** 字段名称（英文）*/private String fieldEnName;/*** 字段名称（汉）*/private String fieldCnName;/*** 表头筛选标志*/private boolean autoFilterFlag;/*** 设置下拉选项的值*/private String [] selectData;/*** 横向合并标志*/private boolean horizontalMergerFlag;/*** 横向合并的列数*/private int horizontalMergerColNum;}

导出测试类DynamicExport2.java

package com.liu.susu.excel.poi.example.export.example2;import com.liu.susu.excel.poi.annotation.ExcelExportCellRange;import com.liu.susu.excel.poi.annotation.ExcelExportField;import com.liu.susu.excel.poi.example.export.example2.data.DogEntity;import com.liu.susu.excel.poi.example.export.example2.data.MakeDogData;import com.liu.susu.excel.poi.fields.ExcelClassField;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import java.io.FileOutputStream;import java.lang.reflect.Field;import java.util.*;/*** description 动态获取表头 + 动态获取数据 + 动态设置筛选 + 动态设置横向合并 + 动态设置下拉数据** @author susu**/public class DynamicExport2 {public static void main(String[] args) throws Exception{//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表String sheetName = "动态测试2-动态";HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//列宽//3.创建行（表头）-->动态填充表头数据 + 设置表头样式 + 动态设置筛选 +动态设置横向合并fillInHeadAndSetStyle(sheet,workbook);//4.创建行（数据行） --> 动态填充表格数据 + 设置表格数据样式 + 动态设置下拉数据fillInDataAndSetStyle(sheet,workbook);//5.创建问卷输出String filePathAndName = "C:\\Users\\Administrator\\Desktop\\excel导出\\动态-导出excel_2.xlsx";FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :处理表头样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealHeadStyle(HSSFWorkbook workbook){//6-1 表头样式HSSFCellStyle headCellStyle = dealPublicStyle(workbook);headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short)12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);return headCellStyle;}/*** description :处理表格数据样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealDataStyle(HSSFWorkbook workbook){//6-2 数据样式HSSFCellStyle dataCellStyle = dealPublicStyle(workbook);dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font dataFont = workbook.createFont();//表头字体样式dataFont.setFontName("宋体");dataFont.setFontHeightInPoints((short)11);//字体大小dataCellStyle.setFont(dataFont);return dataCellStyle;}/*** description :表头和表格数据的公共样式* @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealPublicStyle(HSSFWorkbook workbook){HSSFCellStyle publicCellStyle = workbook.createCellStyle();//设置边框publicCellStyle.setBorderTop(BorderStyle.THIN);publicCellStyle.setBorderBottom(BorderStyle.THIN);publicCellStyle.setBorderLeft(BorderStyle.THIN);publicCellStyle.setBorderRight(BorderStyle.THIN);publicCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中publicCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中return publicCellStyle;}/*** description :设置表头数据* @param sheet* @return void* @author susu*/public static void fillInHeadAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表头样式HSSFCellStyle headStyle = dealHeadStyle(workbook);Row headRow = sheet.createRow(0);//1：动态获取 并 动态动态填充表头数据List<ExcelClassField> headDataList = getHeadDataList(DogEntity.class);for (int i = 0; i < headDataList.size(); i++) {ExcelClassField headClassField = headDataList.get(i);Cell headCell = headRow.createCell(i);headCell.setCellValue(headClassField.getFieldCnName());if (headClassField.isAutoFilterFlag()){//2：动态 设置筛选(当然，如果表头都需要设置筛选，此处可以不用动态，按前面基础入门的处理方式即可)CellRangeAddress rangeAddress = new CellRangeAddress(0,0,i,i);sheet.setAutoFilter(rangeAddress);}if (headClassField.isHorizontalMergerFlag()){//3. 动态处理 横向合并单元格int lastCol = i+headClassField.getHorizontalMergerColNum()-1;sheet.addMergedRegion(new CellRangeAddress(0, 0, i, lastCol));}headCell.setCellStyle(headStyle);//设置单元格样式}}/*** description :根据实体获取表头数据* @param* @return java.util.List<com.liu.susu.excel.poi.fields.ExcelClassField>* @author susu*/public static List<ExcelClassField> getHeadDataList(Class<?> clazz){List<Field> fieldList = new ArrayList<>();Collections.addAll(fieldList, clazz.getDeclaredFields());List<ExcelClassField> headDataList = new ArrayList<>();for (Field field : fieldList) {ExcelClassField classField = new ExcelClassField();classField.setFieldEnName(field.getName());ExcelExportField exportField = field.getAnnotation(ExcelExportField.class);if (exportField != null) {classField.setFieldCnName(exportField.value());classField.setAutoFilterFlag(exportField.autoFilterFlag());classField.setSelectData(exportField.selectData());ExcelExportCellRange excelExportCellRange = exportField.dealHorizontalMerger();classField.setHorizontalMergerFlag(excelExportCellRange.horizontalMergerFlag());classField.setHorizontalMergerColNum(excelExportCellRange.horizontalMergerColNum());} else {classField.setFieldCnName(field.getName());}headDataList.add(classField);}return headDataList;}/*** description :填充表格数据* @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static void fillInDataAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);//4：动态获取 并 动态动态填充表格数据List<DogEntity> dataList = MakeDogData.getDogs();//获取查询数据List<ExcelClassField> headDataList = getHeadDataList(DogEntity.class);//获取表头数据if (dataList!=null && dataList.size()>0){for (int i = 0; i < dataList.size(); i++) {Row dataRow = sheet.createRow(i+1);DogEntity dog = dataList.get(i);Map<String, Object> fieldDataMap = changeRowDataToFieldDataMap(dog);for (int j = 0; j < headDataList.size(); j++) {Cell dataCell = dataRow.createCell(j);ExcelClassField classField = headDataList.get(j);Object value = fieldDataMap.get(classField.getFieldEnName());String cellValue = value !=null?value.toString():"";dataCell.setCellValue(cellValue);//5：动态 设置某列有下拉数据（后续可以根据是否模板导出控制结束行数）if (classField.getSelectData()!=null){setSelectData(sheet,classField.getSelectData(),1, 1000, j, j);}//6：动态处理 纵合并单元格（下一个单独解决的问题）//设置单元格样式dataCell.setCellStyle(dataStyle);}}}}public static <T> Map<String,Object> changeRowDataToFieldDataMap(T t){Map<String, Object> fieldDataMap = new HashMap<>();Field[] fields = t.getClass().getDeclaredFields();try {for (Field field : fields) {field.setAccessible(true);String fieldEnName = field.getName();fieldDataMap.put(fieldEnName, field.get(t));}} catch (IllegalArgumentException | IllegalAccessException e) {e.printStackTrace();}return fieldDataMap;}/*** description :设置下拉（如果动态：主要是知道设置的哪一列 以及设置多少行）* @param sheet* @param firstRow* @param lastRow* @param firstCol* @param lastCol* @return void* @author susu*/public static void setSelectData(HSSFSheet sheet,String [] selectData,int firstRow, int lastRow, int firstCol, int lastCol){CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectData);DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);dataValidation.setSuppressDropDownArrow(false);sheet.addValidationData(dataValidation);}}

1.2.3 动态导出 （优化->实现动态处理纵向合并）

1.2.3.1 功能介绍

本次优化的主要功能：

主要是在上面1.2.2的基础上，增加纵向合并单元格的功能（动态地）

1.2.3.2 实现效果

如图：

1.2.3.3 附核心代码

因为是在上面 1.2.2 的基础上改动的，所以改动的点不是很多，把改动地方和核心代码附上即可，其他代码不需要重复贴上了

① 为了测试方面，再添加几条数据

② 注解里再添加一个属性 以及 实体里使用该注解属性

ExcelExportField.java

ExcelClassField.java

DogEntity.java

③ 最后就是导出类DynamicExport3.java里核心代码的逻辑处理 获取数据的时候排序：

核心代码处理如下：

/*** description :填充表格数据* @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static void fillInDataAndSetStyle(HSSFSheet sheet,HSSFWorkbook workbook){//先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);//4：动态获取 并 动态动态填充表格数据List<DogEntity> dataList = MakeDogData.getDogs();//获取查询数据//先根据狗狗种类进行排序（主要是为了excel可以合并）--当然，连数据库后，排序可通过sql实现Collections.sort(dataList, new Comparator<DogEntity>(){@Overridepublic int compare(DogEntity o1, DogEntity o2) {return o1.getKind().compareTo(o2.getKind());}});List<ExcelClassField> headDataList = getHeadDataList(DogEntity.class);//获取表头数据if (dataList!=null && dataList.size()>0){int firstRow = 0;int rowMergerNum[] = new int [headDataList.size()];for (int i = 0; i < dataList.size(); i++) {Row dataRow = sheet.createRow(i+1);DogEntity dog = dataList.get(i);Map<String, Object> fieldDataMap = changeRowDataToFieldDataMap(dog);//int rowMergerNum = 0;for (int j = 0; j < headDataList.size(); j++) {Cell dataCell = dataRow.createCell(j);ExcelClassField classField = headDataList.get(j);Object value = fieldDataMap.get(classField.getFieldEnName());String cellValue = value !=null?value.toString():"";dataCell.setCellValue(cellValue);//5：动态 设置某列有下拉数据（后续可以根据是否模板导出控制结束行数）if (classField.getSelectData()!=null){setSelectData(sheet,classField.getSelectData(),1, 1000, j, j);}//6：动态处理 纵合并单元格（下一个单独解决的问题）if (classField.isAutoVerticalMergerFlag()){String previousRowValue = "";/*** 如果此列数据设置了纵向合并* 找开始行 和 结束行-->判断上一行的数据和此行数据是否一致，一致则列为要合并的区域*/if (i>0){previousRowValue = sheet.getRow(i).getCell(j).getStringCellValue();}if (previousRowValue.equals(cellValue)){rowMergerNum[j] ++;}else {firstRow = i+1;rowMergerNum[j]=0;}}if (rowMergerNum[j] > 0) {sheet.addMergedRegionUnsafe(new CellRangeAddress(firstRow, (firstRow+rowMergerNum[j]), j, j));}//设置单元格样式dataCell.setCellStyle(dataStyle);}}}}

1.3 第三篇->投入使用（封装通用工具类）

① 先看效果，顺便抛出个问题：怎么实现同时导出多个sheet页，看完这篇再往下继续吧

② 核心代码 为了测试，新加一个实体CatEntity.java，并添加构造数据

重要的是通用工具类ExcelExportUtils1.java，如果上面看的完完整整，明明白白，这个应该就是在汤里加点葱花的意思，就是在 上面DynamicExport3.java的基础上改的，简单截一个程序入口图看看就行，其他方法类似处理：

ExcelExportUtils1.java 的完整代码如下：

package com.liu.susu.excel.poi.utils;import com.liu.susu.excel.poi.annotation.ExcelExportCellRange;import com.liu.susu.excel.poi.annotation.ExcelExportField;import com.liu.susu.excel.poi.example.export.example2.data.CatEntity;import com.liu.susu.excel.poi.example.export.example2.data.DogEntity;import com.liu.susu.excel.poi.example.export.example2.data.MakeCatData;import com.liu.susu.excel.poi.example.export.example2.data.MakeDogData;import com.liu.susu.excel.poi.fields.ExcelClassField;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import java.io.FileOutputStream;import java.lang.reflect.Field;import java.util.*;/*** description 支持动态（动态筛选、合并等）导出excel工具类** @author susu**/public class ExcelExportUtils1 {public static void main(String[] args) throws Exception {String sheetName = "ExcelExportUtils1";String filePathAndName = "C:\\Users\\Administrator\\Desktop\\excel导出\\动态-导出-dog.xlsx";List<DogEntity> dataList = MakeDogData.getDogs();//获取查询数据excelExportProc(sheetName, filePathAndName, dataList, DogEntity.class);///导出猫猫数据String sheetName2 = "ExcelExportUtils2";String filePathAndName2 = "C:\\Users\\Administrator\\Desktop\\excel导出\\动态-导出-cat.xlsx";List<CatEntity> dataList2 = MakeCatData.getCats();//获取查询数据excelExportProc(sheetName2, filePathAndName2, dataList2, CatEntity.class);}/*** description :excel导出程序入口** @param sheetName* @param filePathAndName* @return void* @author susu*/private static <T> void excelExportProc(String sheetName, String filePathAndName, List<T> dataList, Class<T> clazz) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//设置列宽//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, dataList, clazz);//4.创建问卷输出FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :动态填充表头和表格数据 以及各种动态设置处理** @param sheet* @param workbook* @return void* @author susu*/private static <T> void fillInExcelAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<T> dataList, Class<?> clazz) {//1.创建行（表头）-->动态填充表头数据 + 设置表头样式 + 动态设置筛选 +动态设置横向合并fillInHeadAndSetStyle(sheet, workbook, clazz);//2.创建行（数据行） --> 动态填充表格数据 + 设置表格数据样式 + 动态设置下拉数据fillInDataAndSetStyle(sheet, workbook, dataList);}/*** description :设置表头数据** @param sheet* @return void* @author susu*/public static void fillInHeadAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, Class<?> clazz) {//先获取表头样式HSSFCellStyle headStyle = dealHeadStyle(workbook);Row headRow = sheet.createRow(0);//1：动态获取 并 动态动态填充表头数据List<ExcelClassField> headDataList = getHeadDataList(clazz);for (int i = 0; i < headDataList.size(); i++) {ExcelClassField headClassField = headDataList.get(i);Cell headCell = headRow.createCell(i);headCell.setCellValue(headClassField.getFieldCnName());if (headClassField.isAutoFilterFlag()) {//2：动态 设置筛选(当然，如果表头都需要设置筛选，此处可以不用动态，按前面基础入门的处理方式即可)CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, i, i);sheet.setAutoFilter(rangeAddress);}if (headClassField.isHorizontalMergerFlag()) {//3. 动态处理 横向合并单元格int lastCol = i + headClassField.getHorizontalMergerColNum() - 1;sheet.addMergedRegion(new CellRangeAddress(0, 0, i, lastCol));}headCell.setCellStyle(headStyle);//设置单元格样式}}/*** description :填充表格数据** @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static <T> void fillInDataAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<T> dataList) {if (dataList != null && dataList.size() > 0) {//1.先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);//2：动态动态填充表格数据List<ExcelClassField> headDataList = getHeadDataList(dataList.get(0).getClass());//获取表头数据int firstRow = 0;int rowMergerNum[] = new int[headDataList.size()];for (int i = 0; i < dataList.size(); i++) {Row row = sheet.createRow(i + 1);T rowData = dataList.get(i);Map<String, Object> fieldDataMap = changeRowDataToFieldDataMap(rowData);for (int j = 0; j < headDataList.size(); j++) {Cell dataCell = row.createCell(j);ExcelClassField classField = headDataList.get(j);Object value = fieldDataMap.get(classField.getFieldEnName());String cellValue = value != null ? value.toString() : "";dataCell.setCellValue(cellValue);//5：动态 设置某列有下拉数据（后续可以根据是否模板导出控制结束行数）if (classField.getSelectData() != null) {setSelectData(sheet, classField.getSelectData(), 1, 1000, j, j);}//6：动态处理 纵合并单元格（下一个单独解决的问题）if (classField.isAutoVerticalMergerFlag()) {/*** 如果此列数据设置了纵向合并* 找开始行 和 结束行-->判断上一行的数据和此行数据是否一致，一致则列为要合并的区域*/String previousRowValue = sheet.getRow(i).getCell(j).getStringCellValue();if (previousRowValue.equals(cellValue)) {rowMergerNum[j]++;} else {firstRow = i + 1;rowMergerNum[j] = 0;}}if (rowMergerNum[j] > 0) {sheet.addMergedRegionUnsafe(new CellRangeAddress(firstRow, (firstRow + rowMergerNum[j]), j, j));}//设置单元格样式dataCell.setCellStyle(dataStyle);}}}}/*** description :处理表头样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealHeadStyle(HSSFWorkbook workbook) {//6-1 表头样式HSSFCellStyle headCellStyle = dealPublicStyle(workbook);headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short) 12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);return headCellStyle;}/*** description :处理表格数据样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealDataStyle(HSSFWorkbook workbook) {//6-2 数据样式HSSFCellStyle dataCellStyle = dealPublicStyle(workbook);dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font dataFont = workbook.createFont();//表头字体样式dataFont.setFontName("宋体");dataFont.setFontHeightInPoints((short) 11);//字体大小dataCellStyle.setFont(dataFont);return dataCellStyle;}/*** description :表头和表格数据的公共样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealPublicStyle(HSSFWorkbook workbook) {HSSFCellStyle publicCellStyle = workbook.createCellStyle();//设置边框publicCellStyle.setBorderTop(BorderStyle.THIN);publicCellStyle.setBorderBottom(BorderStyle.THIN);publicCellStyle.setBorderLeft(BorderStyle.THIN);publicCellStyle.setBorderRight(BorderStyle.THIN);publicCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中publicCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中return publicCellStyle;}/*** description :根据实体获取表头数据** @param* @return java.util.List<com.liu.susu.excel.poi.fields.ExcelClassField>* @author susu*/public static List<ExcelClassField> getHeadDataList(Class<?> clazz) {List<Field> fieldList = new ArrayList<>();Collections.addAll(fieldList, clazz.getDeclaredFields());List<ExcelClassField> headDataList = new ArrayList<>();for (Field field : fieldList) {ExcelClassField classField = new ExcelClassField();classField.setFieldEnName(field.getName());ExcelExportField exportField = field.getAnnotation(ExcelExportField.class);if (exportField != null) {classField.setFieldCnName(exportField.value());classField.setAutoFilterFlag(exportField.autoFilterFlag());classField.setSelectData(exportField.selectData());ExcelExportCellRange excelExportCellRange = exportField.dealHorizontalMerger();classField.setHorizontalMergerFlag(excelExportCellRange.horizontalMergerFlag());classField.setHorizontalMergerColNum(excelExportCellRange.horizontalMergerColNum());classField.setAutoVerticalMergerFlag(exportField.autoVerticalMergerFlag());} else {classField.setFieldCnName(field.getName());}headDataList.add(classField);}return headDataList;}/*** description :数据行转列结果用map存储* @param t* @return java.util.Map<java.lang.String,java.lang.Object>* @author susu*/public static <T> Map<String, Object> changeRowDataToFieldDataMap(T t) {Map<String, Object> fieldDataMap = new HashMap<>();Field[] fields = t.getClass().getDeclaredFields();try {for (Field field : fields) {field.setAccessible(true);String fieldEnName = field.getName();fieldDataMap.put(fieldEnName, field.get(t));}} catch (IllegalArgumentException | IllegalAccessException e) {e.printStackTrace();}return fieldDataMap;}/*** description :设置下拉（如果动态：主要是知道设置的哪一列 以及设置多少行）** @param sheet* @param firstRow* @param lastRow* @param firstCol* @param lastCol* @return void* @author susu*/public static void setSelectData(HSSFSheet sheet, String[] selectData, int firstRow, int lastRow, int firstCol, int lastCol) {CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectData);DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);dataValidation.setSuppressDropDownArrow(false);sheet.addValidationData(dataValidation);}}

1.4 第四篇->同时导出多个sheet页

1.4.1 先看效果

如图：

1.4.2 核心代码

改动的少，主要在ExcelExportUtils1.java代码的基础上进行改动的，修改和添加代码如下：

public static void main(String[] args) throws Exception {String filePathAndName = "C:\\Users\\Administrator\\Desktop\\excel导出\\多sheet-导出-dogAndCat.xlsx";String sheetName = "dog数据";List<DogEntity> dogList = MakeDogData.getDogs();//获取狗狗数据String sheetName2 = "cat数据";List<CatEntity> catList = MakeCatData.getCats();//获取小猫数据Map<String, List<?>> sheetMap = new HashMap<>();sheetMap.put(sheetName,dogList);sheetMap.put(sheetName2,catList);exportOneMoreSheetProc(sheetMap,filePathAndName);}private static <T> void exportOneMoreSheetProc(Map<String, List<?>> sheetMap, String filePathAndName) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();sheetMap.forEach((k,v)->{//2.创建工作表HSSFSheet sheet = workbook.createSheet(k);sheet.setDefaultColumnWidth(12);//设置列宽//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, v,null);});//4.创建问卷输出FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}

1.5 第五篇->多对象数据导出到同一sheet页(多table写入)

1.5.1 先看效果图

如图：

1.5.2 核心代码

这个是在 ExcelExportUtils2.java 的基础上修改的，核心代码如下：

package com.liu.susu.excel.poi.utils;import com.liu.susu.excel.poi.annotation.ExcelExportCellRange;import com.liu.susu.excel.poi.annotation.ExcelExportField;import com.liu.susu.excel.poi.example.export.example2.data.CatEntity;import com.liu.susu.excel.poi.example.export.example2.data.DogEntity;import com.liu.susu.excel.poi.example.export.example2.data.MakeCatData;import com.liu.susu.excel.poi.example.export.example2.data.MakeDogData;import com.liu.susu.excel.poi.fields.ExcelClassField;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import java.io.FileOutputStream;import java.lang.reflect.Field;import java.util.*;/*** description 同一sheet页导出多个list** @author susu**/public class ExcelExportUtils3 {public static void main(String[] args) throws Exception {String filePathAndName = "C:\\Users\\Administrator\\Desktop\\excel导出\\多list-导出-dogAndCat.xlsx";String sheetName = "dog and cat数据";List<DogEntity> dogList = MakeDogData.getDogs();//获取狗狗数据List<CatEntity> catList = MakeCatData.getCats();//获取小猫数据Map<Integer, List<?>> dataMap = new HashMap<>();dataMap.put(1, dogList);dataMap.put(2, catList);exportOneMoreListProc(dataMap, sheetName, filePathAndName);}private static <T> void exportOneMoreListProc(Map<Integer, List<?>> dataMap, String sheetName, String filePathAndName) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//设置列宽dataMap.forEach((k, v) -> {//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, v, null);});//4.创建问卷输出FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :excel导出程序入口(支持导出多个sheet页)** @param sheetMap* @param filePathAndName* @return void* @author susu*/private static <T> void exportOneMoreSheetProc(Map<String, List<?>> sheetMap, String filePathAndName) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();sheetMap.forEach((k, v) -> {//2.创建工作表HSSFSheet sheet = workbook.createSheet(k);sheet.setDefaultColumnWidth(12);//设置列宽//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, v, null);});//4.创建问卷输出FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :excel导出程序入口** @param sheetName* @param filePathAndName* @return void* @author susu*/private static <T> void excelExportProc(String sheetName, String filePathAndName, List<T> dataList, Class<T> clazz) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//设置列宽//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, dataList, clazz);//4.创建问卷输出FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :动态填充表头和表格数据 以及各种动态设置处理** @param sheet* @param workbook* @return void* @author susu*/private static <T> void fillInExcelAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<T> dataList, Class<?> clazz) {if (dataList != null && dataList.size() > 0) {if (clazz == null) {clazz = dataList.get(0).getClass();}}if (clazz != null) {List<ExcelClassField> headDataList = getHeadDataList(clazz);//获取表头数据//1.创建行（表头）-->动态填充表头数据 + 设置表头样式 + 动态设置筛选 +动态设置横向合并fillInHeadAndSetStyle(sheet, workbook, headDataList);//2.创建行（数据行） --> 动态填充表格数据 + 设置表格数据样式 + 动态设置下拉数据fillInDataAndSetStyle(sheet, workbook, dataList);}}/*** description :设置表头数据** @param sheet* @return void* @author susu*/public static void fillInHeadAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<ExcelClassField> headDataList) {//先获取表头样式HSSFCellStyle headStyle = dealHeadStyle(workbook);int lastRowNum = sheet.getLastRowNum();Row headRow = sheet.createRow(lastRowNum + 1);//1：动态动态填充表头数据for (int i = 0; i < headDataList.size(); i++) {ExcelClassField headClassField = headDataList.get(i);Cell headCell = headRow.createCell(i);headCell.setCellValue(headClassField.getFieldCnName());if (headClassField.isAutoFilterFlag()) {//2：动态 设置筛选(当然，如果表头都需要设置筛选，此处可以不用动态，按前面基础入门的处理方式即可)if (headRow.getRowNum() == 0) {CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, i, i);sheet.setAutoFilter(rangeAddress);}}if (headClassField.isHorizontalMergerFlag()) {//3. 动态处理 横向合并单元格int lastCol = i + headClassField.getHorizontalMergerColNum() - 1;sheet.addMergedRegion(new CellRangeAddress(headRow.getRowNum(), headRow.getRowNum(), i, lastCol));}headCell.setCellStyle(headStyle);//设置单元格样式}}/*** description :填充表格数据** @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static <T> void fillInDataAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<T> dataList) {if (dataList != null && dataList.size() > 0) {//1.先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);//2：动态动态填充表格数据List<ExcelClassField> headDataList = getHeadDataList(dataList.get(0).getClass());//获取表头数据int firstRow = 0;int rowMergerNum[] = new int[headDataList.size()];int lastRowNum = sheet.getLastRowNum();for (int i = 0; i < dataList.size(); i++) {Row row = sheet.createRow(i + lastRowNum + 1);T rowData = dataList.get(i);Map<String, Object> fieldDataMap = changeRowDataToFieldDataMap(rowData);for (int j = 0; j < headDataList.size(); j++) {Cell dataCell = row.createCell(j);ExcelClassField classField = headDataList.get(j);Object value = fieldDataMap.get(classField.getFieldEnName());String cellValue = value != null ? value.toString() : "";dataCell.setCellValue(cellValue);//5：动态 设置某列有下拉数据（后续可以根据是否模板导出控制结束行数）if (classField.getSelectData() != null) {setSelectData(sheet, classField.getSelectData(), 1, 1000, j, j);}//6：动态处理 纵合并单元格（下一个单独解决的问题）if (classField.isAutoVerticalMergerFlag()) {/*** 如果此列数据设置了纵向合并* 找开始行 和 结束行-->判断上一行的数据和此行数据是否一致，一致则列为要合并的区域*/String previousRowValue = sheet.getRow(i + lastRowNum).getCell(j).getStringCellValue();if (previousRowValue.equals(cellValue)) {rowMergerNum[j]++;} else {firstRow = i + lastRowNum + 1;rowMergerNum[j] = 0;}}if (rowMergerNum[j] > 0) {sheet.addMergedRegionUnsafe(new CellRangeAddress(firstRow, (firstRow + rowMergerNum[j]), j, j));}//设置单元格样式dataCell.setCellStyle(dataStyle);}}}}/*** description :处理表头样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealHeadStyle(HSSFWorkbook workbook) {//6-1 表头样式HSSFCellStyle headCellStyle = dealPublicStyle(workbook);headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short) 12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);return headCellStyle;}/*** description :处理表格数据样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealDataStyle(HSSFWorkbook workbook) {//6-2 数据样式HSSFCellStyle dataCellStyle = dealPublicStyle(workbook);dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font dataFont = workbook.createFont();//表头字体样式dataFont.setFontName("宋体");dataFont.setFontHeightInPoints((short) 11);//字体大小dataCellStyle.setFont(dataFont);return dataCellStyle;}/*** description :表头和表格数据的公共样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealPublicStyle(HSSFWorkbook workbook) {HSSFCellStyle publicCellStyle = workbook.createCellStyle();//设置边框publicCellStyle.setBorderTop(BorderStyle.THIN);publicCellStyle.setBorderBottom(BorderStyle.THIN);publicCellStyle.setBorderLeft(BorderStyle.THIN);publicCellStyle.setBorderRight(BorderStyle.THIN);publicCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中publicCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中return publicCellStyle;}/*** description :根据实体获取表头数据** @param* @return java.util.List<com.liu.susu.excel.poi.fields.ExcelClassField>* @author susu*/public static List<ExcelClassField> getHeadDataList(Class<?> clazz) {List<Field> fieldList = new ArrayList<>();Collections.addAll(fieldList, clazz.getDeclaredFields());List<ExcelClassField> headDataList = new ArrayList<>();for (Field field : fieldList) {ExcelClassField classField = new ExcelClassField();classField.setFieldEnName(field.getName());ExcelExportField exportField = field.getAnnotation(ExcelExportField.class);if (exportField != null) {classField.setFieldCnName(exportField.value());classField.setAutoFilterFlag(exportField.autoFilterFlag());classField.setSelectData(exportField.selectData());ExcelExportCellRange excelExportCellRange = exportField.dealHorizontalMerger();classField.setHorizontalMergerFlag(excelExportCellRange.horizontalMergerFlag());classField.setHorizontalMergerColNum(excelExportCellRange.horizontalMergerColNum());classField.setAutoVerticalMergerFlag(exportField.autoVerticalMergerFlag());} else {classField.setFieldCnName(field.getName());}headDataList.add(classField);}return headDataList;}/*** description :数据行转列结果用map存储** @param t* @return java.util.Map<java.lang.String, java.lang.Object>* @author susu*/public static <T> Map<String, Object> changeRowDataToFieldDataMap(T t) {Map<String, Object> fieldDataMap = new HashMap<>();Field[] fields = t.getClass().getDeclaredFields();try {for (Field field : fields) {field.setAccessible(true);String fieldEnName = field.getName();fieldDataMap.put(fieldEnName, field.get(t));}} catch (IllegalArgumentException | IllegalAccessException e) {e.printStackTrace();}return fieldDataMap;}/*** description :设置下拉（如果动态：主要是知道设置的哪一列 以及设置多少行）** @param sheet* @param firstRow* @param lastRow* @param firstCol* @param lastCol* @return void* @author susu*/public static void setSelectData(HSSFSheet sheet, String[] selectData, int firstRow, int lastRow, int firstCol, int lastCol) {CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectData);DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);dataValidation.setSuppressDropDownArrow(false);sheet.addValidationData(dataValidation);}}

1.6 第六篇->特殊问题篇（解决其他各种问题）

1.6.1 属性是枚举问题

先看代码：

问题如下：

解决问题：

枚举加toString()方法即可：

1.6.2 非枚举映射问题

其实我是建议使用枚举的，毕竟也不只是导出用，项目中前后端使用枚举也方便，非要不用，也可以解决，当然你也可以用sql处理了，Java怎么解决，往下看：不适用注解映射 新增注解类，修改原注解，如下：

实体如下：

造数如下：

效果如下：

1.6.3 导出注解使用的其他问题

① 不导出或隐藏某些字段添加注解属性，修改部分代码，如下：

核心代码修改处，替换该方法即可

/*** description :根据实体获取表头数据** @param* @return java.util.List<com.liu.susu.excel.poi.fields.ExcelClassField>* @author susu*/public static List<ExcelClassField> getHeadDataList(Class<?> clazz) {List<Field> fieldList = new ArrayList<>();Collections.addAll(fieldList, clazz.getDeclaredFields());List<ExcelClassField> headDataList = new ArrayList<>();for (Field field : fieldList) {ExcelClassField classField = new ExcelClassField();classField.setFieldEnName(field.getName());ExcelExportField exportField = field.getAnnotation(ExcelExportField.class);if (exportField != null) {if (exportField.hidden()){continue;}classField.setFieldCnName(StringUtils.isNotEmpty(exportField.value())?exportField.value():field.getName());classField.setAutoFilterFlag(exportField.autoFilterFlag());classField.setSelectData(exportField.selectData());ExcelExportCellRange excelExportCellRange = exportField.dealHorizontalMerger();classField.setHorizontalMergerFlag(excelExportCellRange.horizontalMergerFlag());classField.setHorizontalMergerColNum(excelExportCellRange.horizontalMergerColNum());classField.setAutoVerticalMergerFlag(exportField.autoVerticalMergerFlag());ExcelExportKeyToValue[] keyToValues = exportField.keyToValue();if (keyToValues!=null && keyToValues.length>0){Map<String, String> keyToValueMap = new HashMap<>();for (ExcelExportKeyToValue keyToValue : keyToValues) {keyToValueMap.put(keyToValue.dataKey(), keyToValue.dataValue());}classField.setKeyToValueMap(keyToValueMap);}headDataList.add(classField);}}return headDataList;}

效果图如下：

② 排序问题：

其实如果实体的书写字段就是你想要的排序的话，那可以不用刻意地排序，如果非想弄，自己可以在注解类里添加属性实现，此处不介绍了

1.7 web中导出使用

1.7.1 核心代码

如下：

/*** description :数据输出** @param response* @param workbook* @param fileNameOrFilePathName* @return void* @author susu*/private static void executeWrite(HttpServletResponse response, HSSFWorkbook workbook, String fileNameOrFilePathName) throws IOException {if (response != null) {//web 输出到前端webExecuteWrite(response, workbook, fileNameOrFilePathName);} else {//创建文件输出(本地)FileOutputStream fileOut = new FileOutputStream(fileNameOrFilePathName);workbook.write(fileOut);fileOut.close();}}/*** description :web中处理数据输出** @param response* @param book* @param fileName* @return void* @author susu*/private static void webExecuteWrite(HttpServletResponse response, HSSFWorkbook book, String fileName) throws IOException {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");ServletOutputStream out = response.getOutputStream();book.write(out);out.flush();out.close();}

1.7.2 提供接口

如下：

1.7.3 测试接口 + 查看数据效果

如下：

1.7.4 附导出工具类完整代码

工具类代码如下：

package com.liu.susu.excel.poi.utils;import com.liu.susu.excel.poi.annotation.ExcelExportCellRange;import com.liu.susu.excel.poi.annotation.ExcelExportField;import com.liu.susu.excel.poi.annotation.ExcelExportKeyToValue;import com.liu.susu.excel.poi.example.export.example2.data.CatEntity;import com.liu.susu.excel.poi.example.export.example2.data.DogEntity;import com.liu.susu.excel.poi.example.export.example2.data.MakeCatData;import com.liu.susu.excel.poi.example.export.example2.data.MakeDogData;import com.liu.susu.excel.poi.fields.ExcelClassField;import mons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import org.springframework.stereotype.Service;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.FileOutputStream;import java.io.IOException;import java.lang.reflect.Field;import .URLEncoder;import java.util.*;/*** description 增加key-value映射问题** @author susu**/@Servicepublic class ExcelExportUtils5 {private static HttpServletResponse response;/*** description :web中导出入口** @param response* @param dataMap* @param fileName* @return void* @author susu*/public static void webExportOneMoreListProc(HttpServletResponse response, Map<Integer, List<?>> dataMap, String fileName) {ExcelExportUtils5.response = response;String sheetName = fileName;try {exportOneMoreListProc(dataMap, sheetName, fileName);} catch (Exception e) {e.printStackTrace();}}/*** description :同一sheet页导出多个list** @param dataMap* @param sheetName* @param fileNameOrFilePathName* @return void* @author susu*/private static void exportOneMoreListProc(Map<Integer, List<?>> dataMap, String sheetName, String fileNameOrFilePathName) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//设置列宽dataMap.forEach((k, v) -> {//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, v, null);});//4.输出文件executeWrite(response, workbook, fileNameOrFilePathName);}/*** description :excel导出程序入口(支持导出多个sheet页)** @param sheetMap* @param filePathAndName* @return void* @author susu*/private static void exportOneMoreSheetProc(Map<String, List<?>> sheetMap, String filePathAndName) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();sheetMap.forEach((k, v) -> {//2.创建工作表HSSFSheet sheet = workbook.createSheet(k);sheet.setDefaultColumnWidth(12);//设置列宽//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, v, null);});//4.创建问卷输出FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :excel导出程序入口** @param sheetName* @param filePathAndName* @return void* @author susu*/private static <T> void excelExportProc(String sheetName, String filePathAndName, List<T> dataList, Class<T> clazz) throws Exception {//1.创建工作薄HSSFWorkbook workbook = new HSSFWorkbook();//2.创建工作表HSSFSheet sheet = workbook.createSheet(sheetName);sheet.setDefaultColumnWidth(12);//设置列宽//3.动态填充表头和表格数据 + 样式处理 + 各种动态设置处理fillInExcelAndSetStyle(sheet, workbook, dataList, clazz);//4.创建问卷输出FileOutputStream fileOut = new FileOutputStream(filePathAndName);workbook.write(fileOut);fileOut.close();}/*** description :动态填充表头和表格数据 以及各种动态设置处理** @param sheet* @param workbook* @return void* @author susu*/private static <T> void fillInExcelAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<T> dataList, Class<?> clazz) {if (dataList != null && dataList.size() > 0) {if (clazz == null) {clazz = dataList.get(0).getClass();}}if (clazz != null) {List<ExcelClassField> headDataList = getHeadDataList(clazz);//获取表头数据//1.创建行（表头）-->动态填充表头数据 + 设置表头样式 + 动态设置筛选 +动态设置横向合并fillInHeadAndSetStyle(sheet, workbook, headDataList);//2.创建行（数据行） --> 动态填充表格数据 + 设置表格数据样式 + 动态设置下拉数据fillInDataAndSetStyle(sheet, workbook, dataList);}}/*** description :设置表头数据** @param sheet* @return void* @author susu*/public static void fillInHeadAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<ExcelClassField> headDataList) {//先获取表头样式HSSFCellStyle headStyle = dealHeadStyle(workbook);int lastRowNum = sheet.getLastRowNum();Row headRow = sheet.createRow(lastRowNum + 1);//1：动态动态填充表头数据for (int i = 0; i < headDataList.size(); i++) {ExcelClassField headClassField = headDataList.get(i);Cell headCell = headRow.createCell(i);headCell.setCellValue(headClassField.getFieldCnName());if (headClassField.isAutoFilterFlag()) {//2：动态 设置筛选(当然，如果表头都需要设置筛选，此处可以不用动态，按前面基础入门的处理方式即可)if (headRow.getRowNum() == 0) {CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, i, i);sheet.setAutoFilter(rangeAddress);}}if (headClassField.isHorizontalMergerFlag()) {//3. 动态处理 横向合并单元格int lastCol = i + headClassField.getHorizontalMergerColNum() - 1;sheet.addMergedRegion(new CellRangeAddress(headRow.getRowNum(), headRow.getRowNum(), i, lastCol));}headCell.setCellStyle(headStyle);//设置单元格样式}}/*** description :填充表格数据** @param workbook* @return org.apache.poi.ss.usermodel.Row* @author susu*/public static <T> void fillInDataAndSetStyle(HSSFSheet sheet, HSSFWorkbook workbook, List<T> dataList) {if (dataList != null && dataList.size() > 0) {//1.先获取表格数据样式HSSFCellStyle dataStyle = dealDataStyle(workbook);//2：动态动态填充表格数据List<ExcelClassField> headDataList = getHeadDataList(dataList.get(0).getClass());//获取表头数据int firstRow = 0;int rowMergerNum[] = new int[headDataList.size()];int lastRowNum = sheet.getLastRowNum();for (int i = 0; i < dataList.size(); i++) {Row row = sheet.createRow(i + lastRowNum + 1);T rowData = dataList.get(i);Map<String, Object> fieldDataMap = changeRowDataToFieldDataMap(rowData);for (int j = 0; j < headDataList.size(); j++) {ExcelClassField classField = headDataList.get(j);Cell dataCell = row.createCell(j);Object value = fieldDataMap.get(classField.getFieldEnName());String cellValue = value != null ? value.toString() : "";Map<String, String> keyToValueMap = classField.getKeyToValueMap();if (keyToValueMap != null && !keyToValueMap.isEmpty()) {cellValue = keyToValueMap.get(cellValue);}dataCell.setCellValue(cellValue);//5：动态 设置某列有下拉数据（后续可以根据是否模板导出控制结束行数）if (classField.getSelectData() != null) {setSelectData(sheet, classField.getSelectData(), 1, 1000, j, j);}//6：动态处理 纵合并单元格（下一个单独解决的问题）if (classField.isAutoVerticalMergerFlag()) {/*** 如果此列数据设置了纵向合并* 找开始行 和 结束行-->判断上一行的数据和此行数据是否一致，一致则列为要合并的区域*/String previousRowValue = sheet.getRow(i + lastRowNum).getCell(j).getStringCellValue();if (previousRowValue.equals(cellValue)) {rowMergerNum[j]++;} else {firstRow = i + lastRowNum + 1;rowMergerNum[j] = 0;}}if (rowMergerNum[j] > 0) {sheet.addMergedRegionUnsafe(new CellRangeAddress(firstRow, (firstRow + rowMergerNum[j]), j, j));}//设置单元格样式dataCell.setCellStyle(dataStyle);}}}}/*** description :处理表头样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealHeadStyle(HSSFWorkbook workbook) {//6-1 表头样式HSSFCellStyle headCellStyle = dealPublicStyle(workbook);headCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont = workbook.createFont();//表头字体样式headerFont.setFontName("宋体");headerFont.setFontHeightInPoints((short) 12);//字体大小headerFont.setBold(true);//字体加粗headerFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());//字体颜色headCellStyle.setFont(headerFont);return headCellStyle;}/*** description :处理表格数据样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealDataStyle(HSSFWorkbook workbook) {//6-2 数据样式HSSFCellStyle dataCellStyle = dealPublicStyle(workbook);dataCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//背景dataCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font dataFont = workbook.createFont();//表头字体样式dataFont.setFontName("宋体");dataFont.setFontHeightInPoints((short) 11);//字体大小dataCellStyle.setFont(dataFont);return dataCellStyle;}/*** description :表头和表格数据的公共样式** @param workbook* @return org.apache.poi.hssf.usermodel.HSSFCellStyle* @author susu*/private static HSSFCellStyle dealPublicStyle(HSSFWorkbook workbook) {HSSFCellStyle publicCellStyle = workbook.createCellStyle();//设置边框publicCellStyle.setBorderTop(BorderStyle.THIN);publicCellStyle.setBorderBottom(BorderStyle.THIN);publicCellStyle.setBorderLeft(BorderStyle.THIN);publicCellStyle.setBorderRight(BorderStyle.THIN);publicCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中publicCellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中return publicCellStyle;}/*** description :根据实体获取表头数据** @param* @return java.util.List<com.liu.susu.excel.poi.fields.ExcelClassField>* @author susu*/public static List<ExcelClassField> getHeadDataList(Class<?> clazz) {List<Field> fieldList = new ArrayList<>();Collections.addAll(fieldList, clazz.getDeclaredFields());List<ExcelClassField> headDataList = new ArrayList<>();for (Field field : fieldList) {ExcelClassField classField = new ExcelClassField();classField.setFieldEnName(field.getName());ExcelExportField exportField = field.getAnnotation(ExcelExportField.class);if (exportField != null) {if (exportField.hidden()) {continue;}classField.setFieldCnName(StringUtils.isNotEmpty(exportField.value()) ? exportField.value() : field.getName());classField.setAutoFilterFlag(exportField.autoFilterFlag());classField.setSelectData(exportField.selectData());ExcelExportCellRange excelExportCellRange = exportField.dealHorizontalMerger();classField.setHorizontalMergerFlag(excelExportCellRange.horizontalMergerFlag());classField.setHorizontalMergerColNum(excelExportCellRange.horizontalMergerColNum());classField.setAutoVerticalMergerFlag(exportField.autoVerticalMergerFlag());ExcelExportKeyToValue[] keyToValues = exportField.keyToValue();if (keyToValues != null && keyToValues.length > 0) {Map<String, String> keyToValueMap = new HashMap<>();for (ExcelExportKeyToValue keyToValue : keyToValues) {keyToValueMap.put(keyToValue.dataKey(), keyToValue.dataValue());}classField.setKeyToValueMap(keyToValueMap);}headDataList.add(classField);}}return headDataList;}/*** description :数据行转列结果用map存储** @param t* @return java.util.Map<java.lang.String, java.lang.Object>* @author susu*/public static <T> Map<String, Object> changeRowDataToFieldDataMap(T t) {Map<String, Object> fieldDataMap = new HashMap<>();Field[] fields = t.getClass().getDeclaredFields();try {for (Field field : fields) {field.setAccessible(true);String fieldEnName = field.getName();fieldDataMap.put(fieldEnName, field.get(t));}} catch (IllegalArgumentException | IllegalAccessException e) {e.printStackTrace();}return fieldDataMap;}/*** description :设置下拉（如果动态：主要是知道设置的哪一列 以及设置多少行）** @param sheet* @param firstRow* @param lastRow* @param firstCol* @param lastCol* @return void* @author susu*/public static void setSelectData(HSSFSheet sheet, String[] selectData, int firstRow, int lastRow, int firstCol, int lastCol) {CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(selectData);DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);dataValidation.setSuppressDropDownArrow(false);sheet.addValidationData(dataValidation);}/*** description :数据输出** @param response* @param workbook* @param fileNameOrFilePathName* @return void* @author susu*/private static void executeWrite(HttpServletResponse response, HSSFWorkbook workbook, String fileNameOrFilePathName) throws IOException {if (response != null) {//web 输出到前端webExecuteWrite(response, workbook, fileNameOrFilePathName);} else {//创建文件输出(本地)FileOutputStream fileOut = new FileOutputStream(fileNameOrFilePathName);workbook.write(fileOut);fileOut.close();}}/*** description :web中处理数据输出** @param response* @param book* @param fileName* @return void* @author susu*/private static void webExecuteWrite(HttpServletResponse response, HSSFWorkbook book, String fileName) throws IOException {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");ServletOutputStream out = response.getOutputStream();book.write(out);out.flush();out.close();}public static void main(String[] args) throws Exception {String filePathAndName = "C:\\Users\\Administrator\\Desktop\\excel导出\\ExcelExportKeyToValue.xlsx";String sheetName = "dog and cat数据";List<DogEntity> dogList = MakeDogData.getDogs();//获取狗狗数据List<CatEntity> catList = MakeCatData.getCats();//获取小猫数据Map<Integer, List<?>> dataMap = new HashMap<>();dataMap.put(1, dogList);dataMap.put(2, catList);exportOneMoreListProc(dataMap, sheetName, filePathAndName);}}

1.8 代码中需要优化的

上面代码如果运行有问题，可以参考下面的来解决：数据下拉问题问题描述：比如下面这种情况的：

解决此问题：

获取表头数据的时候加个判断，问题即可解决

2. 其他：

1.9 附上官网地址：

官网：/components/spreadsheet/

2. 附项目代码

项目下载地址：

Java导出excel工具详细介绍（POI 和 EasyExcel），各种复杂需求情况的导出（包括动态设置合并单元格等）.项目结构如下：

JAVA导出Excel通用工具类——第一篇：详细介绍POI 导出excel的多种复杂情况 包括动态设置筛选 动态合并横向（纵向）单元格等多种复杂情况——保姆级别 真的不能再详细了 代码拿来即用）