嗯，今天如题，给大家介绍下最最最简单的实现excel导入导出的功能，功能简单叙述：
导入：读取本地的excel表格，将里面的内容都插入对应的数据库表（批量插入）
导出：读取数据库表内容，将其导出到excel文件
进入正题前，还是啰嗦一下，为啥要做一个这样的简单实战介绍呢，因为现在网上很多结合poi实现excel导入导出的教程不是太花了，就是各种版本老旧，所以在此，我也是等于提炼一下可用的，等于结合实战给大家一个最新的，最简单的教程例子。
OK,开始贴码实战（至于连接mybatis的整合以及查询MYSQL这些就不做介绍了，这是前提，我们不用模拟数据，当然你可以用也就是模拟个List而已嘛）：
<!-- 导入和导出--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.0.3</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.0.3</version> </dependency>
OK，创建个实体类，User.java：
package com.example.tdemo.pojo; import cn.afterturn.easypoi.excel.annotation.Excel; public class User { @Excel(name = "学号", orderNum = "0") private Integer id; @Excel(name = "姓名", orderNum = "1") private String userName; @Excel(name = "年龄", orderNum = "2") private String userAge; @Override public String toString() { return "User{" + "id=" + id + ", userName='" + userName + '\'' + ", userAge='" + userAge + '\'' + '}'; } public User() { } public User(Integer id, String userName, String userAge) { this.id = id; this.userName = userName; this.userAge = userAge; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getUserAge() { return userAge; } public void setUserAge(String userAge) { this.userAge = userAge; } }
然后是 excel导入导出的工具类,ExcelUtil：
package com.example.tdemo.util; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; public class ExcelUtil { public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){ ExportParams exportParams = new ExportParams(title, sheetName); exportParams.setCreateHeadRows(isCreateHeader); defaultExport(list, pojoClass, fileName, response, exportParams); } //导出 public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){ defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName)); } public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){ defaultExport(list, fileName, response); } private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) { Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list); if (workbook != null); downLoadExcel(fileName, response, workbook); } private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) { 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()); } catch (IOException e) { throw new RuntimeException(e.getMessage()); } } private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) { Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); if (workbook != null); downLoadExcel(fileName, response, workbook); } //导入 public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){ if (StringUtils.isBlank(filePath)){ return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); }catch (NoSuchElementException e){ throw new RuntimeException("模板不能为空"); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e.getMessage()); } return list; } public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){ if (file == null){ return null; } ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params); }catch (NoSuchElementException e){ throw new RuntimeException("excel文件不能为空"); } catch (Exception e) { throw new RuntimeException(e.getMessage()); } return list; } }
接下来玩导出导入，
既然是结和MYSQL数据库，那么我们简单看看，表，毕竟刚刚也创建了实体类：
好了，然后UserMapper简单贴一下：
@Mapper public interface UserMapper { //查询所有 List<User> queryUserInfo(); //插入所有 void addUserInfo(List<User> list); }
相关的 userMapper.xml：
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.tdemo.mapper.UserMapper"> <!--查询所有用户信息--> <select id="queryUserInfo" resultType="com.example.tdemo.pojo.User"> select * from user </select> <!--批量插入信息--> <insert id="addUserInfo" parameterType="java.util.List"> insert into user( user_name, user_age ) values <foreach collection="list" item="item" index= "index" separator =","> ( #{item.userName}, #{item.userAge} ) </foreach> </insert> </mapper>
然后是UserService：
public interface UserService { //查询所有 List<User> queryUserInfo(); //插入所有 void addUserInfo(List<User> list); }
对应的实现类：
@Service public class UserServiceImpl implements UserService { @Autowired UserMapper userMapper; @Override public List<User> queryUserInfo() { return userMapper.queryUserInfo(); } @Override public void addUserInfo(List<User> list) { userMapper.addUserInfo(list); } }
好了，开始导出导入了！
我们直接看代码，创建一个TestController，
package com.example.tdemo.controller; import com.example.tdemo.pojo.User; import com.example.tdemo.service.impl.UserServiceImpl; import com.example.tdemo.util.ExcelUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; import java.util.List; @RestController public class TestController { @Autowired UserServiceImpl userService; @RequestMapping("exportExcel") public void export(HttpServletResponse response){ List<User> userList = userService.queryUserInfo(); //导出操作 ExcelUtil.exportExcel(userList,"用户信息","sheet1",User.class,"testDATA.xls",response); } @RequestMapping("importExcel") public String importExcel(){ String filePath = "C:\\testInfo.xls"; //解析excel， List<User> userList = ExcelUtil.importExcel(filePath,1,1,User.class); //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入 System.out.println("导入数据一共【"+userList.size()+"】行"); userService.addUserInfo(userList); List<User> userList2 = userService.queryUserInfo(); return userList2.toString(); } }
好像结束了，我们来简单调下接口，验证下吧：
导出前数据表数据是这样的：
调用导出接口（谷歌浏览器默认下载到对应路径，IE\360等浏览器可以选择导出下载地址）：
OK，EXCEL表格已经导出，我们看看打开看看：
那我们继续调下导入吧（记得看下对应的接口，导入选择的路径我们是写死的，文件名也是，后期结合前端交互可以改为路径选择传入），
先看看需要导入数据的EXCEL表格内容，
好，开始调用导入接口（将EXCEL表格内容转化为List，再将List数据批量插入数据库）：
OK，我们导入数据，插入数据库后打印出来当前表信息，其实已经可以确定，肯定是无误了，最后看看数据库：
好了，就到此结束吧，是不是很简单？
