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

2023-09-22 10:14:05 390

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

1、创建导入抽象类

packagecom.gcloud.common.excel;
importjava.io.FileInputStream;
importjava.io.FileNotFoundException;
importjava.io.IOException;
importjava.io.PrintStream;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
importorg.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
importorg.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
importorg.apache.poi.hssf.eventusermodel.HSSFEventFactory;
importorg.apache.poi.hssf.eventusermodel.HSSFListener;
importorg.apache.poi.hssf.eventusermodel.HSSFRequest;
importorg.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
importorg.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
importorg.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
importorg.apache.poi.hssf.model.HSSFFormulaParser;
importorg.apache.poi.hssf.record.BOFRecord;
importorg.apache.poi.hssf.record.BlankRecord;
importorg.apache.poi.hssf.record.BoolErrRecord;
importorg.apache.poi.hssf.record.BoundSheetRecord;
importorg.apache.poi.hssf.record.FormulaRecord;
importorg.apache.poi.hssf.record.LabelRecord;
importorg.apache.poi.hssf.record.LabelSSTRecord;
importorg.apache.poi.hssf.record.NoteRecord;
importorg.apache.poi.hssf.record.NumberRecord;
importorg.apache.poi.hssf.record.RKRecord;
importorg.apache.poi.hssf.record.Record;
importorg.apache.poi.hssf.record.SSTRecord;
importorg.apache.poi.hssf.record.StringRecord;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
*导入抽象类
*Createdbycharlinon2017/9/7.
*/
publicabstractclassHxlsAbstractimplementsHSSFListener{
privateintminColumns;
privatePOIFSFileSystemfs;
privatePrintStreamoutput;
privateintlastRowNumber;
privateintlastColumnNumber;
/**Shouldweoutputtheformula,orthevalueithas?*/
privatebooleanoutputFormulaValues=true;
/**ForparsingFormulas*/
privateSheetRecordCollectingListenerworkbookBuildingListener;
privateHSSFWorkbookstubWorkbook;
//Recordswepickupasweprocess
privateSSTRecordsstRecord;
privateFormatTrackingHSSFListenerformatListener;
/**Soweknownwhichsheetwe'reon*/
privateintsheetIndex=-1;
privateBoundSheetRecord[]orderedBSRs;
@SuppressWarnings("unchecked")
privateArrayListboundSheetRecords=newArrayList();
//Forhandlingformulaswithstringresults
privateintnextRow;
privateintnextColumn;
privatebooleanoutputNextStringRecord;
privateintcurRow;
privateListrowlist;
@SuppressWarnings("unused")
privateStringsheetName;
publicHxlsAbstract(POIFSFileSystemfs)
throwsSQLException{
this.fs=fs;
this.output=System.out;
this.minColumns=-1;
this.curRow=0;
this.rowlist=newArrayList();
}
publicHxlsAbstract(Stringfilename)throwsIOException,
FileNotFoundException,SQLException{
this(newPOIFSFileSystem(newFileInputStream(filename)));
}
//excel记录行操作方法，以行索引和行元素列表为参数，对一行元素进行操作，元素为String类型
//publicabstractvoidoptRows(intcurRow,Listrowlist)throwsSQLException;
//excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型
publicabstractvoidoptRows(intsheetIndex,intcurRow,Listrowlist)throwsException;
/**
*遍历excel文件
*/
publicvoidprocess()throwsIOException{
MissingRecordAwareHSSFListenerlistener=newMissingRecordAwareHSSFListener(
this);
formatListener=newFormatTrackingHSSFListener(listener);
HSSFEventFactoryfactory=newHSSFEventFactory();
HSSFRequestrequest=newHSSFRequest();
if(outputFormulaValues){
request.addListenerForAllRecords(formatListener);
}else{
workbookBuildingListener=newSheetRecordCollectingListener(
formatListener);
request.addListenerForAllRecords(workbookBuildingListener);
}
factory.processWorkbookEvents(request,fs);
}
/**
*HSSFListener监听方法，处理Record
*/
@SuppressWarnings("unchecked")
publicvoidprocessRecord(Recordrecord){
intthisRow=-1;
intthisColumn=-1;
StringthisStr=null;
Stringvalue=null;
switch(record.getSid()){
caseBoundSheetRecord.sid:
boundSheetRecords.add(record);
break;
caseBOFRecord.sid:
BOFRecordbr=(BOFRecord)record;
//进入sheet
if(br.getType()==BOFRecord.TYPE_WORKSHEET){
//Createsubworkbookifrequired
if(workbookBuildingListener!=null&&stubWorkbook==null){
stubWorkbook=workbookBuildingListener
.getStubHSSFWorkbook();
}
//WorksbyorderingtheBSRsbythelocationof
//theirBOFRecords,andthenknowingthatwe
//processBOFRecordsinbyteoffsetorder
sheetIndex++;
if(orderedBSRs==null){
orderedBSRs=BoundSheetRecord
.orderByBofPosition(boundSheetRecords);
}
sheetName=orderedBSRs[sheetIndex].getSheetname();
}
break;
caseSSTRecord.sid:
sstRecord=(SSTRecord)record;
break;
caseBlankRecord.sid:
BlankRecordbrec=(BlankRecord)record;
thisRow=brec.getRow();
thisColumn=brec.getColumn();
thisStr="";
break;
caseBoolErrRecord.sid:
BoolErrRecordberec=(BoolErrRecord)record;
thisRow=berec.getRow();
thisColumn=berec.getColumn();
thisStr="";
break;
caseFormulaRecord.sid:
FormulaRecordfrec=(FormulaRecord)record;
thisRow=frec.getRow();
thisColumn=frec.getColumn();
if(outputFormulaValues){
if(Double.isNaN(frec.getValue())){
//Formularesultisastring
//Thisisstoredinthenextrecord
outputNextStringRecord=true;
nextRow=frec.getRow();
nextColumn=frec.getColumn();
}else{
thisStr=formatListener.formatNumberDateCell(frec);
}
}else{
thisStr='"'+HSSFFormulaParser.toFormulaString(stubWorkbook,
frec.getParsedExpression())+'"';
}
break;
caseStringRecord.sid:
if(outputNextStringRecord){
//Stringforformula
StringRecordsrec=(StringRecord)record;
thisStr=srec.getString();
thisRow=nextRow;
thisColumn=nextColumn;
outputNextStringRecord=false;
}
break;
caseLabelRecord.sid:
LabelRecordlrec=(LabelRecord)record;
curRow=thisRow=lrec.getRow();
thisColumn=lrec.getColumn();
value=lrec.getValue().trim();
value=value.equals("")?"":value;
this.rowlist.add(thisColumn,value);
break;
caseLabelSSTRecord.sid:
LabelSSTRecordlsrec=(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;
caseNoteRecord.sid:
NoteRecordnrec=(NoteRecord)record;
thisRow=nrec.getRow();
thisColumn=nrec.getColumn();
//TODO:Findobjecttomatchnrec.getShapeId()
thisStr='"'+"(TODO)"+'"';
break;
caseNumberRecord.sid:
NumberRecordnumrec=(NumberRecord)record;
curRow=thisRow=numrec.getRow();
thisColumn=numrec.getColumn();
value=formatListener.formatNumberDateCell(numrec).trim();
value=value.equals("")?"":value;
//Format
rowlist.add(thisColumn,value);
break;
caseRKRecord.sid:
RKRecordrkrec=(RKRecord)record;
thisRow=rkrec.getRow();
thisColumn=rkrec.getColumn();
thisStr='"'+"(TODO)"+'"';
break;
default:
break;
}
//遇到新行的操作
if(thisRow!=-1&&thisRow!=lastRowNumber){
lastColumnNumber=-1;
}
//空值的操作
if(recordinstanceofMissingCellDummyRecord){
MissingCellDummyRecordmc=(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(recordinstanceofLastCellOfRowDummyRecord){
if(minColumns>0){
//列值重新置空
if(lastColumnNumber==-1){
lastColumnNumber=0;
}
}
//行结束时，调用optRows()方法
lastColumnNumber=-1;
try{
optRows(sheetIndex,curRow,rowlist);
}catch(Exceptione){
e.printStackTrace();
}
rowlist.clear();
}
}
}

2、创建导入接口

packagecom.gcloud.common.excel;
importjava.util.List;
publicinterfaceHxlsOptRowsInterface{
publicstaticfinalStringSUCCESS="success";
/**
*处理excel文件每行数据方法
*@paramsheetIndex
*@paramcurRow
*@paramrowlist
*@returnsuccess：成功，否则为失败原因
*@throwsException
*/
publicStringoptRows(intsheetIndex,intcurRow,Listrowlist)throwsException;
}

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

packagecom.gcloud.common.excel;
importjava.util.List;
publicclassHxlsInterfaceImplimplementsHxlsOptRowsInterface{
@Override
publicStringoptRows(intsheetIndex,intcurRow,Listdatalist)
throwsException{
//在这里执行数据的插入
//System.out.println(rowlist);
//saveData(datalist);
return"";
}
}

4、导入工具实现

packagecom.gcloud.common.excel;
importjava.io.FileNotFoundException;
importjava.io.IOException;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
/**
*excel导入工具
*Createdbycharlinon2017/9/7.
*/
publicclassExcelImportUtilextendsHxlsAbstract{
//数据处理bean
privateHxlsOptRowsInterfacehxlsOptRowsInterface;
//处理数据总数
privateintoptRows_sum=0;
//处理数据成功数量
privateintoptRows_success=0;
//处理数据失败数量
privateintoptRows_failure=0;
//excel表格每列标题
privateListrowtitle;
//失败数据
privateList>failrows;
//失败原因
privateListfailmsgs;
//要处理数据所在的sheet索引,从0开始
privateintsheetIndex;
publicExcelImportUtil(Stringfilename,intsheetIndex,HxlsOptRowsInterfacehxlsOptRowsInterface)throwsIOException,
FileNotFoundException,SQLException{
super(filename);
this.sheetIndex=sheetIndex;
this.hxlsOptRowsInterface=hxlsOptRowsInterface;
this.rowtitle=newArrayList();
this.failrows=newArrayList>();
this.failmsgs=newArrayList();
}
@Override
publicvoidoptRows(intsheetIndex,intcurRow,Listrowlist)throwsException{
/*for(inti=0;i(rowlist));
failmsgs.add(result);
}else{
optRows_success++;
}
}
}
}
publiclonggetOptRows_sum(){
returnoptRows_sum;
}
publicvoidsetOptRows_sum(intoptRows_sum){
this.optRows_sum=optRows_sum;
}
publiclonggetOptRows_success(){
returnoptRows_success;
}
publicvoidsetOptRows_success(intoptRows_success){
this.optRows_success=optRows_success;
}
publiclonggetOptRows_failure(){
returnoptRows_failure;
}
publicvoidsetOptRows_failure(intoptRows_failure){
this.optRows_failure=optRows_failure;
}
publicListgetRowtitle(){
returnrowtitle;
}
publicList>getFailrows(){
returnfailrows;
}
publicListgetFailmsgs(){
returnfailmsgs;
}
publicvoidsetFailmsgs(Listfailmsgs){
this.failmsgs=failmsgs;
}
}

5、导入实现方法：

publicstaticvoidmain(String[]args){
ExcelImportUtilimportUtil;
try{
importUtil=newExcelImportUtil("d:/data.xls",0,newHxlsInterfaceImpl());
importUtil.process();
}catch(FileNotFoundExceptione){
e.printStackTrace();
}catch(IOExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}
}

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

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

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

热门推荐

随机推荐