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

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

1、maven配置相应jar

  1. <dependency>
  2.             <groupId>org.apache.poi</groupId>
  3.             <artifactId>poi</artifactId>
  4.             <version>3.8-beta3</version>
  5.             <type>jar</type>
  6.             <scope>compile</scope>
  7.         </dependency>
  8.          <dependency>
  9.             <groupId>org.apache.poi</groupId>
  10.             <artifactId>poi-ooxml</artifactId>
  11.             <version>3.9</version>
  12.             <type>jar</type>
  13.         </dependency>

2、创建excel工具类文件ExcelUtils

  1. import java.io.IOException;  
  2. import java.io.InputStream;  
  3. import java.math.BigDecimal;
  4. import java.text.SimpleDateFormat;  
  5. import java.util.ArrayList;  
  6. import java.util.Date;
  7. import java.util.List;  
  8. import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  9. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  10. import org.apache.poi.ss.usermodel.Cell;  
  11. import org.apache.poi.ss.usermodel.Row;  
  12. import org.apache.poi.ss.usermodel.Sheet;  
  13. import org.apache.poi.ss.usermodel.Workbook;  
  14. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  16. public class ExcelUtils { 
  17.     private final static String excel2003L =".xls";    //2003- 版本的excel 
  18.     private final static String excel2007U =".xlsx";   //2007+ 版本的excel 
  20.     /** * 描述：获取IO流中的数据，组装成List<List<Object>>对象 * @param in,fileName * @return * @throws IOException */  
  21.     public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
  22.         List<List<Object>> list = null;  
  24.         //创建Excel工作薄 
  25.         Workbook work = this.getWorkbook(in,fileName);  
  26.         if(null == work){  
  27.             throw new Exception("创建Excel工作薄为空！");  
  28.         }  
  29.         Sheet sheet = null;  //页数
  30.         Row row = null;  //行数
  31.         Cell cell = null;  //列数
  33.         list = new ArrayList<List<Object>>();  
  34.         //遍历Excel中所有的sheet 
  35.         for (int i = 0; i < work.getNumberOfSheets(); i++) {  
  36.             sheet = work.getSheetAt(i);  
  37.             if(sheet==null){
  38.    continue;}  
  40.             //遍历当前sheet中的所有行 
  41.             for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {  
  42.                 row = sheet.getRow(j);  
  43.                 if(row==null||row.getFirstCellNum()==j){
  44.    continue;}  
  46.                 //遍历所有的列 
  47.                 List<Object> li = new ArrayList<Object>();  
  48.                 for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
  49.                     cell = row.getCell(y);  
  50.                     li.add(this.getValue(cell));  
  51.                 }  
  52.                 list.add(li);  
  53.             }  
  54.         }  
  56.         return list;  
  58.     }  
  60.     /** * 描述：根据文件后缀，自适应上传文件的版本 * @param inStr,fileName * @return * @throws Exception */  
  61.     public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
  62.         Workbook wb = null;  
  63.         String fileType = fileName.substring(fileName.lastIndexOf("."));  
  64.         if(excel2003L.equals(fileType)){  
  65.             wb = new HSSFWorkbook(inStr);  //2003- 
  66.         }else if(excel2007U.equals(fileType)){  
  67.             wb = new XSSFWorkbook(inStr);  //2007+ 
  68.         }else{  
  69.             throw new Exception("解析的文件格式有误！");  
  70.         }  
  71.         return wb;  
  72.     }  
  74.     /** * 描述：对表格中数值进行格式化 * @param cell * @return */  
  75.   //解决excel类型问题，获得数值 
  76.     public  String getValue(Cell cell) {  
  77.         String value = "";  
  78.         if(null==cell){  
  79.             return value;  
  80.         }  
  81.         switch (cell.getCellType()) {  
  82.         //数值型 
  83.         case Cell.CELL_TYPE_NUMERIC:  
  84.             if (HSSFDateUtil.isCellDateFormatted(cell)) {  
  85.                 //如果是date类型则 ，获取该cell的date值 
  86.                 Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());  
  87.                 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");  
  88.                 value = format.format(date);;  
  89.             }else {
  90.    // 纯数字 
  91.                 BigDecimal big=new BigDecimal(cell.getNumericCellValue());  
  92.                 value = big.toString();  
  93.                 //解决1234.0 去掉后面的.0 
  94.                 if(null!=value&&!"".equals(value.trim())){  
  95.                      String[] item = value.split("[.]");  
  96.                      if(1<item.length&&"0".equals(item[1])){  
  97.                          value=item[0];  
  98.                      }  
  99.                 }  
  100.             }  
  101.             break;  
  102.             //字符串类型 
  103.         case Cell.CELL_TYPE_STRING:  
  104.             value = cell.getStringCellValue().toString();  
  105.             break;  
  106.         // 公式类型 
  107.         case Cell.CELL_TYPE_FORMULA:  
  108.             //读公式计算值 
  109.             value = String.valueOf(cell.getNumericCellValue());  
  110.             if (value.equals("NaN")) {
  111.    // 如果获取的数据值为非法值,则转换为获取字符串 
  112.                 value = cell.getStringCellValue().toString();  
  113.             }  
  114.             break;  
  115.         // 布尔类型 
  116.         case Cell.CELL_TYPE_BOOLEAN:  
  117.             value = " "+ cell.getBooleanCellValue();  
  118.             break;   
  119.         default:  
  120.             value = cell.getStringCellValue().toString();  
  121.     }  
  122.     if("null".endsWith(value.trim())){  
  123.         value="";  
  124.     }  
  125.   return value;  
  126.     }  
  127. }

3、实体类people.java

  1. public class people {
  2.     private Integer id;
  4.     private String userName;
  6.     private String password;
  9.     public Integer getId() {
  10.         return id;
  11.     }
  13.     public void setId(Integer id) {
  14.         this.id = id;
  15.     }
  17.     public String getUserName() {
  18.         return userName;
  19.     }
  21.     public void setUserName(String userName) {
  22.         this.userName = userName == null ? null : userName.trim();
  23.     }
  25.     public String getPassword() {
  26.         return password;
  27.     }
  29.     public void setPassword(String password) {
  30.         this.password = password == null ? null : password.trim();
  31.     }
  33. }

4、创建peopleMapper.java

  1. public interface peopleMapper {
  2.     int deleteByPrimaryKey(Integer id);
  4.     int insert(people record);
  6.     int insertSelective(people record);
  8.     people selectByPrimaryKey(Integer id);
  10.     int updateByPrimaryKeySelective(people record);
  12.     int updateByPrimaryKey(people record);
  14.     void insertInfoBatch(List<people> list);
  15. }

peopleMapper.xml

  1. <?xml version="1.0" encoding="UTF-8" ?>                                                                                              
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >                         
  3. <mapper namespace="cn.com.vtech.dao.peopleMapper" >                                                                             
  4.   <resultMap id="BaseResultMap" type="cn.com.entity.people" >                                                                 
  5.     <id column="id" property="id" jdbcType="INTEGER" />                                                                              
  6.     <result column="username" property="userName" jdbcType="VARCHAR" />                                                             
  7.     <result column="password" property="password" jdbcType="VARCHAR" />                                                                                                                              
  8.   </resultMap>                                                                                                                       
  9.   <sql id="Base_Column_List" >                                                                                                       
  10.     id, username, password                                                                                             
  11.   </sql>                                                                                                                             
  12.   <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >                                      
  13.     select                                                                                                                           
  14.     <include refid="Base_Column_List" />                                                                                             
  15.     from people                                                                                                                      
  16.     where id = #{id,jdbcType=INTEGER} 
  17.   </select>                                                                                                                          
  18.   <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >                                                                
  19.     delete from people                                                                                                               
  20.     where id = #{id,jdbcType=INTEGER} 
  21.   </delete>                                                                                                                          
  22.   <insert id="insert" parameterType="cn.com.vtech.entity.people" >                                                                  
  23.     insert into people (id, username, password)                                                                                                                     
  24.     values (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}) 
  25.   </insert>                                                                                                                          
  26.   <insert id="insertSelective" parameterType="cn.com.vtech.entity.people" >                                                         
  27.     insert into people                                                                                                               
  28.     <trim prefix="(" suffix=")" suffixOverrides="," >                                                                                
  29.       <if test="id != null" >                                                                                                        
  30.         id,                                                                                                                          
  31.       </if>                                                                                                                          
  32.       <if test="userName != null" >                                                                                                  
  33.         username,                                                                                                                   
  34.       </if>                                                                                                                          
  35.       <if test="password != null" >                                                                                                  
  36.         password,                                                                                                                    
  37.       </if>                                                                                                                          
  39.     </trim>                                                                                                                          
  40.     <trim prefix="values (" suffix=")" suffixOverrides="," >                                                                         
  41.       <if test="id != null" >                                                                                                        
  42.         #{id,jdbcType=INTEGER}, 
  43.       </if>                                                                                                                          
  44.       <if test="userName != null" >                                                                                                  
  45.         #{userName,jdbcType=VARCHAR}, 
  46.       </if>                                                                                                                          
  47.       <if test="password != null" >                                                                                                  
  48.         #{password,jdbcType=VARCHAR}, 
  49.       </if>                                                                                                                                                                                                                                        
  50.     </trim>                                                                                                                          
  51.   </insert>                                                                                                                          
  52.   <update id="updateByPrimaryKeySelective" parameterType="cn.com.vtech.entity.people" >                                             
  53.     update people                                                                                                                    
  54.     <set >                                                                                                                           
  55.       <if test="userName != null" >                                                                                                  
  56.         user_name = #{userName,jdbcType=VARCHAR}, 
  57.       </if>                                                                                                                          
  58.       <if test="password != null" >                                                                                                  
  59.         password = #{password,jdbcType=VARCHAR}, 
  60.       </if>                                                                                                                                                                                                                               
  61.     </set>                                                                                                                           
  62.     where id = #{id,jdbcType=INTEGER} 
  63.   </update>                                                                                                                          
  64.   <update id="updateByPrimaryKey" parameterType="cn.com.vtech.entity.people" >                                                      
  65.     update people                                                                                                                    
  66.     set username = #{userName,jdbcType=VARCHAR}, 
  67.       password = #{password,jdbcType=VARCHAR} 
  68.     where id = #{id,jdbcType=INTEGER} 
  69.   </update>       
  72.   <insert id="insertInfoBatch" parameterType="java.util.List">
  73.   insert into people (id, username, password)                                                                                                                     
  74.     values  
  75.     <foreach collection="list" item="item" index="index" separator=",">
  76.     (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}) 
  77.     </foreach>
  78. </insert>                                                                                                                   
  79. </mapper>

5、service层
创建peopleService.java

  1. public interface PeopleService {
  2.     String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response);
  3. }

创建peopleServiceImpl.java

  1. @Service("PeopleService")
  2. public class PeopleServiceImpl implements PeopleService{
  4.     @Resource(name="peopleMapper")
  5.     private  peopleMapper peopleMapper;
  7.     public String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response){
  8.         MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
  10.         MultipartFile file = multipartRequest.getFile("file");  
  11.         if(file.isEmpty()){  
  12.             try {
  13.                 throw new Exception("文件不存在！");
  14.             } catch (Exception e) {
  15.                 e.printStackTrace();
  16.             }  
  17.         }  
  19.         InputStream in =null;  
  20.         try {
  21.             in = file.getInputStream();
  22.         } catch (IOException e) {
  23.             e.printStackTrace();
  24.         }  
  26.         List<List<Object>> listob = null; 
  27.         try {
  28.             listob = new ExcelUtils().getBankListByExcel(in,file.getOriginalFilename());
  29.         } catch (Exception e) {
  30.             e.printStackTrace();
  31.         }   
  32.            for (int i = 0; i < listob.size(); i++) { 
  33.             /* List<Object> lo = listob.get(i); if (lo.get(i)=="") { continue; }*/
  34.                System.out.println(listob.get(i));
  36.            }
  37.         for (int i = 0; i < listob.size(); i++) {  
  38.             List<Object> lo = listob.get(i);  
  39.             people vo = new people(); 
  40.             people j = null;
  42.             try {
  43.                 j = peopleMapper.selectByPrimaryKey(Integer.valueOf(String.valueOf(lo.get(0))));
  44.             } catch (NumberFormatException e) {
  45.                 // TODO Auto-generated catch block
  46.                 System.out.println("数据库中无该条数据，新增");
  48.             }
  49.                 vo.setId(Integer.valueOf(String.valueOf(lo.get(0))));    
  50.                 vo.setUserName(String.valueOf(lo.get(1)));  
  51.                 vo.setPassword(String.valueOf(lo.get(2)));   
  53.             if(j == null)
  54.             {
  56.                     peopleMapper.insert(vo);
  57.                     System.out.println("susscess");
  58.             }
  59.             else
  60.             {
  61.                     peopleMapper.updateByPrimaryKey(vo);
  62.             }
  64.         }   
  66.         return "文件导入成功！";
  67.     }
  68.     }

6、Contrller层
创建ExcelController.java

  1. @Controller  
  2. @RequestMapping("/upload")    
  3. public class ExcelController {   
  5.      @Resource(name="PeopleService")
  6.     private PeopleService PeopleService;
  7.    @ResponseBody  
  8.    @RequestMapping(value="ajaxUpload.do",method={RequestMethod.GET,RequestMethod.POST})  
  9.    public String ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {  
  10.        return PeopleService.ajaxUploadExcel(request, response);
  11.    }

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

  1. <fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;">
  2.   <legend>选完文件后不自动上传</legend>
  3. </fieldset>
  5. <div class="layui-upload">
  6.   <button type="button" class="layui-btn layui-btn-normal" id="test8">选择文件</button>
  7.   <button type="button" class="layui-btn" id="test9">开始上传</button>
  8. </div>
  10.     <script type="text/javascript" src="../../layui/layui.js"></script>
  11.     <script type="text/javascript" src="../../js/jquery-3.2.1.min.js" ></script>
  12.     <!--<script type="application/javascript" src="../../js/rfq_header.js"></script>-->
  13.     <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表中格式

  1. ID  USERNAME    PASSWORD
  2. 3   rr          rr123
  3. 4   jj2         jjp

发表评论

表情：
评论列表 （有 0 条评论，454人围观）

还没有评论，来说两句吧...

相关阅读