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;

public abstract class HxlsAbstract implements HSSFListener {
  private int minColumns;
  private POIFSFileSystem fs;
  private PrintStream output;
  private int lastRowNumber;
  private int lastColumnNumber;
  
  private boolean outputFormulaValues = true;
  
  private SheetRecordCollectingListener workbookBuildingListener;
  private HSSFWorkbook stubWorkbook;
  // Records we pick up as we process
  private SSTRecord sstRecord;
  private FormatTrackingHSSFListener formatListener;
  
  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 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();
  }
  public HxlsAbstract(String filename) throws IOException,
      FileNotFoundException, SQLException {
    this(new POIFSFileSystem(new FileInputStream(filename)));
  }
  //excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型
// public abstract void optRows(int curRow, List rowlist) throws SQLException ;
  //excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型
  public abstract void optRows(int sheetIndex,int curRow, List rowlist) throws Exception;
  
  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);
  }
  
  @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";
  
  public String optRows(int sheetIndex, int curRow, List 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 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;

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 rowtitle ;
  //失败数据
  private List> failrows;
  //失败原因
  private List 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();
    this.failrows = new ArrayList>();
    this.failmsgs = new ArrayList();
  }
  @Override
  public void optRows(int sheetIndex,int curRow, List rowlist) throws Exception {
    
    //将rowlist的长度补齐和标题一致
    int k=rowtitle.size()-rowlist.size();
    for(int i=0;i(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 getRowtitle() {
    return rowtitle;
  }
  public List> getFailrows() {
    return failrows;
  }
  public List getFailmsgs() {
    return failmsgs;
  }
  public void setFailmsgs(List 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程序设计有所帮助。

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

Java相关栏目本月热门文章