POI读写海量Excel (详细解读)

《POI读写海量Excel (详细解读)》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

目前处理Excel的开源javaAPI主要有两种，一是Jxl（JavaExcelAPI）,Jxl只支持Excel2003以下的版本。另外一种是Apache的JakartaPOI,相比于Jxl,POI对微软办公文档的支持更加强大，但是它使用复杂，上手慢。POI可支持更高的Excel版本2007。对Excel的读取，POI有两种模式，一是用户模式，这种方式同Jxl的使用很类似，使用简单，都是将文件一次性读到内存，文件小的时候，没有什么问题，当文件大的时候，就会出现OutOfMemory的内存溢出问题。第二种是事件驱动模式，拿Excel2007来说，其内容采用XML的格式来存储，所以处理excel就是解析XML，而目前使用事件驱动模式解析XML的API是SAX(SimpleAPIforXML)，这种模型在读取XML文档时，并没有将整个文档读入内存，而是按顺序将整个文档解析完，在解析过程中，会主动产生事件交给程序中相应的处理函数来处理当前内容。因此这种方式对系统资源要求不高，可以处理海量数据。笔者曾经做过测试，这种方法处理一千万条，每条五列的数据花费大约11分钟。可见处理海量数据的文件事件驱动是一个很好的方式。而本文中用到的AbstractExcel2003Reader、AbstractExcel2007Reader对Excel的读取都是采用这种POI的事件驱动模式。至于Excel的写操作，对较高版本的Excel2007，POI提供了很好的支持，主要流程是第一步构建工作薄和电子表格对象，第二步在一个流中构建文本文件，第三步使用流中产生的数据替换模板中的电子表格。这种方式也可以处理海量数据文件。AbstractExcel2007Writer就是使用这种方式进行写操作。对于写入较低版本的Excel2003，POI使用了用户模式来处理，就是将整个文档加载进内存，如果数据量大的话就会出现内存溢出的问题，Excel2003Writer就是使用这种方式。据笔者的测试，如果数据量大于3万条，每条8列的话，就会报OutOfMemory的错误。Excel2003中每个电子表格的记录数必须在65536以下，否则就会发生异常。目前还没有好的解决方案，建议对于海量数据写入操作，尽量使用Excel2007。/***抽象Excel2003读取器，通过实现HSSFListener监听器，采用事件驱动模式解析excel2003*中的内容，遇到特定事件才会触发，大大减少了内存的使用。**/publicclassExcel2003ReaderimplementsHSSFListener{privateintminColumns=-1;privatePOIFSFileSystemfs;privateintlastRowNumber;privateintlastColumnNumber;/**Shouldweoutputtheformula,orthevalueithas?*/privatebooleanoutputFormulaValues=true;/**ForparsingFormulas*/privateSheetRecordCollectingListenerworkbookBuildingListener;//excel2003工作薄privateHSSFWorkbookstubWorkbook;//RecordswepickupasweprocessprivateSSTRecordsstRecord; privateFormatTrackingHSSFListenerformatListener;//表索引privateintsheetIndex=-1;privateBoundSheetRecord[]orderedBSRs;@SuppressWarnings("unchecked")privateArrayListboundSheetRecords=newArrayList();//ForhandlingformulaswithstringresultsprivateintnextRow;privateintnextColumn;privatebooleanoutputNextStringRecord;//当前行privateintcurRow=0;//存储行记录的容器privateListrowlist=newArrayList();;@SuppressWarnings("unused")privateStringsheetName;privateIRowReaderrowReader;publicvoidsetRowReader(IRowReaderrowReader){this.rowReader=rowReader;}/***遍历excel下所有的sheet*@throwsIOException*/publicvoidprocess(StringfileName)throwsIOException{this.fs=newPOIFSFileSystem(newFileInputStream(fileName));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;if(br.getType()==BOFRecord.TYPE_WORKSHEET){//如果有需要，则建立子工作薄if(workbookBuildingListener!=null&&stubWorkbook==null){stubWorkbook=workbookBuildingListener.getStubHSSFWorkbook();}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="";rowlist.add(thisColumn,thisStr); break;caseBoolErrRecord.sid://单元格为布尔类型BoolErrRecordberec=(BoolErrRecord)record;thisRow=berec.getRow();thisColumn=berec.getColumn();thisStr=berec.getBooleanValue()+"";rowlist.add(thisColumn,thisStr);break;caseFormulaRecord.sid://单元格为公式类型FormulaRecordfrec=(FormulaRecord)record;thisRow=frec.getRow();thisColumn=frec.getColumn();if(outputFormulaValues){if(Double.isNaN(frec.getValue())){//Formularesultisastring//ThisisstoredinthenextrecordoutputNextStringRecord=true;nextRow=frec.getRow();nextColumn=frec.getColumn();}else{thisStr=formatListener.formatNumberDateCell(frec);}}else{thisStr='"'+HSSFFormulaParser.toFormulaString(stubWorkbook,frec.getParsedExpression())+'"';}rowlist.add(thisColumn,thisStr);break;caseStringRecord.sid://单元格中公式的字符串if(outputNextStringRecord){//StringforformulaStringRecordsrec=(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;caseNumberRecord.sid://单元格为数字类型NumberRecordnumrec=(NumberRecord)record;curRow=thisRow=numrec.getRow();thisColumn=numrec.getColumn();value=formatListener.formatNumberDateCell(numrec).trim();value=value.equals("")?"":value;//向容器加入列值rowlist.add(thisColumn,value);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(thisRow>-1) lastRowNumber=thisRow;if(thisColumn>-1)lastColumnNumber=thisColumn;//行结束时的操作if(recordinstanceofLastCellOfRowDummyRecord){if(minColumns>0){//列值重新置空if(lastColumnNumber==-1){lastColumnNumber=0;}}lastColumnNumber=-1;//每行结束时，调用getRows()方法rowReader.getRows(sheetIndex,curRow,rowlist);//清空容器rowlist.clear();}}}/***抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析*xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低*内存的耗费，特别使用于大数据量的文件。**/publicclassExcel2007ReaderextendsDefaultHandler{//共享字符串表privateSharedStringsTablesst;//上一次的内容privateStringlastContents;privatebooleannextIsString;privateintsheetIndex=-1;privateListrowlist=newArrayList();//当前行privateintcurRow=0;//当前列 privateintcurCol=0;//日期标志privatebooleandateFlag;//数字标志privatebooleannumberFlag;privatebooleanisTElement;privateIRowReaderrowReader;publicvoidsetRowReader(IRowReaderrowReader){this.rowReader=rowReader;}/**只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始，1-3*@paramfilename*@paramsheetId*@throwsException*/publicvoidprocessOneSheet(Stringfilename,intsheetId)throwsException{OPCPackagepkg=OPCPackage.open(filename);XSSFReaderr=newXSSFReader(pkg);SharedStringsTablesst=r.getSharedStringsTable();XMLReaderparser=fetchSheetParser(sst);//根据rId#或rSheet#查找sheetInputStreamsheet2=r.getSheet("rId"+sheetId);sheetIndex++;InputSourcesheetSource=newInputSource(sheet2);parser.parse(sheetSource);sheet2.close();}/***遍历工作簿中所有的电子表格*@paramfilename*@throwsException*/publicvoidprocess(Stringfilename)throwsException{OPCPackagepkg=OPCPackage.open(filename);XSSFReaderr=newXSSFReader(pkg);SharedStringsTablesst=r.getSharedStringsTable();XMLReaderparser=fetchSheetParser(sst);Iteratorsheets=r.getSheetsData(); while(sheets.hasNext()){curRow=0;sheetIndex++;InputStreamsheet=sheets.next();InputSourcesheetSource=newInputSource(sheet);parser.parse(sheetSource);sheet.close();}}publicXMLReaderfetchSheetParser(SharedStringsTablesst)throwsSAXException{XMLReaderparser=XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");this.sst=sst;parser.setContentHandler(this);returnparser;}publicvoidstartElement(Stringuri,StringlocalName,Stringname,Attributesattributes)throwsSAXException{//c=>单元格if("c".equals(name)){//如果下一个元素是SST的索引，则将nextIsString标记为trueStringcellType=attributes.getValue("t");if("s".equals(cellType)){nextIsString=true;}else{nextIsString=false;}//日期格式StringcellDateType=attributes.getValue("s");if("1".equals(cellDateType)){dateFlag=true;}else{dateFlag=false;}StringcellNumberType=attributes.getValue("s");if("2".equals(cellNumberType)){numberFlag=true;}else{numberFlag=false;} }//当元素为t时if("t".equals(name)){isTElement=true;}else{isTElement=false;}//置空lastContents="";}publicvoidendElement(Stringuri,StringlocalName,Stringname)throwsSAXException{//根据SST的索引值的到单元格的真正要存储的字符串//这时characters()方法可能会被调用多次if(nextIsString){try{intidx=Integer.parseInt(lastContents);lastContents=newXSSFRichTextString(sst.getEntryAt(idx)).toString();}catch(Exceptione){}}//t元素也包含字符串if(isTElement){Stringvalue=lastContents.trim();rowlist.add(curCol,value);curCol++;isTElement=false;//v=>单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引//将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符}elseif("v".equals(name)){Stringvalue=lastContents.trim();value=value.equals("")?"":value;//日期格式处理if(dateFlag){Datedate=HSSFDateUtil.getJavaDate(Double.valueOf(value));SimpleDateFormatdateFormat=newSimpleDateFormat("dd/MM/yyyy"); value=dateFormat.format(date);}//数字类型处理if(numberFlag){BigDecimalbd=newBigDecimal(value);value=bd.setScale(3,BigDecimal.ROUND_UP).toString();}rowlist.add(curCol,value);curCol++;}else{//如果标签名称为row，这说明已到行尾，调用optRows()方法if(name.equals("row")){rowReader.getRows(sheetIndex,curRow,rowlist);rowlist.clear();curRow++;curCol=0;}}}publicvoidcharacters(char[]ch,intstart,intlength)throwsSAXException{//得到单元格内容的值lastContents+=newString(ch,start,length);}}publicclassExcelReaderUtil{//excel2003扩展名publicstaticfinalStringEXCEL03_EXTENSION=".xls";//excel2007扩展名publicstaticfinalStringEXCEL07_EXTENSION=".xlsx";/***读取Excel文件，可能是03也可能是07版本*@paramexcel03*@paramexcel07*@paramfileName*@throwsException*/publicstaticvoidreadExcel(IRowReaderreader,StringfileName)throwsException{//处理excel2003文件 if(fileName.endsWith(EXCEL03_EXTENSION)){Excel2003Readerexcel03=newExcel2003Reader();excel03.setRowReader(reader);excel03.process(fileName);//处理excel2007文件}elseif(fileName.endsWith(EXCEL07_EXTENSION)){Excel2007Readerexcel07=newExcel2007Reader();excel07.setRowReader(reader);excel07.process(fileName);}else{thrownewException("文件格式错误，fileName的扩展名只能是xls或xlsx。");}}}publicinterfaceIRowReader{/**业务逻辑实现方法*@paramsheetIndex*@paramcurRow*@paramrowlist*/publicvoidgetRows(intsheetIndex,intcurRow,Listrowlist);}publicclassRowReaderimplementsIRowReader{/*业务逻辑实现方法*@seecom.eprosun.util.excel.IRowReader#getRows(int,int,java.util.List)*/publicvoidgetRows(intsheetIndex,intcurRow,Listrowlist){//TODOAuto-generatedmethodstubSystem.out.print(curRow+"");for(inti=0;ien=(Enumeration)zip.entries();while(en.hasMoreElements()){ZipEntryze=en.nextElement();if(!ze.getName().equals(entry)){zos.putNextEntry(newZipEntry(ze.getName()));InputStreamis=zip.getInputStream(ze);copyStream(is,zos);is.close();}}zos.putNextEntry(newZipEntry(entry));InputStreamis=newFileInputStream(tmpfile);copyStream(is,zos);is.close();zos.close();}privatestaticvoidcopyStream(InputStreamin,OutputStreamout)throwsIOException{byte[]chunk=newbyte[1024];intcount;while((count=in.read(chunk))>=0){out.write(chunk,0,count);}}/** *在写入器中写入电子表格**/publicstaticclassSpreadsheetWriter{privatefinalWriter_out;privateint_rownum;privatestaticStringLINE_SEPARATOR=System.getProperty("line.separator");publicSpreadsheetWriter(Writerout){_out=out;}publicvoidbeginSheet()throwsIOException{_out.write(""+"");_out.write(""+LINE_SEPARATOR);}publicvoidendSheet()throwsIOException{_out.write("");_out.write("");}/***插入新行**@paramrownum以0开始*/publicvoidinsertRow(intrownum)throwsIOException{_out.write(""+LINE_SEPARATOR);this._rownum=rownum;}/***插入行结束标志*/publicvoidendRow()throwsIOException{_out.write(""+LINE_SEPARATOR);}/***插入新列*@paramcolumnIndex *@paramvalue*@paramstyleIndex*@throwsIOException*/publicvoidcreateCell(intcolumnIndex,Stringvalue,intstyleIndex)throwsIOException{Stringref=newCellReference(_rownum,columnIndex).formatAsString();_out.write("");_out.write(""+XMLEncoder.encode(value)+"");_out.write("");}publicvoidcreateCell(intcolumnIndex,Stringvalue)throwsIOException{createCell(columnIndex,value,-1);}publicvoidcreateCell(intcolumnIndex,doublevalue,intstyleIndex)throwsIOException{Stringref=newCellReference(_rownum,columnIndex).formatAsString();_out.write("");_out.write(""+value+"");_out.write("");}publicvoidcreateCell(intcolumnIndex,doublevalue)throwsIOException{createCell(columnIndex,value,-1);}publicvoidcreateCell(intcolumnIndex,Calendarvalue,intstyleIndex)throwsIOException{createCell(columnIndex,DateUtil.getExcelDate(value,false),styleIndex);}} }publicclassExcel2007WriterImplextendsAbstractExcel2007Writer{/***@paramargs*@throwsException*/publicstaticvoidmain(String[]args)throwsException{//TODOAuto-generatedmethodstubSystem.out.println("............................");longstart=System.currentTimeMillis();//构建excel2007写入器AbstractExcel2007Writerexcel07Writer=newExcel2007WriterImpl();//调用处理方法excel07Writer.process("F://test07.xlsx");longend=System.currentTimeMillis();System.out.println("....................."+(end-start)/1000);}/**可根据需求重写此方法，对于单元格的小数或者日期格式，会出现精度问题或者日期格式转化问题，建议使用字符串插入方法*@seecom.excel.ver2.AbstractExcel2007Writer#generate()*/@Overridepublicvoidgenerate()throwsException{//电子表格开始beginSheet();for(intrownum=0;rownum<100;rownum++){//插入新行insertRow(rownum);//建立新单元格,索引值从0开始,表示第一列createCell(0,"中国<"+rownum+"!");createCell(1,34343.123456789);createCell(2,"23.67%");createCell(3,"12:12:23");createCell(4,"2010-10-1112:12:23");createCell(5,"true");createCell(6,"false");//结束行 endRow();}//电子表格结束endSheet();}}publicclassXMLEncoder{privatestaticfinalString[]xmlCode=newString[256];static{//SpecialcharactersxmlCode[''']="'";xmlCode['"']=""";//doublequotexmlCode['&']="&";//ampersandxmlCode['<']="<";//lowerthanxmlCode['>']=">";//greaterthan}/***

*Encodethegiventextintoxml.*