SpringBoot整合EasyExcel
1.导入依赖
添加maven依赖, 依赖的poi最低版本3.17
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
2.创建实体类
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
@ExcelProperty(value = "用户编号")
private Integer userId;
@ExcelProperty(value = "姓名")
private String userName;
@ExcelProperty(value = "性别")
private String gender;
@ExcelProperty(value = "工资")
private Double salary;
@ExcelProperty(value = "入职时间")
private Date hireDate;
}
3.写入Excel
3.1方法一
根据user模板构建数据
private List<User> getUserData() {
List<User> users = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
User user = User.builder()
.userId(i)
.userName("admin" + i)
.gender(i % 2 == 0 ? "男" : "女")
.salary(i * 1000.00)
.hireDate(new Date())
.build();
users.add(user);
}
return users;
}
@Test
public void testWriteExcel() {
String filename = "D:\\study\\excel\\user1.xlsx";
// 向Excel中写入数据 也可以通过 head(Class<?>) 指定数据模板
EasyExcel.write(filename, User.class)
.sheet("用户信息")
.doWrite(getUserData());
}
3.2方法二
@Test
public void testWriteExcel2() {
String filename = "D:\\study\\excel\\user2.xlsx";
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
// 向Excel中写入数据
excelWriter.write(getUserData(), writeSheet);
// 关闭流
excelWriter.finish();
}
3.3排除模型中某些属性字段
指定字段不写入excel
@Test
public void testWriteExcel3() {
String filename = "D:\\study\\excel\\user3.xlsx";
// 设置排除的属性 也可以在数据模型的字段上加@ExcelIgnore注解排除
Set<String> excludeField = new HashSet<>();
excludeField.add("hireDate");
excludeField.add("salary");
// 写Excel
EasyExcel.write(filename, User.class)
.excludeColumnFiledNames(excludeField)
.sheet("用户信息")
.doWrite(getUserData());
}
3.4向表格中导出指定属性
@Test
public void testWriteExcel4() {
String filename = "D:\\study\\excel\\user4.xlsx";
// 设置要导出的字段
Set<String> includeFields = new HashSet<>();
includeFields.add("userName");
includeFields.add("hireDate");
// 写Excel
EasyExcel.write(filename, User.class)
.includeColumnFiledNames(includeFields)
.sheet("用户信息")
.doWrite(getUserData());
}
3.5在Excel表格中进行列排序
只需要在注解ExcelProperty中使用index属性指定列顺序
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
@ExcelProperty(value = "用户编号", index = 0)
private Integer userId;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
@ExcelProperty(value = "性别", index = 3)
private String gender;
@ExcelProperty(value = "工资", index = 4)
private Double salary;
@ExcelProperty(value = "入职时间", index = 2)
private Date hireDate;
}
3.6复杂头数据写入
@ExcelProperty注解的value属性是一个数组类型, 设置多个head时会自动合并.
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class ComplexHeadUser {
@ExcelProperty(value = {"group1", "用户编号"}, index = 0)
private Integer userId;
@ExcelProperty(value = {"group1", "姓名"}, index = 1)
private String userName;
@ExcelProperty(value = {"group2", "入职时间"}, index = 2)
private Date hireDate;
}
3.7写到Excel的不同Sheet中
@Test
public void testWriteExcel8() {
String filename = "D:\\study\\excel\\user8.xlsx";
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(filename, User.class).build();
// 向Excel的同一个Sheet重复写入数据
for (int i = 0; i < 2; i++) {
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息" + i).build();
excelWriter.write(getUserData(), writeSheet);
}
// 关闭流
excelWriter.finish();
}
3.8日期/数字类型格式化
对于日期和数字,有时候需要对其展示的样式进行格式化, EasyExcel提供了以下注解
@DateTimeFormat 日期格式化
@NumberFormat 数字格式化(小数或百分数)
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
@ExcelProperty(value = "用户编号", index = 0)
private Integer userId;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
@ExcelProperty(value = "性别", index = 3)
private String gender;
@ExcelProperty(value = "工资", index = 4)
@NumberFormat(value = "###.#") // 数字格式化,保留1位小数
private Double salary;
@ExcelProperty(value = "入职时间", index = 2)
@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒") // 日期格式化
private Date hireDate;
}
3.9写入图片到Excel
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@ContentRowHeight(value = 100) // 内容行高
@ColumnWidth(value = 20) // 列宽
public class ImageData {
//使用抽象文件表示一个图片
@ExcelProperty(value = "File类型")
private File file;
// 使用输入流保存一个图片
@ExcelProperty(value = "InputStream类型")
private InputStream inputStream;
// 当使用String类型保存一个图片的时候需要使用StringImageConverter转换器
@ExcelProperty(value = "String类型", converter = StringImageConverter.class)
private String str;
// 使用二进制数据保存为一个图片
@ExcelProperty(value = "二进制数据(字节)")
private byte[] byteArr;
// 使用网络链接保存为一个图片
@ExcelProperty(value = "网络图片")
private URL url;
}
@Test
public void testWriteImageToExcel() throws IOException {
String filename = "D:\\study\\excel\\user10.xlsx";
// 图片位置
String imagePath = "D:\\study\\excel\\me.jpg";
// 网络图片
URL url = new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN3246625733_1920x1080.jpg&rf=LaDigue_1920x1080.jpg&pid=hp");
// 将图片读取到二进制数据中
byte[] bytes = new byte[(int) new File(imagePath).length()];
InputStream inputStream = new FileInputStream(imagePath);
inputStream.read(bytes, 0, bytes.length);
List<ImageData> imageDataList = new ArrayList<>();
// 创建数据模板
ImageData imageData = ImageData.builder()
.file(new File(imagePath))
.inputStream(new FileInputStream(imagePath))
.str(imagePath)
.byteArr(bytes)
.url(url)
.build();
// 添加要写入的图片模型
imageDataList.add(imageData);
// 写数据
EasyExcel.write(filename, ImageData.class)
.sheet("帅哥")
.doWrite(imageDataList);
}
3.10设置写入Excel的列宽和行高
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽, 可以作用在类或字段上
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽
public class WidthAndHeightData {
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
@ColumnWidth(value = 25)
private Double doubleData;
}
3.11通过注解形式设置写入Excel样式
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 30) // 头部行高
@ContentRowHeight(value = 25) // 内容行高
@ColumnWidth(value = 20) // 列宽
// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20, 字体默认宋体
@HeadFontStyle(fontName = "宋体", fontHeightInPoints = 20)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成20, 字体默认宋体
@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
public class DemoStyleData {
// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
// 字符串的头字体设置成20
@HeadFontStyle(fontHeightInPoints = 30)
// 字符串的内容背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
// 字符串的内容字体设置成20,默认宋体
@ContentFontStyle(fontName = "宋体", fontHeightInPoints = 20)
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
private Double doubleData;
}
3.12合并单元格
@OnceAbsoluteMerge 指定从哪一行/列开始,哪一行/列结束,进行单元格合并
- firstRowIndex 起始行索引,从0开始
- lastRowIndex 结束行索引
- firstColumnIndex 起始列索引,从0开始
- lastColumnIndex 结束列索引
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 25) // 头部行高
@ContentRowHeight(value = 20) // 内容行高
@ColumnWidth(value = 20) // 列宽
// 例如: 第2-3行,2-3列进行合并
@OnceAbsoluteMerge(firstRowIndex = 1, lastRowIndex = 2, firstColumnIndex = 1, lastColumnIndex = 2)
public class DemoMergeData {
// 每隔两行合并一次(竖着合并单元格)
// @ContentLoopMerge(eachRow = 2)
@ExcelProperty(value = "字符串标题")
private String string;
@ExcelProperty(value = "日期标题")
private Date date;
@ExcelProperty(value = "数字标题")
private Double doubleData;
}
3.13数据转换器
在实际应用场景中, 我们系统db存储的数据可以是枚举, 在界面或导出到Excel文件需要展示为对于的枚举值形式.
比如: 性别, 状态等. EasyExcel提供了转换器接口Converter供我们使用, 我们只需要自定义转换器实现接口, 并将自定义转换器类型传入要转换的属性字段中. 以下面的性别gender字段为例:
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class UserModel {
@ExcelProperty(value = "用户编号", index = 0)
private Integer userId;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
// 性别添加了转换器, db中存入的是integer类型的枚举 0 , 1 ,2
@ExcelProperty(value = "性别", index = 3, converter = GenderConverter.class)
private Integer gender;
@ExcelProperty(value = "工资", index = 4)
@NumberFormat(value = "###.#")
private Double salary;
@ExcelProperty(value = "入职时间", index = 2)
@DateTimeFormat(value = "yyyy年MM月dd日 HH时mm分ss秒")
private Date hireDate;
}
/**
* 类描述:性别字段的数据转换器
* @Author wang_qz
* @Date 2021/8/15 19:16
* @Version 1.0
*/
public class GenderConverter implements Converter<Integer> {
private static final String MALE = "男";
private static final String FEMALE = "女";
private static final String NONE = "未知";
// Java数据类型 integer
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
// Excel文件中单元格的数据类型 string
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
// 读取Excel文件时将string转换为integer
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration)
throws Exception {
String value = cellData.getStringValue();
if (Objects.equals(FEMALE, value)) {
return 0; // 0-女
} else if (Objects.equals(MALE, value)) {
return 1; // 1-男
}
return 2; // 2-未知
}
// 写入Excel文件时将integer转换为string
@Override
public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (value == 1) {
return new CellData(MALE);
} else if (value == 0) {
return new CellData(FEMALE);
}
return new CellData(NONE); // 不男不女
}
}
4.读Excel
4.1读API的拆分
在读取Excel表格数据时, 将读取的每行记录映射成一条LinkedHashMap记录, 而没有映射成实体类.
@Test
public void testRead() {
String filename = "D:\\study\\excel\\read.xlsx";
// 创建ExcelReaderBuilder对象
ExcelReaderBuilder readerBuilder = EasyExcel.read();
// 获取文件对象
readerBuilder.file(filename);
// 指定映射的数据模板
// readerBuilder.head(DemoData.class);
// 指定sheet
readerBuilder.sheet(0);
// 自动关闭输入流
readerBuilder.autoCloseStream(true);
// 设置Excel文件格式
readerBuilder.excelType(ExcelTypeEnum.XLSX);
// 注册监听器进行数据的解析
readerBuilder.registerReadListener(new AnalysisEventListener() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(Object demoData, AnalysisContext analysisContext) {
// 如果没有指定数据模板, 解析的数据会封装成 LinkedHashMap返回
// demoData instanceof LinkedHashMap 返回 true
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
});
readerBuilder.doReadAll();
/* // 构建读取器
ExcelReader excelReader = readerBuilder.build();
// 读取Excel
excelReader.readAll();
// 关闭流
excelReader.finish();*/
}
4.2方法一
关键是写一个监听器,实现AnalysisEventListener, 每解析一行数据会调用invoke方法返回解析的数据, 当全部解析完成后会调用doAfterAllAnalysed方法. 我们重写invoke方法和doAfterAllAnalysed方法即可.
@Test
public void testReadExcel() {
// 读取的excel文件路径
String filename = "D:\\study\\excel\\read.xlsx";
// 读取excel
EasyExcel.read(filename, DemoData.class, new AnalysisEventListener<DemoData>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).sheet().doRead();
}
4.3方法二
@Test
public void testReadExcel2() {
// 读取的excel文件路径
String filename = "D:\\study\\excel\\read.xlsx";
// 创建一个数据格式来装读取到的数据
Class<DemoData> head = DemoData.class;
// 创建ExcelReader对象
ExcelReader excelReader = EasyExcel.read(filename, head, new AnalysisEventListener<DemoData>() {
// 每解析一行数据,该方法会被调用一次
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("解析数据为:" + demoData.toString());
}
// 全部解析完成被调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("解析完成...");
// 可以将解析的数据保存到数据库
}
}).build();
// 创建sheet对象,并读取Excel的第一个sheet(下标从0开始), 也可以根据sheet名称获取
ReadSheet sheet = EasyExcel.readSheet(0).build();
// 读取sheet表格数据, 参数是可变参数,可以读取多个sheet
excelReader.read(sheet);
// 需要自己关闭流操作,在读取文件时会创建临时文件,如果不关闭,会损耗磁盘,严重的磁盘爆掉
excelReader.finish();
}
5.Excel下载
5.1方法一
**
* 使用EasyExcel操作excel文件上传/下载
*/
@Controller
@RequestMapping(value = "/xlsx")
public class EasyExcelController {
@RequestMapping("/toExcelPage")
public String todownloadPage() {
return "excelPage";
}
/**
* 下载Excel
* @param request
* @param response
*/
@RequestMapping(value = "/downloadExcel")
public void downloadExcel(HttpServletRequest request,
HttpServletResponse response) throws Exception {
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 设置防止中文名乱码
String filename = URLEncoder.encode("员工信息", "utf-8");
// 文件下载方式(附件下载还是在当前浏览器打开)
response.setHeader("Content-disposition", "attachment;filename=" +
filename + ".xlsx");
// 构建写入到excel文件的数据
List<UserExcel> userExcels = new ArrayList<>();
UserExcel userExce1 = new UserExcel(1001, "张三", "男", 1333.33,
new Date());
UserExcel userExce2 = new UserExcel(1002, "李四", "男", 1356.83,
new Date());
UserExcel userExce3 = new UserExcel(1003, "王五", "男", 1883.66,
new Date());
UserExcel userExce4 = new UserExcel(1004, "赵六", "男", 1393.39,
new Date());
userExcels.add(userExce1);
userExcels.add(userExce2);
userExcels.add(userExce3);
userExcels.add(userExce4);
// 写入数据到excel
EasyExcel.write(response.getOutputStream(), UserExcel.class)
.sheet("用户信息")
.doWrite(userExcels);
}
}
5.2方法二
**
* 使用EasyExcel操作excel文件上传/下载
*/
@Controller
@RequestMapping(value = "/xlsx")
public class EasyExcelController {
/**
* 下载Excel
* @param request
* @param response
*/
@RequestMapping(value = "/downloadExcel")
public void downloadExcel(HttpServletRequest request,
String fileName = "user2.xlsx";
String path = "D:\\study\\excel\\"+fileName;
// 创建ExcelWriter对象
ExcelWriter excelWriter = EasyExcel.write(path, User.class).build();
// 创建Sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
// 向Excel中写入数据
excelWriter.write(getUserData(), writeSheet);
// 关闭流
excelWriter.finish();
File file = new File(path);//创建出要下载的文件
String downname = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
resp.setHeader("content-disposition", "attachment;filename=" + downname);//filename=下载文件的名字 只认iso-8859-1
FileInputStream inputStream = new FileInputStream(file);//读取文件
OutputStream outputStream = resp.getOutputStream();//写到客户端
byte[] bs = new byte[(int) file.length()];
inputStream.read(bs);
outputStream.write(bs);
outputStream.close();
inputStream.close();
}
}
前端页面文章来源:https://www.toymoban.com/news/detail-467154.html
<a th:href="@{/xlsx/downloadExcel}" style="color: white;text-decoration: none;">导出Excel</a>
6.Excl上传
@PostMapping("/upload")
public R<List> fileUpload(MultipartFile file) {
//获取文件名
String originalFilename = file.getOriginalFilename();
//获取文件后缀名
String substring = originalFilename.substring(originalFilename.lastIndexOf("."));
//用UUID随机生成文件名,防止文件名重复导入文件覆盖
String filename = UUID.randomUUID().toString() + substring;
//判断当前文件是否存在
File file1 = new File(filePath);
if (!file1.exists()) {
// 不存在,需要创建
file1.mkdir();
}
//保存文件到指定位置
try {
file.transferTo(new File(filePath + filename));
} catch (IOException e) {
e.printStackTrace();
}
List list = planCourseByExcel(filePath + filename);
return R.success(list);
}
前端页面文章来源地址https://www.toymoban.com/news/detail-467154.html
<!doctype html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form th:action="@{/admin/upload}" method="POST" enctype="multipart/form-data">
<input type="file" id="folder" name="file" accept=".xls,.xlsx"/>
<input type="submit" value="上传文件夹"/>
</form>
</body>
</html>
到了这里,关于SpringBoot整合EasyExcel的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!