Java 对Excel进行导入操作

前台页面

<form action="addUsers" method="post" onsubmit="return check()"
        enctype="multipart/form-data">
        <div class="panel admin-panel">
            <div class="panel-head">
                <strong class="icon-reorder">批量导入用户</strong>
            </div>
            <div class="padding border-bottom">
                <ul class="search">
                    <li><input type="file" name="input" id="input"
                        style="width: 200px; line-height: 17px; display: inline-block"
                        class="input" value="" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                        <input type="submit" class="button border-main icon-search"
                        value="导入" /></li>
                </ul>
            </div>
            <div id="showmsg" style="display: none; font-size: 20px; color: red;">
                数据正在导入中,请稍后......</div>
        </div>
    </form>
    
    function check(){
    var file = document.getElementById("input").value;
    var fileType = file.substr(file.lastIndexOf("."),file.length).toLowerCase();
    
    if (file.length <=0) {
        alert("请选择要导入的文件");
        return false;
    }
    if (fileType !=".xls"&& fileType !=".et"&& fileType !=".xlsx") {
        alert("文件格式不符合要求");
        return false;
    }
    $("#showmsg").show();
    return true;
};

 

后台代码

/**
     * 批量导入用户
     * @throws IOException 
     */
    @RequestMapping("addUsers")
    
    public String addUsers( HttpServletRequest request, MultipartFile input,HttpServletResponse rsp) throws IOException{
        request.setCharacterEncoding("UTF-8");
        rsp.setContentType("text/html;charset=UTF-8");
        CommonsMultipartFile cFile = (CommonsMultipartFile) input;
        DiskFileItem fileItem = (DiskFileItem) cFile.getFileItem();
        InputStream is = fileItem.getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowNum = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            
            XSSFCell cell = row.getCell(0);
            cell.setCellType(XSSFCell.CELL_TYPE_STRING);  
            String cellStr = cell.getStringCellValue();
            System.out.println(cellStr);
            XSSFCell cell1 = row.getCell(1);
            cell1.setCellType(XSSFCell.CELL_TYPE_STRING);  
            String cellStr1 = cell1.getStringCellValue();
            
            XSSFCell cell2 = row.getCell(2);
            cell2.setCellType(XSSFCell.CELL_TYPE_STRING);  
            String cellStr2 = cell2.getStringCellValue();
            
            XSSFCell cell3 = row.getCell(3);
            cell3.setCellType(XSSFCell.CELL_TYPE_STRING);  
            String cellStr3 = cell3.getStringCellValue();
            
            User user = new User();
            user.setUserName(cellStr1);
            user.setGender(cellStr2);
            user.setAge(cellStr3);
            userservice.addUser(user);
            
            
        }
        return selectAll(request, rsp);
        
    }
    
    

Excel

pojo

package com.demo.pojo;

public class User {
 
    private int id;
    private String userName;
    private String pwd;
    private String age ;
    private String gender;
    private String address;
    private int roleId;
    private String roleName;
    public String getRoleName() {
        return roleName;
    }
    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
    public int getRoleId() {
        return roleId;
    }
    public void setRoleId(int roleId) {
        this.roleId = roleId;
    }
    public String getAge() {
        return age;
    }
    public void setAge(String age) {
        this.age = age;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", userName=" + userName + ", pwd=" + pwd + ", age=" + age + ", gender=" + gender
                + ", address=" + address + ", roleId=" + roleId + ", roleName=" + roleName + "]";
    }
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((address == null) ? 0 : address.hashCode());
        result = prime * result + ((age == null) ? 0 : age.hashCode());
        result = prime * result + ((gender == null) ? 0 : gender.hashCode());
        result = prime * result + id;
        result = prime * result + ((pwd == null) ? 0 : pwd.hashCode());
        result = prime * result + roleId;
        result = prime * result + ((userName == null) ? 0 : userName.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        User other = (User) obj;
        if (address == null) {
            if (other.address != null)
                return false;
        } else if (!address.equals(other.address))
            return false;
        if (age == null) {
            if (other.age != null)
                return false;
        } else if (!age.equals(other.age))
            return false;
        if (gender == null) {
            if (other.gender != null)
                return false;
        } else if (!gender.equals(other.gender))
            return false;
        if (id != other.id)
            return false;
        if (pwd == null) {
            if (other.pwd != null)
                return false;
        } else if (!pwd.equals(other.pwd))
            return false;
        if (roleId != other.roleId)
            return false;
        if (userName == null) {
            if (other.userName != null)
                return false;
        } else if (!userName.equals(other.userName))
            return false;
        return true;
    }
    
}

 导入成功示例