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

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();    }  }}

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;}

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 "";  }}

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;  }}

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();    }}