easyExcel导入

/**
* 导入excel
* @return
/
@ResponseBody
@RequestMapping(value = “/excel”, method = RequestMethod.POST)
public Result excel(@RequestParam(“file”) MultipartFile file) {
try {
// 获取Excel信息
//第一个sheet页
List list1 = EasyExcelUtil.readExcel(file, new SatisFactionSurveyExcelDTO(), 1, 1);
//第二个sheet页
List list2 = EasyExcelUtil.readExcel(file, new SatisFactionSurveyExcelDTO(), 2, 1);
for (Object o : list1) {
log.info(“list1: " + o);
}
for (Object o : list2) {
log.info(“list2: " + o);
}
return new Result();
} catch (Exception e) {
log.error(”【获取id异常】”, e);
return new Result(-1, “获取id异常”, “”);
}
}
}
/*

• Excel 项目模板实体类
  /
  @Data
  @Entity
  @ToString
  @HeadRowHeight(40)//高度
  public class ProjectExcel extends BaseRowModel implements Serializable {
  @ColumnWidth(20)
  @ExcelProperty(value = "年月(yyyy)",index = 0)
  private String createyear;//年月(yyyy)
  @ColumnWidth(20)
  @ExcelProperty(value = “账套编码*”,index = 1)
  private String zt;//账套编码
  @ColumnWidth(20) //宽度
  @ExcelProperty(value = “账套名称”,index = 2)
  private String ztName;//账套名称
  }

//————————————————————————————————————————————————工具类
public class EasyExcelUtil {
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @return Excel 数据 list
/
public static List 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 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 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;
}
}
}

//————————————————————————————————————————————————工具类

public class ExcelListener extends AnalysisEventListener {
//自定义用于暂时存储data
//private List datas = Collections.synchronizedList(new ArrayList<>());
private List datas = new ArrayList<>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
/
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);
}
/*
* 读取完之后的操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public List getDatas() {
return datas;
}
public void setDatas(List datas) {
this.datas = datas;
}
}