使用以前的jxl和apache的poi,今天在项目中给别人帮忙写导入excel,项目框架引入了easy poi,那就再开始用这个玩玩导入吧,废话不说,上代码:
1,先引入pom依赖:
<!-- easyexcel-excel导出 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
2,写个工具类:
package com.ymw.admin.util;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.ymw.admin.util.listener.ExcelListener;
import org.springframework.web.multipart.MultipartFile;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class EasyExcelUtil {
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类 sheetNo sheet 的序号 从1开始
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
return readExcel(excel, rowModel, 1, 1);
}
/**
* 读取某个 sheet 的 Excel
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @param headLineNum 表头行数,默认为1
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
return excelListener.getDatas();
}
/**
* 读取指定sheetName的Excel(多个 sheet)
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @return Excel 数据 list
* @throws IOException
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel,String sheetName) throws IOException {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
for (Sheet sheet : reader.getSheets()) {
if (rowModel != null) {
sheet.setClazz(rowModel.getClass());
}
//读取指定名称的sheet
if(sheet.getSheetName().contains(sheetName)){
reader.read(sheet);
break;
}
}
return excelListener.getDatas();
}
/**
* 返回 ExcelReader
* @param excel 需要解析的 Excel 文件
* @param excelListener new ExcelListener()
* @throws IOException
*/
private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
String filename = excel.getOriginalFilename();
if(filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
InputStream is = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(is, null, excelListener, false);
}else{
return null;
}
}
}
2-1:ExcelListener工具类
package com.admin.util.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
public class ExcelListener extends AnalysisEventListener {
//自定义用于暂时存储data
//private List<Object> datas = Collections.synchronizedList(new ArrayList<>());
private List<Object> datas = new ArrayList<>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet,当前行等数据
*/
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);
}
/**
* 读取完之后的操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
}
3,导入excel接口controller代码:
@ApiOperation(value = "导入excel")
@PostMapping("/import")
public String importExcel(@RequestParam("files") MultipartFile[] files) throws IOException {
int a = 0;
int b = 0;
if(files != null && files.length > 0){
MultipartFile file = files[0];
List<Object> list = EasyExcelUtil.readExcel(file, new CoalSupplyPlan(),1,1);
if(list != null && list.size() > 0){
b = list.size();
for(Object o : list){
CoalSupplyPlan plan = (CoalSupplyPlan) o;
YundaoCoalSupplyPlan cPlan = new YundaoCoalSupplyPlan();
cPlan.setPlanId(plan.getPlanId());//第1列 计划id
cPlan.setPlanDate(plan.getPlanDate());//第2列 计划日期
cPlan.setCompanyName(plan.getCompanyName());//第3列供应商
cPlan.setMinesName(plan.getMinesName());//第四列 矿点名称
cPlan.setCoalType(plan.getCoalType());//第6列 煤种类型
cPlan.setTons(plan.getTons());//第8列采购重量
cPlan.setTruckNum(plan.getTruckNum());//第16列 计划车数
coalSupplyPlanService.insert(cPlan);
a++;
}
}
}
System.out.println("表格总共有"+b +"条数据");
System.out.println("导入成功"+a +"条数据");
return "index";
}
4,对应的实体类加注解(哪些字段需要就在哪些字段上面加注解)其中的@ExcelProperty(index = 2)代表对应excel中的第二列
@Data
public class CoalSupplyPlan extends BaseRowModel {
/**
* 主键
*/
@TableId(value = "id", type = IdType.UUID)
private String id;
/**
* 创建者
*/
private String createBy;
/**
* 创建时间
*/
private Date createDate;
/**
* 更新者
*/
private String updateBy;
/**
* 更新时间
*/
private Date updateDate;
/**
* 备注信息
*/
private String remarks;
/**
* 逻辑删除标记(0:显示;1:隐藏)
*/
private String delFlag;
/**
* 计划id
*/
@ExcelProperty(index = 1)
private String planId;
/**
* 计划日期
*/
@ExcelProperty(index = 2)
private String planDate;
/**
* 供应商名称
*/
@ExcelProperty(index = 3)
private String companyName;
/**
* 矿点名称
*/
@ExcelProperty(index = 4)
private String minesName;
/**
* 煤种类型
*/
@ExcelProperty(index = 6)
private String coalType;
/**
* 采购重量
*/
@ExcelProperty(index = 7)
private String tons;
/**
* 来煤车数
*/
@ExcelProperty(index = 16)
private String truckNum;
/**
* 厂商id
*/
private String venderId;
}
5,导出待定吧,暂时没写,后续再补。
6,测试: