我这里用的是springBoot来写的，用的依赖在下面：
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!--引入web依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--引入数据库连接驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--引入java操作excel的api-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<!--引入poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--引入上传文件的依赖-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
<!--引入mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<!--导入lang的工具包-->
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
</dependencies>//ps:第一部分：controller层
@RestController
@RequestMapping("/user")
public class UploadExcelController {
@Resource
TestImportMapper testImportMapper;
/**
* 导入Excel
* @param userInformation
* @return
*/
@RequestMapping("/uploadExcel")
public String uploadExcel(UserInformation userInformation){
MultipartFile file = userInformation.getSelectExcel();
String type = userInformation.getSelectType();
String flag = userInformation.getFlag();
System.out.println("type为：" + type);
System.out.println("flag为：" + flag);
String errorMsg ="";
System.out.println(file.getOriginalFilename());
if (StringUtils.isNotBlank(type) && type.equals("excel") && StringUtils.isNotBlank(flag) && flag.equals("1")){
ReadExcel readExcel = new ReadExcel();
List<User> userList = null;
try {
userList = readExcel.getExcelInfo(file);
errorMsg = readExcel.getErrorMsg();
//如果没有返回出错信息，保存导入的信息
if (errorMsg.equals("")) {
testImportMapper.insertUser(userList);
}
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(errorMsg);
//System.out.println(userList.toString());
}
return errorMsg;
}
//ps：这里我是参考上边链接那里，把读取Excel这部分独立出来，单独写了一个类，在下面：package com.example.testimport.tool;
import com.example.testimport.entity.User;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
/**
* 读取导入的Excel
*/
public class ReadExcel {
//总行数
private int totalRows = 0;
//总列数
private int totalCells = 0;
//错误信息
private String errorMsg = "";
public int getTotalRows() {
return totalRows;
}
public int getTotalCells() {
return totalCells;
}
public String getErrorMsg() {
return errorMsg;
}
/**
* 获取导入的Excel，进行转换
* @param file
* @return
*/
public List<User> getExcelInfo(MultipartFile file) {
List<User> userList = null;
String filename = file.getOriginalFilename();//获取文件名称
try {
if(!validateExcel(filename)){//校验文件
return null;
}
//校验是否是2003的Excel文件
boolean isExcel2003 = true;
if (isExcel2007(filename)) {
isExcel2003 = false;
}
//创建Excel
userList = creatExcel(file.getInputStream(), isExcel2003);
} catch (Exception e) {
e.printStackTrace();
}
return userList;
}
/**
* 创建Excel，并把值赋给实体类
* @param stream
* @param isExcel2003
* @return
*/
private List<User> creatExcel (InputStream stream, boolean isExcel2003) {
List<User> userList = null;
try {
Workbook workbook;
//判断是否是2003Excel，还是2007Excel
if(isExcel2003){//为true，创建2003Excel
workbook = new HSSFWorkbook(stream);
} else {
workbook = new XSSFWorkbook(stream);
}
//读取Excel里面的信息，并付给实体类
userList = readeExcelValue(workbook);
} catch (Exception e) {
e.printStackTrace();
}
return userList;
}
/**
* 读取Excel内的数据，并写入到实体类中
* @param workbook
* @return
*/
private List<User> readeExcelValue (Workbook workbook) {
List<User> userList = new ArrayList<>();
//获取第一个sheet页
Sheet sheet = workbook.getSheetAt(0);
//获取第一个sheet总行数
this.totalRows = sheet.getPhysicalNumberOfRows();
//获取列数(前提得有行以及行中有数据)
if(totalRows > 1 && null != sheet.getRow(0)){
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}else {
this.errorMsg = "请输入您要录入的信息！";
return null;
}
//循环遍历行数
for(int i = 1; i < totalRows; i++) {
//获取每一行
Row row = sheet.getRow(i);
if(row == null){//如果行为空，退出本次循环
continue;
}
//循环遍历列
User user = new User();
for(int j = 0; j < totalCells; j++) {
Cell cell = row.getCell(j);//获取每一行对应的列
if(null != cell) {//如果每一列有数据
//每一行第一条数据
if(j == 0){
//判断数据格式，如果是纯数字，比如你写的是25，cell.getNumericCellValue()获得的是25.0
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String value = String.valueOf(cell.getNumericCellValue());
user.setName(value.substring(0, value.length()-2 > 0?value.length()-2:1));
}else if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK){//若单元格为空
this.errorMsg = "第" + i + "行，第一列数据为空，请输入数据后在重新导入！";
return null;
} else {
user.setName(cell.getStringCellValue());
}
}
//每一行第二条数据
else if(j == 1) {
//纯数字类型，需要截取
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String value = String.valueOf(cell.getNumericCellValue());
user.setName(value.substring(0, value.length()-2 > 0?value.length()-2:1));
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {//若单元格为空
this.errorMsg = "第" + i + "行，第二列数据为空，请输入数据后在重新导入！";
return null;
} else {
user.setSex(cell.getStringCellValue());
}
} else if (j == 2) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String value = String.valueOf(cell.getNumericCellValue());
int age = Integer.parseInt(value);
user.setAge(age);
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {//若单元格为空
this.errorMsg = "第" + i + "行，第三列数据为空，请输入数据后在重新导入！";
return null;
} else {
user.setAge(0);
}
}
}
}
//保存到集合中
userList.add(user);
}
return userList;
}
/**
* 校验文件是否是Excel格式
* @param filePath
* @return
*/
private boolean validateExcel(String filePath) {
//校验格式是否正确，只要是Excel任意一种格式即可
if(filePath != null && isExcel2003(filePath) || isExcel2007(filePath)) {
return true;
} else {
this.errorMsg = "请上传Excel格式的文件";
return false;
}
}
/**
* 是否是2003的Excel，返回true是
* @param filePath
* @return
*/
private boolean isExcel2003(String filePath){
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* 是否是2007的Excel，返回true是
* @param filePath
* @return
*/
private boolean isExcel2007(String filePath){
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
//application.properties配置文件中的配置，如下：#指定应用名称和端口号
spring.application.name=Test_Import
server.port=8888
#指定静态资源路径
spring.resources.static-locations=classpath:/
#配置数据源
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&&serverTimezone=GMT
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#设置上传文件的大小和请求头的大小
spring.servlet.multipart.max-file-size=5MB
spring.servlet.multipart.max-request-size=20MB
#设置上传的路径，和编码方式
spring.servlet.multipart.location=D:/home/data/temp
#设置xml的路径
mybatis.mapper-locations=classpath:mapper/*.xml//下面就是页面代码，写的有点乱<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>上传Excel文件</title>
</head>
<body>
<form id="form" action="/user/uploadExcel" method="post" enctype="multipart/form-data">
<label>请选择你要上传的Excel</label>
<input id="uploadFile" type="file" name="selectExcel" />
<input type="button" onclick="submitExcel()" value="提交" />
<input type="hidden" id="selectType" name="selectType" value="excel" />
<input type="hidden" id="flag" name="flag" value="1" />
<br>
<label style="background-color: red;"><a href="#" id="downLoadTemplate" style="text-decoration: none;cursor:pointer" onclick="downLoadTemplate()">下载模板</a></label>
<label style="background-color: red;"><a href="/static/模板.xlsx" style="text-decoration: none;cursor:pointer">下载模板</a></label>
</form>
</body>
<script type="text/javascript">
//导入Excel
function submitExcel() {
var form = document.getElementById('form');
var uploadFile = document.getElementById("uploadFile");
var selectType = document.getElementById("selectType").value;
var flag = document.getElementById("flag").value;
var url = "/user/uploadExcel"
var formData = new FormData(form);
if(uploadFile.value == null || uploadFile.value.trim() == '') {
alert("请选择您要上传的文件！");
return;
}
if(uploadFile.value.lastIndexOf(".xls") < 0 || uploadFile.value.lastIndexOf(".xlsx") < 0) {
alert("只能上传Excel文件！");
uploadFile.value = "";
return;
}
//判断游览器是否是IE7 8，不是的话用第一个request 是的话用第二个request
var xmlHttp = null;
if (XMLHttpRequest) {
xmlHttp = new XMLHttpRequest();
} else {
xmlHttp = new ActiveXObject('Microsoft.XMLHTTP');
};
xmlHttp.open('POST',url);//打开请求的后台
//请求头不要设置，使用默认的，服务器解析会自动选择与之匹配的
//xmlHttp.setRequestHeader('Content-Type','multipart/form-data;boundary=ebf9f03029db4c2799ae16b5428b06bd');//设置请求头
xmlHttp.send(formData);//发送数据
//请求的状态
xmlHttp.onreadystatechange = function () {
//根据状态，判断是否请求成功
if(xmlHttp.readyState == 4 && xmlHttp.status == 200){
alert(JSON.parse(xmlHttp.responseText));
}
};
}
//下载Excel模板
function downLoadTemplate() {
var downLoadTemplate = document.getElementById("downLoadTemplate");
//window.location="/static/模板.xlsx";
//测试js移除点击事件，以及设置点击事件
downLoadTemplate.removeAttribute('onclick');
downLoadTemplate.setAttribute("onclick","sendMessage('1')");
//发送请求下载模板
//判断游览器是否是IE7 8，不是的话用第一个request 是的话用第二个request
var xmlHttp = null;
if (XMLHttpRequest) {
xmlHttp = new XMLHttpRequest();
} else {
xmlHttp = new ActiveXObject('Microsoft.XMLHTTP');//IE游览器访问
};
var url = '/user/downloadExcelTemplate';
var method = 'get';
xmlHttp.open(method,url);
xmlHttp.onreadystatechange = function () {
//判断请求是否成功
if(xmlHttp.readyState == 4 && xmlHttp.status == 200) {
alert(JSON.parse(xmlHttp.responseText));
}
}
}
function sendMessage(infor) {
alert(infor);
}
</script>
</html>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
