spring boot整合POI實現(xiàn)excel上傳视译、解析并存貯
1、項目目錄結構如下
2019-05-09_140126_stitch.jpg
2入蛆、spring boot pom.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.excel.poi</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- Spring boot Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- spring boot 測試-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- 數(shù)據(jù)庫連接 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- mybaties 映射 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<!-- Mysql連接驅動 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--解析Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!--導入excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3响蓉、spring boot 上傳文件的配置項
package com.excel.poi.demo.config;
import org.springframework.boot.web.servlet.MultipartConfigFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import javax.servlet.MultipartConfigElement;
/**
* @author hushixian
* @date 2019-05-09 9:54
*/
@Configuration
public class UploadFileConfig extends WebMvcConfigurerAdapter {
@Bean
public MultipartConfigElement multipartConfigElement(){
MultipartConfigFactory factory = new MultipartConfigFactory();
// 設置文件大小限制 ,超出設置頁面會拋出異常信息，
// 這樣在文件上傳的地方就需要進行異常信息的處理了;
factory.setMaxFileSize("128MB"); // KB,MB
/// 設置總上傳數(shù)據(jù)總大小
factory.setMaxRequestSize("256MB");
//設置文件路徑
//factory.setLocation("");
return factory.createMultipartConfig();
}
}
4哨毁、我們要建立一個實體類枫甲，用于接收從excel中解析出來列的數(shù)據(jù)（根據(jù)自己實際的業(yè)務功能來創(chuàng)建這個實體類，可自行修改）
package com.excel.poi.demo.entity;
import java.io.Serializable;
/**
* @author hushixian
* @date 2019-05-09 10:01
*/
public class ReqImportClient implements Serializable {
private String id;
private String userName;
private String loginName;
private String passWord;
public ReqImportClient() {
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getLoginName() {
return loginName;
}
public void setLoginName(String loginName) {
this.loginName = loginName;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
@Override
public String toString() {
return "id='" + id + '\'' +
", loginName='" + loginName + '\'' +
", userName='" + userName + '\'' +
", passWord='" + passWord;
}
}
5扼褪、三個工具類想幻，用來輔助我們的返回值和自定義異常信息
- 1、ApiResponse類话浇，用來封裝返回值
package com.excel.poi.demo.response;
/**
* Created by guocai.zhang on 16/5/28.
*/
public class ApiResponse {
public static final ApiResponse SUC = new ApiResponse(ReturnCode.CODE_SUCCESS, "Success", null);
public static final ApiResponse FAIL = new ApiResponse();
private int status;
private String info;
private Object resultObject;
public ApiResponse() {
this.status = ReturnCode.CODE_FAIL;
}
public ApiResponse(int status, String info, Object resultObject) {
this.status = status;
this.info = info;
this.resultObject = resultObject;
}
public static ApiResponse immediateOf(int status) {
return new ApiResponse(status, "", null);
}
public static ApiResponse immediateOf(int status, String info) {
return new ApiResponse(status, info, null);
}
public static ApiResponse failOf(Integer status, String info) {
if (status == null) {
status = ReturnCode.CODE_FAIL;
}
return new ApiResponse(status, info, null);
}
public static ApiResponse immediateOf(int status, String info, Object data) {
return new ApiResponse(status, info, data);
}
public static ApiResponse successOf(Object data) {
return immediateOf(200, "success", data);
}
public Object getResultObject() {
return resultObject;
}
public void setResultObject(Object resultObject) {
this.resultObject = resultObject;
}
public String getInfo() {
return info;
}
public void setInfo(String info) {
this.info = info;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public boolean hasError() {
return getStatus() != ReturnCode.CODE_SUCCESS;
}
}
- 2脏毯、BusinessException自定義異常信息
package com.excel.poi.demo.response;
/**
* Created by guocai.zhang on 16/5/29.
*/
public class BusinessException extends Exception {
private int errCode;
private String errMsg;
public BusinessException() {
}
public BusinessException(int errCode, String errMsg) {
super(errMsg);
this.errCode = errCode;
this.errMsg = errMsg;
}
public int getErrCode() {
return errCode;
}
public String getErrMsg() {
return errMsg;
}
}
- 3、ReturnCode返回值編碼類型
package com.excel.poi.demo.response;
public class ReturnCode {
/**
* 失敗
*/
public final static int CODE_FAIL = -1;
/**
* 成功
*/
public final static int CODE_SUCCESS = 0;
}
6幔崖、mapper
package com.excel.poi.demo.mapper;
import com.excel.poi.demo.entity.ReqImportClient;
/**
* @author hushixian
* @date 2019-05-09 10:23
*/
public interface ReqImportClientMapper {
/**
* 添加方法
* @param reqImportClient 實體類
* @return int 返回值
*/
int addReq(ReqImportClient reqImportClient);
}
7 食店、mapper.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.excel.poi.demo.mapper.ReqImportClientMapper">
<resultMap id="BaseResultMap" type="com.excel.poi.demo.entity.ReqImportClient">
<id column="Id" property="id" jdbcType="VARCHAR"></id>
<result column="User_Name" property="userName" jdbcType="VARCHAR"></result>
<result column="Login_Name" property="loginName" jdbcType="VARCHAR"></result>
<result column="Pass_Word" property="passWord" jdbcType="VARCHAR"></result>
</resultMap>
<insert id="addReq" parameterType="com.excel.poi.demo.entity.ReqImportClient">
insert into ReqImportClient (Id,User_Name,Login_Name,Pass_Word)
values ( #{id,jdbcType=VARCHAR},#{userName,jdbcType=VARCHAR},
#{loginName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR}
)
</insert>
</mapper>
8渣淤、service
package com.excel.poi.demo.service;
import com.excel.poi.demo.entity.ReqImportClient;
import com.excel.poi.demo.response.BusinessException;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
/**
* @author hushixian
* @date 2019-05-09 10:05
*/
public interface ResolveExcelService {
public List<ReqImportClient> resolveExcel(MultipartFile file) throws BusinessException;
}
9、serviceImpl
package com.excel.poi.demo.service.impl;
import com.excel.poi.demo.entity.ReqImportClient;
import com.excel.poi.demo.mapper.ReqImportClientMapper;
import com.excel.poi.demo.response.BusinessException;
import com.excel.poi.demo.response.ReturnCode;
import com.excel.poi.demo.service.ResolveExcelService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;
/**
* @author hushixian
* @date 2019-05-09 10:07
*/
@Service("resolveExcelServiceImpl")
public class ResolveExcelServiceImpl implements ResolveExcelService {
/**
* 打印日志
*/
private static final Logger logger = LoggerFactory.getLogger(ResolveExcelServiceImpl.class);
/**
* 上傳文件后綴的地址
*/
private static final String SUFFIX_2003 = ".xls";
private static final String SUFFIX_2007 = ".xlsx";
/**
* 電話的正則
*/
public static final String PHONE_NUMBER_REG = "^(13[0-9]|14[579]|15[0-3,5-9]|16[6]|17[01356789]|18[0-9]|19[89])\\d{8}$";
/**
* 密碼長度
*/
public static final int passWardLength = 6;
@Autowired
private ReqImportClientMapper mapper;
@Override
public List<ReqImportClient> resolveExcel(MultipartFile file) throws BusinessException {
List<ReqImportClient> list = new ArrayList<>();
if(file==null){
throw new BusinessException(ReturnCode.CODE_FAIL,"對象不能為空");
}
// 獲取文件的名字
String originalFilename = file.getOriginalFilename();
Workbook workbook = null;
try {
if (originalFilename.endsWith(SUFFIX_2003)) {
workbook = new HSSFWorkbook(file.getInputStream());
} else if (originalFilename.endsWith(SUFFIX_2007)) {
workbook = new XSSFWorkbook(file.getInputStream());
}
} catch (Exception e) {
logger.info(originalFilename);
e.printStackTrace();
throw new BusinessException(ReturnCode.CODE_FAIL, "格式錯誤");
}
if(workbook==null){
logger.info(originalFilename);
throw new BusinessException(ReturnCode.CODE_FAIL, "格式錯誤");
}else{
//獲取所有的工作表的的數(shù)量
int numOfSheet = workbook.getNumberOfSheets();
//遍歷這個這些表
for (int i = 0; i < numOfSheet ; i++) {
//獲取一個sheet也就是一個工作簿
Sheet sheet = workbook.getSheetAt(i);
int lastRowNum = sheet.getLastRowNum();
// 從第一行開始 第一行一般是標題
for (int j = 1; j <= lastRowNum; j++) {
Row row = sheet.getRow(j);
ReqImportClient reqImportClient = new ReqImportClient();
// 獲取第一行id的值
if(row.getCell(0) !=null){
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
String id = row.getCell(0).getStringCellValue();
reqImportClient.setId(id);
}
// 姓名
if(row.getCell(1) !=null){
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String userName = row.getCell(1).getStringCellValue();
reqImportClient.setUserName(userName);
}
// 手機號
if (row.getCell(2) !=null){
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
String loginName = row.getCell(2).getStringCellValue();
// todo 正則對比
boolean matche = Pattern.matches(PHONE_NUMBER_REG,loginName);
if(!matche){
throw new BusinessException(ReturnCode.CODE_FAIL, "電話格式錯誤");
}
reqImportClient.setLoginName(loginName);
}
// 密碼
if(row.getCell(3) !=null){
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
String passWord = row.getCell(3).getStringCellValue();
if (passWord.replace("", "").length() < passWardLength) {
//校驗密碼長度
throw new BusinessException(ReturnCode.CODE_FAIL, "密碼的格式有誤");
}
reqImportClient.setPassWord(passWord);
}
// 添加方法
mapper.addReq(reqImportClient);
list.add(reqImportClient);
}
}
}
return list;
}
}
10吉嫩、controller
package com.excel.poi.demo.controller;
import com.excel.poi.demo.response.ApiResponse;
import com.excel.poi.demo.response.BusinessException;
import com.excel.poi.demo.service.ResolveExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
/**
* @author hushixian
* @date 2019-05-09 11:17
*/
@RestController
@RequestMapping("/resolve")
public class ResolveExcelController {
@Autowired
private ResolveExcelService resolveExcelService;
@RequestMapping(value = "/upload",method = RequestMethod.POST)
public ApiResponse uploadExcel(@RequestParam("file") MultipartFile file){
Object result;
try {
result = resolveExcelService.resolveExcel(file);
}catch (BusinessException e){
e.printStackTrace();
return ApiResponse.failOf(-1, e.getErrMsg());
}
return ApiResponse.successOf(result);
}
}
11砂代、 yml文件的配置
server:
port: 9008
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.mysql.cj.jdbc.MysqlConnectionPoolDataSource
mybatis:
mapper-locations: classpath:mappers/*.xml
# 雖然可以配置這項來進行pojo包掃描，但其實我更傾向于在mapper.xml寫全類名
# type-aliases-package: com.spring.shiro.demo.entity
12率挣、spring boot 啟動類
package com.excel.poi.demo;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.excel.poi.demo.mapper")
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
簡單的html頁面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>文件上傳示例</title>
</head>
<body>
<h2>文件上傳示例</h2>
<hr/>
<form method="post" enctype="multipart/form-data" action="/resolve/upload">
<p>
文件：<input type="file" name="file" />
</p>
<p>
<input type="submit" value="上傳" />
</p>
</form>
</body>
</html>
