前言
嗯，今天如题，给大家介绍下最最最简单的实现excel导入导出的功能，功能简单叙述：
导入：读取本地的excel表格，将里面的内容都插入对应的数据库表（批量插入）
导出：读取数据库表内容，将其导出到excel文件
进入正题前，还是啰嗦一下，为啥要做一个这样的简单实战介绍呢，因为现在网上很多结合poi实现excel导入导出的教程不是太花了，就是各种版本老旧，所以在此，我也是等于提炼一下可用的，等于结合实战给大家一个最新的，最简单的教程例子。
（至于连接mybatis的整合以及查询MYSQL这些就不做介绍了，这是前提，我们不用模拟数据，当然你可以用也就是模拟个List而已嘛）
ps ：(当前这篇是入门，还有几篇扩展篇)
正文
开始实战！
先上pom.xml中需要用到关于excel导入导出的依赖：
<!-- 导入和导出-->
<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;
}
}
OK，接下来玩导出导入，
既然是结和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表格已经导出，我们看看打开看看：
OK的，没有什么问题。
那我们继续调下导入吧（记得看下对应的接口，导入选择的路径我们是写死的，文件名也是，后期结合前端交互可以改为路径选择传入），
先看看需要导入数据的EXCEL表格内容，
好，开始调用导入接口（将EXCEL表格内容转化为List，再将List数据批量插入数据库）：
OK，我们导入数据，插入数据库后打印出来当前表信息，其实已经可以确定，肯定是无误了，最后看看数据库：
好了，就到此结束吧，是不是很简单？
