【Java】将数据导入到指定Excel模板(多个sheet页)当中（代码实现）
Mxin5 2024-09-10 17:05:02 阅读 87
目录
1.引入依赖
2.工具类
ExcelUtils:
ExcelFillCellMergeStrategyUtils:
ExcelFillCellMergePrevColUtils:
3.上传Excel模板文件
4.接口Controller测试
实体类User
接口ExcelController
Postman进行接口测试：
excel文件验证
实际开发过程中，经常会遇到这种业务，第一种方式就是后台直接生成一个带有数据的excel文件，第二种就是提前编写好Excel模板文件，直接将数据填充到Excel文件当中。那么下述就分享一下这个功能代码如何实现。
1.引入依赖
<code> <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");code>
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");code>
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
<code>
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.time.LocalDate;
@Data
@AllArgsConstructor
@NoArgsConstructor
public 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指定模板中了，非常的好用。
声明
本文内容仅代表作者观点，或转载于其他网站，本站不以此文作为商业用途
如有涉及侵权，请联系本站进行删除
转载本站原创文章，请注明来源及作者。