springboot 2.4.1
JDK 1.8
mybatis-plus 3.2.0
mysql-connector-java 8.0.11
druid 1.1.18
easyexcel 2.0.2
导出导入 Excel 效果图1
导出导入 Excel 效果图2
目录
1、controller 表现层调用
表现层的接口调用
1.导出的调用
2.导入的调用
package com.ilyuc.easy_excel.controller;
import com.ilyuc.easy_excel.entity.UserEntity;
import com.ilyuc.easy_excel.service.Impl.UserBasicServiceImpl;
import com.ilyuc.easy_excel.service.UserService;
import com.ilyuc.easy_excel.util.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
/**
* @author ilyuc
* @version v 1.0.0
* @Description
* @date 2021/1/8 10:42
*/
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Autowired
UserService userService;
@Autowired
UserBasicServiceImpl userBasicService;
/**
* 下载 Excel
* @param response
*/
@GetMapping("/download")
public void downloadTemplate(HttpServletResponse response){
String fileName = "文件名";
String sheetName="sheet页名";
List<UserEntity> userList = userService.getUserList(new UserEntity());
try {
//导出不同的模板传不同的 “数据集合userList” 和 “实体类字节码对象UserEntity.class”
ExcelUtil.writeExcel(response,userList,fileName,sheetName,UserEntity.class);
} catch (Exception e) {
System.out.println(e.getCause());
}
}
/**
* 上传 Excel
* @param excel
* @return
*/
@PostMapping(value = "/upload")
public String read(MultipartFile excel) {
//不同的模板传入不同的 批处理业务类、实体类 即可
ExcelUtil.readExcel(excel,new UserBasicServiceImpl(), UserEntity.class, 0);
//使用这个传参方式可以结合 spring 的 bean 调用项目 dao 或其他
// 上面的 new 没交给 spring 容器,后面 Autowired 会空指针
//ExcelUtil.readExcel(excel,userBasicService, UserEntity.class, 0);
return "Excel import successful";
}
}
2、ExcelUtil 工具类
导出时使用自定义 表格的样式策略(标题策略,内容策略)
导出时 自定义行拦截器(给首列加序号和样式)
导入时使用 自定义监听 (主要做导入时数据的批处理)
package com.ilyuc.easy_excel.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.ilyuc.easy_excel.service.BasicService;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* @author ilyuc
* @version v 1.0.0
* @Description
* @date 2021/1/8 10:39
*/
public class ExcelUtil {
/**
* 导出 Excel
* @param response
* @param data 需导出的实体类列表
* @param fileName 导出的文件名
* @param sheetName sheet页名
* @param clazz 实体类字节码对象
* @throws Exception
*/
public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
/* 1.使用简单样式,并通过浏览器保存
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(getOutputStream(fileName, response), clazz)
.excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
.registerWriteHandler(horizontalCellStyleStrategy)
.doWrite(data);
*/
/* 2.使用自定义样式,通过绝对路径保存
String fileName = "C:\\Users\\Administrator\\Desktop\\customHandlerWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
EasyExcel.write(fileName, clazz)
.registerWriteHandler(CellStyleUtil.getHorizontalCellStyleStrategy())
.registerWriteHandler(new CustomRowWriteHandler())
.sheet(sheetName)
.doWrite(data);
*/
// 3.使用自定义样式,并通过浏览器保存
// 注册的第一个 Handler 是表格的样式策略
// 注册的第二个 Handler 是自定义自定义行拦截器,给首列加序号和样式
EasyExcel.write(getOutputStream(fileName, response), clazz)
.registerWriteHandler(CellStyleUtil.getHorizontalCellStyleStrategy())
.registerWriteHandler(new CustomRowWriteHandler())
.sheet(sheetName)
.doWrite(data);
}
private static ServletOutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
response.addHeader("Access-Control-Expose-Headers", "Content-disposition");
return response.getOutputStream();
}
/**
* 单页读取导入
* @param excel 导入的excel
* @param basicService 业务处理类
* @param clazz 实体类
* @param sheetNo 页面号
* @param <T>
*/
public static <T>void readExcel(MultipartFile excel, BasicService basicService, Class<T> clazz, int sheetNo) {
BasicExcelListener excelListener = new BasicExcelListener(basicService);
ExcelReader excelReader = getReader(excel,clazz,excelListener);
if (excelReader == null) {
return ;
}
ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
excelReader.read(readSheet);
excelReader.finish();
}
/**
* 多页读取导入
* @param excel 导入的excel
* @param basicService 业务数据处理类
* @param clazz 实体类
* @param <T>
*/
public static <T> void readExcel(MultipartFile excel, BasicService basicService,Class<T> clazz) {
BasicExcelListener excelListener = new BasicExcelListener(basicService);
ExcelReader excelReader = getReader(excel,clazz,excelListener);
if (excelReader == null) {
return ;
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet:readSheetList){
excelReader.read(readSheet);
}
excelReader.finish();
}
/**
* 返回 ExcelReader
* @param excel 文件
* @param clazz 实体类
* @param excelListener
*/
private static <T> ExcelReader getReader(MultipartFile excel, Class<T> clazz, BasicExcelListener excelListener) {
String filename = excel.getOriginalFilename();
try {
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
return null;
}
InputStream inputStream = new BufferedInputStream(excel.getInputStream());
ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
inputStream.close();
return excelReader;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
3、1> CellStyleUtil 导出时样式策略
设置首列单元格样式 (居中、边框、文字大小等)
设置标题头和文本的样式策略 (背景色、字体、居中、边框等)
package com.ilyuc.easy_excel.util;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
/**
* @author ilyuc
* @version v 1.0.0
* @Description
* @date 2021/1/8 11:12
*/
public class CellStyleUtil {
/**
* @Author ilyuc
* @Description 设置首列单元格样式 CellStyle
* @Date 11:00 2021/1/12
* @Param [workbook]
* @return org.apache.poi.ss.usermodel.CellStyle
*/
public static CellStyle firstCellStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
//设置边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
//文字
Font font = workbook.createFont();
font.setBold(Boolean.TRUE);
cellStyle.setFont(font);
return cellStyle;
}
/**
* @Author ilyuc
* @Description 设置标题头和文本的样式策略
* @Date 10:59 2021/1/12
* @Param []
* @return com.alibaba.excel.write.style.HorizontalCellStyleStrategy
*/
public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
// 标题头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为蓝色
headWriteCellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)12);
headWriteFont.setFontName("微软雅黑");
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景黄色
// contentWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//边框
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//文字
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)12);
contentWriteFont.setFontName("微软雅黑");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
3、2> CustomRowWriteHandler 导出时自定义行拦截器
该拦截器就是导出表格时给首列加序号,加样式的
package com.ilyuc.easy_excel.util;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @author ilyuc
* @version v 1.0.0
* @Description 自定义行拦截器
* @date 2021/1/8 11:19
*/
public class CustomRowWriteHandler implements RowWriteHandler {
private static Logger LOGGER= LoggerFactory.getLogger(CustomRowWriteHandler.class);
/**
* 一定将样式设置成全局变量
* 首行只需要创建一次样式就可以 不然每行都创建一次 数据量大的话会保错
* 异常信息:The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
*/
private CellStyle firstCellStyle;
/**
* 列号
*/
private int count = 0;
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, int i, int i1, boolean b) {
}
/**
* @Author ilyuc
* @Description 给首列加序号和样式
* @Date 15:00 2021/1/8
* @Param [writeSheetHolder, writeTableHolder, row, i, b]
* @return void
*/
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, int i, boolean b) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("第{"+row.getRowNum()+"}行创建完毕!");
}
if(row.getRowNum()==0){
return;
}
Cell cell = row.createCell(0);
if (firstCellStyle == null) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
firstCellStyle = CellStyleUtil.firstCellStyle(workbook);
LOGGER.info("设置首列样式成功");
}
cell.setCellStyle(firstCellStyle);
//设置列宽 0列 10个字符宽度
writeSheetHolder.getSheet().setColumnWidth(0, 10 * 256);
// 实体类中没有首列 序号 标题时候,可以使用下面代码,且还需加标题样式
// if (row.getRowNum() == 0) {
// cell.setCellValue("序号");
// return;
// }
cell.setCellValue(++count);
}
}
3、3> BasicExcelListener 导入时自定义监听
主要做表格导入读取数据时即对数据做批处理
需要在构建的时候传入对应的批处理业务类
不同的模板只要写一个不同的业务类,即可复用该代码来做处理
package com.ilyuc.easy_excel.util;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ilyuc.easy_excel.service.BasicService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
/**
* @author ilyuc
* @version v 1.0.0
* @Description 上传 Excel 时自定义监听
* @date 2021/1/12 11:33
*/
public class BasicExcelListener<T> extends AnalysisEventListener<T> {
private static Logger LOGGER = LoggerFactory.getLogger(BasicExcelListener.class);
private BasicService basicService;
List<T> list = new ArrayList<T>();
/**
* 构造时传入 对应的 批处理业务类
* @param basicService
*/
public BasicExcelListener(BasicService basicService) {
this.basicService = basicService;
}
/**
* 批处理阈值2000
*/
private static final int BATCH_COUNT = 2000;
/**
* 计数
*/
private int count = 0;
@Override
public void invoke(T t, AnalysisContext analysisContext) {
//获取对应的行数
int num = analysisContext.readRowHolder().getRowIndex();
LOGGER.info("处理第{}行",num);
list.add(t);
count++;
if (count >= BATCH_COUNT) {
saveData();
list.clear();
count=0;
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
LOGGER.info("所有数据解析完成!");
}
private void saveData() {
//调用处理数据的业务类的方法
if(list.size()>0){
basicService.saveData(list);
}
}
}
4、BasicService 批处理接口以及业务类
导入不同的模板,只需要以下三点
1.重写一个批处理业务类实现该接口
2.重写一个 entity 实体类
3.调用 ExcelUtil 工具类时传入对应的参数
泛型对象有一个转换成对象的过程,使用工具类 BeanConvert
批处理接口
package com.ilyuc.easy_excel.service;
import java.util.List;
/**
* @author ilyuc
* @Description
* @date 2021/1/12 14:48
*/
public interface BasicService {
<T> void saveData(List<T> list);
}
批处理业务类
package com.ilyuc.easy_excel.service.Impl;
import com.ilyuc.easy_excel.entity.UserEntity;
import com.ilyuc.easy_excel.service.BasicService;
import com.ilyuc.easy_excel.service.UserService;
import com.ilyuc.easy_excel.util.BeanConvert;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author ilyuc
* @version v 1.0.0
* @Description 批处理业务类
* @date 2021/1/12 14:49
*/
@Service
public class UserBasicServiceImpl implements BasicService {
@Autowired
private UserService userService;
/**
* @Author ilyuc
* @Description 批处理保存操作
* @Date 14:52 2021/1/12
* @Param [list]
* @return void
*/
@Override
public <T> void saveData(List<T> list) {
List<UserEntity> userEntities = BeanConvert.objectConvertBean(list, UserEntity.class);
userEntities.forEach(System.out::println);
//调用各个处理数据的业务类
//userService.saveEasyExcelData(userEntities);
}
}
5、BeanConvert 对象转换工具类
泛型转对应的实体类对象
package com.ilyuc.easy_excel.util;
import org.springframework.beans.BeanUtils;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* @author ilyuc
* @version v 1.0.0
* @Description 装换成目标对象工具类
* @date 2021/1/12 14:04
*/
public class BeanConvert {
/**
* 将List<Object> 转换为List<Bean>
* @param sources 源对象
* @param targetClass 目标类
* @param <T>
* @return
*/
public static <T> List<T> objectConvertBean(List<?> sources, Class<T> targetClass) {
List<?> sourcesObj = sources;
if (sourcesObj == null) {
sourcesObj = Collections.emptyList();
}
List<T> targets = new ArrayList<>(sourcesObj.size());
convert(sourcesObj, targets, targetClass);
return targets;
}
/**
* 复制源对象到目的对象
* 注意:
* org.springframework.beans.BeanUtils.copyProperties 是一个Spring提供的名称相同的工具类
* 但它不支持类型自动转换,如果某个类型属性不同,则不予转换那个属性
* org.apache.commons.beanutils.BeanUtils 是一个Apache提供的名称相同的工具类
* 支持类型自动转换,如Date类型会自动转换为字符串
* @param sources 源对象
* @param targets 目的对象
* @param targetClass 目标类
* @param <T>
*/
private static <T> void convert(List<?> sources, List<T> targets, Class<T> targetClass) {
if (sources == null) {
return;
}
if (targets == null) {
return;
}
targets.clear();
for (Object obj : sources) {
try {
T target = targetClass.newInstance();
targets.add(target);
BeanUtils.copyProperties(obj, target);
} catch (Exception e) {
return;
}
}
}
}
6、UserEntity 实体类
该实体类与 mybatis-plus 公用
类中设置了 EasyExcel 的该表格行高,标题名称,规定的字段宽度
有两种方式,合并标题,与单列简单标题
当前是复杂标题样式
package com.ilyuc.easy_excel.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
/**
* @author ilyuc
* @version v 1.0.0
* @Description
* @date 2021/1/6 13:50
*/
@ContentRowHeight(18)//EasyExcel的该表格行高
public class UserEntity{
@ExcelProperty(value = "序号",index = 0)
private String num;
// @ExcelProperty(value = "名称",index = 1)
@ExcelProperty(value = {"合并列1", "名称"},index = 1)
private String name;
// @ExcelProperty(value = "绰号",index = 2)
@ExcelProperty(value = {"合并列1", "绰号"},index = 2)
private String alisa;
// @ExcelProperty(value = "年龄",index = 3)
@ExcelProperty(value = {"合并列2", "年龄"},index = 3)
private int age;
// @ExcelProperty(value = "身高",index = 4)
@ExcelProperty(value = {"合并列2","合并列3", "身高"},index = 4)
private String hall;
// @ExcelProperty(value = "性别",index = 5)
@ExcelProperty(value = {"合并列2","合并列3", "性别"},index = 5)
private String sex;
@ExcelProperty(value = "体重",index = 6)
private double weight;
@ColumnWidth(30)//EasyExcel的该字段列宽
@ExcelProperty(value = "邮箱",index = 7)
private String email;
public void setName(String name) {
this.name = name;
}
public void setAlisa(String alisa) {
this.alisa = alisa;
}
public void setAge(int age) {
this.age = age;
}
public void setHall(String hall) {
this.hall = hall;
}
public void setSex(String sex) {
this.sex = sex;
}
public void setWeight(double weight) {
this.weight = weight;
}
public void setEmail(String email) {
this.email = email;
}
public String getName() {
return name;
}
public String getAlisa() {
return alisa;
}
public int getAge() {
return age;
}
public String getHall() {
return hall;
}
public String getSex() {
return sex;
}
public double getWeight() {
return weight;
}
public String getEmail() {
return email;
}
@Override
public String toString() {
return "UserEntity{" +
"name='" + name + '\'' +
", alisa='" + alisa + '\'' +
", age='" + age + '\'' +
", hall='" + hall + '\'' +
", sex='" + sex + '\'' +
", weight='" + weight + '\'' +
", email='" + email + '\'' +
'}';
}
}
7、导出 Excel 测试
浏览器直接输入地址并回车
8、导入 Excel 测试
使用 postman 测试
代码我已上传github: 快速通道
(完)