![[Java中实现Excel表导入导出]基于easy-poi和EasyExcel两种方式实现](https://300zi.50zi.cn/uploadfile/img/15/508/f33fb1af10597ce3ced7803c232f796b.jpg)
第一种：基于easy-poi实现Excel导入导出
1、导出Excel表格
第一步：在pom文件中导入依赖
<!--基于easy-poi实现Excel导入导出--><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.0</version></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-validator</artifactId><version>6.1.3.Final</version></dependency>
第二步：在数据库表对应的实体类中给每个属性添加注解@Excel
// 这里@Excel注解中的name属性制定了之后导出Excel表的第一行表头@Excel(name = "员工编号")private Integer id;@Excel(name = "员工姓名")private String name; @Excel(name = "性别")private String gender;@Excel(name = "出生日期")private Date birthday;@Excel(name = "身份证号")private String idCard;@Excel(name = "婚姻状况")private String wedlock;@Excel(name = "民族")private Integer nationId;@Excel(name = "籍贯")private String nativePlace;@Excel(name = "政治面貌")private Integer politicId;@Excel(name = "邮箱")private String email;
第三步：编写service层代码，service实现类实现接口
//文件下载@Overridepublic Workbook download() {QueryWrapper<Employee> wrapper = new QueryWrapper<>();// 查询出后台实体类集合List<Employee> employeeList = employeeMapper.selectList(wrapper);ExportParams exportParams = new ExportParams();exportParams.setType(ExcelType.XSSF); //对应的xlsxWorkbook workbook = ExcelExportUtil.exportExcel(exportParams, Employee.class, employeeList);return workbook;}
第四步：编写controller层代码
//导出下载表格，注意返回值必须是void@RequestMapping("/download")public void download(HttpServletResponse response) {Workbook workbook = employeeService.download();//命名表格String fileName = "emp.xlsx";try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));//输出流workbook.write(response.getOutputStream());// 清除responseresponse.flushBuffer();} catch (Exception e) {log.error(e.getMessage());}}
第五步：访问controller方法的路径进行文件下载
如果在项目中实现Excel表导出，只需写一个按钮，点击之后跳转controller方法路径，实现导出表格
2、导入Excel表格
第一步：编写service层代码，service实现类实现接口
//上传表格@Overridepublic List<Employee> uploadFile(MultipartFile multipartFile) {// 新建导入对象ImportParams importParams = new ImportParams();List<Employee> employeeList = null;try {employeeList = ExcelImportUtil.importExcel(multipartFile.getInputStream(),Employee.class,importParams);} catch (Exception e) {e.printStackTrace();}// 添加实体类集合中的实体类数据到数据库表中if (employeeList != null) {for (Employee emp : employeeList) {employeeMapper.insert(emp);log.info(emp + "");}}return employeeList;}
第二步：编写controller层代码
//实现上传,返回值 R 是我自定义的一个工具类，只是将数据返回到页面@PostMapping("/upload")public R uploadFile(@RequestParam("file")MultipartFile multipartFile, Model model) {List<Employee> employeeList = employeeService.uploadFile(multipartFile);return R.ok().data("employeeList",employeeList);}
第三步：在前端页面中写一个表单进行文件导入
<!DOCTYPE html><html lang="en" xmlns:th=""><head><meta charset="UTF-8"><title>Title</title></head><body><form action="upload" method="post" enctype="multipart/form-data"><input type="file" name="file"><input type="submit" value="上传"></form></body></html>
至此就完成Excel表格的导出与导入啦。
第二种：使用EasyExcel导入导出功能
1、导出Excel表格
第一步：添加依赖
<!--使用EasyExcel导入导出功能--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.3</version></dependency>
第二步：在实体类中属性上添加注解@ExcelProperty(value = "编号",index = 0)
@ExcelProperty(value = "编号",index = 0)private Integer id;@ExcelProperty(value = "姓名",index = 1)private String name;@ExcelProperty(value = "手机号码",index = 2)private String phone;@ExcelProperty(value = "住宅电话",index = 3)private String telephone;@ExcelProperty(value = "联系地址",index = 4)private String address;@ExcelProperty(value = "是否启用",index = 5)private Boolean enabled;@ExcelProperty(value = "用户名",index = 6)private String username;@ExcelProperty(value = "密码",index = 7)private String password;@ExcelProperty(value = "用户头像",index = 8)private String userFace;@ExcelProperty(value = "备注",index = 9)private String remark;@ExcelProperty(value = "盐值",index = 10)private String slot;
第三步：编写controller层代码
//导出文件@GetMapping("/write")public R writeExcel(HttpServletResponse response) {response.setCharacterEncoding("UTF-8");//content-disposition 指示如何处理响应内容,一般有两种方式：// inline ：直接在页面显示, attchment ：以附件形式下载response.setHeader("Content-disposition","attachment;filename=demo.xlsx");//获取所有数据List<Admin> list = adminService.list();try {EasyExcel.write(response.getOutputStream(),Admin.class).sheet("账号基本信息").doWrite(list);} catch (IOException e) {e.printStackTrace();}return R.ok().message("数据导出成功");}
第四步：访问controller方法的路径进行文件下载
如果在项目中实现Excel表导出，只需写一个按钮，点击之后跳转controller方法路径，实现导出表格
2、导入Excel表格
第一步：导入功能需要自己去重写一个监听器类
package com.zjy.listener;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.alibaba.excel.metadata.CellData;import com.zjy.entity.Admin;import com.zjy.service.AdminService;import java.util.ArrayList;import java.util.List;import java.util.Map;/*** @Author ZJY* @Date: /11/17 22:25*/public class ExcelListener extends AnalysisEventListener<Admin> {// 用于存储导入进来的数据private List<Admin> adminList = new ArrayList<>();/*** 间隔多少条数据进行保存* @param headMap* @param context*/private static final int BATCH_COUNT=5;/*** @param headMap* @param context*/private AdminService adminService;public ExcelListener(AdminService adminService){this.adminService=adminService;}@Overridepublic void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {System.out.println("读取表头位置："+headMap);super.invokeHead(headMap, context);}// 一行一行读取数据,每一次都需要调用该方法@Overridepublic void invoke(Admin admin, AnalysisContext analysisContext) {System.out.println("*******555555555**"+ admin+"====>");adminList.add(admin);// 数字大小int size = adminList.size();if(size>=BATCH_COUNT){//存储数据到数据库adminService.saveBatch(adminList);adminList.clear();}}// 读取完毕操作@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {adminService.saveBatch(adminList);System.out.println("读取完毕");}}
这里面注意一个问题，如果获取到的数据全部是null的情况下， 请注意检查下自己的实体类中：是否有这个注解：@Accessors(chain = true) ，有的话删除掉即可。
第二步：编写controller层代码
//上传文件@PostMapping("/read")public R readExcel(@RequestParam("file") MultipartFile file) throws IOException {// EasyExcel.read(multipartFile.getInputStream(),User.class)//String filePath="D:\\bdqn_log\\filepath\\aa.xlsx";// EasyExcel.read(filePath,User.class,newExcelListener(adminService)).sheet().doRead();EasyExcel.read(file.getInputStream(),Admin.class,newExcelListener(adminService)).sheet().doRead();return R.ok().message("数据导入成功！");}
第三步：在前端页面中写代码进行文件导入，我使用的是vue
<el-uploadaction="/user/read":show-file-list="false":on-success="fileSucc"style="display: inline-block"><el-button type="primary" style="margin-left: 5px;">导入<i class="el-iconbottom"></i></el-button></el-upload>
至此第二种方式实现Excel表导入导出就完成啦。
