Java按模板导出Excel数据

Is Aliang

一、使用

1. 创建Excel模板，如图：

 2. 使用工具导出，代码示例：

 1 @GetMapping("/excel")
 2 public void exportExcel(HttpServletResponse response) throws Exception {
 3     List<Equipment> equipmentList = Arrays.asList(
 4             new Equipment("001", "设备A", "区域1", "仓库X"),
 5             new Equipment("002", "设备B", "区域2", "仓库Y"),
 6             new Equipment("003", "设备C", "区域3", "仓库Z")
 7     );
 8 
 9     List<Map<String, Object>> tempDataList = MapObjectUtil.objListToMapList(equipmentList);
10     List<DynamicDataMapping> dynamicDataMappingList = DynamicDataMapping.createOneDataList("equipment", tempDataList);
11     ExcelTemplateProc.doExportExcelByTemplateProc("excel-template/equipment.xlsx", response, null, dynamicDataMappingList);
12 }
13 
14 
15 
16 @Data
17 @AllArgsConstructor
18 class Equipment {
19     private String id;
20     private String name;
21     private String area;
22     private String wh;
23 }

 

二、工具类

1. 数据格式转换工具类：

 1 import java.lang.reflect.Field;
 2 import java.util.ArrayList;
 3 import java.util.HashMap;
 4 import java.util.List;
 5 import java.util.Map;
 6 
 7 public class MapObjectUtil {
 8 
 9     /**
10     * @description: 将object的list数据 转换成 map的list（如：List<Map<String, Object>>）
11     * @param objDataList
12     * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
13     */
14     public static List<Map<String, Object>> objListToMapList(List<?> objDataList){
15         List<Map<String, Object>> dataList = new ArrayList<>();
16         if (objDataList==null || objDataList.size()<1){
17             return null;
18         }
19         objDataList.forEach(obj->{
20             try {
21                 Map<String, Object> map = MapObjectUtil.objectToMap(obj);
22                 dataList.add(map);
23             } catch (IllegalAccessException e) {
24                 throw new RuntimeException(e);
25             }
26         });
27         return dataList;
28     }
29 
30     /**
31     * @description: 将object数据转换成map数据
32     * @param obj
33     * @return java.util.Map<java.lang.String,java.lang.Object>
34     */
35     public static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
36         Map<String, Object> map = new HashMap();
37         Class<?> cla = obj.getClass();
38         Field[] fields = cla.getDeclaredFields();
39         for (Field field : fields) {
40             field.setAccessible(true);
41             String keyName = field.getName();
42             Object value = field.get(obj);
43             if (value == null)
44                 value = "";
45             map.put(keyName, value);
46         }
47         return map;
48     }
49 
50 }

2. 存放导出动态数据类：

 1 import lombok.Data;
 2 
 3 import java.util.ArrayList;
 4 import java.util.Collections;
 5 import java.util.List;
 6 import java.util.Map;
 7 
 8 /**
 9  * @Description 存放需要导出的动态数据
10  */
11 @Data
12 public class DynamicDataMapping {
13 
14     private String dataId;
15     private List<Map<String, Object>> dataList;
16 
17 
18     /**
19     * @description: 组装只有一个list类型的动态数据
20     * @param dataId
21     * @param dataList
22     * @return
23     */
24     public static List<DynamicDataMapping> createOneDataList(String dataId, List<Map<String, Object>> dataList) {
25         if (dataList == null)
26             return null;
27         return Collections.singletonList(getDynamicDataMapping(dataId,dataList));
28     }
29 
30     /**
31     * @description: 组装只有多个list类型的动态数据
32     * @param transMap
33     * @return
34     */
35     public static List<DynamicDataMapping> createMorDataList(Map<String,List<Map<String, Object>>> transMap) {
36         if (transMap == null)
37             return null;
38         List<DynamicDataMapping> list = new ArrayList<>();
39         transMap.forEach((dataId,dataList)->{
40             list.add(getDynamicDataMapping(dataId,dataList));
41         });
42         return list;
43     }
44 
45     public static DynamicDataMapping getDynamicDataMapping(String dataId, List<Map<String, Object>> dataList){
46         DynamicDataMapping dynamicData = new DynamicDataMapping();
47         dynamicData.dataId = dataId;
48         dynamicData.dataList = dataList;
49         return dynamicData;
50     }
51 
52 } 
3. 导出Excel工具类： 
  1 import jakarta.servlet.http.HttpServletResponse;
  2 import org.apache.poi.ss.usermodel.*;
  3 import org.apache.poi.xssf.usermodel.XSSFCell;
  4 import org.apache.poi.xssf.usermodel.XSSFRow;
  5 import org.apache.poi.xssf.usermodel.XSSFSheet;
  6 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  7 
  8 import java.io.*;
  9 import java.util.List;
 10 import java.util.Map;
 11 
 12 /**
 13  * @Description 根据模版导出Excel程序
 14  */
 15 public class ExcelTemplateProc {
 16 
 17     /**
 18      * @param templateFileName
 19      * @param
 20      * @param staticDataMap
 21      * @param dynamicDataMappingList
 22      * @return void
 23      * @description: 根据模版导出Excel入口
 24      */
 25     public static void doExportExcelByTemplateProc(String templateFileName, HttpServletResponse response,
 26                                                    Map<String, Object> staticDataMap,
 27                                                    List<DynamicDataMapping> dynamicDataMappingList) throws IOException {
 28         /**
 29          * 1. 从resources下加载模板并替换
 30          *    使用 ResourceUtils 加载文件
 31          */
 32         InputStream inputStream = ExcelTemplateProc.class.getClassLoader().getResourceAsStream(templateFileName);
 33        Workbook workbook = dealFirstSheetByTemplate(inputStream, staticDataMap, dynamicDataMappingList);
 34         // 替换里信息
 35 //        extracted(workbook);
 36         // 2. 反遣给前端
 37         try {
 38             //设置响应头
 39             response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
 40             response.setHeader("Content-Disposition", "attachment; filename=" + templateFileName);
 41             // 输出流
 42             OutputStream outputStream = response.getOutputStream();
 43             workbook.write(outputStream);
 44             outputStream.flush();
 45         } finally {
 46             // Clean up resources
 47             if (workbook != null) {
 48                 workbook.close();
 49             }
 50             if (inputStream != null) {
 51                 inputStream.close();
 52             }
 53         }
 54     }
 55 
 56     private static void extracted(Workbook workbook) {
 57         Sheet sheet = workbook.getSheetAt(0); // 获取第一张表
 58         for (Row row : sheet) {
 59             for (Cell cell : row) {
 60                 if (cell.getCellType() == CellType.STRING) {
 61                     String cellValue = cell.getStringCellValue();
 62                     if ("已打卡".equals(cellValue) || "申诉成功".equals(cellValue)) {
 63                         // 替换成绿色对勾
 64                         CellStyle greenCheckStyle = workbook.createCellStyle();
 65                         Font greenFont = workbook.createFont();
 66                         greenFont.setColor(IndexedColors.GREEN.getIndex());
 67                         greenFont.setBold(true);
 68                         greenCheckStyle.setFont(greenFont);
 69                         cell.setCellValue("✓"); // 使用绿色对勾的替换字符
 70                         cell.setCellStyle(greenCheckStyle);
 71                     } else if (cellValue == null || "".equals(cellValue) || "申诉驳回".equals(cellValue)) {
 72                         // 替换成红色叉号
 73                         CellStyle redCrossStyle = workbook.createCellStyle();
 74                         Font redFont = workbook.createFont();
 75                         redFont.setColor(IndexedColors.RED.getIndex());
 76                         redFont.setBold(true);
 77                         redCrossStyle.setFont(redFont);
 78                         cell.setCellValue("✗"); // 使用红色叉号的替换字符
 79                         cell.setCellStyle(redCrossStyle);
 80                     } else if ("未签退".equals(cellValue)) {
 81                         // 替换成绿色对勾
 82                         CellStyle greenCheckStyle = workbook.createCellStyle();
 83                         Font yellowFont = workbook.createFont();
 84                         yellowFont.setColor(IndexedColors.ORANGE.getIndex());
 85                         yellowFont.setBold(true);
 86                         greenCheckStyle.setFont(yellowFont);
 87                         cell.setCellValue("✓"); // 使用橘色对勾的替换字符
 88                         cell.setCellStyle(greenCheckStyle);
 89                     } else if ("请假".equals(cellValue)) {
 90                         // 替换成黄色对勾
 91                         CellStyle greenCheckStyle = workbook.createCellStyle();
 92                         Font yellowFont = workbook.createFont();
 93                         yellowFont.setColor(IndexedColors.YELLOW.getIndex());
 94                         yellowFont.setBold(true);
 95                         greenCheckStyle.setFont(yellowFont);
 96                         cell.setCellValue("⚪"); // 使用绿色对勾的替换字符
 97                         cell.setCellStyle(greenCheckStyle);
 98                     }
 99                     else if ("申诉中".equals(cellValue)) {
100                         // 替换成宗色对勾
101                         CellStyle greenCheckStyle = workbook.createCellStyle();
102                         Font yellowFont = workbook.createFont();
103                         yellowFont.setColor(IndexedColors.BROWN.getIndex());
104                         yellowFont.setBold(true);
105                         greenCheckStyle.setFont(yellowFont);
106                         cell.setCellValue("🔺"); // 使用zong色对勾的替换字符
107                         cell.setCellStyle(greenCheckStyle);
108                     }
109                 }
110             }
111         }
112     }
113 
114     /**
115      * @param workbook
116      * @param excelFilePath
117      * @return void
118      * @description: 保存导出的Excel文件到服务器
119      */
120     public static void saveExportFile(Workbook workbook, String excelFilePath) throws IOException {
121         FileOutputStream outputStream = new FileOutputStream(excelFilePath);
122         executeWorkBookWrite(workbook, outputStream);
123     }
124 
125     /**
126      * @param workbook
127      * @param outputStream
128      * @return void
129      * @description: 数据输出
130      */
131     public static void executeWorkBookWrite(Workbook workbook, OutputStream outputStream) throws IOException {
132         workbook.write(outputStream);
133         outputStream.flush();
134         outputStream.close();
135         workbook.close();
136     }
137 
138     /**
139      * @param inputStream
140      * @param staticDataMap
141      * @param dynamicDataMappingList
142      * @return org.apache.poi.ss.usermodel.Workbook
143      * @description: 处理只有一个sheet页的模版
144      */
145     public static Workbook dealFirstSheetByTemplate(InputStream inputStream,
146                                                     Map<String, Object> staticDataMap,
147                                                     List<DynamicDataMapping> dynamicDataMappingList) throws IOException {
148         XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
149         XSSFSheet sheet = workbook.getSheetAt(0);
150         // 按模板处理sheet页
151         dealSheetDataByTemplate(sheet, staticDataMap, dynamicDataMappingList);
152         return workbook;
153     }
154 
155     /**
156      * @param sheet
157      * @param staticDataMap
158      * @param dynamicDataMappingList
159      * @return void
160      * @description: 按模板处理sheet页里的数据
161      */
162     private static void dealSheetDataByTemplate(XSSFSheet sheet, Map<String, Object> staticDataMap, List<DynamicDataMapping> dynamicDataMappingList) {
163         // 循环sheet里每一行
164         for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
165             XSSFRow row = sheet.getRow(i);
166             DynamicDataMapping dynamicDataMapping = getDynamicRowDataByMatch(row, dynamicDataMappingList);
167             if (dynamicDataMapping != null) {
168                 i = getTemplateLastRowIndexAfterDealTemplate(sheet, i, dynamicDataMapping);
169             } else {
170                 dealTemplateDataRow(row, null, staticDataMap);
171             }
172         }
173     }
174 
175     /**
176      * @param row
177      * @param dataMap
178      * @param dataPrefix
179      * @return void
180      * @description: 循环处理模版中每行的数据
181      */
182     private static void dealTemplateDataRow(XSSFRow row, String dataPrefix, Map<String, Object> dataMap) {
183         if (dataMap == null) {
184             return;
185         }
186         for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
187             XSSFCell cell = row.getCell(i);
188             fillInTemplateCellDataValue(cell, dataPrefix, dataMap);
189         }
190     }
191 
192     /**
193      * @param cell
194      * @param dataPrefix
195      * @param dataMap
196      * @return void
197      * @description: 填充模版里单元格的值
198      */
199     private static void fillInTemplateCellDataValue(XSSFCell cell, String dataPrefix, Map<String, Object> dataMap) {
200         if (cell == null) {
201             return;
202         }
203         String cellValue = cell.getStringCellValue(); // 获取模版里设置的数据
204         if (cellValue == null || cellValue.trim().isEmpty()) {
205             return;
206         }
207         boolean flag = false;
208         dataPrefix = (dataPrefix == null || dataPrefix.isEmpty()) ? "" : (dataPrefix + ".");
209         for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
210             // 循环所有，因为可能一行有多个占位符
211             String cellTemplateStr = "{{" + dataPrefix + entry.getKey() + "}}";
212             if (cellValue.contains(cellTemplateStr)) {
213                 // 替换模版中单元格的数据
214                 cellValue = cellValue.replace(cellTemplateStr, entry.getValue() == null ? "" : entry.getValue().toString());
215                 flag = true;
216             }
217         }
218         if (flag) {
219             cell.setCellValue(cellValue);
220         }
221     }
222 
223     /**
224      * @param row
225      * @param dynamicDataMappingList
226      * @return com.liu.susu.excel.template.poi.common.DynamicDataMapping
227      * @description: 通过模版sheet中的行数据 与 动态数据匹配,获取此行需要填充的动态数据
228      */
229     private static DynamicDataMapping getDynamicRowDataByMatch(XSSFRow row, List<DynamicDataMapping> dynamicDataMappingList) {
230         if (dynamicDataMappingList == null || dynamicDataMappingList.size() < 1) {
231             return null;
232         }
233         if (row != null) {
234             for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
235                 XSSFCell cell = row.getCell(j);
236                 CellType cellType = cell.getCellType();
237                 switch (cellType) {
238                     case STRING:
239                         String value = cell.getStringCellValue();
240                         if (value != null) {
241                             for (DynamicDataMapping dynamicData : dynamicDataMappingList) {
242                                 if (value.startsWith("{{" + dynamicData.getDataId() + ".")) {
243                                     return dynamicData;
244                                 }
245                             }
246                         }
247                         ;
248                         break;
249                 }
250 
251             }
252         }
253         return null;
254 
255     }
256 
257     /**
258      * @param sheet
259      * @param rowIndex
260      * @param dynamicDataMapping
261      * @return int
262      * @description: 根据动态数据的条数动态复制模版行，每处理一个类型的list返回最后的行数，进而处理下一个类型的list
263      */
264     private static int getTemplateLastRowIndexAfterDealTemplate(XSSFSheet sheet, int rowIndex, DynamicDataMapping dynamicDataMapping) {
265         if (dynamicDataMapping == null) {
266             return rowIndex;
267         }
268         int dataRows = dynamicDataMapping.getDataList().size();
269         // 需要拷贝的行数（因为模板行本身占1行，所以-1）
270         int copyRows = dataRows - 1;
271         if (copyRows > 0) {
272             /**
273              * shiftRows: 从动态数据模版行(rowIndex)到最后一行，这些全部行都向下移copyRows行
274              *            相当于模版行上面插入n行空行（n=copyRows）
275              */
276             sheet.shiftRows(rowIndex, sheet.getLastRowNum(), copyRows, true, false);
277             // 拷贝策略
278             CellCopyPolicy cellCopyPolicy = makeCellCopyPolicy();
279             // 因为从模版行开始向下平移了copyRows行，所以这里 模板行=rowIndex + copyRows,
280             int templateDataRow = rowIndex + copyRows;
281             // 因为模版行上新增了空行，所以要把模板所在行的模版 拷贝到上面新增的空行
282             for (int i = 0; i < copyRows; i++) {
283                 //templateDataRow-模版行数据   rowIndex + i循环的当前空行
284                 sheet.copyRows(templateDataRow, templateDataRow, rowIndex + i, cellCopyPolicy);
285             }
286         }
287         // 循环模版行：动态替换模版行（将模版行里的模版替换成动态数据）
288         for (int j = rowIndex; j < rowIndex + dataRows; j++) {
289             Map<String, Object> dataMap = dynamicDataMapping.getDataList().get(j - rowIndex);
290             dealTemplateDataRow(sheet.getRow(j), dynamicDataMapping.getDataId(), dataMap);
291         }
292         return rowIndex + copyRows;
293     }
294 
295     /**
296      * @param
297      * @return org.apache.poi.ss.usermodel.CellCopyPolicy
298      * @description: 拷贝策略
299      */
300     public static CellCopyPolicy makeCellCopyPolicy() {
301         CellCopyPolicy cellCopyPolicy = new CellCopyPolicy();
302         cellCopyPolicy.setCopyCellValue(true);
303         cellCopyPolicy.setCopyCellStyle(true);
304         return cellCopyPolicy;
305     }
306 
307 
308 }

 4. 依赖

        <!-- Apache POI Excel 支持 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <!-- XMLBeans 支持（Java 9+ 必需） -->
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>5.2.1</version>
        </dependency>

 

posted @ 2025-07-23 15:48  一愚亮  阅读(528)  评论(0)    收藏  举报