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

http://jacksonlhj.cn/articles/2022/03/02/1646231261821.html

序言

在日常开发工作中,我们经常会有对Excel表格进行操作的需求,其实Java有很多方法操作去操作Excel,例如有poi,阿里的easy excel等还有其它封装好的工具,在这里我其实更推荐Hutool工具,里面集成了很多功能,操作API也比较详细,里面也有对Excel的操作方法。

(本篇博客用于记录最原始的POI方法,以用于后文温习)

废话不多说,我们直接上代码

1、引入依赖

 <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 type="application/javascript" src="../../js/rfq_header.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
  • 8
    点赞
  • 28
    收藏
    觉得还不错? 一键收藏
  • 13
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 13
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值