poi 机制 效率最高的 excel 导入Oracle （源码）

说明：下面是三个类，复制粘贴就能运行 

             下载需要的包

package com.icss.poi.test2;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

public abstract class XxlsAbstract extends DefaultHandler {
 private SharedStringsTable sst;
 private String lastContents;
 private boolean nextIsString;
 private int sheetIndex = -1;
 private List<String> rowlist = new ArrayList<String>();
 private int curRow = 0;  //当前行
 private int curCol = 0;  //当前列索引
 private int preCol = 0;  //上一列列索引
 private int titleRow = 0; //标题行，一般情况下为0
 private int rowsize = 0; //列数
 
 //excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型
// public abstract void optRows(int curRow, List<String> rowlist) throws SQLException ;
 
 //excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型
 public abstract void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException;
 
 //只遍历一个sheet，其中sheetId为要遍历的sheet索引，从1开始，1-3
 public void processOneSheet(String filename,int sheetId) throws Exception {
  OPCPackage pkg = OPCPackage.open(filename);
  XSSFReader r = new XSSFReader(pkg);
  SharedStringsTable sst = r.getSharedStringsTable();
  
  XMLReader parser = fetchSheetParser(sst);

  // rId2 found by processing the Workbook
  // 根据 rId# 或 rSheet# 查找sheet
  InputStream sheet2 = r.getSheet("rId"+sheetId);
  sheetIndex++;
  InputSource sheetSource = new InputSource(sheet2);
  parser.parse(sheetSource);
  sheet2.close();
 }

 /**
  * 遍历 excel 文件
  */
 public void process(String filename) throws Exception {
  OPCPackage pkg = OPCPackage.open(filename);
  XSSFReader r = new XSSFReader(pkg);
  SharedStringsTable sst = r.getSharedStringsTable();

  XMLReader parser = fetchSheetParser(sst);

  Iterator<InputStream> sheets = r.getSheetsData();
  while (sheets.hasNext()) {
   curRow = 0;
   sheetIndex++;
   InputStream sheet = sheets.next();
   InputSource sheetSource = new InputSource(sheet);
   parser.parse(sheetSource);
   sheet.close();
  }
 }

 public XMLReader fetchSheetParser(SharedStringsTable sst)
   throws SAXException {
  XMLReader parser = XMLReaderFactory
    .createXMLReader("org.apache.xerces.parsers.SAXParser");
  this.sst = sst;
  parser.setContentHandler(this);
  return parser;
 }

 public void startElement(String uri, String localName, String name,
   Attributes attributes) throws SAXException {
  // c => 单元格
  if (name.equals("c")) {
   // 如果下一个元素是 SST 的索引，则将nextIsString标记为true
   String cellType = attributes.getValue("t");
   String rowStr = attributes.getValue("r");
   curCol = this.getRowIndex(rowStr);
   if (cellType != null && cellType.equals("s")) {
    nextIsString = true;
   } else {
    nextIsString = false;
   }
  }
  // 置空
  lastContents = "";
 }

 public void endElement(String uri, String localName, String name)
   throws SAXException {
  // 根据SST的索引值的到单元格的真正要存储的字符串
  // 这时characters()方法可能会被调用多次
  if (nextIsString) {
   try {
    int idx = Integer.parseInt(lastContents);
    lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
      .toString();
   } catch (Exception e) {

   }
  }

  // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
  // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
  if (name.equals("v")) {
   String value = lastContents.trim();
   value = value.equals("")?" ":value;
   int cols = curCol-preCol;
   if (cols>1){
    for (int i = 0;i < cols-1;i++){
     rowlist.add(preCol,"");
    }
   }
   preCol = curCol;
   rowlist.add(curCol-1, value);
  }else {
   //如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
   if (name.equals("row")) {
    int tmpCols = rowlist.size();
    if(curRow>this.titleRow && tmpCols<this.rowsize){
     for (int i = 0;i < this.rowsize-tmpCols;i++){
      rowlist.add(rowlist.size(), "");
     }
    }
    try {
     optRows(sheetIndex,curRow,rowlist);
    } catch (SQLException e) {
     e.printStackTrace();
    }
    if(curRow==this.titleRow){
     this.rowsize = rowlist.size();
    }
    rowlist.clear();
    curRow++;
    curCol = 0;
    preCol = 0;
   }
  }
 }

 public void characters(char[] ch, int start, int length)
   throws SAXException {
  //得到单元格内容的值
  lastContents += new String(ch, start, length);
 }
 
 //得到列索引，每一列c元素的r属性构成为字母加数字的形式，字母组合为列索引，数字组合为行索引，
 //如AB45,表示为第（A-A+1）*26+（B-A+1）*26列，45行
 public int getRowIndex(String rowStr){
  rowStr = rowStr.replaceAll("[^A-Z]", "");
  byte[] rowAbc = rowStr.getBytes();
  int len = rowAbc.length;
  float num = 0;
  for (int i=0;i<len;i++){
   num += (rowAbc[i]-'A'+1)*Math.pow(26,len-i-1 );
  }
  return (int) num;
 }

 public int getTitleRow() {
  return titleRow;
 }

 public void setTitleRow(int titleRow) {
  this.titleRow = titleRow;
 }
}

 

package com.icss.poi.test2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
public class XxlsBig extends XxlsAbstract {
 public  void test(String path) throws Exception {
  XxlsBig howto = new XxlsBig("test2");   //传入表
  //howto.processOneSheet("D:/test/test5.xlsx",1);
  howto.process("D:/test/test4.xlsx");
  howto.close();
 }
 public XxlsBig(String tableName) throws SQLException, ClassNotFoundException{  //接收一个参数   表名
  this.conn = getNew_Conn();
  this.statement = conn.createStatement();
  this.tableName = tableName;
 }

 private Connection conn = null;
 private Statement statement = null;
 private PreparedStatement newStatement = null;
 private String tableName = "temp_table";
 private boolean create = true;
 
 public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
  if (sheetIndex == 0 && curRow == 0) {
   StringBuffer preSql = new StringBuffer("insert into test2 values(");
   //StringBuffer table = new StringBuffer("create table " + tableName+ "(");
   int c = rowlist.size();
   for (int i = 0; i < c; i++) {
    preSql.append("?,");
    //table.append(rowlist.get(i));
    //table.append("  varchar2(100) ,");
   }
   //table.deleteCharAt(table.length() - 1);   //删除 创建表语句的最后一个逗号
   preSql.deleteCharAt(preSql.length() - 1);
   //table.append(")");
   preSql.append(")");
   /*if (create) {
    statement = conn.createStatement();
    try{
     statement.execute("drop table "+tableName);
    }catch(Exception e){
     
    }finally{
     System.out.println("表 "+tableName+" 删除成功");
    }
    if (!statement.execute(table.toString())) {
     System.out.println("创建表 "+tableName+" 成功");
     // return;
    } else {
     System.out.println("创建表 "+tableName+" 失败");
     return;
    }
   }*/
   conn.setAutoCommit(false);  //默认不提交事务
   newStatement = conn.prepareStatement(preSql.toString());
   System.out.println(preSql.toString());

  } else if(curRow>0) {
   // 一般行  不是表头  是真正的数据
   int col = rowlist.size();
   for (int i = 0; i < col; i++) {
    newStatement.setString(i + 1, rowlist.get(i).toString());
   }
   newStatement.addBatch();
   if (curRow % 1000 == 0) {
    newStatement.executeBatch();
    conn.commit();
   }
  }
 }
 
    private static Connection getNew_Conn() throws ClassNotFoundException, SQLException {
     Connection con = null;
  Class.forName("oracle.jdbc.driver.OracleDriver");
  con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
  return con;
    }
   
 public int close() {
  try {
   newStatement.executeBatch();
   conn.commit();
   System.out.println("数据写入完毕");
   this.newStatement.close();
   this.statement.close();
   this.conn.close();
   return 1;
  } catch (SQLException e) {
   return 0;
  }
 }
}

 

package com.icss.poi.test2;

 

import java.sql.SQLException;
import java.util.List;

 

public class XxlsPrint extends XxlsAbstract {

 @Override
 public void optRows(int sheetIndex,int curRow, List<String> rowlist) throws SQLException {
  for (int i = 0; i < rowlist.size(); i++) {
   System.out.print("'" + rowlist.get(i) + "',");
  }
  System.out.println();
 }

 public static void main(String[] args) throws Exception {
  XxlsPrint howto = new XxlsPrint();
  howto.processOneSheet("F:/new.xlsx",1);
//  howto.processAllSheets("F:/new.xlsx");
 }
}