Java根据excel模版导出Excel（easyexcel、poi）——含项目测试例子拿来即用

1. 前言

1.1 关于Excel的一般导出

• 一般列表导出以及个性化样式设置请看下面的文章：
  • JAVA导出Excel通用工具类——第一篇：详细介绍POI 导出excel的多种复杂情况，包括动态设置筛选、动态合并横向（纵向）单元格等多种复杂情况.
  • JAVA导出Excel通用工具——第二篇：使用EasyExcel导出excel的多种情况的例子介绍.

2.2 关于easyexcel的根据模版导出

• 如果使用easy Excel的话，下面就不用看了，这个官网关于怎么使用以及例子提供的非常详细，git上还能下载源码，官网如下，使用不再介绍，具体看官网即可：
  Easy Excel 之 填充excel.

2. 先看效果

2.1 模版

• 静态填充简单模版如下：
  
• 动态填充简单模版：
  • 单个list：
    
  • 多个list：
    
  • 单个list单条数据：
    

2.2 效果

• 静态填充效果如下：
  
• 动态填充效果如下：
  • 单个list：
    
  • 多个list：
    
  • 单个list单条数据：
    

3. 代码实现（核心代码）

3.1 项目代码结构

• 如下：
  

3.2 静态填充例子代码

• 工具类代码已封装，所以调用很简单即可实现，如下：
  

3.3 动态list填充代码

• 单个list：
  
• 多个list
  
• 单个list单条数据：
  

3.4 附核心代码

3.4.1 object转map工具类

• MapObjectUtil.java 如下：

  package com.liu.susu.excel.template.poi.common;import com.alibaba.fastjson.JSONObject;
  import com.liu.susu.excel.template.poi.example.data.DogEntity2;
  import org.apache.commons.beanutils.BeanMap;import java.lang.reflect.Field;
  import java.util.ArrayList;
  import java.util.HashMap;
  import java.util.List;
  import java.util.Map;/*** @Description* @Author susu* @Date 2024/2/19*/
  public class MapObjectUtil {/*** @description: 将object的list数据 转换成 map的list（如：List<Map<String, Object>>）* @param objDataList* @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>* @author susu*/public static List<Map<String, Object>> objListToMapList(List<?> objDataList){List<Map<String, Object>> dataList = new ArrayList<>();if (objDataList==null || objDataList.size()<1){return null;}objDataList.forEach(obj->{try {Map<String, Object> map = MapObjectUtil.objectToMap(obj);dataList.add(map);} catch (IllegalAccessException e) {throw new RuntimeException(e);}});return dataList;}/*** @description: 将object数据转换成map数据* @param obj* @return java.util.Map<java.lang.String,java.lang.Object>* @author susu*/public static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {Map<String, Object> map = new HashMap();Class<?> cla = obj.getClass();Field[] fields = cla.getDeclaredFields();for (Field field : fields) {field.setAccessible(true);String keyName = field.getName();Object value = field.get(obj);if (value == null)value = "";map.put(keyName, value);}return map;}/*** @description: 使用 JSONObject 将object转换成map* @param obj* @return java.util.Map<?,?>* @author susu*/public static Map<?, ?> objectToMap2(Object obj) {if (obj == null)return null;return JSONObject.parseObject(JSONObject.toJSONString(obj),Map.class);}/*** @description: 使用BeanMap将object转换成map* @param obj* @return java.util.Map<?,?>* @author susu*/public static Map<?, ?> objectToMap3(Object obj) {if (obj == null)return null;return new BeanMap(obj);}public static void main(String[] args) {DogEntity2 dog = new DogEntity2();dog.setDogId("A-1001");dog.setDogAge(3);dog.setDogName("aaa");
  //        Map map = JSONObject.parseObject(JSONObject.toJSONString(dog),Map.class);Map map = objectToMap2(dog);System.out.println(map);}}
  

3.4.2 根据模版导出Excel程序代码

• ExcelTemplateProc.java 如下：

  package com.liu.susu.excel.template.poi.common;import org.apache.commons.lang3.StringUtils;
  import org.apache.poi.ss.usermodel.CellCopyPolicy;
  import org.apache.poi.ss.usermodel.Workbook;
  import org.apache.poi.xssf.usermodel.XSSFCell;
  import org.apache.poi.xssf.usermodel.XSSFRow;
  import org.apache.poi.xssf.usermodel.XSSFSheet;
  import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  import org.springframework.util.ResourceUtils;import java.io.*;
  import java.util.List;
  import java.util.Map;/*** @Description 根据模版导出Excel程序* @Author susu* @Date 2024/2/19*/
  public class ExcelTemplateProc {/*** @param templateFileName* @param exportFilePathAndName* @param staticDataMap* @param dynamicDataMappingList* @return void* @description: 根据模版导出Excel入口* @author susu* @date 2024/2/20*/public static void doExportExcelByTemplateProc(String templateFileName, String exportFilePathAndName,Map<String, Object> staticDataMap,List<DynamicDataMapping> dynamicDataMappingList) throws IOException {/*** 1. 从resources下加载模板并替换*    使用 ResourceUtils 加载文件*/File file = ResourceUtils.getFile("classpath:"+templateFileName);InputStream inputStream = new FileInputStream(file);Workbook workbook = dealFirstSheetByTemplate(inputStream, staticDataMap, dynamicDataMappingList);// 2. 保存到本地saveExportFile(workbook, exportFilePathAndName);}/*** @param workbook* @param excelFilePath* @return void* @description: 保存导出的Excel文件到服务器* @author susu* @date 2024/2/20*/public static void saveExportFile(Workbook workbook, String excelFilePath) throws IOException {FileOutputStream outputStream = new FileOutputStream(excelFilePath);executeWorkBookWrite(workbook, outputStream);}/*** @param workbook* @param outputStream* @return void* @description: 数据输出* @author susu* @date 2024/2/20*/public static void executeWorkBookWrite(Workbook workbook, OutputStream outputStream) throws IOException {workbook.write(outputStream);outputStream.flush();outputStream.close();workbook.close();}/*** @param inputStream* @param staticDataMap* @param dynamicDataMappingList* @return org.apache.poi.ss.usermodel.Workbook* @description: 处理只有一个sheet页的模版* @author susu* @date 2024/2/20*/public static Workbook dealFirstSheetByTemplate(InputStream inputStream,Map<String, Object> staticDataMap,List<DynamicDataMapping> dynamicDataMappingList) throws IOException {XSSFWorkbook workbook = new XSSFWorkbook(inputStream);XSSFSheet sheet = workbook.getSheetAt(0);// 按模板处理sheet页dealSheetDataByTemplate(sheet, staticDataMap, dynamicDataMappingList);return workbook;}/*** @param sheet* @param staticDataMap* @param dynamicDataMappingList* @return void* @description: 按模板处理sheet页里的数据* @author susu* @date 2024/2/19*/private static void dealSheetDataByTemplate(XSSFSheet sheet, Map<String, Object> staticDataMap, List<DynamicDataMapping> dynamicDataMappingList) {// 循环sheet里每一行for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {XSSFRow row = sheet.getRow(i);DynamicDataMapping dynamicDataMapping = getDynamicRowDataByMatch(row, dynamicDataMappingList);if (dynamicDataMapping != null) {i = getTemplateLastRowIndexAfterDealTemplate(sheet, i, dynamicDataMapping);} else {dealTemplateDataRow(row, null, staticDataMap);}}}/*** @param row* @param dataMap* @param dataPrefix* @return void* @description: 循环处理模版中每行的数据* @author susu* @date 2024/2/20*/private static void dealTemplateDataRow(XSSFRow row, String dataPrefix, Map<String, Object> dataMap) {if (dataMap == null) {return;}for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {XSSFCell cell = row.getCell(i);fillInTemplateCellDataValue(cell, dataPrefix, dataMap);}}/*** @param cell* @param dataPrefix* @param dataMap* @return void* @description: 填充模版里单元格的值* @author susu* @date 2024/2/20*/private static void fillInTemplateCellDataValue(XSSFCell cell, String dataPrefix, Map<String, Object> dataMap) {if (cell == null) {return;}String cellValue = cell.getStringCellValue();//获取模版里设置的数据if (StringUtils.isEmpty(cellValue)) {return;}boolean flag = false;dataPrefix = StringUtils.isEmpty(dataPrefix) ? "" : (dataPrefix + ".");for (Map.Entry<String, Object> entry : dataMap.entrySet()) {// 循环所有，因为可能一行有多个占位符String cellTemplateStr = "{{" + dataPrefix + entry.getKey() + "}}";if (cellValue.contains(cellTemplateStr)) {// 替换模版中单元格的数据cellValue = cellValue.replace(cellTemplateStr, entry.getValue() == null ? "" : entry.getValue().toString());flag = true;}}if (flag) {cell.setCellValue(cellValue);}}/*** @param row* @param dynamicDataMappingList* @return com.liu.susu.excel.template.poi.common.DynamicDataMapping* @description: 通过模版sheet中的行数据 与 动态数据匹配,获取此行需要填充的动态数据* @author susu* @date 2024/2/21*/private static DynamicDataMapping getDynamicRowDataByMatch(XSSFRow row, List<DynamicDataMapping> dynamicDataMappingList) {if (dynamicDataMappingList == null || dynamicDataMappingList.size() < 1) {return null;}for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {XSSFCell cell = row.getCell(j);String value = cell.getStringCellValue();if (value != null) {for (DynamicDataMapping dynamicData : dynamicDataMappingList) {if (value.startsWith("{{" + dynamicData.getDataId() + ".")) {return dynamicData;}}}}return null;}/*** @param sheet* @param rowIndex* @param dynamicDataMapping* @return int* @description: 根据动态数据的条数动态复制模版行，每处理一个类型的list返回最后的行数，进而处理下一个类型的list* @author susu* @date 2024/2/20*/private static int getTemplateLastRowIndexAfterDealTemplate(XSSFSheet sheet, int rowIndex, DynamicDataMapping dynamicDataMapping) {if (dynamicDataMapping == null) {return rowIndex;}int dataRows = dynamicDataMapping.getDataList().size();// 需要拷贝的行数（因为模板行本身占1行，所以-1）int copyRows = dataRows - 1;if (copyRows > 0) {/*** shiftRows: 从动态数据模版行(rowIndex)到最后一行，这些全部行都向下移copyRows行*            相当于模版行上面插入n行空行（n=copyRows）*/sheet.shiftRows(rowIndex, sheet.getLastRowNum(), copyRows, true, false);// 拷贝策略CellCopyPolicy cellCopyPolicy = makeCellCopyPolicy();// 因为从模版行开始向下平移了copyRows行，所以这里 模板行=rowIndex + copyRows,int templateDataRow = rowIndex + copyRows;// 因为模版行上新增了空行，所以要把模板所在行的模版 拷贝到上面新增的空行for (int i = 0; i < copyRows; i++) {//templateDataRow-模版行数据   rowIndex + i循环的当前空行sheet.copyRows(templateDataRow, templateDataRow, rowIndex + i, cellCopyPolicy);}}// 循环模版行：动态替换模版行（将模版行里的模版替换成动态数据）for (int j = rowIndex; j < rowIndex + dataRows; j++) {Map<String, Object> dataMap = dynamicDataMapping.getDataList().get(j - rowIndex);dealTemplateDataRow(sheet.getRow(j), dynamicDataMapping.getDataId(), dataMap);}return rowIndex + copyRows;}/*** @param* @return org.apache.poi.ss.usermodel.CellCopyPolicy* @description: 拷贝策略* @author susu* @date 2024/2/20*/public static CellCopyPolicy makeCellCopyPolicy() {CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();cellCopyPolicy.setCopyCellValue(true);cellCopyPolicy.setCopyCellStyle(true);return cellCopyPolicy;}}

3.4.3 导出工具类入口代码

• ExportExcelByTemplateUtils.java 如下：

  package com.liu.susu.excel.template.poi.common;import java.util.HashMap;
  import java.util.List;
  import java.util.Map;/*** @Description 根据模版导出Excel工具类* @Author susu* @Date 2024/2/19*/
  public class ExportExcelByTemplateUtils {/*** @description: 根据模版导出Excel入口（单个list数据）* @param templateFileName* @param exportFilePathAndName* @param staticDataMap* @param dataId* @param originDataList* @return void* @author susu* @date 2024/2/21*/public static void doExportExcelOneListByTemplate(String templateFileName, String exportFilePathAndName,Map<String, Object> staticDataMap,String dataId,List<?> originDataList) throws Exception{List<Map<String, Object>> exportDataList = MapObjectUtil.objListToMapList(originDataList);// 只有一个list数据List<DynamicDataMapping> dynamicDataMappingList = DynamicDataMapping.createOneDataList(dataId, exportDataList);// 导出ExcelTemplateProc.doExportExcelByTemplateProc(templateFileName,exportFilePathAndName,staticDataMap,dynamicDataMappingList);}/*** @description: 根据模版导出Excel入口（多个list数据）* @param templateFileName* @param exportFilePathAndName* @param staticSource* @param originDataMapList* @return void* @author susu* @date 2024/2/20*/public static void doExportExcelMoreListByTemplate(String templateFileName,String exportFilePathAndName,Map<String, Object> staticSource,Map<String, List<?>> originDataMapList) throws Exception{Map<String,List<Map<String, Object>>> transMap = new HashMap<>();originDataMapList.forEach((dataId,originDataList)->{List<Map<String, Object>> transDataList = MapObjectUtil.objListToMapList(originDataList);transMap.put(dataId,transDataList);});// 多个list类型数据List<DynamicDataMapping> dynamicDataMappingList = DynamicDataMapping.createMorDataList(transMap);// 导出ExcelTemplateProc.doExportExcelByTemplateProc(templateFileName,exportFilePathAndName,staticSource,dynamicDataMappingList);}}
  

4. 附项目

4.1 一般导出项目代码

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

4.2 根据模版导出项目代码

• 如下：
  Java根据自定义模版导出各种需求的Excel（使用POI）.

5. 关于 DynamicDataMapping 代码

• 如下：

  package com.liu.susu.excel.template.poi.common;import lombok.Data;import java.util.ArrayList;
  import java.util.Collections;
  import java.util.List;
  import java.util.Map;/*** @Description 存放需要导出的动态数据* @Author susu* @Date 2024/2/19*/
  @Data
  public class DynamicDataMapping {private String dataId;private List<Map<String, Object>> dataList;/*** @description: 组装只有一个list类型的动态数据* @param dataId* @param dataList* @return java.util.List<com.liu.susu.excel.template.poi.common.DynamicDataMapping>* @author susu* @date 2024/2/20*/public static List<DynamicDataMapping> createOneDataList(String dataId, List<Map<String, Object>> dataList) {if (dataList == null)return null;return Collections.singletonList(getDynamicDataMapping(dataId,dataList));}/*** @description: 组装只有多个list类型的动态数据* @param transMap* @return java.util.List<com.liu.susu.excel.template.poi.common.DynamicDataMapping>* @author susu* @date 2024/2/20*/public static List<DynamicDataMapping> createMorDataList(Map<String,List<Map<String, Object>>> transMap) {if (transMap == null)return null;List<DynamicDataMapping> list = new ArrayList<>();transMap.forEach((dataId,dataList)->{list.add(getDynamicDataMapping(dataId,dataList));});return list;}public static DynamicDataMapping getDynamicDataMapping(String dataId, List<Map<String, Object>> dataList){DynamicDataMapping dynamicData = new DynamicDataMapping();dynamicData.dataId = dataId;dynamicData.dataList = dataList;return dynamicData;}}