目录
1.引入依赖
2.工具类
ExcelUtils:
ExcelFillCellMergeStrategyUtils:
ExcelFillCellMergePrevColUtils:
3.上传Excel模板文件
4.接口Controller测试
实体类User
接口ExcelController
Postman进行接口测试：
excel文件验证
实际开发过程中，经常会遇到这种业务，第一种方式就是后台直接生成一个带有数据的excel文件，第二种就是提前编写好Excel模板文件，直接将数据填充到Excel文件当中。那么下述就分享一下这个功能代码如何实现。
1.引入依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.7</version> </dependency> <!--实现数据导出到excel模板当中--> <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>2.工具类
ExcelUtils:
package com.slice.reactminiospring.mapper;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.write.metadata.WriteSheet;import com.alibaba.excel.write.metadata.fill.FillConfig;import com.alibaba.excel.write.metadata.fill.FillWrapper;import com.slice.reactminiospring.util.ExcelFillCellMergePrevColUtils;import com.slice.reactminiospring.util.ExcelFillCellMergeStrategyUtils;import jakarta.servlet.http.HttpServletResponse;import org.apache.commons.lang3.StringUtils;import org.springframework.core.io.ClassPathResource;import java.io.IOException;import java.io.InputStream;import java.net.URLEncoder;import java.time.LocalDate;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.*;/** * excel导出工具类 */public class ExcelUtils { /** * 传递多个sheet页 * <p> * // 创建额外的Sheet页的数据 * List<List<Map<String, Object>>> additionalSheetsData = new ArrayList<>(); * additionalSheetsData.add(innerMapList); * additionalSheetsData.add(innerMapList); * <p> * ExcelExportUtils.exportToTemplateWithMultipleSheets(response, "excel/excel模板1.xls", "excel模板1", * outerMap, innerMapList, additionalSheetsData, null, null); */ public static void exportToTemplateWithMultipleSheets1(HttpServletResponse response, String excelPath, String excelFileName, Map<String, Object> outerMap, List<Map<String, Object>> innerMapList, List<List<Map<String, Object>>> additionalSheetsData, ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils, ExcelFillCellMergeStrategyUtils excelFillCellMergeStrategyUtils) throws IOException { InputStream inputStream = new ClassPathResource(excelPath).getInputStream(); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode(excelFileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()) .withTemplate(inputStream) .registerWriteHandler(excelFillCellMergePrevColUtils) .registerWriteHandler(excelFillCellMergeStrategyUtils) .build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); // 处理第一个Sheet页的数据 WriteSheet writeSheet = EasyExcel.writerSheet().build(); if (null != innerMapList && innerMapList.size() > 0) { FillWrapper listWrapper = new FillWrapper("list", innerMapList); excelWriter.fill(listWrapper, fillConfig, writeSheet); } if (null != outerMap && outerMap.size() > 0) { excelWriter.fill(outerMap, writeSheet); } // 处理额外的Sheet页的数据 for (int i = 0; i < additionalSheetsData.size(); i++) { List<Map<String, Object>> sheetData = additionalSheetsData.get(i); WriteSheet additionalSheet = EasyExcel.writerSheet(i + 1).sheetName("Sheet" + (i + 2)).build(); System.out.println("I+1:" + (i + 1) + "Sheet:" + "Sheet" + (i + 2)); if (null != sheetData && sheetData.size() > 0) { FillWrapper additionalListWrapper = new FillWrapper("list", sheetData); excelWriter.fill(additionalListWrapper, fillConfig, additionalSheet); } } excelWriter.finish(); } /** * 导出数据到指定Excel * * @param response HttpServletResponse对象 * @param excelPath Excel模板地址 * @param excelFileName 文件名称 * @param outerMap 头部内容map * @param innerMapList 表格内容list * @param excelFillCellMergePrevColUtils 列合并参数 * @param excelFillCellMergeStrategyUtils 行合并参数 * @throws IOException 异常错误 */ public static void exportToTemplate(HttpServletResponse response, String excelPath, String excelFileName, Map<String, Object> outerMap, List<Map<String, Object>> innerMapList, ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils, ExcelFillCellMergeStrategyUtils excelFillCellMergeStrategyUtils) throws IOException { InputStream inputStream = new ClassPathResource(excelPath).getInputStream(); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode(excelFileName, "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()) .withTemplate(inputStream) .registerWriteHandler(excelFillCellMergePrevColUtils) .registerWriteHandler(excelFillCellMergeStrategyUtils) .build(); WriteSheet writeSheet = EasyExcel.writerSheet().build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); if (null != innerMapList && innerMapList.size() > 0) { FillWrapper listWrapper = new FillWrapper("list", innerMapList); excelWriter.fill(listWrapper, fillConfig, writeSheet); } if (null != outerMap && outerMap.size() > 0) { excelWriter.fill(outerMap, writeSheet); } excelWriter.finish(); } /** * 验证并获取excel单元格内的值 * * @param columnData 列值,object类型 * @param rowIndex 行号 * @param columnIndex 列号 * @param fieldName 字段名称 * @param lengthLimit 限制长度数（null时不做判断） * @param ifJudgeEmpty 是否需要判空（默认是） * @return 字符串格式值 * @throws Exception 逻辑异常 */ public static String checkValue(Object columnData, int rowIndex, int columnIndex, String fieldName, Integer lengthLimit, Boolean ifJudgeEmpty) throws Exception { String value = getStringValue(columnData); ifJudgeEmpty = null == ifJudgeEmpty ? true : ifJudgeEmpty; if (ifJudgeEmpty) { //需要判空 if (StringUtils.isEmpty(value)) { throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，" + fieldName + "不能为空"); } } if (null != lengthLimit && lengthLimit > 0) { //需要判断字符长度 if (StringUtils.isNotEmpty(value)) { if (value.length() > lengthLimit) { throw new Exception("第" + (rowIndex + 1) + "行，第" + (columnIndex + 1) + "列，" + fieldName + "不能超过" + lengthLimit + "个字符"); } } } return value; } /** * String => LocalDate * 入参str和pattern格式需要对应 * * @param str * @return LocalDate */ public static LocalDate str2LocalDate(String str) { if (StringUtils.isEmpty(str)) { return null; } if (str.indexOf("-") != -1 || str.indexOf("/") != -1) { String pattern = str.indexOf("/") != -1 ? "yyyy/MM/dd" : "yyyy-MM-dd"; try { //测试日期字符串是否符合日期 DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern); return LocalDate.parse(str, dateTimeFormatter); } catch (Exception e) { pattern = str.indexOf("/") != -1 ? "yyyy/M/d" : "yyyy-M-d"; DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern); return LocalDate.parse(str, dateTimeFormatter); } } else { Calendar calendar = new GregorianCalendar(1900, 0, -1); Date date = calendar.getTime(); int amount = Integer.parseInt(str); if (amount > 0) { Calendar calendar1 = Calendar.getInstance(); calendar1.setTime(date); calendar1.add(Calendar.DAY_OF_YEAR, amount); date = calendar.getTime(); } return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate(); } } /** * 获取String类型的值 * * @param columnData 列值,object类型 * @return 字符串格式值 */ public static String getStringValue(Object columnData) { if (columnData == null) { return null; } else { String res = columnData.toString().replace("[\\t\\n\\r]", "").trim(); return res;// //判断是否是科学计数法 true是科学计数法,false不是科学计数法// boolean isMache=SCIENTIFIC_COUNTING_METHOD_PATTERN.matcher(res).matches();// if(isMache){// BigDecimal resDecimal = new BigDecimal(res);// return resDecimal.toPlainString();// }else {// return res;// } } }}ExcelFillCellMergeStrategyUtils:
import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List; /** * 行合并工具类 */public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler { /** * 合并字段的下标 */ private int[] mergeColumnIndex; /** * 合并几行 */ private int mergeRowIndex; public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { this.mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; } } } } private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并 //获取当前行的第一列 Cell firstNowCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex); Object curData = firstNowCell.getCellTypeEnum() == CellType.STRING ? firstNowCell.getStringCellValue() : firstNowCell.getNumericCellValue(); Row preRow = cell.getSheet().getRow(curRowIndex - 1); if (preRow == null) { // 当获取不到上一行数据时，使用缓存sheet中数据 preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1); } Cell preCell = preRow.getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行 if (curData.equals(preData)) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } }}ExcelFillCellMergePrevColUtils:
import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.HashMap;import java.util.List;import java.util.Map; /** * 列合并工具类 */public class ExcelFillCellMergePrevColUtils implements CellWriteHandler { private static final String KEY = "%s-%s"; //所有的合并信息都存在了这个map里面 Map<String, Integer> mergeInfo = new HashMap<>(); public ExcelFillCellMergePrevColUtils() { } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex)); if (null != num) { // 合并最后一行 ,列 this.mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex, num); } } public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) { Sheet sheet = writeSheetHolder.getSheet(); CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num); sheet.addMergedRegion(cellRangeAddress); } //num从第几列开始增加多少列，(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并 public void add(int curRowIndex, int curColIndex, int num) { mergeInfo.put(String.format(KEY, curRowIndex, curColIndex), num); } }3.上传Excel模板文件
将excel模板文件上传至resources下的excel目录下（路径没有限制只要能够读取到）。
模板excel文件截图（直接创建一个excel文件就行，我就不上传了）
4.接口Controller测试
实体类User
import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.time.LocalDate;@Data@AllArgsConstructor@NoArgsConstructorpublic class User { private Integer id; // 编号 private String name; // 姓名 private String idNumber; // 身份证号 private String phoneNumber; // 手机号 private LocalDate createTime; // 创建日期}接口ExcelController
import com.slice.reactminiospring.entity.User;import com.slice.reactminiospring.util.ExcelUtils;import jakarta.servlet.http.HttpServletResponse;import lombok.extern.slf4j.Slf4j;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.time.LocalDate;import java.time.format.DateTimeFormatter;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;@RestController@Slf4j@RequestMapping("/")public class ExcelController { //127.0.0.1:8080/generateWordForm @GetMapping( "/generateExcelForm") public void generateExcelForm(HttpServletResponse response) throws Exception { // excelParam为入参对象，也可拆成单个参数来接收 // 根据入参查询用户数据集合// List<User> resultList = userService.list(); List<User> resultList = new ArrayList<>(); resultList.add(new User(1, "张三", "513701199001011111", "15592111112", LocalDate.now())); resultList.add(new User(2, "李四", "513701199001011112", "15592111113", LocalDate.now())); resultList.add(new User(3, "王五", "513701199001011113", "15592111114", LocalDate.now())); String title = "这个是Excel导出后Excel里面显示的标题"; Map<String, Object> outerMap = new HashMap<>(2); outerMap.put("title", title); List<Map<String, Object>> innerMapList = new ArrayList<>(); User item; String startDate; for (int i = 0; i < resultList.size(); ++i) { item = resultList.get(i); Map<String, Object> innerMap = new HashMap<>(16); innerMap.put("index", i + 1); innerMap.put("name", item.getName()); innerMap.put("idNumber", item.getIdNumber()); innerMap.put("phoneNumber", item.getPhoneNumber()); // 注意：时间需要转换成字符串形式 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); startDate = item.getCreateTime().format(formatter); innerMap.put("createTime", startDate); innerMapList.add(innerMap); } //注意这个文件创建的时候格式就要是xls，不然会报错，读取不到等问题 ExcelUtils.exportToTemplate(response, "excel/excel模板1.xls", title, outerMap, innerMapList, null, null); }}Postman进行接口测试：
excel文件验证
上述就实现了将数据导出到excel指定模板中了，非常的好用。