Java实现批量导入excel表格数据到数据库中的方法

本文实例讲述了java实现批量导入excel表格数据到数据库中的方法。分享给大家供大家参考，具体如下：

1、创建导入抽象类

package com.gcloud.common.excel;
import java.io.fileinputstream;
import java.io.filenotfoundexception;
import java.io.ioexception;
import java.io.printstream;
import java.sql.sqlexception;
import java.util.arraylist;
import java.util.list;
import org.apache.poi.hssf.eventusermodel.eventworkbookbuilder.sheetrecordcollectinglistener;
import org.apache.poi.hssf.eventusermodel.formattrackinghssflistener;
import org.apache.poi.hssf.eventusermodel.hssfeventfactory;
import org.apache.poi.hssf.eventusermodel.hssflistener;
import org.apache.poi.hssf.eventusermodel.hssfrequest;
import org.apache.poi.hssf.eventusermodel.missingrecordawarehssflistener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.lastcellofrowdummyrecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.missingcelldummyrecord;
import org.apache.poi.hssf.model.hssfformulaparser;
import org.apache.poi.hssf.record.bofrecord;
import org.apache.poi.hssf.record.blankrecord;
import org.apache.poi.hssf.record.boolerrrecord;
import org.apache.poi.hssf.record.boundsheetrecord;
import org.apache.poi.hssf.record.formularecord;
import org.apache.poi.hssf.record.labelrecord;
import org.apache.poi.hssf.record.labelsstrecord;
import org.apache.poi.hssf.record.noterecord;
import org.apache.poi.hssf.record.numberrecord;
import org.apache.poi.hssf.record.rkrecord;
import org.apache.poi.hssf.record.record;
import org.apache.poi.hssf.record.sstrecord;
import org.apache.poi.hssf.record.stringrecord;
import org.apache.poi.hssf.usermodel.hssfworkbook;
import org.apache.poi.poifs.filesystem.poifsfilesystem;
/**
 * 导入抽象类
 * created by charlin on 2017/9/7.
 */
public abstract class hxlsabstract implements hssflistener {
  private int mincolumns;
  private poifsfilesystem fs;
  private printstream output;
  private int lastrownumber;
  private int lastcolumnnumber;
  /** should we output the formula, or the value it has? */
  private boolean outputformulavalues = true;
  /** for parsing formulas */
  private sheetrecordcollectinglistener workbookbuildinglistener;
  private hssfworkbook stubworkbook;
  // records we pick up as we process
  private sstrecord sstrecord;
  private formattrackinghssflistener formatlistener;
  /** so we known which sheet we're on */
  private int sheetindex = -1;
  private boundsheetrecord[] orderedbsrs;
  @suppresswarnings("unchecked")
  private arraylist boundsheetrecords = new arraylist();
  // for handling formulas with string results
  private int nextrow;
  private int nextcolumn;
  private boolean outputnextstringrecord;
  private int currow;
  private list<string> rowlist;
  @suppresswarnings( "unused")
  private string sheetname;
  public hxlsabstract(poifsfilesystem fs)
      throws sqlexception {
    this.fs = fs;
    this.output = system.out;
    this.mincolumns = -1;
    this.currow = 0;
    this.rowlist = new arraylist<string>();
  }
  public hxlsabstract(string filename) throws ioexception,
      filenotfoundexception, sqlexception {
    this(new poifsfilesystem(new fileinputstream(filename)));
  }
  //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 exception;
  /**
   * 遍历 excel 文件
   */
  public void process() throws ioexception {
    missingrecordawarehssflistener listener = new missingrecordawarehssflistener(
        this);
    formatlistener = new formattrackinghssflistener(listener);
    hssfeventfactory factory = new hssfeventfactory();
    hssfrequest request = new hssfrequest();
    if (outputformulavalues) {
      request.addlistenerforallrecords(formatlistener);
    } else {
      workbookbuildinglistener = new sheetrecordcollectinglistener(
          formatlistener);
      request.addlistenerforallrecords(workbookbuildinglistener);
    }
    factory.processworkbookevents(request, fs);
  }
  /**
   * hssflistener 监听方法，处理 record
   */
  @suppresswarnings("unchecked")
  public void processrecord(record record) {
    int thisrow = -1;
    int thiscolumn = -1;
    string thisstr = null;
    string value = null;
    switch (record.getsid()) {
    case boundsheetrecord.sid:
      boundsheetrecords.add(record);
      break;
    case bofrecord.sid:
      bofrecord br = (bofrecord) record;
      //进入sheet
      if (br.gettype() == bofrecord.type_worksheet) {
        // create sub workbook if required
        if (workbookbuildinglistener != null && stubworkbook == null) {
          stubworkbook = workbookbuildinglistener
              .getstubhssfworkbook();
        }
        // works by ordering the bsrs by the location of
        // their bofrecords, and then knowing that we
        // process bofrecords in byte offset order
        sheetindex++;
        if (orderedbsrs == null) {
          orderedbsrs = boundsheetrecord
              .orderbybofposition(boundsheetrecords);
        }
        sheetname = orderedbsrs[sheetindex].getsheetname();
      }
      break;
    case sstrecord.sid:
      sstrecord = (sstrecord) record;
      break;
    case blankrecord.sid:
      blankrecord brec = (blankrecord) record;
      thisrow = brec.getrow();
      thiscolumn = brec.getcolumn();
      thisstr = "";
      break;
    case boolerrrecord.sid:
      boolerrrecord berec = (boolerrrecord) record;
      thisrow = berec.getrow();
      thiscolumn = berec.getcolumn();
      thisstr = "";
      break;
    case formularecord.sid:
      formularecord frec = (formularecord) record;
      thisrow = frec.getrow();
      thiscolumn = frec.getcolumn();
      if (outputformulavalues) {
        if (double.isnan(frec.getvalue())) {
          // formula result is a string
          // this is stored in the next record
          outputnextstringrecord = true;
          nextrow = frec.getrow();
          nextcolumn = frec.getcolumn();
        } else {
          thisstr = formatlistener.formatnumberdatecell(frec);
        }
      } else {
        thisstr = '"' + hssfformulaparser.toformulastring(stubworkbook,
            frec.getparsedexpression()) + '"';
      }
      break;
    case stringrecord.sid:
      if (outputnextstringrecord) {
        // string for formula
        stringrecord srec = (stringrecord) record;
        thisstr = srec.getstring();
        thisrow = nextrow;
        thiscolumn = nextcolumn;
        outputnextstringrecord = false;
      }
      break;
    case labelrecord.sid:
      labelrecord lrec = (labelrecord) record;
      currow = thisrow = lrec.getrow();
      thiscolumn = lrec.getcolumn();
      value = lrec.getvalue().trim();
      value = value.equals("")?" ":value;
      this.rowlist.add(thiscolumn, value);
      break;
    case labelsstrecord.sid:
      labelsstrecord lsrec = (labelsstrecord) record;
      currow = thisrow = lsrec.getrow();
      thiscolumn = lsrec.getcolumn();
      if (sstrecord == null) {
        rowlist.add(thiscolumn, " ");
      } else {
        value = sstrecord
        .getstring(lsrec.getsstindex()).tostring().trim();
        value = value.equals("")?" ":value;
        rowlist.add(thiscolumn,value);
      }
      break;
    case noterecord.sid:
      noterecord nrec = (noterecord) record;
      thisrow = nrec.getrow();
      thiscolumn = nrec.getcolumn();
      // todo: find object to match nrec.getshapeid()
      thisstr = '"' + "(todo)" + '"';
      break;
    case numberrecord.sid:
      numberrecord numrec = (numberrecord) record;
      currow = thisrow = numrec.getrow();
      thiscolumn = numrec.getcolumn();
      value = formatlistener.formatnumberdatecell(numrec).trim();
      value = value.equals("")?" ":value;
      // format
      rowlist.add(thiscolumn, value);
      break;
    case rkrecord.sid:
      rkrecord rkrec = (rkrecord) record;
      thisrow = rkrec.getrow();
      thiscolumn = rkrec.getcolumn();
      thisstr = '"' + "(todo)" + '"';
      break;
    default:
      break;
    }
    // 遇到新行的操作
    if (thisrow != -1 && thisrow != lastrownumber) {
      lastcolumnnumber = -1;
    }
    // 空值的操作
    if (record instanceof missingcelldummyrecord) {
      missingcelldummyrecord mc = (missingcelldummyrecord) record;
      currow = thisrow = mc.getrow();
      thiscolumn = mc.getcolumn();
      rowlist.add(thiscolumn," ");
    }
    // 如果遇到能打印的东西，在这里打印
    if (thisstr != null) {
      if (thiscolumn > 0) {
        output.print(',');
      }
      output.print(thisstr);
    }
    // 更新行和列的值
    if (thisrow > -1)
      lastrownumber = thisrow;
    if (thiscolumn > -1)
      lastcolumnnumber = thiscolumn;
    // 行结束时的操作
    if (record instanceof lastcellofrowdummyrecord) {
      if (mincolumns > 0) {
        // 列值重新置空
        if (lastcolumnnumber == -1) {
          lastcolumnnumber = 0;
        }
      }
      // 行结束时， 调用 optrows() 方法
      lastcolumnnumber = -1;
      try {
        optrows(sheetindex,currow, rowlist);
      } catch (exception e) {
        e.printstacktrace();
      }
      rowlist.clear();
    }
  }
}

2、创建导入接口

package com.gcloud.common.excel;
import java.util.list;
public interface hxlsoptrowsinterface {
  public static final string success="success";
  /**
   * 处理excel文件每行数据方法
   * @param sheetindex
   * @param currow
   * @param rowlist
   * @return success：成功，否则为失败原因
   * @throws exception
   */
  public string optrows(int sheetindex, int currow, list<string> rowlist) throws exception;
}

3、创建实现类， 在这个方法实现把导入的数据添加到数据库中

package com.gcloud.common.excel;
import java.util.list;
public class hxlsinterfaceimpl implements hxlsoptrowsinterface {
  @override
  public string optrows(int sheetindex, int currow, list<string> datalist)
      throws exception {
    //在这里执行数据的插入
    //system.out.println(rowlist);
    //savedata(datalist);
    return "";
  }
}

4、导入工具实现

package com.gcloud.common.excel;
import java.io.filenotfoundexception;
import java.io.ioexception;
import java.sql.sqlexception;
import java.util.arraylist;
import java.util.list;
/**
 * excel导入工具
 * created by charlin on 2017/9/7.
 */
public class excelimportutil extends hxlsabstract{
  //数据处理bean
  private hxlsoptrowsinterface hxlsoptrowsinterface;
  //处理数据总数
  private int optrows_sum = 0;
  //处理数据成功数量
  private int optrows_success = 0;
  //处理数据失败数量
  private int optrows_failure = 0;
  //excel表格每列标题
  private list<string> rowtitle ;
  //失败数据
  private list<list<string>> failrows;
  //失败原因
  private list<string> failmsgs ;
  //要处理数据所在的sheet索引,从0开始
  private int sheetindex;
  public excelimportutil(string filename, int sheetindex, hxlsoptrowsinterface hxlsoptrowsinterface) throws ioexception,
      filenotfoundexception, sqlexception {
    super(filename);
    this.sheetindex = sheetindex;
    this.hxlsoptrowsinterface = hxlsoptrowsinterface;
    this.rowtitle = new arraylist<string>();
    this.failrows = new arraylist<list<string>>();
    this.failmsgs = new arraylist<string>();
  }
  @override
  public void optrows(int sheetindex,int currow, list<string> rowlist) throws exception {
    /*for (int i = 0 ;i< rowlist.size();i++){
      system.out.print("'"+rowlist.get(i)+"',");
    }
    system.out.println();*/
    //将rowlist的长度补齐和标题一致
    int k=rowtitle.size()-rowlist.size();
    for(int i=0;i<k;i++){
      rowlist.add(null);
    }
    if(sheetindex == this.sheetindex){
      optrows_sum++;
      if(currow == 0){//记录标题
        rowtitle.addall(rowlist);
      }else{
        string result = hxlsoptrowsinterface.optrows(sheetindex, currow, rowlist);
        if(!result.equals(hxlsoptrowsinterface.success)){
          optrows_failure++;
          //失败数据
          failrows.add(new arraylist<string>(rowlist));
          failmsgs.add(result);
        }else{
          optrows_success++;
        }
      }
    }
  }
  public long getoptrows_sum() {
    return optrows_sum;
  }
  public void setoptrows_sum(int optrows_sum) {
    this.optrows_sum = optrows_sum;
  }
  public long getoptrows_success() {
    return optrows_success;
  }
  public void setoptrows_success(int optrows_success) {
    this.optrows_success = optrows_success;
  }
  public long getoptrows_failure() {
    return optrows_failure;
  }
  public void setoptrows_failure(int optrows_failure) {
    this.optrows_failure = optrows_failure;
  }
  public list<string> getrowtitle() {
    return rowtitle;
  }
  public list<list<string>> getfailrows() {
    return failrows;
  }
  public list<string> getfailmsgs() {
    return failmsgs;
  }
  public void setfailmsgs(list<string> failmsgs) {
    this.failmsgs = failmsgs;
  }
}

5、导入实现方法：

public static void main(string[] args){
    excelimportutil importutil;
    try {
      importutil = new excelimportutil("d:/data.xls",0, new hxlsinterfaceimpl());
      importutil.process();
    } catch (filenotfoundexception e) {
      e.printstacktrace();
    } catch (ioexception e) {
      e.printstacktrace();
    } catch (sqlexception e) {
      e.printstacktrace();
    }
}

更多关于java相关内容感兴趣的读者可查看本站专题：《java操作excel技巧总结》、《java+mysql数据库程序设计总结》、《java数据结构与算法教程》、《java文件与目录操作技巧汇总》及《java操作dom节点技巧总结》

希望本文所述对大家java程序设计有所帮助。