POI 读取excel大量数据方法

你的名字 2022-05-25 07:41 397阅读 0赞

今天在做excel文件上传时，发现数据量超过10万条后，系统会出现内存溢出。  
跟踪代码发现程序卡在如下代码这里：

Workbook workbook=WorkbookFactory.create(new File(path));

通过poi的api发现此方法属于能对excel读写，但是内存消耗大，速度较慢。  
在poi的官方文档中找到了读取大量数据的方法，但只能读数据，不能进行写操作。  
代码整理如下

import java.io.InputStream;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.Map;
    import java.util.Map.Entry;
    
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.model.SharedStringsTable;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    import org.junit.Test;
    import org.xml.sax.Attributes;
    import org.xml.sax.ContentHandler;
    import org.xml.sax.InputSource;
    import org.xml.sax.SAXException;
    import org.xml.sax.XMLReader;
    import org.xml.sax.helpers.DefaultHandler;
    import org.xml.sax.helpers.XMLReaderFactory;
    
    /** * 数据量比较大(8万条以上)的excel文件解析，将excel文件解析为 行列坐标-值的形式存入map中，此方式速度快，内存耗损小 但只能读取excle文件 * 提供处理单个sheet方法 processOneSheet(String filename) 以及处理多个sheet方法 processAllSheets(String filename) * 只需传入文件路径+文件名即可 调用处理方法结束后，只需 接收LargeExcelFileReadUtil.getRowContents()返回值即可获得解析后的数据 * */
    public class LargeExcelFileReadUtil { 
    
        private  LinkedHashMap<String, String>rowContents=new LinkedHashMap<String, String>(); 
        private  SheetHandler sheetHandler;
    
    public LinkedHashMap<String, String> getRowContents() {
            return rowContents;
        }
        public void setRowContents(LinkedHashMap<String, String> rowContents) {
            this.rowContents = rowContents;
        }
    
        public SheetHandler getSheetHandler() {
            return sheetHandler;
        }
        public void setSheetHandler(SheetHandler sheetHandler) {
            this.sheetHandler = sheetHandler;
        }
        //处理一个sheet
        public void processOneSheet(String filename) throws Exception {
            InputStream sheet2=null;
            OPCPackage pkg =null;
            try {
                    pkg = OPCPackage.open(filename);
                    XSSFReader r = new XSSFReader(pkg);
                    SharedStringsTable sst = r.getSharedStringsTable();
                    XMLReader parser = fetchSheetParser(sst);
                    sheet2 = r.getSheet("rId1");
                    InputSource sheetSource = new InputSource(sheet2);
                    parser.parse(sheetSource);
                    setRowContents(sheetHandler.getRowContents());
                    }catch (Exception e) {
                        e.printStackTrace();
                        throw e;
                        }finally{
                            if(pkg!=null){
                                pkg.close();
                                         }
                            if(sheet2!=null){
                                sheet2.close();
                                            }
                    }
        }
    //处理多个sheet
        public void processAllSheets(String filename) throws Exception {
            OPCPackage pkg =null;
            InputStream sheet=null;
            try{
                    pkg=OPCPackage.open(filename);
                    XSSFReader r = new XSSFReader( pkg );
                    SharedStringsTable sst = r.getSharedStringsTable();
                    XMLReader parser = fetchSheetParser(sst);
                    Iterator<InputStream> sheets = r.getSheetsData();
                    while(sheets.hasNext()) {
                        System.out.println("Processing new sheet:\n");
                        sheet = sheets.next();
                        InputSource sheetSource = new InputSource(sheet);
                        parser.parse(sheetSource);
                                            }
                }catch (Exception e) {
                        e.printStackTrace();
                        throw e;
                       }finally{
                           if(pkg!=null){
                               pkg.close();
                                     }
                           if(sheet!=null){
                               sheet.close();
                                        }
                                }
        }
    
        public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
            XMLReader parser =
                XMLReaderFactory.createXMLReader(
                        "com.sun.org.apache.xerces.internal.parsers.SAXParser"
                );
            setSheetHandler(new SheetHandler(sst));
            ContentHandler handler = (ContentHandler) sheetHandler;
            parser.setContentHandler(handler);
            return parser;
        }
    
        /** * See org.xml.sax.helpers.DefaultHandler javadocs */
        //测试
        @Test
        public  void test ()throws Exception {
           Long time=System.currentTimeMillis();
            LargeExcelFileReadUtil example = new LargeExcelFileReadUtil();
    
            example.processOneSheet("C:/Users/Desktop/2018041310024112.xlsx");
            Long endtime=System.currentTimeMillis();
            LinkedHashMap<String, String>  map=example.getRowContents();
            Iterator<Entry<String, String>> it= map.entrySet().iterator();
            int count=0;
            String prePos="";
            while (it.hasNext()){
                Map.Entry<String, String> entry=(Map.Entry<String, String>)it.next();
                String pos=entry.getKey();
                if(!pos.substring(1).equals(prePos)){
                    prePos=pos.substring(1);
                    count++;
                }
                System.out.println(pos+";"+entry.getValue());
            }
            System.out.println("解析数据"+count+"条;耗时"+(endtime-time)/1000+"秒");
        }
    }
    
    
    /** SheetHandler 类中处理从excle获取的数据，官方文档中 SheetHandler以内部类形式，为保证更新代码减少内部类class文件忘记打包，改为一般java类 */
    import java.util.LinkedHashMap;
    
    import org.apache.poi.xssf.model.SharedStringsTable;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    import org.xml.sax.Attributes;
    import org.xml.sax.SAXException;
    import org.xml.sax.helpers.DefaultHandler;
    
    public class SheetHandler extends DefaultHandler{ 
    
        private SharedStringsTable sst;
        private String lastContents;
        private boolean nextIsString;
        private String  cellPosition;
        private  LinkedHashMap<String, String>rowContents=new LinkedHashMap<String, String>(); 
    
        public LinkedHashMap<String, String> getRowContents() {
            return rowContents;
        }
    
        public void setRowContents(LinkedHashMap<String, String> rowContents) {
            this.rowContents = rowContents;
        }
    
        public SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }
    
        public void startElement(String uri, String localName, String name,
                Attributes attributes) throws SAXException {
            if(name.equals("c")) {
             // System.out.print(attributes.getValue("r") + " - ");
                cellPosition=attributes.getValue("r");
                String cellType = attributes.getValue("t");
                if(cellType != null && cellType.equals("s")) {
                    nextIsString = true;
                } else {
                    nextIsString = false;
                }
            }
            // 清楚缓存内容
            lastContents = "";
        }
    
        public void endElement(String uri, String localName, String name)
                throws SAXException {
            if(nextIsString) {
                int idx = Integer.parseInt(lastContents);
                lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                nextIsString = false;
            }
    
            if(name.equals("v")) {
    // System.out.println("lastContents:"+cellPosition+";"+lastContents);
                //数据读取结束后，将单元格坐标,内容存入map中
                if(!(cellPosition.length()==2)||(cellPosition.length()==2&&!"1".equals(cellPosition.substring(1)))){
       //不保存第一行数据
                    rowContents.put(cellPosition, lastContents);
                }
            }
        }
    
        public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }
    }

亲测可用，20多万条数据，4秒解析完，执行结果  
![这里写图片描述][70]  
poi jar包如下：  
![这里写图片描述][70 1]

[70]: /images/20220525/4eb6e7e6c7cb49b3bee8ebef573990cb.png
[70 1]: /images/20220525/74318d8b46cd409c9662042f7e3d8609.png