环境介绍
技术栈 |
springboot3+easyexcel |
软件 |
版本 |
IDEA |
IntelliJ IDEA 2022.2.1 |
JDK |
17 |
Spring Boot |
3 |
EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。
官网https://easyexcel.opensource.alibaba.com/
Sheet工作簿
Row,行,索引从0开始
Column,列,索引从0开始
Cell,单元格
目录
Read
Read1
List<Corrdinate> newlist =new ArrayList<>(); String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx"; EasyExcel.read(fileName, Corrdinate.class, new ReadListener<Corrdinate>() { @Override public void invoke(Corrdinate corrdinate, AnalysisContext analysisContext) { try { if (11<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<120 && 2<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<244 ){ corrdinate.setZ(true); }else { corrdinate.setZ(false); } newlist.add(corrdinate); }catch (Exception e){ System.out.println(e); corrdinate.setDesc("类型转换失败"); newlist.add(corrdinate); } } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("读取完成"); } }).sheet().doRead();
Read2
String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx"; EasyExcel.read(fileName, Corrdinate.class,new PageReadListener<Corrdinate>(corrdinates -> { for (Corrdinate corrdinate : corrdinates) { try { if (1<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<110 && 2<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<240 ){ corrdinate.setZ(true); }else { corrdinate.setZ(false); } newlist.add(corrdinate); }catch (Exception e){ System.out.println(e); corrdinate.setDesc("类型转换失败"); newlist.add(corrdinate); } } })).sheet().doRead();
异常处理
重写onException方法
@Test void readException() { List<Corrdinate> oldlist =new ArrayList<>(); List<Corrdinate> newlist =new ArrayList<>(); String fileName="C:\\Users\\Administrator\\Desktop\\坐标测试.xlsx"; EasyExcel.read(fileName, Corrdinate.class, new ReadListener<Corrdinate>() { @Override public void invoke(Corrdinate corrdinate, AnalysisContext analysisContext) { if (1<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<110 && 2<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<240 ){ corrdinate.setZ(true); }else { corrdinate.setZ(false); } newlist.add(corrdinate); } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("读取完成"); } @Override public void onException(Exception exception, AnalysisContext context) throws Exception { System.out.println(exception); } }).sheet().doRead(); for (Corrdinate corrdinate : newlist) { System.out.println(corrdinate.toString()); } }
编辑
try- catch
@Test void read1() { List<Corrdinate> oldlist =new ArrayList<>(); List<Corrdinate> newlist =new ArrayList<>(); String fileName="C:\\Users\\Administrator\\Desktop\\测试.xlsx"; EasyExcel.read(fileName, Corrdinate.class, new ReadListener<Corrdinate>() { @Override public void invoke(Corrdinate corrdinate, AnalysisContext analysisContext) { try { if (1<=Double.parseDouble(corrdinate.getX()) && Double.parseDouble(corrdinate.getX())<200 && 20<=Double.parseDouble(corrdinate.getY()) && Double.parseDouble(corrdinate.getY())<300 ){ corrdinate.setZ(true); }else { corrdinate.setZ(false); } newlist.add(corrdinate); }catch (Exception e){ System.out.println(e); corrdinate.setDesc("类型转换失败"); newlist.add(corrdinate); } } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("读取完成"); } }).sheet().doRead(); for (Corrdinate corrdinate : newlist) { System.out.println(corrdinate.toString()); } }
读sheet
读所有工作簿
@Test void readManyShoot() { String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx"; EasyExcel.read(fileName, Corrdinate.class,new PageReadListener<>(corrdinates -> { corrdinates.forEach(System.out::println); })).doReadAll(); }
读任意工作簿
@Test void readAppointSheet() { try (ExcelReader excelReader = EasyExcel.read("C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx").build();){ //创建工作簿对象sheet1 ReadSheet sheet1 = EasyExcel.readSheet(0).head(Corrdinate.class) .registerReadListener(new PageReadListener<>(corrdinates -> { corrdinates.forEach(System.out::println); } )).build(); //创建工作簿对象sheet2 ReadSheet sheet2 = EasyExcel.readSheet("Sheet2").head(Corrdinate.class) .registerReadListener(new PageReadListener<>(corrdinates -> { corrdinates.forEach(System.out::println); } )).build(); excelReader.read(sheet1,sheet2); } }
自定义格式转换 日期,数字-Read
@Data
public class man {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("日期")
@DateTimeFormat("yyyy年mm月dd日")
private Date date;
@ExcelProperty("成功率")
private BigDecimal successrate;
}
编辑
自定义转换器-Read
public class StringConverterString implements Converter<String> { //支持java类型 @Override public Class<?> supportJavaTypeKey() { return String.class; } //支持Excel单元格类型 @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } //读的数据符合类型 @Override public String convertToJavaData(ReadConverterContext<?> context) throws Exception { //转换 return "姓名:"+context.getReadCellData().getStringValue(); } //写的数据符合类型 @Override public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception { return new WriteCellData<>(context.getValue()); } } @Data public class man { @ExcelProperty(converter = StringConverterString.class) private String name; @ExcelProperty("日期") @DateTimeFormat("yyyy年mm月dd日") private Date date; @ExcelProperty("成功率") private BigDecimal successrate; }
编辑
读指定行
EasyExcel默认从第一行开始读取,第0行默认为列头
@Test void selectRead(){ String fileName="C:\\Users\\Administrator\\Desktop\\demofile\\坐标测试.xlsx"; EasyExcel.read(fileName, man.class,new PageReadListener<>(mans -> { mans.forEach(System.out::println); })).sheet("Sheet3") .headRowNumber(2)//第几行,0-n .doRead(); }
Write
实体类
@Data public class Corrdinate { private long id; @ExcelProperty("x") private String x; private String y; @ExcelProperty("是否匹配") private boolean z; @ExcelProperty("描述") private String desc; @ExcelIgnore//忽略字段 private String de; }
Write1
//生成数据方法 private List<Corrdinate> getData(int count) { List<Corrdinate> list = ListUtils.newArrayList(); for (int i = 0; i < count; i++) { Corrdinate corrdinate = new Corrdinate(); //生成10-100随机数 corrdinate.setId(RandomUtil.randomInt(10, 100)); corrdinate.setX(String.valueOf(RandomUtil.randomInt(10, 100))); corrdinate.setY(String.valueOf(RandomUtil.randomInt(10, 100))); list.add(corrdinate); } return list; } @Test void write1(){ EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class) .sheet()//指定工作簿
编辑
编辑
Write2
//生成数据方法 private List<Corrdinate> getData(int count) { List<Corrdinate> list = ListUtils.newArrayList(); for (int i = 0; i < count; i++) { Corrdinate corrdinate = new Corrdinate(); corrdinate.setId(Long.parseLong(String.valueOf(RandomUtil.randomInt(10, 100)))); //生成10-100随机数 corrdinate.setX(String.valueOf(RandomUtil.randomInt(10, 100))); corrdinate.setY(String.valueOf(RandomUtil.randomInt(10, 100))); //随机生成MD5 corrdinate.setDesc(DigestUtils.md5Hex("hello")); list.add(corrdinate); } return list; }
@Test void write2(){ try (ExcelWriter excelWriter = EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class).build();){ WriteSheet sheet2 = EasyExcel.writerSheet("Sheet2").build(); WriteSheet sheet1 =EasyExcel.writerSheet("Sheet1").build(); excelWriter.write(getData(10),sheet1); excelWriter.write(getData(10),sheet2); }
编辑
写入指定列
实体类
@Data public class Corrdinate { private long id; @ExcelProperty(value = "x",index = 1)//指定列名和顺序 private String x; private String y; @ExcelProperty("是否匹配") private boolean z; @ExcelProperty("描述") private String desc; @ExcelIgnore//忽略字段 private String de; }
生成数据方法
//生成数据方法 private List<Corrdinate> getData(int count) { List<Corrdinate> list = ListUtils.newArrayList(); for (int i = 0; i < count; i++) { Corrdinate corrdinate = new Corrdinate(); corrdinate.setId(Long.parseLong(String.valueOf(RandomUtil.randomInt(10, 100)))); //生成10-100随机数 corrdinate.setX(String.valueOf(RandomUtil.randomInt(10, 100))); corrdinate.setY(String.valueOf(RandomUtil.randomInt(10, 100))); //随机生成MD5 corrdinate.setDesc(DigestUtils.md5Hex("hello")); corrdinate.setDe(DigestUtils.md5Hex("de")); list.add(corrdinate); } return list; }
写测试
@Test void writeColumn(){ Set<String> set = new TreeSet<>(); set.add("de"); EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class) .excludeColumnFieldNames(set) .sheet("Sheet2")//指定工作簿 .doWrite(getData(10));//写入10个数据 }
多级列名
实体类
@Data public class Corrdinate { private long id; @ExcelProperty(value = "x",index = 1)//指定列名和顺序 private String x; private String y; @ExcelProperty({"一级列名","是否匹配"}) private boolean z; @ExcelProperty({"一级列名","描述"}) private String desc; //@ExcelIgnore//忽略字段 //设置一级列名,二级列名 @ExcelProperty({"一级列名","二级列名"}) private String de; }
写测试
@Test void writeDemo(){ EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Corrdinate.class) .sheet("Sheet3")//指定工作簿 .doWrite(getData(10));//写入10个数据 }
编辑
自定义格式转换 日期,数字-Write
实体类
@Data public class man { @ExcelProperty(converter = StringConverterString.class) private String name; @ExcelProperty("日期") @DateTimeFormat("yyyy年mm月dd日") private Date date; @NumberFormat("#.##%") private BigDecimal successrate; }
生成数据并测试写入
//生成数据方法 private List<Man> getData2(int count) { List<Man> list = ListUtils.newArrayList(); for (int i = 0; i < count; i++) { Man m = new Man(); m.setName("张三"+i); m.setSuccessrate(BigDecimal.valueOf(RandomUtil.randomDouble(0.0, 1))); m.setDate(new Date()); list.add(m); } return list; } @Test void writeDemo(){ EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Man.class) .sheet("Sheet3")//指定工作簿 .doWrite(getData2(10));//写入10个数据 } }
编辑
自定义转换器-Write
自定义转换器
public class StringConverterString implements Converter<String> { //支持java类型 @Override public Class<?> supportJavaTypeKey() { return String.class; } //支持Excel单元格类型 @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } //读的数据符合类型 @Override public String convertToJavaData(ReadConverterContext<?> context) throws Exception { //转换 return "姓名:"+context.getReadCellData().getStringValue(); } //写的数据符合类型 @Override public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception { return new WriteCellData<>("写入数据"+context.getValue()); } }
生成数据并测试写入
private List<Man> getData2(int count) { List<Man> list = ListUtils.newArrayList(); for (int i = 0; i < count; i++) { Man m = new Man(); m.setName("张三"+i); m.setSuccessrate(BigDecimal.valueOf(RandomUtil.randomDouble(0.0, 1))); m.setDate(new Date()); list.add(m); } return list; } @Test void writeDemo(){ EasyExcel.write("C:\\Users\\Administrator\\Desktop\\demofile\\test01.xlsx", Man.class) .sheet("Sheet3")//指定工作簿 .doWrite(getData2(10));//写入10个数据 }
编辑
指定列宽行高
@Data @HeadRowHeight(30)// 指定列头行高度 @ContentRowHeight(15)// 指定内容行高度 @ColumnWidth(12)//指定列宽 public class Man { @ExcelProperty(converter = StringConverterString.class) private String name; @ExcelProperty("日期") @DateTimeFormat("yyyy年mm月dd日") private Date date; @NumberFormat("#.##%") private BigDecimal successrate; }
批量写入excel方法
编写实体类
@TableName(value ="product")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Product implements Serializable {
/**
* 序号
*/
@TableId(type = IdType.AUTO)
@ExcelProperty("序号")
private Integer number;
/**
* 创建时间
*/
@ExcelProperty("创建时间")
private Date createtime;
/**
* 产品名称
*/
@ExcelProperty("产品名称")
private String productname;
/**
* 产品编号
*/
@ExcelProperty("产品编号")
private String productnumber;
/**
* 产品型号
*/
@ExcelProperty("产品型号")
private String manufacturer;
/**
* 产品位置
*/
@ExcelProperty("产品位置")
private String producepath;
/**
* 图片位置
*/
@ExcelProperty("图片位置")
private String imagepath;
/**
* 使用单位
*/
@ExcelProperty("使用单位")
private String unit;
/**
* 金额
*/
@ExcelProperty("金额")
private Integer money;
/**
* 入库时间
*/
@ExcelProperty("入库时间")
private Date intime;
/**
* 出库时间
*/
@ExcelProperty("出库时间")
private Date puttime;
/**
* 操作人
*/
@ExcelProperty("操作人")
private String operator;
/**
* 创建人
*/
@ExcelProperty("创建人")
private String createduser;
/**
* 备注
*/
@ExcelProperty("备注")
private String notes;
/**
* 产品数量
*/
@ExcelProperty("产品数量")
private Integer producedigit;
/**
* 产品单位
*/
@ExcelProperty("产品单位")
private String productunit;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
}
//重复多次写入(写到单个或者多个Sheet) @Test public void manyWrite() { // 方法1: 如果写到同一个sheet String fileName = "C:\\Users\\13631\\Desktop\\simpleWriteTest1702391756908.xlsx"; // 这里 需要指定写用哪个class去写 try (ExcelWriter excelWriter = EasyExcel.write(fileName, Product.class).build()) { // 这里注意 如果同一个sheet只要创建一次 WriteSheet writeSheet = EasyExcel.writerSheet("测试").build(); long star = System.currentTimeMillis(); // 去调用写入,这里我调用了五次,实际使用时根据数据库分页的总的页数来 for (int i = 0; i < 5; i++) { // 分页去数据库查询数据 这里可以去数据库查询每一页的数据 List<Product> data = getData(1000); excelWriter.write(data, writeSheet); } long end = System.currentTimeMillis(); System.out.println("耗时:" + (end - star)/1000 + "秒"); }
自定义模板写入excel
填充单行
编辑
填充集合
编辑
编辑
//根据模板填充数据 @Test public void fillWrite() { // 方案2 分多次 填充 会使用文件缓存(省内存) String fileName = "C:\\Users\\13631\\Desktop\\模板写数据.xlsx"; String templateFileName = "C:\\Users\\13631\\Desktop\\模板.xlsx"; try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) { WriteSheet writeSheet = EasyExcel.writerSheet().build(); excelWriter.fill(getData2(100), writeSheet); } }
填充效果
编辑
自定义监听器
1、实体类(如上述实体类)
2、自定义监听器
Invoke和doAfterAllAnalysed是必选的
编辑
编辑
public class MyListener implements ReadListener<Product> { private TestMapper testMapper; private ArrayList<Product> list = new ArrayList<>(); int sum=0; public MyListener(TestMapper testMapper) { this.testMapper = testMapper; } //每读一行,则调用该方法 @Override public void invoke(Product product, AnalysisContext analysisContext) { sum++; list.add(product); } //每读完整个excel,则调用该方法 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("读取了"+sum+"行数据"); } }
@Test void contextLoads() { String fileName = "C:\\Users\\13631\\Desktop\\simpleWriteTest1702391756908.xlsx"; // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行 // 具体需要返回多少行可以在`PageReadListener`的构造函数设置 ExcelReader reader = EasyExcel.read(fileName, Product.class, new MyListener(new TestMapper())).build(); ReadSheet sheet = EasyExcel.readSheet().build(); reader.read(sheet); }
编辑
Web上传下载
web中的读(上传)
后端
//上传
//上传
@PostMapping("/upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
long start = System.currentTimeMillis();
EasyExcel.read(file.getInputStream(), Product.class, new MyListener(productService)).sheet().doRead();
long end = System.currentTimeMillis();
System.out.println("耗时:"+(end-start)/1000+"秒");
return "success";
}
前端(vue2+Element)
<el-upload
class="upload-demo"
action="http://192.168.1.8:8007/excel/upload"
:on-preview="handlePreview"
:on-remove="handleRemove"
:before-remove="beforeRemove"
multiple
:limit="3"
:on-exceed="handleExceed"
:file-list="fileList">
<el-button size="small" type="primary">点击上传</el-button>
</el-upload>
效果
编辑
web中的写(下载)
后端
@GetMapping("download") public void download(HttpServletResponse response) throws IOException { // 这里注意 有同学反应使用swagger 会导致各种问题,请直接用浏览器或者用postman response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系 String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), Product.class).sheet("模板").doWrite(productService.list()); }
前端
<button @click="download">导出Excel</button> methods:{ download(){ document.location.href="http://192.168.1.8:8007/excel/download"; } },
效果
编辑