前言
嗯,今天如題,給大家介紹下最最最簡單的實作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,我們導入資料,插入資料庫後列印出來目前表資訊,其實已經可以确定,肯定是無誤了,最後看看資料庫: