Java实现批量导入excel表格数据到数据库中的方法
本文实例讲述了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,List rowlist)throwsSQLException; //excel记录行操作方法,以sheet索引,行索引和行元素列表为参数,对sheet的一行元素进行操作,元素为String类型 publicabstractvoidoptRows(intsheetIndex,intcurRow,List rowlist)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; //失败原因 privateList
failmsgs; //要处理数据所在的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,List rowlist)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; } publicList getRowtitle(){ returnrowtitle; } publicList >getFailrows(){ returnfailrows; } publicList
getFailmsgs(){ returnfailmsgs; } publicvoidsetFailmsgs(List failmsgs){ 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程序设计有所帮助。