文章目录
一、简单一键导出Excel
直接给出生成效果
Empty,这个很关键
- 因为是通过empty上的 @ExcelProperty注解生成表头的,excel中的数据也需要使用这个类导入
package com.ljj.empty;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @Author lijunyun
* @Date 2022/10/26 10:20
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentEmpty {
@ExcelProperty(index = 0,value = "学生id")
private String id;
@ExcelProperty(index = 1,value = "姓名")
private String name;
@ExcelProperty(index = 2,value = "年龄")
private Integer age;
}
controller层
- 用的是 alibaba 的工具类,按照这个import就能导入适当的依赖了,大家在maven自行导入。
package com.ljj.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.ljj.empty.StudentEmpty;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.LinkedList;
import java.util.List;
/**
* @Author lijunyun
* @Date 2022/10/11 17:25
*/
@RequestMapping("/test")
@RestController
public class TestController {
@GetMapping("")
public String test01() {
return "test01,端口9001";
}
@GetMapping("/ExportExcel")
public void ExportExcel(HttpServletResponse response) {
OutputStream outputStream = null;
try{
List<StudentEmpty> data=new LinkedList<StudentEmpty>();
data.add(new StudentEmpty("1","张三",18));
data.add(new StudentEmpty("2","小明",19));
outputStream =response.getOutputStream();
response.setHeader("Content-disposition","attachment;filename=tableStructureInfo_"+".xlsx");
ExcelWriter excelWriter= EasyExcel.write(outputStream).build();
WriteSheet writeSheet=EasyExcel.writerSheet(0,"表结构").head(StudentEmpty.class).build();
excelWriter.write(data,writeSheet);
excelWriter.finish();
}catch (IOException e){
e.printStackTrace();
}finally {
if (outputStream !=null){
try {
outputStream.close();
}catch (IOException e){
e.printStackTrace();
}
}
}
}
}
EasyExcel类的多种使用方式
- 快速生成Excel,这里举多种方式
EasyExcel.write(outputStream).head(StudentEmpty.class).sheet(1,"表结构1").doWrite(data);
EasyExcel.write(outputStream,StudentEmpty.class).sheet(2,"表结构2").doWrite(data);
- 快速生成Excel,一个Excel生成多个表
WriteSheet writeSheet=EasyExcel.writerSheet(1,"表1").head(StudentEmpty.class).build();
WriteSheet writeSheet01=EasyExcel.writerSheet(2,"表2").head(StudentEmpty.class).build();
EasyExcel.write(outputStream).head(StudentEmpty.class).build().write(data,writeSheet).write(data,writeSheet01).finish();
二、导入Excel生成List<>数据
controller层,简单写法
/**
* 导入用户信息
* @param file
* @throws IOException
*/
@PostMapping("/excelImport")
public void excelImport(MultipartFile file) throws IOException {
//调用方法实现读取
List<StudentEmpty> lists=EasyExcel.read(file.getInputStream()).head(StudentEmpty.class).sheet().doReadSync();
}
监听器写法(观察者模式),稍微麻烦
- 观察者,数据处理在这个类中执行
package com.ljj.other;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ljj.empty.StudentEmpty;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSON;
import java.util.ArrayList;
import java.util.List;
/**
* @Author lijunyun
* @Date 2022/10/26 13:09
*/
@Slf4j
@Service
public class ExcelItemListener extends AnalysisEventListener<StudentEmpty> {
/**
* 批处理阈值
*/
private static final int BATCH_COUNT = 5;
List<StudentEmpty> list = new ArrayList<StudentEmpty>(BATCH_COUNT);
@Override
public void invoke(StudentEmpty excelItem, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(excelItem));
list.add(excelItem);
if (list.size() >= BATCH_COUNT) {
importItemInfo(list);
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
importItemInfo(list);
log.info("所有数据解析完成!");
}
public void importItemInfo(List<StudentEmpty> infoList) {
//导入操作---新增公司和项目数据
}
}
- controller 层,不做excel的数据处理
/**
* 导入用户信息
* @param file
* @throws IOException
*/
@PostMapping("/excelImport")
public void excelImport(MultipartFile file) throws IOException {
//调用方法实现读取
EasyExcel.read(file.getInputStream(),StudentEmpty.class,new ExcelItemListener()).sheet().doRead();
}
常用的注解
- @ContentRowHeight(14) 设置列高
- @HeadRowHeight(18) 设置表头高度
- @ColumnWidth(25) 设置列宽
其他
如果要使类中的某个字段不导出
- 在类头上加@ExcelIgnoreUnannotated 注解即可,这个注解的意思是:忽略不加@ExcelProperty 的字段。这样即可控制需要的字段了
导入的时候,大家要注意 @ExcelProperty 上的属性的优先级
- @ExcelProperty(index = 2,value = “学生id”) 这个来说,index才是有用的,easy并不会再去比较学生id,因为index的优先级大于value的优先级。@ExcelProperty(value = “学生id”) 才会去比较文件头。