先上效果图~
批量导入效果图
点击批量导入：
image.png
选择文件
image.png
image.png
下面开始说如何实现的。
先说后台准备工作
springboot导入maven依赖
poi导入需要用到
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
然后写控制器:ImportExcelController
image.png
package com.lencity.securitymanagementplatform.controller;
import java.io.InputStream;
import java.util.StringJoiner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.fastjson.JSONObject;
import com.lencity.securitymanagementplatform.data.entity.Department;
import com.lencity.securitymanagementplatform.data.entity.User;
import com.lencity.securitymanagementplatform.data.service.DepartmentService;
import com.lencity.securitymanagementplatform.data.service.UserService;
@RestController
@RequestMapping(value = "/importExcel")
public class ImportExcelController {
@Autowired
private UserService userService;
@Autowired
private DepartmentService departmentService;
/**
*
* @param 导入excel
* @param name：文件名
* @param file：文件路径
* @return
*/
int flag=0;
@ResponseBody
@RequestMapping(value = "/uploadExcel")
public String uploadExcel(@RequestParam("file") MultipartFile file,String name, HttpServletRequest request,HttpServletResponse response) {
int count=0;
int code=0;
StringJoiner buffer = new StringJoiner("\n");
JSONObject jsonObject = new JSONObject();
try {
if(name!=null) {
InputStream inputStream = file.getInputStream();
Workbook book=null;
if(isExcel2003(name)) {
book=new HSSFWorkbook(inputStream);
}
if(isExcel2007(name)) {
book = new XSSFWorkbook(inputStream);
}
int sheetsNumber=book.getNumberOfSheets();
Sheet sheet = book.getSheetAt(0);
int allRowNum = sheet.getLastRowNum();
if(allRowNum==0) {
flag=100;//flag是进度条的值
buffer.add("导入文件数据为空");
}
for(int i=1;i<=allRowNum;i++){
if(flag<100) {
flag=flag+(100/i);
}else {
flag=100;
}
//加载状态值，当前进度
User user = new User();//我需要插入的数据类型
Row row = sheet.getRow(i); //获取第i行
if(row!=null) {
Cell cell1 = row.getCell(0); //获取第1个单元格的数据
Cell cell2 = row.getCell(1);
Cell cell3 = row.getCell(2);
Cell cell4 = row.getCell(3);
if(cell1!=null) {//姓名列验证
cell1.setCellType(CellType.STRING);
user.setName(cell1.getStringCellValue());
}
else {
buffer.add("第"+i+"行的第1列的数据不能为空");
}
if(cell2!=null) {//手机号码列验证
cell2.setCellType(CellType.STRING);
String verify = "^((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(17[013678])|(18[0,5-9]))\\d{8}$";
String moblie=cell2.getStringCellValue();
if(moblie.length()!=11) {
buffer.add("第"+i+"行的第1列的手机号码不复合要求11位");
}else
{
Pattern p = Pattern.compile(verify);
Matcher m = p.matcher(moblie);
boolean isMatch = m.matches();
if(isMatch) {
User userByMoblie= userService.getUserByMobile(cell2.getStringCellValue());
if(userByMoblie==null) {
user.setMobile(cell2.getStringCellValue());
}
else {
buffer.add("第"+i+"行的第1列的手机号码已存在");
}
}
else {
buffer.add("第"+i+"行的第1列的手机号码格式错误");
}
}
}
else {
buffer.add("第"+i+"行的第1列的数据不能为空");
}
if(cell3!=null) {//职位列验证
cell3.setCellType(CellType.STRING);
user.setPosition(cell3.getStringCellValue());
}else {
buffer.add("第"+i+"行的第2列的数据不能为空");
}
if(cell4!=null) {//部门列验证
cell4.setCellType(CellType.STRING);
String departmentName = cell4.getStringCellValue();
Department department = departmentService.getDepartmentByName(departmentName);
if(department!=null) {
user.setDepartmentCode(department.getDepartmentCode());
}
else {
buffer.add("第"+i+"行的第3列的数据不复合要求");
}
}else {
buffer.add("第"+i+"行的第3列的数据不能为空");
}
if(user.getName()!=null&&user.getMobile()!=null&&user.getPosition()!=null&&user.getDepartmentCode()!=null) {
count++;
userService.addUser(user);//保存到数据库
}
}
}
jsonObject.put("count", "共计"+allRowNum+"条数据，导入成功"+count+"条数据，导入失败"+(allRowNum-count)+"条");
code=1;
}
} catch (Exception e) {
e.printStackTrace();
}
jsonObject.put("code",code);
jsonObject.put("message",buffer.toString());
return jsonObject.toString();
}
/***
*
* @param 判断文件类型是不是2003版本
* @return
*/
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
*
* @param 判断文件类型是不是2007版本
* @return
*/
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
@RequestMapping("/test")
@ResponseBody
public String test(HttpServletResponse response) {
JSONObject jsonObject=new JSONObject();
if(flag==100) {
jsonObject.put("code", 1);
}
jsonObject.put("flag", flag);
return jsonObject.toString();
}
}
html部分（我做的是弹出层）效果如图
弹出层效果图
代码如下
<div class="modal fade" id="showUserDialog" tabindex="-1"
role="dialog">
<div class="modal-dialog" role="document" style="margin-top:2%;width:800px;">
<div id="modal-content" class="modal-content">
<div class="card">
<div class="modal-header">
<button type="button" class="close" onclick="closeModal()" aria-label="Close"><span aria-hidden="true">×</span></button>
<h4 class="modal-title title">人员批量导入</h4>
</div>
<div class="modal-body">
<div class="row">
<div class="col-lg-6">
<input type="file" name="file" id="fileUpload">
</div>
<button type="button" id="upload" class="btn btn-success" >上传</button>
</div>
<div style="height:20px;line-height:20px">
<div class="progress-bar" id="progress" role="progressbar" aria-valuenow="40" aroa-valuemin="0" ariva-valuemax="100" style="width:0%;margin:20px 0px 20px 0px">
<font style="vertical-align:inherit;"><font style="vertical-align:inherit;" id="fontSize" height:20px;>0%</font></font>
</div>
</div>
<div>
<span><b id="uploadState" style="display:none"></b></span>
<div>
<textarea id="console" style="width:750px;height:400px;resize:none">
</textarea>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
image.png
<button type="button" class="btn btn-success waves-effect" onclick="importExcel()" id="uploadExcel"><i class="material-icons">file_upload</i> <span>批量导入</span></button>
js部分：
var table;
var searchFlag;
var pageFlag = false;
function closeDialog() {
$('#showUserDialog').modal('hide');
}
function importExcel(){
$('#showUserDialog').modal({
show : true,
backdrop : 'static'
});
}
function closeModal(){
$('#showUserDialog').modal('hide');
window.location.reload();
}
function downloadTemplate() {
var form = $("<form>");
$('body').append(form);
form.attr('style', 'display:none');
form.attr('target', '');
form.attr('method', 'post');
form.attr('action', contextPath + '/excel/downloadTemplate');// 下载文件的请求路径
form.submit();
}
function searchRecord() {
searchFlag = true;
table.ajax.reload();
}
function reset() {
searchFlag = false;
$("#name").val("");
$("#departmentCode").val("");
window.location.reload();
}
function buildSearchParameters(data) {
var name = $("#name").val();
var departmentCode = $("#departmentCode").find("option:selected").val();
if (name) {
data.name = name;
}
if (departmentCode) {
data.departmentCode = departmentCode;
}
}
function showUserDetail(id) {
$.ajax({
url : contextPath + "/user/detail/" + id,
type : "get",
cache : false,
dataType : "html",
success : function(result) {
$("#modal-content").html(result);
},
complete : function(xhr, status) {
forwardToLogin(xhr, status);
}
});
$('#showUserDialog').modal({
show : true,
backdrop : 'static'
});
}
function addUser() {
location.href = contextPath + "/user/add";
}
function exportExcel() {
var name = $("#name").val();
var departmentCode = $("#departmentCode").find("option:selected").val();
var form = $("<form>");
$('body').append(form);
form.attr('style','display:none');
form.attr('target','');
form.attr('method','post');
form.attr('action',contextPath+'/excel/getUser');//下载文件的请求路径
//对应查询条件的开始时间
var input1 = $('<input>');
input1.attr('type','hidden');
input1.attr('name',"name");
input1.attr('value',name);
form.append(input1);
//对应查询条件的结束时间
var input2 = $('<input>');
input2.attr('type','hidden');
input2.attr('name','departmentCode');
input2.attr('value',departmentCode);
form.append(input2);
form.submit();
}
function updateUser(id) {
location.href = contextPath + "/user/update/" + id;
}
function deleteOperation(id) {
swal({
title : "删除账号",
text : "<span style='color:#F44336;'>删除操作后该记录将被删除，并且无法恢复</span>",
type : "info",
showCancelButton : true,
confirmButtonColor : "#2196F3",
confirmButtonText : "确定",
cancelButtonText : "取消",
closeOnConfirm : false,
html : true
}, function() {
deleteCallback(id);
});
}
function deleteCallback(id) {
$.ajax({
url : contextPath + "/user/delete/" + id,
cache : false,
dataType : "json",
success : function(result) {
if (result.code == 1) {
swal({
title : "删除账号",
text : "删除账号成功",
type : "success",
showConfirmButton : false,
timer : 1000
});
table.draw(false);// 刷新当前页
}
}
});
}
$(function() {
$('#fileUpload').click(function(){
var name =$("#fileUpload")[0].files[0];
if(!name){
$("#uploadExcel").attr("disabled",false);
}
})
searchFlag = false;
var operateCell = function(data, type, row, meta) {
var html ='<button class="btn btn-sm btn-primary waves-effect" value="'
+ row.id
+ '" onclick="updateUser('
+ row.id
+ ')">编辑</button> '
+ '<button class="btn btn-sm btn-danger waves-effect" value="'
+ row.id
+ '" onclick="deleteOperation('
+ row.id
+ ')">删除</button>';
return html;
}
var configJson = buildTableConfigJson(operateCell);
var configAjaxJson = configJson.ajax;
configAjaxJson.url = contextPath + '/user/list';
configAjaxJson.data = function(data) {
if (searchFlag) {
buildSearchParameters(data);
}
};
configJson.columns = [ {
"data" : null
}, {
"data" : "name"
}, {
"data" : "mobile"
}, {
"data" : "position"
}, {
"data" : "department"
}, {
"data" : null
} ], table = $(".dataTable").DataTable(configJson)
deleteSelect();
$("#upload").on('click', function(){
var formData = new FormData();
var name = $("#fileUpload").val();
var fileName=$("#fileUpload")[0].files[0];
if(fileName==undefined){
$("#console").html("请先选择正确的导入文件");
}
else{
var point=fileName.name.lastIndexOf(".");
var type=fileName.name.substr(point);
if(type==".xls"||type=="xlsx"){
$("#console").html("");
$("#showUserDialog").attr("disabled","disabled");
formData.append("file",$("#fileUpload")[0].files[0]);
formData.append("name",name);//这个地方可以传递多个参数
$.ajax({
url : contextPath + "/importExcel/uploadExcel",
type : 'POST',
dataType: "json",
async : false,
data : formData,
processData : false,
contentType : false,
success : function(result) {
$("#uploadState").prepend(result.count);
$("#console").prepend(result.message+"\n");
}
});
var timer = setInterval(function() {
$.ajax({
url : contextPath + "/importExcel/test",
dataType: "json",
success : function(result) {
$("#progress").width(result.flag+"%");
$("#fontSize").html(result.flag+"%");
if (result.code ==1) {
clearInterval(timer);
}
if(result.flag==100){
$("#uploadState").show();
}
}
});
}, 200);
}
else{
$("#console").html("请先选择正确的导入文件");
}
}
});
});
over~
