【poi-3.8】poi解析excel&插入数据库详解

poi解析excel&插入数据库详解

说明

        1：在写此博文之前博主已经写过类似的一篇关于就exce文本中的数据插入到数据库之中。
        2：此文章就是一篇最简单无任何条件限制的直接读取excel文本中的数据，然后insert到数据库中。
        3：没有对excel表格中的数据进行文本，日期，数值的处理，只是单纯的全部作为文本进行了处理。
        4：也没有进行在数据库中select以此来和excel表中的数据进行对比查重。
        5：前台页面也没有提示插入多少行数据，成功导入多少行数据，重复多少行数据。
基于以上几点，此篇博文是对poi解析&插入到数据库中的一个更新版本

---

        因为excel文件分为两种版本，一种是以.xls后缀结尾的03版，一种是以.xlsx结尾的07版，所以为了兼顾两种版本的excel文件，对于此次选取poi-3.8进行解析。
        具体详情可以参考：https://www.aliyun.com/jiaocheng/574164.html

一、目的

        1：将excel中的数据通过解析导入到mysql数据库中
        2：和数据库对比使重复的数据不进行导入
        3：前台页面也反馈出导入的具体情况

二、准备工作

        1：获取poi-3.8解析包相关文件：https://download.csdn.net/download/jiajikang_jjk/10698866
        2：excel文件模板

        3：数据库对应表单

三、思路分析

1：上传文件

        上传文件到指定的路径，换句话说也就是将要解析的excel文件上传到指定的绝对路径下。

2：获取文件输入流

        获取上传的文件（也就是获取第一步上传要解析的excel文件）的文件名

3：解析

        解析获取的上传的文件，解析二字听着挺高大上的，个人对它的理解就去处理它的数据(日期，数值，文本)，使程序能够原封不动的按照excel文本中的格式原模原样的插入到数据库中。

4：存入list集合

        将解析后的excel中的数据存入到list集合之中

5：连接数据库

        解析完成之后，就是开始连接数据库，实现数据的插入

6：查重

        在将excel解析的数据进行插入数据库之前，要判断一下数据库中的表单是否有数据存在且在存在数据的情况下，进行判断是否和即将要插入的excel文件中的数据是否重复的一个判定。

        a：要是重复，记录重复的行数以及一共重复的个数。

        b：将没有重复的数据，执行插入到数据库中。

        c：前台界面要反馈出：一共插入多少行数据，成功插入多少行数据，重复多少行数据。

7：前台反馈

        前台界面要反馈出：一共插入多少行数据，成功插入多少行数据，重复多少行数据。

四、流程图分析

五、核心代码

1：上传文件

// 创建接收上传文件的对象
		// 需导入import="com.jspsmart.upload.*" lib下导入servlet.jar和jspsmartupload.jar包
		SmartUpload myUpload=new SmartUpload(); 
		myUpload.initialize(pageContext); //获取jsp的pageContext
		
		//从pageContext中接收要上载的文件，
		//pageContext中可以含有超文本表单提交的多个file域，且还可以包含超文本表单提交的其他字段
		myUpload.upload();//上传，这里有可能报错，一般是没导入servlet.jar包

		
		System.out.println("\n222222222222222222222222222222222222222222222222");
		String in_filename=""; //指向上载好的temp目录中的文件名
		String in_filedir=""; //服务器上temp目录中含文件名的绝对物理路径
	    
		// long startTime = System.currentTimeMillis();    //获取开始时间	
		String err_code="0x00";//用于异常判定的代码
		int num = 0;
	    try
		{
			//以新给定的文件名进行存储，要对上载的文件一个一个地依次进行处理
			for (int i=0; i < myUpload.getFiles().getCount(); i++) //myUpload.getFiles().getCount();要上传的文件数量
			{
			   com.jspsmart.upload.File my_file=myUpload.getFiles().getFile(i); //获取文件对象
			   //如果文件不存在
			   if (my_file.isMissing())	   
			{	 
			     // 报错
                 //err_code="ERR_A";			
				 throw new Exception("『back::_self』服务器无法接收此文件，上载文件失败！");
			}
			   //用操作系统的根目录作为存盘时相对路径的根目录
			   in_filename=my_file.getFileName();//文件名不变
			   in_filedir=dqz_str.StrReplace(WEB_ROOT03,"/","\\")+in_filename;//in_filedir为文件夹位置加上文件名
			   System.out.println("\nin_filedir = " + in_filedir);
			   my_file.saveAs(in_filedir,myUpload.SAVE_PHYSICAL);//保存文件到指定目录下
		   }
		 }
		catch(Exception e)
		{
		  throw new Exception("『back::_self』文件没有上传成功！");  
		} 

2：获取输入流

        虽然poi-3.8已经兼容03和07，但是博主这里依然为了用户查看源码时更能一目了然依然进行了判别：

// 开始解析
		//  获取文件输入流
		FileInputStream excelfile = new FileInputStream(in_filedir);//读取指定位置中的文件，就是刚才保存在指定文件夹中的上传文件
		System.out.println("\n文件位置："+excelfile);
		
		System.out.println("000000000000000000000000000");
		
		Workbook wb = null;	
		 if(in_filename.endsWith(".xlsx")){
			 
			wb = new XSSFWorkbook(excelfile);//07版
			System.out.println("\n111111111111111111111");
			
		 }else{
			 
			wb = new HSSFWorkbook(excelfile);// 03版
			System.out.println("\n222222222222222222222");
			
		 }
		 
		 // 初始化工作表
		 Sheet sheet = null; // excel工作表sheet
		 Row row = null;     // excel工作表行
		 Cell cell = null;   // excel工作表列
		 int SUM_cf=0;       // 记录数据库中重复的条数(使用序列号作为唯一标识符)
		 int SUM_cg=0;       // 成功插入的条数
		 List<Integer> hang = new ArrayList<Integer>();// 作为记录重复的行编号
		 
		sheet= wb.getSheet("丙机房发射机备件定额"); //  获取xssfworkbook对象--以此得到sheet(工作表)对象：丙机房发射机备件定额
		// 第二步
		// 判断获取的工作表（丙机房发射机备件定额）是否为空
		// 使用工作表之前要检查行对象是否为null，否则会报空指针异常
		sheet = wb.getSheetAt(0);//读取下标为0的表(也就是第一张表：sheet1)

3：初始化excel表

 // 初始化工作表
		 Sheet sheet = null; // excel工作表sheet
		 Row row = null;     // excel工作表行
		 Cell cell = null;   // excel工作表列
		 int SUM_cf=0;       // 记录数据库中重复的条数(使用序列号作为唯一标识符)
		 int SUM_cg=0;       // 成功插入的条数
		 List<Integer> hang = new ArrayList<Integer>();// 作为记录重复的行编号
		 
		sheet= wb.getSheet("丙机房发射机备件定额"); //  获取xssfworkbook对象--以此得到sheet(工作表)对象：丙机房发射机备件定额
		// 第二步
		// 判断获取的工作表（丙机房发射机备件定额）是否为空
		// 使用工作表之前要检查行对象是否为null，否则会报空指针异常
		sheet = wb.getSheetAt(0);//读取下标为0的表(也就是第一张表：sheet1)
		if(null==sheet){
			 System.out.println("工作表0【默认名为 Sheet1 】为空");  
			 throw new Exception("『back::_self』错误：工作表为空！");		
		}
		
		System.out.println("\n3333333333333333333333333333");
		
		// 第三步（在满足第二步的基础上进行）:获取一共的行数和列数
		// 行数
		int allRowNum= sheet.getLastRowNum(); // 获取一共的行数,因为从0行开始算起的，所以不+1就少一行
		System.out.println("一共 " + allRowNum + " 行");
		
		// 列数
		row=sheet.getRow(0);
		//System.out.println("检测："+row); // 初始化:	A1,B1,C1,,,M1
		
		int allCellNum1 = row.getLastCellNum();
		//int allCellNum2 = Row.getPhysicalNumberOfCells();
		
		System.out.println("一共 " + allCellNum1 + " 列");
		//System.out.println("有效物理列："+allCellNum2);
		 
		// 获取03,07版的行和列数结束

4：遍历行和列

        在遍历行和列的时候，进行了对excel中单元格数据进行了分析，然后存入到list集合中。其中在进行将String型转化为int型时博主使用的是此方法实现的。

 // 开始一行一行解析---一行解析出来的就是个数组----------全部就是一个list集合
	    // 遍历行
	    List<List<String>>list=new ArrayList<List<String>>();
		for(int i2=2;i2<=sheet.getLastRowNum();i2++)
		{    
			 System.out.println("\n开始遍历第" + i2 + " 行"); // 从最初有效行开始遍历
			 // 获取当前行
			 row=sheet.getRow(i2);
		
			if(null==row)
			 {
				 //row="";
				continue;   
			 }
		 
			 // 单元格列表
			List<String> dataList= new ArrayList<String>(); 
			
			for (int j1 = row.getFirstCellNum();j1<=allCellNum1;j1++) 
			{ 
				 String cellValue = "";
                 Cell cell2 = row.getCell(j1);// 获取当前行的单元格
				 System.out.println("当前单元格cell2："+cell2);
				 if(null==cell2){// 非空验证
					 
					//cell2="";
					continue;
				 }
				try {
				  if(cell2.getCellType()==Cell.CELL_TYPE_NUMERIC){// 数值型	
				  
				  if(HSSFDateUtil.isCellDateFormatted(cell2)){// 日期 poi 3.8
				    // System.out.println("日期格式");
					// System.out.println("cell2内容："+cell2);
					
				    Date date = cell2.getDateCellValue();// 获取日期
					// System.out.println("date01:"+date);// Sun Sep 09 00:00:00 CST 2018
					SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
					cellValue= simpleDateFormat.format(date); // 将获取去的单元格值(日期)设置为日期格式
                    // System.out.println("cellValue02："+cellValue);  // 
					
				  }else{
					    // System.out.println("\n数字格式");
					    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                        cellValue = hSSFDataFormatter.formatCellValue(cell2); // 使用EXCEL原来格式的方式取得值
						// System.out.println("cellValue03:"+cellValue);
						
				  }	
					  
				  }else{// 非数值类型
				    // System.out.println("\n非日期格式");
				    HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                    cellValue = hSSFDataFormatter.formatCellValue(cell2); // 使用EXCEL原来格式的方式取得值
					// System.out.println("cellValue04:"+cellValue);
				   
				  }

				  
				 // 将上面cellValue判断获取的值进行添加到集合dataList中
			       dataList.add(cellValue);
				   //System.out.println("dataList.size():"+dataList.size());// 12
				
				}catch (Exception e) {
					e.printStackTrace();
				}
			  }
			  
              //遍历当前行中所有单元格结束			  
              list.add(dataList);// 此处是将单元格值的dataList集合添加到list集合中！！！
		      //System.out.println("\nlist.add(dataList):"+ list.size());// 133-2= 131
			}
			
			// 解析完毕
			System.out.println("\n4444444444444444444444444444444444444");

5：连接数据库

        在连接数据库完成之后，下一步就执行插入数据到mysql数据库对应的表单之中。其中在插入前，进行了判别是否和数据库中已有的数据重复，其中判断数据查重操作博主依靠的此案例进行实现的查重

// 连接数据库
			boolean ok=false; //成功操作则此标志转为true
			//下面使用JNDI数据库连接池连接到MySQL服务器
			Connection my_con=null;
			String my_sql = "";
			
			try{
			
			String my_dbdriver=com_station_datebase_driver; //MYSQL 5.0 用的JDBC驱动
			String my_url=com_station_datebase_url; //MYSQL数据库名为dqz_mysql_test
			String my_username=com_station_datebase_user;  //数据库管理用户
			String my_password=com_station_datebase_password; //数据库管理密码
			Class.forName(my_dbdriver); //装载驱动程序
			my_con=DriverManager.getConnection(my_url,my_username,my_password); //连接数据库
			
			//建立远程视图
			Statement my_stmt=my_con.createStatement();
	        err_code="0x01"; //成功执行到获取了远程视图
			System.out.println("连接数据库成功！！");
			  // 注意：因为SQL中规定字符串值（String变量或字符串常量）必须用单引号''括起来，
			  // 所以下面的jsp程序中在SQL语句中传递的字符串不能使用双引号，而用了单引号。
			  // 发送并执行SQL
			
			
			 // 进行查询数据库----判断数据库中的数据是否和要插入的数据是否重复 
			 // 从有效数据行开始遍历	
			 
			 
			 //int SUM_cf=0; // 记录数据库中重复的条数(使用序列号作为唯一标识符)
			 int SUM_gs=0; // 记录格式错误的条数
			 //int SUM_cg=0; // 成功插入的条数 
			 int c = 0;  // 设置String 转 int型
			 
		  
		 String dates = ""; // 获取到的数据
		 System.out.println("\n5555555555555555555555555555555");
		   
		 
		 List<String> list2= new ArrayList<String>();// 作为将查询数据库中xlh存入到list2这个集合中
		 // List<Integer> hang = new ArrayList<Integer>();// 作为记录重复的行编号
		 
		
		 my_sql = "SELECT DISTINCT xlh FROM excel_test GROUP BY xlh"; // 查询数据库中的xlh
		 ResultSet my_rs=my_stmt.executeQuery(my_sql); //执行查询
		
		 
		 // ResultSetMetaData md = my_rs.getMetaData(); // 获取字段个数 
		 // int columnCount = md.getColumnCount();
		 
		 // my_rs.last();// 移到最后一行
		 // int count = my_rs.getRow();// 获取数据库中一共多少行
		 // System.out.println("count：" +count);
		  
		  if(null!=my_rs){// 如果查询数据不为空--进行将数据添加到list2集合中
			  while(my_rs.next()){
			  
			  // System.out.println("66666666666666666666666");
			  dates = my_rs.getString("xlh");// 获取xlh的值
			  list2.add(dates); // 将获取到的xlh存入到list2集合中
			  System.out.println("dates："+ dates);
			  
		     }  
		  }
		  
		   
		    // 添加list2 完成之后---或者查询为空时-------》进行插入操作
		    // 遍历excel表的list集合
		   for(int x=0;x<list.size();x++){ 
		   try{
			 
			// 没有查到也就是没有重复的-----执行插入
			// 遍历(单元格中的数据存入到list集合中的)list集合
			my_sql = "INSERT INTO excel_test(xlh,xh,zwmc,gnms,cksm,C01,C02,ljyl,bfbl,ybsl,bjsl,bz) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";		
			// 创建一个Statment对象
			PreparedStatement ps = my_con.prepareStatement(my_sql);
			
		 
			// 为sql语句中第一个问号赋值
			ps.setString(1, list.get(x).get(0));// 产品序列号
			//System.out.println("list中的内容："+list.get(x).get(0));
			
			// 为sql语句中第二个问号赋值
			ps.setString(2, list.get(x).get(1));// 规格型号
			// 为sql语句中第三个问号赋值
			ps.setString(3, list.get(x).get(2));// 中文名称
			ps.setString(4, list.get(x).get(3));// 功能描述
			ps.setString(5, list.get(x).get(4));// 参考寿命
			
			// String -------转化---------int
			// 说明：对应的单元格中的数字不能为空--即使为空也要用0来代替
			if(list.get(x).get(5)!=null && list.get(x).get(5).length()>0){	
				 c = Integer.parseInt(list.get(x).get(5));// 将list.get(x).get(5)得到的String型的数据转化为Int型
				 ps.setInt(6, c);// C01用量	
			}else{
				ps.setInt(6,c);
			}	 
			ps.setString(7, list.get(x).get(6));// C02用量
			ps.setString(8, list.get(x).get(7));// 两机用量
			ps.setString(9, list.get(x).get(8));// 备份比例
			ps.setString(10, list.get(x).get(9));// 应备数量
			ps.setString(11, list.get(x).get(10));// 备件数量
			ps.setString(12, list.get(x).get(11));// 备注
			
			// System.out.println("\n6666666666666666666666666666666666");
			// System.out.println("\nlist中的内容："+list.get(x).get(0));
			
			
			boolean flag = true;
			
	        if(null==list2||list2.size()==0){// 如何list2集合为空就直接进行插入
				System.out.println("list2集合为空，直接插入数据");
				ps.executeUpdate();// 执行sql语句
				err_code="0x02"; //成功执行了插入语句
				
			}else{
				  
				 // list2集合不为空
				 // 进行判断查重
                 // 遍历查询--list2集合是添加数据库序列号的集合
				for(int j=0; j<list2.size();j++){
					if(list.get(x).get(0).equals(list2.get(j))){// excel中获取的序列号和数据库中获取的序列进行判断是否相等(重复)
						flag = false;
					    hang.add(x+3);
						System.out.println("hang.add(x+3)" + hang);
						System.out.println("第"+(x+3)+"行,"+"序列号："+list.get(x).get(0)+",在集合list2中出现，不能重复添加");
                        //                        

					   break;						
					} 	
				}
				
				//  如果 boolean flag = true-->无重复
				if(flag){
					
					// 执行没有重复的数据---将excel中未重复的数据插入的数据库
					System.out.println("无重复，执行插入");
					ps.executeUpdate(); // 执行sql语句
					err_code="0x03";   //成功执行了插入语句
					SUM_cg++; // 添加成功的个数
				}else{	
					SUM_cf++; //记录重复个数
				}
		   }
	 	 
		  }catch(Exception e2){
			 // throw new Exception("在excel_add_list_ok.jsp 中插入数据时发生异常：" + e);
		     err_code="0x04";   //成功执行了插入语句
			 System.out.println("出现插入异常");
		 }
	 
		}
		
		  System.out.println("\nfor循环结束");
		  System.out.println("\n一共重复："+ SUM_cf+"行");
		  System.out.println("\n一共成功插入："+SUM_cg+"行");
		  
		  int sum_cf = SUM_cf;// 一共重复的行数
          int sum_cg = SUM_cg;// 一共成功插入的行数
		  System.out.println("sum_cf:"+sum_cf);
		  System.out.println("sum_cg:"+sum_cg);
		  
		  
		  
		  ok=true; //处理成功
		  my_con.close();  //关闭数据库
	 
	   }catch(Exception e){
			 System.out.println("\ne:"+e);
			 System.out.println("\nmy_sql:"+my_sql);	 
	   }finally{
				
			if(null!=my_con) my_con.close(); //在发生异常时关闭数据库连接
		
		
		}if(!ok)
		{
		  //操作失败：
		  throw new Exception("『back::_self』添加新记录失败，请重试！");
		}else{
			
		     System.out.println("成功！！！");	
		    //  操作成功
		    //throw new Exception("『wait:tps_list.jsp:_self』记录添加成功！");
		   
		}

6：前台反馈

 <html>
  <head>
    <title>导入Excel表</title>
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<link rel="stylesheet" href="../css/bootstrap.min.css" />

	<script type="text/javascript" src="../js/jquery-1.9.1.min.js"></script>
	
  </head> 
  <body>
       
	<div align="center">
	<p><font size=15px>Excel表格信息导入成功</font></p>
	<br/>
	<p>一共导入<%=allRowNum-1%>行数据，成功导入<%=SUM_cg%>行数据，重复<%=SUM_cf%>条数据</p>
	</div>
	<div>
	其中重复的行编号分别是：第 <%=hang%> 行！！！
	</div>
	 
	<!--js部分-->
	
	<div align="center"><a size=50px href="excel_add_test_list.jsp">返回</a></div>
  </body>
</html>

六、效果展示