excel批量数据直接入库的两种方法
在前期某项目的应用实践中，遇到了一个从excel数据批量直接入库的功能需求。
这种需求的出现是基于大数据量的行式报表数据一次性入库的情况，比如：一个报表有50列，1万行（实际遇到的情况就是如此），这种情况下，采用行式报表的从excel导入功能，肯定是不行的，会出现页面等待时间过长，以至崩溃的情况。当然，这种方案在小数据量（数据在1000行以下时），是完全可行，且简单直观的。
对于这种数据量的导入要求，我们就会想到快逸报表中提供的另外一种解决方案，即API直接入库的方式，这种方案使用ireport来解析excel，并把解析好的excel所有单元格值填充一张事先做好的填报表单元格，然后让填报表运算，从而实现数据的入库操作。
这种方法在API帮助中有，大体代码如下：
| String excelFile = “D:/abc.xls”; ExcelImporter ei = new ExcelImporter(excelFile); //读入excel文件及其数据 IReport report=…… ; //计算出填报表，不能分页 for(int i=1;i<=report.getRowCount();i++){ //逐行逐列把excel数据写入填报表 for(int j=1;j<=report.getColCount();j++){ INormalCell iCell=report.getCell(i, (short)j); //取得报表单元格 INormalCell iExcelCell=excelReport.getCell(i, (short)j); //取得excel单元格 if(iCell.getInputProperty()!=null){ //判断单元格是否存在填报属性 //如果有填报属性，就为其设置填报值 iCell.getInputProperty().setInputValue(iExcelCell.getValue()); }else{ //没有填报属性，重新增加填报属性 InputProperty ip = new InputProperty(); ip.setInputValue(iExcelCell.getValue()); iCell.setInputProperty( ip ); } } } DataSaver dsave = new DataSaver((ExtCellSet)report,null,cxt); //构造数据保存对象 dsave.save(); //执行保存 |
第二种方法，也是最终在项目中采用的方法：是使用JXL开源框架进行excel数据的直接入库。
JXL(Java Excel API)是一个用来动态读写Excel文件的开源框架，利用它可以在任何支持Java的操作系统上动态读写Excel文件。并且，它的API是纯Java的，方便我们和应用的集成。这种框架对于excel读取数据有着很高的效率。因此，实现方案，是采用JXL框架逐行读取excel数据，每一行数据生成一条数据库的insert语句，所有行读取完毕后，批量一次性提交数据库事务，插入所有数据入库。
大体的源码如下：
|
//初始化数据库连接 Connection conn =null; Statement stmt = null; ResultSet rs = null; String sql = null; ConnectionManager cm = new ConnectionManager();
try { //连数据库 conn = cm.getConnection(); stmt = conn.createStatement(); conn.setAutoCommit(false); //设置事务不自动提交
// 用jxt导入数据————————————————————– Workbook book= Workbook.getWorkbook(new File(“人员表.xls”)); //获得excel文件输入流 Sheet sheet=book.getSheet(0); //获得第一个excel第一个sheet对象 int columnum = sheet.getColumns(); // 得到列数 int rownum = sheet.getRows(); // 得到行数 out.println(“文件列数:”+columnum); out.println(“文件行数:”+rownum);
String sql= “insert into 人员表 values(“;
for (int i=4;i<=rownum;i++) //从第4行开始为数据行，即从第4行开始导入数据 { for (int j=0;j < columnum;j++ ) //从第0列开始 { sql=sql+sheet.getCell(j,i)+”,”; } String sql=sql.substring(0,sql.length()-1); //去掉最后一个逗号 sql=sql+”)”; //加上sql右侧的括号 stmt.executeUpdate(sql); //读出一行就执行一行sql } conn.commit(); //批量提交数据 book.close(); //关闭文件 } catch(Exception e) { System.out.println(e); } finally { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } |
注意：jsp页面不要忘记引用JXL类包：<%@ page import=”jxl.*”%>
小结：对于excel批量数据直接入库的需求，在实际的应用场景中（50列，10000行的excel导入库），采用JXL数据导入方案，比采用快逸API的方式导入至少能节省1/3的时间，可以说效率更高。
对于客户提出类似的功能需求，在我们提供解决方案时，又多了一种解决思路。