springboot整合poi导入excel

先上效果图~

批量导入效果图

点击批量导入：

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">&times;</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>&nbsp;&nbsp;&nbsp;'
                + '<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~