ssm解析excel表导入数据到数据库

待我称王封你为后i 2022-05-15 00:53 399阅读 0赞

1、maven配置相应jar

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.8-beta3</version>
                <type>jar</type>
                <scope>compile</scope>
            </dependency>
             <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.9</version>
                <type>jar</type>
            </dependency>

2、创建excel工具类文件ExcelUtils

import java.io.IOException;  
    import java.io.InputStream;  
    import java.math.BigDecimal;
    import java.text.SimpleDateFormat;  
    import java.util.ArrayList;  
    import java.util.Date;
    import java.util.List;  
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    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;  
    
    public class ExcelUtils { 
        private final static String excel2003L =".xls";    //2003- 版本的excel 
        private final static String excel2007U =".xlsx";   //2007+ 版本的excel 
    
        /** * 描述：获取IO流中的数据，组装成List<List<Object>>对象 * @param in,fileName * @return * @throws IOException */  
        public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
            List<List<Object>> list = null;  
    
            //创建Excel工作薄 
            Workbook work = this.getWorkbook(in,fileName);  
            if(null == work){  
                throw new Exception("创建Excel工作薄为空！");  
            }  
            Sheet sheet = null;  //页数
            Row row = null;  //行数
            Cell cell = null;  //列数
    
            list = new ArrayList<List<Object>>();  
            //遍历Excel中所有的sheet 
            for (int i = 0; i < work.getNumberOfSheets(); i++) {  
                sheet = work.getSheetAt(i);  
                if(sheet==null){
       continue;}  
    
                //遍历当前sheet中的所有行 
                for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {  
                    row = sheet.getRow(j);  
                    if(row==null||row.getFirstCellNum()==j){
       continue;}  
    
                    //遍历所有的列 
                    List<Object> li = new ArrayList<Object>();  
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
                        cell = row.getCell(y);  
                        li.add(this.getValue(cell));  
                    }  
                    list.add(li);  
                }  
            }  
    
            return list;  
    
        }  
    
        /** * 描述：根据文件后缀，自适应上传文件的版本 * @param inStr,fileName * @return * @throws Exception */  
        public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
            Workbook wb = null;  
            String fileType = fileName.substring(fileName.lastIndexOf("."));  
            if(excel2003L.equals(fileType)){  
                wb = new HSSFWorkbook(inStr);  //2003- 
            }else if(excel2007U.equals(fileType)){  
                wb = new XSSFWorkbook(inStr);  //2007+ 
            }else{  
                throw new Exception("解析的文件格式有误！");  
            }  
            return wb;  
        }  
    
        /** * 描述：对表格中数值进行格式化 * @param cell * @return */  
      //解决excel类型问题，获得数值 
        public  String getValue(Cell cell) {  
            String value = "";  
            if(null==cell){  
                return value;  
            }  
            switch (cell.getCellType()) {  
            //数值型 
            case Cell.CELL_TYPE_NUMERIC:  
                if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                    //如果是date类型则 ，获取该cell的date值 
                    Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());  
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");  
                    value = format.format(date);;  
                }else {
       // 纯数字 
                    BigDecimal big=new BigDecimal(cell.getNumericCellValue());  
                    value = big.toString();  
                    //解决1234.0 去掉后面的.0 
                    if(null!=value&&!"".equals(value.trim())){  
                         String[] item = value.split("[.]");  
                         if(1<item.length&&"0".equals(item[1])){  
                             value=item[0];  
                         }  
                    }  
                }  
                break;  
                //字符串类型 
            case Cell.CELL_TYPE_STRING:  
                value = cell.getStringCellValue().toString();  
                break;  
            // 公式类型 
            case Cell.CELL_TYPE_FORMULA:  
                //读公式计算值 
                value = String.valueOf(cell.getNumericCellValue());  
                if (value.equals("NaN")) {
       // 如果获取的数据值为非法值,则转换为获取字符串 
                    value = cell.getStringCellValue().toString();  
                }  
                break;  
            // 布尔类型 
            case Cell.CELL_TYPE_BOOLEAN:  
                value = " "+ cell.getBooleanCellValue();  
                break;   
            default:  
                value = cell.getStringCellValue().toString();  
        }  
        if("null".endsWith(value.trim())){  
            value="";  
        }  
      return value;  
        }  
    }

3、实体类people.java

public class people {
        private Integer id;
    
        private String userName;
    
        private String password;
    
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUserName() {
            return userName;
        }
    
        public void setUserName(String userName) {
            this.userName = userName == null ? null : userName.trim();
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password == null ? null : password.trim();
        }
    
    }

4、创建peopleMapper.java

public interface peopleMapper {
        int deleteByPrimaryKey(Integer id);
    
        int insert(people record);
    
        int insertSelective(people record);
    
        people selectByPrimaryKey(Integer id);
    
        int updateByPrimaryKeySelective(people record);
    
        int updateByPrimaryKey(people record);
    
        void insertInfoBatch(List<people> list);
    }

peopleMapper.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="cn.com.vtech.dao.peopleMapper" >                                                                             
      <resultMap id="BaseResultMap" type="cn.com.entity.people" >                                                                 
        <id column="id" property="id" jdbcType="INTEGER" />                                                                              
        <result column="username" property="userName" jdbcType="VARCHAR" />                                                             
        <result column="password" property="password" jdbcType="VARCHAR" />                                                                                                                              
      </resultMap>                                                                                                                       
      <sql id="Base_Column_List" >                                                                                                       
        id, username, password                                                                                             
      </sql>                                                                                                                             
      <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >                                      
        select                                                                                                                           
        <include refid="Base_Column_List" />                                                                                             
        from people                                                                                                                      
        where id = #{id,jdbcType=INTEGER} 
      </select>                                                                                                                          
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >                                                                
        delete from people                                                                                                               
        where id = #{id,jdbcType=INTEGER} 
      </delete>                                                                                                                          
      <insert id="insert" parameterType="cn.com.vtech.entity.people" >                                                                  
        insert into people (id, username, password)                                                                                                                     
        values (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}) 
      </insert>                                                                                                                          
      <insert id="insertSelective" parameterType="cn.com.vtech.entity.people" >                                                         
        insert into people                                                                                                               
        <trim prefix="(" suffix=")" suffixOverrides="," >                                                                                
          <if test="id != null" >                                                                                                        
            id,                                                                                                                          
          </if>                                                                                                                          
          <if test="userName != null" >                                                                                                  
            username,                                                                                                                   
          </if>                                                                                                                          
          <if test="password != null" >                                                                                                  
            password,                                                                                                                    
          </if>                                                                                                                          
    
        </trim>                                                                                                                          
        <trim prefix="values (" suffix=")" suffixOverrides="," >                                                                         
          <if test="id != null" >                                                                                                        
            #{id,jdbcType=INTEGER}, 
          </if>                                                                                                                          
          <if test="userName != null" >                                                                                                  
            #{userName,jdbcType=VARCHAR}, 
          </if>                                                                                                                          
          <if test="password != null" >                                                                                                  
            #{password,jdbcType=VARCHAR}, 
          </if>                                                                                                                                                                                                                                        
        </trim>                                                                                                                          
      </insert>                                                                                                                          
      <update id="updateByPrimaryKeySelective" parameterType="cn.com.vtech.entity.people" >                                             
        update people                                                                                                                    
        <set >                                                                                                                           
          <if test="userName != null" >                                                                                                  
            user_name = #{userName,jdbcType=VARCHAR}, 
          </if>                                                                                                                          
          <if test="password != null" >                                                                                                  
            password = #{password,jdbcType=VARCHAR}, 
          </if>                                                                                                                                                                                                                               
        </set>                                                                                                                           
        where id = #{id,jdbcType=INTEGER} 
      </update>                                                                                                                          
      <update id="updateByPrimaryKey" parameterType="cn.com.vtech.entity.people" >                                                      
        update people                                                                                                                    
        set username = #{userName,jdbcType=VARCHAR}, 
          password = #{password,jdbcType=VARCHAR} 
        where id = #{id,jdbcType=INTEGER} 
      </update>       
    
    
      <insert id="insertInfoBatch" parameterType="java.util.List">
      insert into people (id, username, password)                                                                                                                     
        values  
        <foreach collection="list" item="item" index="index" separator=",">
        (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}) 
        </foreach>
    </insert>                                                                                                                   
    </mapper>

5、service层  
创建peopleService.java

public interface PeopleService {
        String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response);
    }

创建peopleServiceImpl.java

@Service("PeopleService")
    public class PeopleServiceImpl implements PeopleService{
    
        @Resource(name="peopleMapper")
        private  peopleMapper peopleMapper;
    
        public String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response){
            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
    
            MultipartFile file = multipartRequest.getFile("file");  
            if(file.isEmpty()){  
                try {
                    throw new Exception("文件不存在！");
                } catch (Exception e) {
                    e.printStackTrace();
                }  
            }  
    
            InputStream in =null;  
            try {
                in = file.getInputStream();
            } catch (IOException e) {
                e.printStackTrace();
            }  
    
            List<List<Object>> listob = null; 
            try {
                listob = new ExcelUtils().getBankListByExcel(in,file.getOriginalFilename());
            } catch (Exception e) {
                e.printStackTrace();
            }   
               for (int i = 0; i < listob.size(); i++) { 
                /* List<Object> lo = listob.get(i); if (lo.get(i)=="") { continue; }*/
                   System.out.println(listob.get(i));
    
               }
            for (int i = 0; i < listob.size(); i++) {  
                List<Object> lo = listob.get(i);  
                people vo = new people(); 
                people j = null;
    
                try {
                    j = peopleMapper.selectByPrimaryKey(Integer.valueOf(String.valueOf(lo.get(0))));
                } catch (NumberFormatException e) {
                    // TODO Auto-generated catch block
                    System.out.println("数据库中无该条数据，新增");
    
                }
                    vo.setId(Integer.valueOf(String.valueOf(lo.get(0))));    
                    vo.setUserName(String.valueOf(lo.get(1)));  
                    vo.setPassword(String.valueOf(lo.get(2)));   
    
                if(j == null)
                {
    
                        peopleMapper.insert(vo);
                        System.out.println("susscess");
                }
                else
                {
                        peopleMapper.updateByPrimaryKey(vo);
                }
    
            }   
    
            return "文件导入成功！";
        }
        }

6、Contrller层  
创建ExcelController.java

@Controller  
    @RequestMapping("/upload")    
    public class ExcelController {   
    
         @Resource(name="PeopleService")
        private PeopleService PeopleService;
       @ResponseBody  
       @RequestMapping(value="ajaxUpload.do",method={RequestMethod.GET,RequestMethod.POST})  
       public String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {  
           return PeopleService.ajaxUploadExcel(request, response);
       }

7、前端页面  
前端使用的是layui，此处贴出关键代码

<fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
      <legend>选完文件后不自动上传</legend>
    </fieldset>
    
    <div class="layui-upload">
      <button type="button" class="layui-btn layui-btn-normal" id="test8">选择文件</button>
      <button type="button" class="layui-btn" id="test9">开始上传</button>
    </div>
    
        <script type="text/javascript" src="../../layui/layui.js"></script>
        <script type="text/javascript" src="../../js/jquery-3.2.1.min.js" ></script>
        
        <script> layui.use('upload', function(){ var $ = layui.jquery ,upload = layui.upload; var url=path+'uploadExcel/ajaxUpload.do'; //选完文件后不自动上传 upload.render({ elem: '#test8' ,url: url ,auto: false ,accept:"file" //指定允许上传的文件类型 //,multiple: true ,bindAction: '#test9' ,done: function(res){ console.log("上传成功") } }); }); </script>

8、excel表中格式

ID  USERNAME    PASSWORD
    3   rr          rr123
    4   jj2         jjp