1.依赖
2.实体类和excel一一对应
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Studend {
private String id;
private String name;
private String gender;
private Date birthday;
}
3.读数据
//监听器
public class StudentListener extends AnalysisEventListener<Studend> {
//每读取一行数据就会调用一次这个方法
@Override
public void invoke(Studend studend, AnalysisContext analysisContext) {
System.out.println("studend = " + studend);
}
//全部读取完了之后会调用一次这个方法
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("读取完毕");
}
}
public class tses1 {
@Test
public void test1(){
//转成一个流
InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("read.xlsx");
//读取这个流,并把它封装到Studend实体类中,readListener监听器
ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(resourceAsStream, Studend.class, new StudentListener());
//获得一个工作表对象
ExcelReaderSheetBuilder sheet = excelReaderBuilder.sheet();
//读取工作表中的内容
sheet.doRead();
}
}
4.写数据
@Test
public void tsetWrite(){
ExcelWriterBuilder write = EasyExcel.write("./src/main/resources/write.xlsx");
ExcelWriterSheetBuilder sheet = write.sheet();
List<Studend> list = Arrays.asList(
new Studend("1","张三","男",new Date()),
new Studend("2","李四","女",new Date()),
new Studend("3","ww","男",new Date())
);
sheet.doWrite(list);
}
自定义表头
2.文件上传(上传excel文件的数据到数据库上)
<!-- 文件上传解析器 -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="104857600"/>
</bean>
usermapper层,sql语句可以进行非空判断
下载(把数据库数据下载到excel中)
4.数据库写execl(简单模式)
@RequestMapping("/writeall")
@ResponseBody
public void writeall(HttpServletResponse response)throws Exception{
List<User> allwrite = userService.allwrite();
EasyExcel.write("C:\\Users\\磊\\Desktop\\read1.xlsx",User.class).sheet("列表").doWrite(allwrite);
}
4.1数据库写execl(加上数据格式验证)
@RequestMapping("/writeall")
@ResponseBody
public void writeall(HttpServletResponse response)throws Exception{
List<User> allwrite = userService.allwrite();
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("C:\\Users\\磊\\Desktop\\read1.xlsx",User.class).sheet("列表").doWrite(allwrite);
}
4.1数据库写execl(加上数据格式验证)
5.上传
ssm要求