easyexcel的读写操作

easyexcel是基于java的读写excel的开源项目 --读写也可理解为上传和下载

写操作

一、引入依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version>
</dependency>

二、封装相应对象

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelDemo {@ExcelProperty(value = "姓名")    //标记excel的标题内容private  String name;@ExcelProperty(value = "年龄")private Integer age;@ExcelIgnore    //标记该属性不写入表格private String sex;
}

三、通过eayexcel完成写入的操作

public class TestWrite{public static void main(String[] args){String fileName = "表格要保存的地址\保存后的文件名称";	//例：D:\projeck\新建文件夹\a.xlsxList<ExcelDemo> data = new ArrayList();data.add(new ExcelDemo("aaa",18,"男"));data.add(new ExcelDemo("bbb",13,"女"));EasyExcel.write(fileName, ExcelDemo.class).sheet("表格的工程名").doWrite(data);}
}

web页面实现写操作

@Controller
public class ExcelController{@GetMapping("download")public void download(HttpServletResponse response) throws IOException{response.setContenType("application/vnd.opencmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");//这里的URLEncoder.encode用来防止中文乱码String fileName = URLEncoder.encode("下载的文件名", "UTF-8").replaceALL("\\+","%28");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename = ".xlsx");List<ExcelDemo> data = new ArrayList();data.add(new ExcelDemo("aaa",18,"男"));data.add(new ExcelDemo("bbb",13,"女"));EasyExcel.write(response.getOutputStream(), ExcelDemo.class).sheet("表格的工程名").doWrite(data);}
}

读操作

一、监听器

@Slf4j
public class DemoDataListener implements ReadListener<ExcelDemo>{
private static final int BATCH_COUNT = 100;	//每100条存储数据库，然后清理list
private List<ExcelDemo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);//数据缓存
private DemoDAO demoDAO;	//这个可以是一个dao，如果有业务逻辑也可以是service
public DemoDataListener(){demoDAO = new DemoDAO();	//因为这里是dao所以随便new一个
}
public DemoDataListener(){this.demoDAO = demoDAO;	//如果使用的是spring，使用这个构造方法，每次创建Listener需要传入spring管理类
}
@Override
//每条数据解析都会调用这个方法
public void invoke(ExcelDemo data, AnalysisContext context){sout(JSON.toJSONString(data));if(cachedDataList.size() >= BATCH_COUNT){	//达到BATCH_COUNT的数量需要存储一次数据库，防止数据过多损坏saveData();cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);	//存储完成清理list}
}
@Override
//所有数据解析完成后调用
public void doAfterAllAnalysed(AnalysisContext context){saveData();	//这里保存数据确保最后遗留的数据也会存储到数据库
}
//存储数据库
private void saveData(){demoDAO.sava(cachedDataList);
}
}

二、持久层dao

public class DemoDAO{
public void save(List<ExcelDemo> list){//自己写一个mapper，新增一个方法batchInsert，将数据放入数据库
}
}

三、测试

public class TestReadDemo{
psvm{String fileName = "将要读取的文件存放的地址";	//例：D:\projeck\新建文件夹\a.xlsx//默认读取第一个sheetEasyExcel.red(fileName, ExcelDemo.class, new DemoDataListener()).sheet().doRead();
}
}

web页面实现读操作

1. 引入依赖

   <dependency>
   <groupId>commons-fileupload</groupId>
   <artifactId>commons-fileupload</artifactId>
   <version>1.4</version>
   </dependency>

2. 文件上传解析器

   <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
   <property name="maxUploadSize" value="1000000000"/>
   </bean>

3. Controller层

   @Autowired
   private DemoDAO demoDAO;	//注入dao层，未来根据业务逻辑也可以注入service
   @PostMapping("upload")
   @ResponseBody
   public String upload(MultipartFile file) throws IOException{
   EasyExcel.read(file.getInputStream(), ExcelDemo.class, new DemoDataListener(demoDAO)).sheet().doRead();
   return "success";
   }