2.导入数据使用示例com.alibaba easyexcel2.2.6
首先准备导入的Excel文件
导入数据需要准备两个东西:
1.监听器
2.封装每格数据的实体类
public class StatementExcel {
//这个注解指定字段在表格中的位置
@ExcelProperty(index = 0)
private String sn;//单号
@ExcelProperty(index = 1)
private String during;//期间
@ExcelProperty(index = 2)
private Integer num;//数量
@ExcelProperty(index = 3)
private BigDecimal amount;//金额
}
2.监听器
package com.grgbanking.shopMmg.listener; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.grgbanking.shopMmg.excel.StatementExcel; import java.util.ArrayList; import java.util.List; public class ExcelListener extends AnalysisEventListener3.读取准备完毕开始读取数据{ private final List allList = new ArrayList<>(); @Override public void invoke(StatementExcel statementExcel, AnalysisContext analysisContext) { //将读取到的数据添加到集合 allList.add(statementExcel); } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { //输出读取到的数据 System.out.println("从excel中读取到的数据:"); for (StatementExcel statementExcel : allList) { System.out.println(statementExcel); } } public List getList(){ return this.allList; } }
@Test
public void testExcel() throws Exception {
//1.获得传入文件的输入在这里插入代码片流
File file = new File("C:\Users\User\Desktop\category.xlsx");
InputStream inputStream = new FileInputStream(file);
//2.创建监听器
ExcelListener excelListener = new ExcelListener();
//3.读取传入的excel文件
EasyExcel.read(inputStream, StatementExcel.class,excelListener).sheet().doRead();
//读取的全部内容
List list = excelListener.getList();
//输出读取到的数据
System.out.println("从excel中读取到的数据:");
for (StatementExcel statementExcel : list) {
System.out.println(statementExcel);
}
}
4.执行测试结果
2.数据Excel导出
1.指定导出的模板(也可以不指定)
{.字段名}
2.也需要指定实体类封装数据
public class StatementExcel {
//指定导出位置
@ExcelProperty({"单号"})
private String sn;//单号
@ExcelProperty({"期间"})
private String during;//结算期间
@ExcelProperty({"数量"})
private Integer reconciliationNum;//数量
@ExcelProperty({"金额"})
private BigDecimal amount;//金额
}
3.导出代码
@GetMapping("/export")
public void exportStatement(HttpServletResponse response) throws Exception {
//1.查询数据库中的数据
Result> pageDataResult = adminFindStatementByPage(new StatementSearchDto());
List row = pageDataResult.getData().getRow();
List excels = new ArrayList<>();
for (StatementVO statementVO : row) {
StatementExcel statementExcel = BeanHelper.copyProperties(statementVO, StatementExcel.class);
excels.add(statementExcel);
}
//2.读取表格模板
File file = new File("F:\exportStaTemplate.xlsx");
FileInputStream inputStream = new FileInputStream(file);
//3.防止文件名乱码
String fileNameEncode = URLEncoder.encode("category", "UTF-8");
//3.设置响应为excel表格文件
response.setHeader("content-disposition","attachment;filename=" + fileNameEncode + ".xlsx");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
//4.使用模板导出
EasyExcel.write(response.getOutputStream(), StatementExcel.class).withTemplate(inputStream).sheet("1").doFill(excels);
//5.不适用模板导出
//EasyExcel.write(response.getOutputStream(), StatementExcel.class).sheet(1).doWrite(excels);
}
4.导出结果
有帮助请点赞!!


