• NULL 博文链接:https://javaedge-yc.iteye.com/blog/1308714
  • poi读取的三种模式 模式 说明 读写性 SXSSF 内存中保留一定行数数据,超过行数,将索引最低的数据刷入硬盘 只写 eventmodel 基于事件驱动,SAX的方式解析excel,cup和内存消耗低 只读 ...

     

    前言

    poi的读取的三种模式

    模式 说明 读写性
    SXSSF 内存中保留一定行数数据,超过行数,将索引最低的数据刷入硬盘 只写
    eventmodel 基于事件驱动,SAX的方式解析excel,cup和内存消耗低 只读
    usermodel 传统方式,cpu和内存消耗大 可读可写

     

    依赖包(3.17版本)

    <!-- apache poi 操作Microsoft Document -->
    		<dependency>
    		    <groupId>org.apache.poi</groupId>
    		    <artifactId>poi</artifactId>
    		    <version>3.17</version>
    		</dependency>
    	  	<!-- Apache POI - Java API To Access Microsoft Format Files -->
    		<dependency>
    		    <groupId>org.apache.poi</groupId>
    		    <artifactId>poi-ooxml</artifactId>
    		    <version>3.17</version>
    		</dependency>
    		<!-- Apache POI - Java API To Access Microsoft Format Files -->
    	  	<dependency>
    		    <groupId>org.apache.poi</groupId>
    		    <artifactId>poi-ooxml-schemas</artifactId>
    		    <version>3.17</version>
    		</dependency>
    	  	<!-- poi eventmodel方式 依赖包 -->
    	  	<dependency>
    		    <groupId>xerces</groupId>
    		    <artifactId>xercesImpl</artifactId>
    		    <version>2.11.0</version>
    		</dependency>

     

    一、SXSSF (Since POI 3.8 beta3)

     

    说明

    3.8-beta3以来,POI提供了一个低内存占用的SXSSF API,它构建在XSSF之上。

    SXSSF是一个兼容于api的XSSF的流扩展,当需要生成非常大的电子表格时,它将被使用,而堆空间是有限的。SXSSF通过限制对滑动窗口中的行的访问来实现它的低内存占用,而XSSF允许访问文档中的所有行。不再出现在窗口中的较老的行变得不可访问,因为它们被写到磁盘上

    在自动刷新模式中,可以指定存取窗口的大小,以便在内存中持有一定数量的行。当达到这个值时,额外一行的创建会导致从存取窗口删除最低索引的行,并将其写到磁盘上。或者,窗口大小可以被设置为动态增长;根据需要,可以通过显式调用flushRows(int keepRows)定期对其进行修剪。

    由于实现的流特性,与XSSF相比有以下限制:

    • 只有有限数量的行可以在某个时间点访问。
    • 不支持Sheet.clone()。
    • 不支持公式评估

    更多细节

    下面的表格对POI的电子表格API的比较特性进行了比较:

    示例-写数据到excel

    下面的例子写了一张有百行窗口的表格。当行数达到101时,rownum=0的行会被刷新到磁盘,并从内存中删除,当rownum达到102时,          rownum=1的行被刷新

    @Test
    	public void test() throws Exception{
    		Long start = System.currentTimeMillis();
    		//内存最大存放100行数据 超过100自动刷新到硬盘中
    		SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
            Sheet sh = wb.createSheet();
            for(int rownum = 0; rownum < 500000; rownum++){
                Row row = sh.createRow(rownum);//一行
                for(int cellnum = 0; cellnum < 10; cellnum++){
                    Cell cell = row.createCell(cellnum); //一行中一个方格
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
    
            }  
    		
            FileOutputStream out = new FileOutputStream("f:/temp/sxssf.xlsx");
            wb.write(out);
            out.close();
    
            // dispose of temporary files backing this workbook on disk
            //处理在磁盘上支持本工作簿的临时文件
            wb.dispose();
            wb.close();
            Long end = System.currentTimeMillis();
            System.out.println(end - start + "ms"); //50万条数据写入大概在16秒
    		
    	}

     

    下一个例子关闭了自动刷新(windows size=-1),代码手动控制将数据的部分写入磁盘

     

    @Test
    	public void test3() throws IOException{
    		Long start = System.currentTimeMillis();
    		SXSSFWorkbook wb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows in memory
            Sheet sh = wb.createSheet();
            for(int rownum = 0; rownum < 1000; rownum++){
                Row row = sh.createRow(rownum);
                for(int cellnum = 0; cellnum < 10; cellnum++){
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
    
               // manually control how rows are flushed to disk 
               if(rownum % 100 == 0) {
                    ((SXSSFSheet)sh).flushRows(100); // retain 100 last rows and flush all others
    
                    // ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),
                    // this method flushes all rows
               }
    
            }
    
            FileOutputStream out = new FileOutputStream("f:/temp/sxssf.xlsx");
            wb.write(out);
            out.close();
    
            // dispose of temporary files backing this workbook on disk
            wb.dispose();
            Long end = System.currentTimeMillis();
            System.out.println(end - start + "ms"); //100条数据 650ms
    	}

    小结

    其核心是减少存储在内存当中的数据,达到一定行数就存储到硬盘的临时文件中

     

    二、XSSF and SAX (Event API)

    说明

    如果内存占用是一个问题,那么对于XSSF来说,您可以获得底层XML数据,并自己处理它。

    要使用这个API,您可以构建一个org.apache.poi.xssf.eventmodel.xssfreader的实例。这将在共享字符串表和样式上提供一个不错的接口。它提供了从文件的其余部分获取原始xml数据的方法,然后您将把这些数据传递给SAX。

    背景

     

    Excel2003与Excel2007

    两个版本的最大行数和列数不同,2003版最大行数是65536行,最大列数是256列,2007版及以后的版本最大行数是1048576行,最大列数是16384列。

    excel2003是以二进制的方式存储,这种格式不易被其他软件读取使用;而excel2007采用了基于XML的ooxml开放文档标准,ooxml使用XML和ZIP技术结合进行文件存储,XML是一个基于文本的格式,而且ZIP容器支持内容的压缩,所以其一大优势是可以大大减小文件的尺寸。

    把xlsx后缀改为zip,打开文件发现目录

    打开xl

    sharedStrings.xml  共享字符串

    styles.xml  excel的样式数据

    workbooks.xml excel的sheet

    示例

    package com.java.poi;
    
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.xml.parsers.ParserConfigurationException;
    
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.openxml4j.opc.PackageAccess;
    import org.apache.poi.util.SAXHelper;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.model.SharedStringsTable;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    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;
    
    import com.mysql.jdbc.util.LRUCache;
    
    public class ExampleEventUserModel {
    	public void processFirstSheet(String filename) throws Exception {
    		try(OPCPackage pkg = OPCPackage.open(filename,PackageAccess.READ);){
    			XSSFReader r = new XSSFReader( pkg );
    			SharedStringsTable sst = r.getSharedStringsTable();
    			
    			XMLReader parser = fetchSheetParser(sst);
    			//process the first sheet
    			try(InputStream sheet = r.getSheetsData().next()){
    				InputSource sheetSource = new InputSource(sheet);
    				parser.parse(sheetSource);
    			}
    		}
    
    	}
    	
    	
    	public void processAllSheets(String filename) throws Exception {
    		try (OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ)) {
                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");
                    try (InputStream sheet = sheets.next()) {
    					InputSource sheetSource = new InputSource(sheet);
    					parser.parse(sheetSource);
    				}
                    System.out.println("");
                }
            }
    	}
    	
    	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException, ParserConfigurationException {
    		XMLReader parser =SAXHelper.newXMLReader();
    		ContentHandler handler = new SheetHandler(sst);
    		parser.setContentHandler(handler);
    		return parser;
    	}
    	
    	
    	
    	/** 
    	 * See org.xml.sax.helpers.DefaultHandler javadocs 重写 startElement characters endElements方法 
    	 */
    	private static class SheetHandler extends DefaultHandler {
    		private SharedStringsTable sst;
    		private String lastContents;
    		private boolean nextIsString; //是否为string格式标识
    	    private final LruCache<Integer,String> lruCache = new LruCache<>(60);
    		/*private int sheetIndex = -1;
    		private int curRow = 0;
    		private int curCol = 0;
    		private List<String> rowlist = new ArrayList<String>(); */  
    		
    	    /**
    	     * 缓存
    	     * @author Administrator
    	     *
    	     * @param <A>
    	     * @param <B>
    	     */
    		private static class LruCache<A,B> extends LinkedHashMap<A, B> {
                private final int maxEntries;
    
                public LruCache(final int maxEntries) {
                    super(maxEntries + 1, 1.0f, true);
                    this.maxEntries = maxEntries;
                }
    
                @Override
                protected boolean removeEldestEntry(final Map.Entry<A, B> eldest) {
                    return super.size() > maxEntries;
                }
            }
    		
    		private SheetHandler(SharedStringsTable sst) {
    			this.sst = sst;
    		}
    		
    		/**  
    	     * 该方法自动被调用,每读一行调用一次,在方法中写自己的业务逻辑即可 
    	     * @param sheetIndex 工作簿序号 
    	     * @param curRow 处理到第几行 
    	     * @param rowList 当前数据行的数据集合 
    	     */  
    	   /* public void optRow(int sheetIndex, int curRow, List<String> rowList) {   
    	        String temp = "";   
    	        for(String str : rowList) {   
    	            temp += str + "_";   
    	        } 
    	        this.rowlist.clear();
    	        this.curRow++;
    	        this.curCol=0;
    	        System.out.println(temp);   
    	    } */
    		
    		@Override
    		public void startElement(String uri, String localName, String name,
    				Attributes attributes) throws SAXException {
    			// c => cell 代表单元格
    			if(name.equals("c")) {
    				// Print the cell reference
    				//获取单元格的位置,如A1,B1
    				System.out.print(attributes.getValue("r") + " - "); 
    				// Figure out if the value is an index in the SST 如果下一个元素是 SST 的索引,则将nextIsString标记为true
    				//单元格类型
    				String cellType = attributes.getValue("t"); 
    				//cellType值 s:字符串 b:布尔 e:错误处理
    				if(cellType != null && cellType.equals("s")) { 
    					//标识为true 交给后续endElement处理
    					nextIsString = true;
    				} else {
    					nextIsString = false;
    				}
    			}
    			// Clear contents cache
    			lastContents = "";
    		}
    		
    		/**
    		 * 得到单元格对应的索引值或是内容值
    		 * 如果单元格类型是字符串、INLINESTR、数字、日期,lastIndex则是索引值
    		 * 如果单元格类型是布尔值、错误、公式,lastIndex则是内容值
    		 */
    		@Override
    		public void characters(char[] ch, int start, int length)
    				throws SAXException {
    			lastContents += new String(ch, start, length);
    		}
    		
    		@Override
    		public void endElement(String uri, String localName, String name)
    				throws SAXException {
    			// Process the last contents as required.
    			// Do now, as characters() may be called more than once
    			if(nextIsString) {
    				int idx = Integer.parseInt(lastContents);
    				lastContents = lruCache.get(idx);
    				//如果内容为空 或者Cache中存在相同key 不保存到Cache中
    				if(lastContents == null &&!lruCache.containsKey(idx)){
    					lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
    					lruCache.put(idx, lastContents);
    				}
    				nextIsString = false;
    			}
    
    			// v => contents of a cell
    			// Output after we've seen the string contents
    			if(name.equals("v")) {
    				System.out.println(lastContents);
    				//rowlist.add(curCol++,lastContents);
    			}else{
    				//如果标签名称为 row , 已到行尾
    				if(name.equals("row")){
    					//optRow(sheetIndex, curRow, rowlist);
    					System.out.println(lruCache);
    					lruCache.clear();
    				}
    			}
    		}
    
    		
    	}
    	
    	public static void main(String[] args) throws Exception {
    		new ExampleEventUserModel().processFirstSheet("F:/temp/template.xlsx");
    	}
    	
    }

    SheetHandler类说明:程序依次调用重写的startElement,characters,endElement方法

     

    小结

    excel2007后采用了基于XML的ooxml开放文档标准,通过操作原始xml数据的方法获得数据。

     

    三、User API (HSSF and XSSF)

     

     

    New Workbook 

    Workbook wb = new HSSFWorkbook();
        ...
        try  (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
    
        Workbook wb = new XSSFWorkbook();
        ...
        try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
            wb.write(fileOut);
        }

     

    Files vs InputStreams

    在打开一本工作簿时,不管是一个.xls HSSFWorkbook,还是一个.xlsx XSSFWorkbook,工作簿都可以从文件或InputStream中加载。使用File对象可以降低内存消耗,而InputStream则需要更多的内存,因为它必须缓冲整个文件。

    如果使用WorkbookFactory,它很容易使用其中一个或另一个:

    // Use a file
      Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));
    
      // Use an InputStream, needs more memory
      Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));

     

    如果直接使用HSSFWorkbook或XSSFWorkbook,您通常应该通过 NPOIFSFileSystemOPCPackage的使用来完全控制生命周期(包括在完成时关闭文件):

     

    // HSSFWorkbook, File
      NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("file.xls"));
      HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
      ....
      fs.close();
    
      // HSSFWorkbook, InputStream, needs more memory
      NPOIFSFileSystem fs = new NPOIFSFileSystem(myInputStream);
      HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
    
      // XSSFWorkbook, File
      OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
      XSSFWorkbook wb = new XSSFWorkbook(pkg);
      ....
      pkg.close();
    
      // XSSFWorkbook, InputStream, needs more memory
      OPCPackage pkg = OPCPackage.open(myInputStream);
      XSSFWorkbook wb = new XSSFWorkbook(pkg);
      ....
      pkg.close();

     行列操作

     Workbook wb = WorkbookFactory.create(inputStream);
     Sheet sheet = wb.getSheetAt(0);
     //行数
     int lastRowNum = sheet.getPhysicalNumberOfRows();
     //读取body
     for(int rowNum = 1;rowNum <lastRowNum;rowNum++){
         //获得当前行
         Row row = sheet.getRow(rowNum); //index序列号从0开始,跳过首行,取1
         //当前列数
         int lastCellNum = row.getPhysicalNumberOfCells();
         for(int cellNum=0;cellNum<lastCellNum;cellNum++){
             //当前行的某列,确定小方格
             Cell cell = row.getCell(cellNum);
             String str = cell.getStringCellValue();//获取字符值,当然提供不同的接口获得不同类型值
             // TODO
         }
     wb.close();
     }

     

    小结:

    数据量比较小使用NPOIFSFileSystem或OPCPackage来操作excel,并尽可能使用文件对象参数

     

    参考 apache poi官网文档 (随版本更新,api可能有变动)

     

     

     

     

     

     

     

     

     

    展开全文
  • 使用Poi读取大数据量excel的方法 支持2003和2007的版本
  • maven依赖使用Poi4.1.1 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.1</version> </depend...

    maven依赖使用Poi4.1.1

    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.1</version>
        </dependency>

    主要方法

    package com.poi.study;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    
    import org.apache.poi.ooxml.util.SAXHelper;
    import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.ss.usermodel.BuiltinFormats;
    import org.apache.poi.ss.usermodel.DataFormatter;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.model.SharedStringsTable;
    import org.apache.poi.xssf.model.StylesTable;
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    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 javax.xml.parsers.ParserConfigurationException;
    public class ExcelEventUserModel {
        
        
        public static void processAllSheets(String filename, ExcelReadHandler excelReadHandler) {
            Iterator<InputStream> sheets = null;
            XMLReader parser = null;
            try {
                OPCPackage pkg = OPCPackage.open(filename);
                XSSFReader reader = new XSSFReader( pkg );
                SharedStringsTable sst = reader.getSharedStringsTable();
                StylesTable styleTable = reader.getStylesTable();
                parser = fetchSheetParser(sst, styleTable, excelReadHandler);
                sheets = reader.getSheetsData();
            } catch (IOException | OpenXML4JException | SAXException | ParserConfigurationException e) {
                throw new ExcelReadException("读取Excel报错");
            }
            
            while(sheets != null && sheets.hasNext()) {
                try(InputStream sheet = sheets.next();){
                    InputSource sheetSource = new InputSource(sheet);
                    parser.parse(sheetSource);
                } catch (IOException | SAXException e) {
                    throw new ExcelReadException("读取Excel中sheet表报错");
                }
            }
        }
        
        public static XMLReader fetchSheetParser(SharedStringsTable sst, StylesTable styleTable, ExcelReadHandler excelReadHandler) throws SAXException, ParserConfigurationException {
            XMLReader parser = SAXHelper.newXMLReader();
            ContentHandler handler = new SheetHandler(sst, styleTable, excelReadHandler);
            parser.setContentHandler(handler);
            return parser;
        }
        /**
         * See org.xml.sax.helpers.DefaultHandler javadocs
         */
        private static class SheetHandler extends DefaultHandler {
            
            /**
             * 单元格中的数据可能的数据类型
             */
            enum CellDataType {
                BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
            }
            
            private final DataFormatter formatter = new DataFormatter();
            
            private SharedStringsTable sst;                //excel中,若单元格内的内容是字符串,那么这些字符串都存在这个变量中
            private StylesTable styleTable;                //用于获取时间类型单元格的时间格式
            
            private String currentContents;                //当前单元格的内容
            private String ref;                            //当前单元格的位置
            private CellDataType cellDataType;            //当前单元格的类型
            private short formatIndex;                    //当前单元格为时间时的格式索引
            private String formatString;                //当前单元格为时间时的格式
            private Map<String, String> result = new HashMap<>();
            private ExcelReadHandler excelReadHandler;    //读取一行的回调
            
            private SheetHandler(SharedStringsTable sst, StylesTable styleTable, ExcelReadHandler excelReadHandler) {
                this.sst = sst;
                this.styleTable = styleTable;
                this.excelReadHandler = excelReadHandler;
            }
            
            /**
             * 这个方法在遇到一个xml文件的元素开始之前被触发,
             * 在这里,我们可以筛选出单元格,并提前取出单元格内存放的内容的类型
             */
            @Override
            public void startElement(String uri, String localName, String name,
                                     Attributes attributes) throws SAXException {
                // name为c表示遇到了单元格
                if(name.equals("c")) {
                    ref = attributes.getValue("r");
                    setNextDataType(attributes);
                }
                // 即将获取单元格的内容,所以置空该变量
                currentContents = "";
            }
            
            
            /**
             * 处理数据类型
             *
             * @param attributes 单元格参数
             */
            private void setNextDataType(Attributes attributes) {
                cellDataType = CellDataType.NUMBER; //cellType为空,则表示该单元格类型为数字
                formatIndex = -1;
                formatString = null;
                String cellType = attributes.getValue("t"); //单元格类型
                String cellStyleStr = attributes.getValue("s");
    
                if ("b".equals(cellType)) { //处理布尔值
                    cellDataType = CellDataType.BOOL;
                } else if ("e".equals(cellType)) {  //处理错误
                    cellDataType = CellDataType.ERROR;
                } else if ("inlineStr".equals(cellType)) {
                    cellDataType = CellDataType.INLINESTR;
                } else if ("s".equals(cellType)) { //处理字符串
                    cellDataType = CellDataType.SSTINDEX;
                } else if ("str".equals(cellType)) {
                    cellDataType = CellDataType.FORMULA;
                }
    
                if (cellStyleStr != null) { //处理日期
                    int styleIndex = Integer.parseInt(cellStyleStr);
                    XSSFCellStyle style = styleTable.getStyleAt(styleIndex);
                    formatIndex = style.getDataFormat();
                    formatString = style.getDataFormatString();
    
                    if (formatString == null) {
                        cellDataType = CellDataType.NULL;
                        formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
                    }
    
                    if (formatString.contains("m/d/yy")) {
                        cellDataType = CellDataType.DATE;
                        formatString = "yyyy-MM-dd hh:mm:ss";
                    }
                }
            }
            
            private String getDataValue(String value) {
                String thisStr;
                switch (cellDataType) {
                    // 这几个的顺序不能随便交换,交换了很可能会导致数据错误
                    case BOOL: //布尔值
                        char first = value.charAt(0);
                        thisStr = first == '0' ? "FALSE" : "TRUE";
                        break;
                    case ERROR: //错误
                        thisStr = "\"ERROR:"   value   '"';
                        break;
                    case FORMULA: //公式
                        thisStr = '"'   value   '"';
                        break;
                    case INLINESTR:
                        XSSFRichTextString rtsi = new XSSFRichTextString(value);
                        thisStr = rtsi.toString();
                        break;
                    case SSTINDEX: //字符串
                        int idx = Integer.parseInt(value);
                        thisStr = sst.getItemAt(idx).getString();
                        break;
                    case NUMBER: //数字
                        if (formatString != null) {
                            thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                        } else {
                            thisStr = value;
                        }
                        thisStr = thisStr.replace("_", "").trim();
                        break;
                    case DATE: //日期
                        thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                        // 对日期字符串作特殊处理,去掉T
                        thisStr = thisStr.replace("T", " ");
                        break;
                    default:
                        thisStr = " ";
                        break;
                }
                return thisStr;
            }
            
            /**
             * 第二个被触发,currentContents存储当前单元格的内容
             */
            @Override
            public void characters(char[] ch, int start, int length) {
                currentContents  = new String(ch, start, length);
            }
            
            /**
             * 第三个被触发,读取完单元格的内容后被执行
             */
            @Override
            public void endElement(String uri, String localName, String name)
                    throws SAXException {
                
                if(name.equals("v")) {
                    result.put(ref, getDataValue(currentContents));
                }
                
                if(name.equals("row")) {
                    excelReadHandler.processOneRow(result);
                    result.clear();
                }
            }
            
            
        }
        public static void main(String[] args) throws Exception {
            ExcelEventUserModel.processAllSheets("C:\\Users\\jinliujie\\Desktop\\createCell.xlsx", (result) -> {
                System.out.println(result);
            });
        }
    }

    ExcelReadException自定义异常

    package com.poi.study;
    
    public class ExcelReadException extends RuntimeException {
    
        /**
         * 
         */
        private static final long serialVersionUID = 1L;
    
        public ExcelReadException() {
            super();
        }
    
        public ExcelReadException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
            super(message, cause, enableSuppression, writableStackTrace);
        }
    
        public ExcelReadException(String message, Throwable cause) {
            super(message, cause);
        }
    
        public ExcelReadException(String message) {
            super(message);
        }
    
        public ExcelReadException(Throwable cause) {
            super(cause);
        }
        
        
    
    }
    

    ExcelReadHandler 读取一行的回调

    package com.poi.study;
    
    import java.util.Map;
    
    public interface ExcelReadHandler {
        
        void processOneRow(Map<String, String> row);
    
    }
    

    本文由博客一文多发平台 OpenWrite 发布!

    展开全文
  • java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁...

    java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁的的工具类需要2个参数改成只需要一个参数就可以完成调用,当然你可以根据你自己的情况使用。
    下面是一个工具类,复制到自己的项目中直接调用即可:

    1、工具类

    package com.xxx.xxx.xxx;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
    import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
    import org.apache.poi.xssf.model.StylesTable;
    import org.apache.poi.xssf.usermodel.XSSFComment;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.stereotype.Component;
    import org.xml.sax.InputSource;
    import org.xml.sax.SAXException;
    import org.xml.sax.XMLReader;
    import org.xml.sax.helpers.XMLReaderFactory;
    /**
     * 解析大数据量Excel工具类
     * @author RobinTime
     *
     */
    @Component
    public class ExcelParser {
        private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);
        /**
         * 表格默认处理器
         */
        private ISheetContentHandler contentHandler = new DefaultSheetHandler();
        /**
         * 读取数据
         */
        private List<String[]> datas = new ArrayList<String[]>();
    
        /**
         * 转换表格,默认为转换第一个表格
         * @param stream
         * @return
         * @throws InvalidFormatException
         * @throws IOException
         * @throws ParseException
         */
        public ExcelParser parse(InputStream stream)
                throws InvalidFormatException, IOException, ParseException {
            return parse(stream, 1);
        }
    
    
        /**
         * 
         * @param stream
         * @param sheetId:为要遍历的sheet索引,从1开始
         * @return
         * @throws InvalidFormatException
         * @throws IOException
         * @throws ParseException
         */
        public synchronized ExcelParser parse(InputStream stream, int sheetId)
                throws InvalidFormatException, IOException, ParseException {
            // 每次转换前都清空数据
            datas.clear();
            // 打开表格文件输入流
            OPCPackage pkg = OPCPackage.open(stream);
            try {
                // 创建表阅读器
                XSSFReader reader;
                try {
                    reader = new XSSFReader(pkg);
                } catch (OpenXML4JException e) {
                    logger.error("读取表格出错");
                    throw new ParseException(e.fillInStackTrace());
                }
    
                // 转换指定单元表
                InputStream shellStream = reader.getSheet("rId" + sheetId);
                try {
                    InputSource sheetSource = new InputSource(shellStream);
                    StylesTable styles = reader.getStylesTable();
                    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
                    getContentHandler().init(datas);// 设置读取出的数据
                    // 获取转换器
                    XMLReader parser = getSheetParser(styles, strings);
                    parser.parse(sheetSource);
                } catch (SAXException e) {
                    logger.error("读取表格出错");
                    throw new ParseException(e.fillInStackTrace());
                } finally {
                    shellStream.close();
                }
            } finally {
                pkg.close();
    
            }
            return this;
    
        }
    
        /**
         * 获取表格读取数据,获取数据前,需要先转换数据<br>
         * 此方法不会获取第一行数据
         * 
         * @return 表格读取数据
         */
        public List<String[]> getDatas() {
            return getDatas(true);
    
        }
    
        /**
         * 获取表格读取数据,获取数据前,需要先转换数据
         * 
         * @param dropFirstRow
         *            删除第一行表头记录
         * @return 表格读取数据
         */
        public List<String[]> getDatas(boolean dropFirstRow) {
            if (dropFirstRow && datas.size() > 0) {
                datas.remove(0);// 删除表头
            }
            return datas;
    
        }
    
        /**
         * 获取读取表格的转换器
         * 
         * @return 读取表格的转换器
         * @throws SAXException
         *             SAX错误
         */
        protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException {
            XMLReader parser = XMLReaderFactory.createXMLReader();
            parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));
            return parser;
        }
    
        public ISheetContentHandler getContentHandler() {
            return contentHandler;
        }
    
        public void setContentHandler(ISheetContentHandler contentHandler) {
            this.contentHandler = contentHandler;
        }
    
        /**
         * 表格转换错误
         */
        public class ParseException extends Exception {
            private static final long serialVersionUID = -2451526411018517607L;
    
            public ParseException(Throwable t) {
                super("表格转换错误", t);
            }
    
        }
    
        public interface ISheetContentHandler extends SheetContentsHandler {
    
            /**
             * 设置转换后的数据集,用于存放转换结果
             * 
             * @param datas
             *            转换结果
             */
            void init(List<String[]> datas);
        }
    
        /**
         * 默认表格解析handder
         */
        class DefaultSheetHandler implements ISheetContentHandler {
            /**
             * 读取数据
             */
            private List<String[]> datas;
            private int columsLength;
            // 读取行信息
            private String[] readRow;
            private ArrayList<String> fristRow = new ArrayList<String>();
    
            @Override
            public void init(List<String[]> datas) {
                this.datas = datas;
    //          this.columsLength = columsLength;
            }
    
            @Override
            public void startRow(int rowNum) {
                if (rowNum != 0) {
                    readRow = new String[columsLength];
                }
            }
    
            @Override
            public void endRow(int rowNum) {
            //将Excel第一行表头的列数当做数组的长度,要保证后续的行的列数不能超过这个长度,这是个约定。
                if (rowNum == 0) {
                    columsLength = fristRow.size();
                    readRow = fristRow.toArray(new String[fristRow.size()]);
                }else {
                    readRow = fristRow.toArray(new String[columsLength]);
                }
                datas.add(readRow.clone());
                readRow = null;
                fristRow.clear();
            }
    
            @Override
            public void cell(String cellReference, String formattedValue, XSSFComment comment) {
                int index = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置
                try {
                    fristRow.set(index, formattedValue);
                } catch (IndexOutOfBoundsException e) {
                    int size = fristRow.size();
                    for (int i = index - size+1;i>0;i--){
                        fristRow.add(null);
                    }
                    fristRow.set(index,formattedValue);
                }
            }
    
            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {
            }
    
            /**
             * 转换表格引用为列编号
             * 
             * @param cellReference
             *            列引用
             * @return 表格列位置,从0开始算
             */
            public int getCellIndex(String cellReference) {
                String ref = cellReference.replaceAll("\\d+", "");
                int num = 0;
                int result = 0;
                for (int i = 0; i < ref.length(); i++) {
                    char ch = cellReference.charAt(ref.length() - i - 1);
                    num = (int) (ch - 'A' + 1);
                    num *= Math.pow(26, i);
                    result += num;
                }
                return result - 1;
            }
        }
    }

    2、调用

    File tempFile = new File(this.getClass().getClassLoader().getResource("").getPath() + "tempFile\\" + (new Date()).getTime() + ".xlsx");
    //传入一个路径产生流再将流传入工具类,返回解析对象,Excel的所有数据就被解析到List<String[]> 里面,遍历list任由你处置。
    FileInputStream inputStream = new FileInputStream(tempFile);
    ExcelParser parse = excelParser.parse(inputStream);
    List<String[]> datas = parse.getDatas();
    展开全文
  • 前言 最近生产环境有个老项目一直内存报警,不时的还出现内存泄漏,导致需要重启服务器,已经严重影响正常服务了。 分析 1.dump内存文件 liunx使用如下命令: ? 1 ./jmap -dump:format=... 2.......

    前言

    最近生产环境有个老项目一直内存报警,不时的还出现内存泄漏,导致需要重启服务器,已经严重影响正常服务了。

    分析

    1.dump内存文件

    liunx使用如下命令:

    ?

    1

    ./jmap -dump:format=b,file=heap.hprof pid

    2.使用Eclipse Memory Analysis进行分析

     

    异常如下:

    ?

    1

    2

    3

    4

    5

    6

    7

    at org.apache.poi.xssf.usermodel.XSSFRow.<init>(Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTRow;Lorg/apache/poi/xssf/usermodel/XSSFSheet;)V (XSSFRow.java:68)

    at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorksheet;)V (XSSFSheet.java:157)

    at org.apache.poi.xssf.usermodel.XSSFSheet.read(Ljava/io/InputStream;)V (XSSFSheet.java:132)

    at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead()V (XSSFSheet.java:119)

    at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead()V (XSSFWorkbook.java:222)

    at org.apache.poi.POIXMLDocument.load(Lorg/apache/poi/POIXMLFactory;)V (POIXMLDocument.java:200)

    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(Ljava/io/InputStream;)V (XSSFWorkbook.java:179)

    POI在加载Excel引发了内存泄漏,中间创建了大量的对象,占用了大量的内存

    3.查看上传的Excel大小

    经查看发现很多Excel大小在9M的文件

    4.查看代码POI读取Excel的方式

    发现使用的是用户模式,这样会占用大量的内存;POI提供了2中读取Excel的模式,分别是:

    • 用户模式:也就是poi下的usermodel有关包,它对用户友好,有统一的接口在ss包下,但是它是把整个文件读取到内存中的,
      对于大量数据很容易内存溢出,所以只能用来处理相对较小量的数据;
    • 事件模式:在poi下的eventusermodel包下,相对来说实现比较复杂,但是它处理速度快,占用内存少,可以用来处理海量的Excel数据。

    经上面分析基本可以确定问题出在使用POI的用户模式去读取Excel大文件,导致内存泄漏。

    本地重现

    下面模拟一个600kb大小的Excel(test.xlsx),分别用两种模式读取,然后观察内存波动;

    1.需要引入的库maven:

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    <dependencies>

     <dependency>

      <groupId>org.apache.poi</groupId>

      <artifactId>poi-ooxml</artifactId>

      <version>3.6</version>

     </dependency>

     <dependency>

      <groupId>com.syncthemall</groupId>

      <artifactId>boilerpipe</artifactId>

      <version>1.2.1</version>

     </dependency>

    </dependencies>

    2.用户模式代码如下:

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    27

    28

    29

    30

    31

    32

    33

    34

    35

    36

    37

    38

    39

    import java.io.File;

    import java.io.FileInputStream;

    import java.io.IOException;

    import java.io.InputStream;

      

    import org.apache.poi.ss.usermodel.Cell;

    import org.apache.poi.ss.usermodel.Row;

    import org.apache.poi.ss.usermodel.Sheet;

    import org.apache.poi.ss.usermodel.Workbook;

    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

      

    public class UserModel {

      

     public static void main(String[] args) throws InterruptedException {

      try {

       Thread.sleep(5000);

       System.out.println("start read");

       for (int i = 0; i < 100; i++) {

        try {

         Workbook wb = null;

         File file = new File("D:/test.xlsx");

         InputStream fis = new FileInputStream(file);

         wb = new XSSFWorkbook(fis);

         Sheet sheet = wb.getSheetAt(0);

         for (Row row : sheet) {

          for (Cell cell : row) {

           System.out.println("row:" + row.getRowNum() + ",cell:" + cell.toString());

          }

         }

        } catch (IOException e) {

         e.printStackTrace();

        }

       }

       Thread.sleep(1000);

      } catch (Exception e) {

       e.printStackTrace();

      }

     }

    }

    3.事件模式代码如下:

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    27

    28

    29

    30

    31

    32

    33

    34

    35

    36

    37

    38

    39

    40

    41

    42

    43

    44

    45

    46

    47

    48

    49

    50

    51

    52

    53

    54

    55

    56

    57

    58

    59

    60

    61

    62

    63

    64

    65

    66

    67

    68

    69

    70

    71

    72

    73

    74

    75

    76

    77

    78

    79

    80

    81

    82

    83

    84

    import java.io.InputStream;

      

    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.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;

      

    public class EventModel {

      

     public void processOneSheet(String filename) throws Exception {

      OPCPackage pkg = OPCPackage.open(filename);

      XSSFReader r = new XSSFReader(pkg);

      SharedStringsTable sst = r.getSharedStringsTable();

      

      XMLReader parser = fetchSheetParser(sst);

      InputStream sheet2 = r.getSheet("rId1");

      InputSource sheetSource = new InputSource(sheet2);

      parser.parse(sheetSource);

      sheet2.close();

     }

      

     public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {

      XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");

      ContentHandler handler = new SheetHandler(sst);

      parser.setContentHandler(handler);

      return parser;

     }

      

     private static class SheetHandler extends DefaultHandler {

      private SharedStringsTable sst;

      private String lastContents;

      private boolean nextIsString;

      

      private 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") + " - ");

        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);

       }

      }

      

      public void characters(char[] ch, int start, int length) throws SAXException {

       lastContents += new String(ch, start, length);

      }

     }

      

     public static void main(String[] args) throws Exception {

      Thread.sleep(5000);

      System.out.println("start read");

      for (int i = 0; i < 100; i++) {

       EventModel example = new EventModel();

       example.processOneSheet("D:/test.xlsx");

       Thread.sleep(1000);

      }

     }

    }

    具体代码来源:http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api

    4.设置VM arguments:-Xms100m -Xmx100m

    UserModel运行结果直接报OutOfMemoryError,如下所示:

    ?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded

     at java.lang.String.substring(String.java:1877)

     at org.apache.poi.ss.util.CellReference.separateRefParts(CellReference.java:353)

     at org.apache.poi.ss.util.CellReference.<init>(CellReference.java:87)

     at org.apache.poi.xssf.usermodel.XSSFCell.<init>(XSSFCell.java:105)

     at org.apache.poi.xssf.usermodel.XSSFRow.<init>(XSSFRow.java:68)

     at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(XSSFSheet.java:157)

     at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:132)

     at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:119)

     at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:222)

     at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:200)

     at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:179)

     at zh.excelTest.UserModel.main(UserModel.java:23)

     

    UserModel模式下读取600kbExcel文件直接内存溢出,看了600kbExcel文件映射到内存中还是占用了不少内存;EventModel模式下可以流畅的运行。

    5.设置VM arguments:-Xms200m -Xmx200m

    UserModel模式和EventModel模式都可以正常运行,但是很明显UserModel模式回收内存更加频繁,而且在cpu的占用上更高。

    总结

    通过简单的分析以及本地运行两种模式进行比较,可以看到UserModel模式下使用的简单的代码实现了读取,但是在读取大文件时CPU和内存都不理想;

    而EventModel模式虽然代码写起来比较繁琐,但是在读取大文件时CPU和内存更加占优。

    展开全文
  • 1. Excel2003与Excel2007两个版本的最大...excel2003是以二进制的方式存储,这种格式不易被其他软件读取使用;而excel2007采用了基于XML的ooxml开放文档标准,ooxml使用XML和ZIP技术结合进行文件存储,XML是一个基...

    1. Excel2003与Excel2007

    两个版本的最大行数和列数不同,2003版最大行数是65536行,最大列数是256列,2007版及以后的版本最大行数是1048576行,最大列数是16384列。

    excel2003是以二进制的方式存储,这种格式不易被其他软件读取使用;而excel2007采用了基于XML的ooxml开放文档标准,ooxml使用XML和ZIP技术结合进行文件存储,XML是一个基于文本的格式,而且ZIP容器支持内容的压缩,所以其一大优势是可以大大减小文件的尺寸。

    2. 大批量数据读写

    2.1 大批量数据写入

    对于大数据的Xlsx文件的写入,POI3.8提供了SXSSFSXSSFWorkbook类,采用缓存方式进行大批量写文件。

    详情可以查看poi官网示例:http://poi.apache.org/spreadsheet/how-to.html#sxssf 或 http://blog.csdn.net/daiyutage/article/details/53010491

    2.2 大批量数据读取

    POI读取Excel有两种模式,一种是用户模式,一种是SAX事件驱动模式,将xlsx格式的文档转换成CSV格式后进行读取。用户模式API接口丰富,使用POI的API可以很容易读取Excel,但用户模式消耗的内存很大,当遇到很大sheet、大数据网格,假空行、公式等问题时,很容易导致内存溢出。POI官方推荐解决内存溢出的方式使用CVS格式解析,即SAX事件驱动模式。下面主要是讲解如何读取大批量数据:

    pom.xml所需jar包:

    复制代码
     1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     2   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
     3   <modelVersion>4.0.0</modelVersion>
     4   <groupId>POIExcel</groupId>
     5   <artifactId>POIExcel</artifactId>
     6   <packaging>war</packaging>
     7   <version>1.0-SNAPSHOT</version>
     8   <name>POIExcel Maven Webapp</name>
     9   <url>http://maven.apache.org</url>
    10   <dependencies>
    11     <dependency>
    12       <groupId>junit</groupId>
    13       <artifactId>junit</artifactId>
    14       <version>3.8.1</version>
    15       <scope>test</scope>
    16     </dependency>
    17 
    18     <dependency>
    19       <groupId>org.apache.poi</groupId>
    20       <artifactId>poi</artifactId>
    21       <version>3.17</version>
    22     </dependency>
    23 
    24     <dependency>
    25       <groupId>org.apache.poi</groupId>
    26       <artifactId>poi-ooxml</artifactId>
    27       <version>3.17</version>
    28     </dependency>
    29 
    30     <dependency>
    31       <groupId>org.apache.poi</groupId>
    32       <artifactId>poi-ooxml-schemas</artifactId>
    33       <version>3.17</version>
    34     </dependency>
    35 
    36     <dependency>
    37       <groupId>com.syncthemall</groupId>
    38       <artifactId>boilerpipe</artifactId>
    39       <version>1.2.1</version>
    40     </dependency>
    41 
    42     <dependency>
    43       <groupId>xerces</groupId>
    44       <artifactId>xercesImpl</artifactId>
    45       <version>2.11.0</version>
    46     </dependency>
    47 
    48     <dependency>
    49       <groupId>xml-apis</groupId>
    50       <artifactId>xml-apis</artifactId>
    51       <version>1.4.01</version>
    52     </dependency>
    53 
    54     <dependency>
    55       <groupId>org.apache.xmlbeans</groupId>
    56       <artifactId>xmlbeans</artifactId>
    57       <version>2.6.0</version>
    58     </dependency>
    59 
    60     <dependency>
    61       <groupId>sax</groupId>
    62       <artifactId>sax</artifactId>
    63       <version>2.0.1</version>
    64     </dependency>
    65 
    66     <dependency>
    67       <groupId>org.apache.commons</groupId>
    68       <artifactId>commons-lang3</artifactId>
    69       <version>3.7</version>
    70     </dependency>
    71 
    72   </dependencies>
    73   <build>
    74     <finalName>POIExcel</finalName>
    75   </build>
    76 </project>
    复制代码

    POI以SAX解析excel2007文件:

    解决思路:通过继承DefaultHandler类,重写process(),startElement(),characters(),endElement()这四个方法。process()方式主要是遍历所有的sheet,并依次调用startElement()、characters()方法、endElement()这三个方法。startElement()用于设定单元格的数字类型(如日期、数字、字符串等等)。characters()用于获取该单元格对应的索引值或是内容值(如果单元格类型是字符串、INLINESTR、数字、日期则获取的是索引值;其他如布尔值、错误、公式则获取的是内容值)。endElement()根据startElement()的单元格数字类型和characters()的索引值或内容值,最终得出单元格的内容值,并打印出来。

    复制代码
      1 package org.poi;
      2 
      3 import org.apache.poi.openxml4j.opc.OPCPackage;
      4 import org.apache.poi.ss.usermodel.BuiltinFormats;
      5 import org.apache.poi.ss.usermodel.DataFormatter;
      6 import org.apache.poi.xssf.eventusermodel.XSSFReader;
      7 import org.apache.poi.xssf.model.SharedStringsTable;
      8 import org.apache.poi.xssf.model.StylesTable;
      9 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
     10 import org.apache.poi.xssf.usermodel.XSSFRichTextString;
     11 import org.xml.sax.Attributes;
     12 import org.xml.sax.InputSource;
     13 import org.xml.sax.SAXException;
     14 import org.xml.sax.XMLReader;
     15 import org.xml.sax.helpers.DefaultHandler;
     16 import org.xml.sax.helpers.XMLReaderFactory;
     17 
     18 import java.io.InputStream;
     19 import java.util.ArrayList;
     20 import java.util.List;
     21 
     22 /**
     23  * @author y
     24  * @create 2018-01-18 14:28
     25  * @desc POI读取excel有两种模式,一种是用户模式,一种是事件驱动模式
     26  * 采用SAX事件驱动模式解决XLSX文件,可以有效解决用户模式内存溢出的问题,
     27  * 该模式是POI官方推荐的读取大数据的模式,
     28  * 在用户模式下,数据量较大,Sheet较多,或者是有很多无用的空行的情况下,容易出现内存溢出
     29  * <p>
     30  * 用于解决.xlsx2007版本大数据量问题
     31  **/
     32 public class ExcelXlsxReader extends DefaultHandler {
     33 
     34     /**
     35      * 单元格中的数据可能的数据类型
     36      */
     37     enum CellDataType {
     38         BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
     39     }
     40 
     41     /**
     42      * 共享字符串表
     43      */
     44     private SharedStringsTable sst;
     45 
     46     /**
     47      * 上一次的索引值
     48      */
     49     private String lastIndex;
     50 
     51     /**
     52      * 文件的绝对路径
     53      */
     54     private String filePath = "";
     55 
     56     /**
     57      * 工作表索引
     58      */
     59     private int sheetIndex = 0;
     60 
     61     /**
     62      * sheet名
     63      */
     64     private String sheetName = "";
     65 
     66     /**
     67      * 总行数
     68      */
     69     private int totalRows=0;
     70 
     71     /**
     72      * 一行内cell集合
     73      */
     74     private List<String> cellList = new ArrayList<String>();
     75 
     76     /**
     77      * 判断整行是否为空行的标记
     78      */
     79     private boolean flag = false;
     80 
     81     /**
     82      * 当前行
     83      */
     84     private int curRow = 1;
     85 
     86     /**
     87      * 当前列
     88      */
     89     private int curCol = 0;
     90 
     91     /**
     92      * T元素标识
     93      */
     94     private boolean isTElement;
     95 
     96     /**
     97      * 异常信息,如果为空则表示没有异常
     98      */
     99     private String exceptionMessage;
    100 
    101     /**
    102      * 单元格数据类型,默认为字符串类型
    103      */
    104     private CellDataType nextDataType = CellDataType.SSTINDEX;
    105 
    106     private final DataFormatter formatter = new DataFormatter();
    107 
    108     /**
    109      * 单元格日期格式的索引
    110      */
    111     private short formatIndex;
    112 
    113     /**
    114      * 日期格式字符串
    115      */
    116     private String formatString;
    117 
    118     //定义前一个元素和当前元素的位置,用来计算其中空的单元格数量,如A6和A8等
    119     private String preRef = null, ref = null;
    120 
    121     //定义该文档一行最大的单元格数,用来补全一行最后可能缺失的单元格
    122     private String maxRef = null;
    123 
    124     /**
    125      * 单元格
    126      */
    127     private StylesTable stylesTable;
    128 
    129     /**
    130      * 遍历工作簿中所有的电子表格
    131      * 并缓存在mySheetList中
    132      *
    133      * @param filename
    134      * @throws Exception
    135      */
    136     public int process(String filename) throws Exception {
    137         filePath = filename;
    138         OPCPackage pkg = OPCPackage.open(filename);
    139         XSSFReader xssfReader = new XSSFReader(pkg);
    140         stylesTable = xssfReader.getStylesTable();
    141         SharedStringsTable sst = xssfReader.getSharedStringsTable();
    142         XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
    143         this.sst = sst;
    144         parser.setContentHandler(this);
    145         XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
    146         while (sheets.hasNext()) { //遍历sheet
    147             curRow = 1; //标记初始行为第一行
    148             sheetIndex++;
    149             InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置,否则sheetName报错
    150             sheetName = sheets.getSheetName();
    151             InputSource sheetSource = new InputSource(sheet);
    152             parser.parse(sheetSource); //解析excel的每条记录,在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
    153             sheet.close();
    154         }
    155         return totalRows; //返回该excel文件的总行数,不包括首列和空行
    156     }
    157 
    158     /**
    159      * 第一个执行
    160      *
    161      * @param uri
    162      * @param localName
    163      * @param name
    164      * @param attributes
    165      * @throws SAXException
    166      */
    167     @Override
    168     public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
    169         //c => 单元格
    170         if ("c".equals(name)) {
    171             //前一个单元格的位置
    172             if (preRef == null) {
    173                 preRef = attributes.getValue("r");
    174             } else {
    175                 preRef = ref;
    176             }
    177 
    178             //当前单元格的位置
    179             ref = attributes.getValue("r");
    180             //设定单元格类型
    181             this.setNextDataType(attributes);
    182         }
    183 
    184         //当元素为t时
    185         if ("t".equals(name)) {
    186             isTElement = true;
    187         } else {
    188             isTElement = false;
    189         }
    190 
    191         //置空
    192         lastIndex = "";
    193     }
    194 
    195     /**
    196      * 第二个执行
    197      * 得到单元格对应的索引值或是内容值
    198      * 如果单元格类型是字符串、INLINESTR、数字、日期,lastIndex则是索引值
    199      * 如果单元格类型是布尔值、错误、公式,lastIndex则是内容值
    200      * @param ch
    201      * @param start
    202      * @param length
    203      * @throws SAXException
    204      */
    205     @Override
    206     public void characters(char[] ch, int start, int length) throws SAXException {
    207         lastIndex += new String(ch, start, length);
    208     }
    209 
    210     /**
    211      * 第三个执行
    212      *
    213      * @param uri
    214      * @param localName
    215      * @param name
    216      * @throws SAXException
    217      */
    218     @Override
    219     public void endElement(String uri, String localName, String name) throws SAXException {
    220 
    221         //t元素也包含字符串
    222         if (isTElement) {//这个程序没经过
    223             //将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
    224             String value = lastIndex.trim();
    225             cellList.add(curCol, value);
    226             curCol++;
    227             isTElement = false;
    228             //如果里面某个单元格含有值,则标识该行不为空行
    229             if (value != null && !"".equals(value)) {
    230                 flag = true;
    231             }
    232         } else if ("v".equals(name)) {
    233             //v => 单元格的值,如果单元格是字符串,则v标签的值为该字符串在SST中的索引
    234             String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
    235             //补全单元格之间的空单元格
    236             if (!ref.equals(preRef)) {
    237                 int len = countNullCell(ref, preRef);
    238                 for (int i = 0; i < len; i++) {
    239                     cellList.add(curCol, "");
    240                     curCol++;
    241                 }
    242             }
    243             cellList.add(curCol, value);
    244             curCol++;
    245             //如果里面某个单元格含有值,则标识该行不为空行
    246             if (value != null && !"".equals(value)) {
    247                 flag = true;
    248             }
    249         } else {
    250             //如果标签名称为row,这说明已到行尾,调用optRows()方法
    251             if ("row".equals(name)) {
    252                 //默认第一行为表头,以该行单元格数目为最大数目
    253                 if (curRow == 1) {
    254                     maxRef = ref;
    255                 }
    256                 //补全一行尾部可能缺失的单元格
    257                 if (maxRef != null) {
    258                     int len = countNullCell(maxRef, ref);
    259                     for (int i = 0; i <= len; i++) {
    260                         cellList.add(curCol, "");
    261                         curCol++;
    262                     }
    263                 }
    264 
    265                 if (flag&&curRow!=1){ //该行不为空行且该行不是第一行,则发送(第一行为列名,不需要)
    266                     ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow, cellList);
    267                     totalRows++;
    268                 }
    269 
    270                 cellList.clear();
    271                 curRow++;
    272                 curCol = 0;
    273                 preRef = null;
    274                 ref = null;
    275                 flag=false;
    276             }
    277         }
    278     }
    279 
    280     /**
    281      * 处理数据类型
    282      *
    283      * @param attributes
    284      */
    285     public void setNextDataType(Attributes attributes) {
    286         nextDataType = CellDataType.NUMBER; //cellType为空,则表示该单元格类型为数字
    287         formatIndex = -1;
    288         formatString = null;
    289         String cellType = attributes.getValue("t"); //单元格类型
    290         String cellStyleStr = attributes.getValue("s"); //
    291         String columnData = attributes.getValue("r"); //获取单元格的位置,如A1,B1
    292 
    293         if ("b".equals(cellType)) { //处理布尔值
    294             nextDataType = CellDataType.BOOL;
    295         } else if ("e".equals(cellType)) {  //处理错误
    296             nextDataType = CellDataType.ERROR;
    297         } else if ("inlineStr".equals(cellType)) {
    298             nextDataType = CellDataType.INLINESTR;
    299         } else if ("s".equals(cellType)) { //处理字符串
    300             nextDataType = CellDataType.SSTINDEX;
    301         } else if ("str".equals(cellType)) {
    302             nextDataType = CellDataType.FORMULA;
    303         }
    304 
    305         if (cellStyleStr != null) { //处理日期
    306             int styleIndex = Integer.parseInt(cellStyleStr);
    307             XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
    308             formatIndex = style.getDataFormat();
    309             formatString = style.getDataFormatString();
    310 
    311             if (formatString.contains("m/d/yy")) {
    312                 nextDataType = CellDataType.DATE;
    313                 formatString = "yyyy-MM-dd hh:mm:ss";
    314             }
    315 
    316             if (formatString == null) {
    317                 nextDataType = CellDataType.NULL;
    318                 formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
    319             }
    320         }
    321     }
    322 
    323     /**
    324      * 对解析出来的数据进行类型处理
    325      * @param value   单元格的值,
    326      *                value代表解析:BOOL的为0或1, ERROR的为内容值,FORMULA的为内容值,INLINESTR的为索引值需转换为内容值,
    327      *                SSTINDEX的为索引值需转换为内容值, NUMBER为内容值,DATE为内容值
    328      * @param thisStr 一个空字符串
    329      * @return
    330      */
    331     @SuppressWarnings("deprecation")
    332     public String getDataValue(String value, String thisStr) {
    333         switch (nextDataType) {
    334             // 这几个的顺序不能随便交换,交换了很可能会导致数据错误
    335             case BOOL: //布尔值
    336                 char first = value.charAt(0);
    337                 thisStr = first == '0' ? "FALSE" : "TRUE";
    338                 break;
    339             case ERROR: //错误
    340                 thisStr = "\"ERROR:" + value.toString() + '"';
    341                 break;
    342             case FORMULA: //公式
    343                 thisStr = '"' + value.toString() + '"';
    344                 break;
    345             case INLINESTR:
    346                 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
    347                 thisStr = rtsi.toString();
    348                 rtsi = null;
    349                 break;
    350             case SSTINDEX: //字符串
    351                 String sstIndex = value.toString();
    352                 try {
    353                     int idx = Integer.parseInt(sstIndex);
    354                     XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
    355                     thisStr = rtss.toString();
    356                     rtss = null;
    357                 } catch (NumberFormatException ex) {
    358                     thisStr = value.toString();
    359                 }
    360                 break;
    361             case NUMBER: //数字
    362                 if (formatString != null) {
    363                     thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
    364                 } else {
    365                     thisStr = value;
    366                 }
    367                 thisStr = thisStr.replace("_", "").trim();
    368                 break;
    369             case DATE: //日期
    370                 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
    371                 // 对日期字符串作特殊处理,去掉T
    372                 thisStr = thisStr.replace("T", " ");
    373                 break;
    374             default:
    375                 thisStr = " ";
    376                 break;
    377         }
    378         return thisStr;
    379     }
    380 
    381     public int countNullCell(String ref, String preRef) {
    382         //excel2007最大行数是1048576,最大列数是16384,最后一列列名是XFD
    383         String xfd = ref.replaceAll("\\d+", "");
    384         String xfd_1 = preRef.replaceAll("\\d+", "");
    385 
    386         xfd = fillChar(xfd, 3, '@', true);
    387         xfd_1 = fillChar(xfd_1, 3, '@', true);
    388 
    389         char[] letter = xfd.toCharArray();
    390         char[] letter_1 = xfd_1.toCharArray();
    391         int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
    392         return res - 1;
    393     }
    394 
    395     public String fillChar(String str, int len, char let, boolean isPre) {
    396         int len_1 = str.length();
    397         if (len_1 < len) {
    398             if (isPre) {
    399                 for (int i = 0; i < (len - len_1); i++) {
    400                     str = let + str;
    401                 }
    402             } else {
    403                 for (int i = 0; i < (len - len_1); i++) {
    404                     str = str + let;
    405                 }
    406             }
    407         }
    408         return str;
    409     }
    410 
    411     /**
    412      * @return the exceptionMessage
    413      */
    414     public String getExceptionMessage() {
    415         return exceptionMessage;
    416     }
    417 }
    复制代码

    POI通过继承HSSFListener类来解决Excel2003文件:

     解决思路:重写process(),processRecord()两个方法,其中processRecord是核心方法,用于处理sheetName和各种单元格数字类型。

    复制代码
      1 package org.poi;
      2 
      3 import org.apache.poi.hssf.eventusermodel.*;
      4 import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
      5 import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
      6 import org.apache.poi.hssf.model.HSSFFormulaParser;
      7 import org.apache.poi.hssf.record.*;
      8 import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
      9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
     10 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
     11 
     12 import java.io.FileInputStream;
     13 import java.util.ArrayList;
     14 import java.util.List;
     15 
     16 /**
     17  * @author y
     18  * @create 2018-01-19 14:18
     19  * @desc 用于解决.xls2003版本大数据量问题
     20  **/
     21 public class ExcelXlsReader implements HSSFListener {
     22 
     23     private int minColums = -1;
     24 
     25     private POIFSFileSystem fs;
     26 
     27     /**
     28      * 总行数
     29      */
     30     private int totalRows=0;
     31 
     32     /**
     33      * 上一行row的序号
     34      */
     35     private int lastRowNumber;
     36 
     37     /**
     38      * 上一单元格的序号
     39      */
     40     private int lastColumnNumber;
     41 
     42     /**
     43      * 是否输出formula,还是它对应的值
     44      */
     45     private boolean outputFormulaValues = true;
     46 
     47     /**
     48      * 用于转换formulas
     49      */
     50     private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;
     51 
     52     //excel2003工作簿
     53     private HSSFWorkbook stubWorkbook;
     54 
     55     private SSTRecord sstRecord;
     56 
     57     private FormatTrackingHSSFListener formatListener;
     58 
     59     private final HSSFDataFormatter formatter = new HSSFDataFormatter();
     60 
     61     /**
     62      * 文件的绝对路径
     63      */
     64     private String filePath = "";
     65 
     66     //表索引
     67     private int sheetIndex = 0;
     68 
     69     private BoundSheetRecord[] orderedBSRs;
     70 
     71     @SuppressWarnings("unchecked")
     72     private ArrayList boundSheetRecords = new ArrayList();
     73 
     74     private int nextRow;
     75 
     76     private int nextColumn;
     77 
     78     private boolean outputNextStringRecord;
     79 
     80     //当前行
     81     private int curRow = 0;
     82 
     83     //存储一行记录所有单元格的容器
     84     private List<String> cellList = new ArrayList<String>();
     85 
     86     /**
     87      * 判断整行是否为空行的标记
     88      */
     89     private boolean flag = false;
     90 
     91     @SuppressWarnings("unused")
     92     private String sheetName;
     93 
     94     /**
     95      * 遍历excel下所有的sheet
     96      *
     97      * @param fileName
     98      * @throws Exception
     99      */
    100     public int process(String fileName) throws Exception {
    101         filePath = fileName;
    102         this.fs = new POIFSFileSystem(new FileInputStream(fileName));
    103         MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
    104         formatListener = new FormatTrackingHSSFListener(listener);
    105         HSSFEventFactory factory = new HSSFEventFactory();
    106         HSSFRequest request = new HSSFRequest();
    107         if (outputFormulaValues) {
    108             request.addListenerForAllRecords(formatListener);
    109         } else {
    110             workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
    111             request.addListenerForAllRecords(workbookBuildingListener);
    112         }
    113         factory.processWorkbookEvents(request, fs);
    114 
    115         return totalRows; //返回该excel文件的总行数,不包括首列和空行
    116     }
    117     
    118     /**
    119      * HSSFListener 监听方法,处理Record
    120      * 处理每个单元格
    121      * @param record
    122      */
    123     @SuppressWarnings("unchecked")
    124     public void processRecord(Record record) {
    125         int thisRow = -1;
    126         int thisColumn = -1;
    127         String thisStr = null;
    128         String value = null;
    129         switch (record.getSid()) {
    130             case BoundSheetRecord.sid:
    131                 boundSheetRecords.add(record);
    132                 break;
    133             case BOFRecord.sid: //开始处理每个sheet
    134                 BOFRecord br = (BOFRecord) record;
    135                 if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
    136                     //如果有需要,则建立子工作簿
    137                     if (workbookBuildingListener != null && stubWorkbook == null) {
    138                         stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
    139                     }
    140 
    141                     if (orderedBSRs == null) {
    142                         orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
    143                     }
    144                     sheetName = orderedBSRs[sheetIndex].getSheetname();
    145                     sheetIndex++;
    146                 }
    147                 break;
    148             case SSTRecord.sid:
    149                 sstRecord = (SSTRecord) record;
    150                 break;
    151             case BlankRecord.sid: //单元格为空白
    152                 BlankRecord brec = (BlankRecord) record;
    153                 thisRow = brec.getRow();
    154                 thisColumn = brec.getColumn();
    155                 thisStr = "";
    156                 cellList.add(thisColumn, thisStr);
    157                 break;
    158             case BoolErrRecord.sid: //单元格为布尔类型
    159                 BoolErrRecord berec = (BoolErrRecord) record;
    160                 thisRow = berec.getRow();
    161                 thisColumn = berec.getColumn();
    162                 thisStr = berec.getBooleanValue() + "";
    163                 cellList.add(thisColumn, thisStr);
    164                 checkRowIsNull(thisStr);  //如果里面某个单元格含有值,则标识该行不为空行
    165                 break;
    166             case FormulaRecord.sid://单元格为公式类型
    167                 FormulaRecord frec = (FormulaRecord) record;
    168                 thisRow = frec.getRow();
    169                 thisColumn = frec.getColumn();
    170                 if (outputFormulaValues) {
    171                     if (Double.isNaN(frec.getValue())) {
    172                         outputNextStringRecord = true;
    173                         nextRow = frec.getRow();
    174                         nextColumn = frec.getColumn();
    175                     } else {
    176                         thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
    177                     }
    178                 } else {
    179                     thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
    180                 }
    181                 cellList.add(thisColumn, thisStr);
    182                 checkRowIsNull(thisStr);  //如果里面某个单元格含有值,则标识该行不为空行
    183                 break;
    184             case StringRecord.sid: //单元格中公式的字符串
    185                 if (outputNextStringRecord) {
    186                     StringRecord srec = (StringRecord) record;
    187                     thisStr = srec.getString();
    188                     thisRow = nextRow;
    189                     thisColumn = nextColumn;
    190                     outputNextStringRecord = false;
    191                 }
    192                 break;
    193             case LabelRecord.sid:
    194                 LabelRecord lrec = (LabelRecord) record;
    195                 curRow = thisRow = lrec.getRow();
    196                 thisColumn = lrec.getColumn();
    197                 value = lrec.getValue().trim();
    198                 value = value.equals("") ? "" : value;
    199                 cellList.add(thisColumn, value);
    200                 checkRowIsNull(value);  //如果里面某个单元格含有值,则标识该行不为空行
    201                 break;
    202             case LabelSSTRecord.sid: //单元格为字符串类型
    203                 LabelSSTRecord lsrec = (LabelSSTRecord) record;
    204                 curRow = thisRow = lsrec.getRow();
    205                 thisColumn = lsrec.getColumn();
    206                 if (sstRecord == null) {
    207                     cellList.add(thisColumn, "");
    208                 } else {
    209                     value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
    210                     value = value.equals("") ? "" : value;
    211                     cellList.add(thisColumn, value);
    212                     checkRowIsNull(value);  //如果里面某个单元格含有值,则标识该行不为空行
    213                 }
    214                 break;
    215             case NumberRecord.sid: //单元格为数字类型
    216                 NumberRecord numrec = (NumberRecord) record;
    217                 curRow = thisRow = numrec.getRow();
    218                 thisColumn = numrec.getColumn();
    219 
    220                 //第一种方式
    221                 //value = formatListener.formatNumberDateCell(numrec).trim();//这个被写死,采用的m/d/yy h:mm格式,不符合要求
    222 
    223                 //第二种方式,参照formatNumberDateCell里面的实现方法编写
    224                 Double valueDouble=((NumberRecord)numrec).getValue();
    225                 String formatString=formatListener.getFormatString(numrec);
    226                 if (formatString.contains("m/d/yy")){
    227                     formatString="yyyy-MM-dd hh:mm:ss";
    228                 }
    229                 int formatIndex=formatListener.getFormatIndex(numrec);
    230                 value=formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim();
    231 
    232                 value = value.equals("") ? "" : value;
    233                 //向容器加入列值
    234                 cellList.add(thisColumn, value);
    235                 checkRowIsNull(value);  //如果里面某个单元格含有值,则标识该行不为空行
    236                 break;
    237             default:
    238                 break;
    239         }
    240 
    241         //遇到新行的操作
    242         if (thisRow != -1 && thisRow != lastRowNumber) {
    243             lastColumnNumber = -1;
    244         }
    245 
    246         //空值的操作
    247         if (record instanceof MissingCellDummyRecord) {
    248             MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
    249             curRow = thisRow = mc.getRow();
    250             thisColumn = mc.getColumn();
    251             cellList.add(thisColumn, "");
    252         }
    253 
    254         //更新行和列的值
    255         if (thisRow > -1)
    256             lastRowNumber = thisRow;
    257         if (thisColumn > -1)
    258             lastColumnNumber = thisColumn;
    259 
    260         //行结束时的操作
    261         if (record instanceof LastCellOfRowDummyRecord) {
    262             if (minColums > 0) {
    263                 //列值重新置空
    264                 if (lastColumnNumber == -1) {
    265                     lastColumnNumber = 0;
    266                 }
    267             }
    268             lastColumnNumber = -1;
    269 
    270             if (flag&&curRow!=0) { //该行不为空行且该行不是第一行,发送(第一行为列名,不需要)
    271                 ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow + 1, cellList); //每行结束时,调用sendRows()方法
    272                 totalRows++;
    273             }
    274             //清空容器
    275             cellList.clear();
    276             flag=false;
    277         }
    278     }
    279 
    280     /**
    281      * 如果里面某个单元格含有值,则标识该行不为空行
    282      * @param value
    283      */
    284     public void checkRowIsNull(String value){
    285         if (value != null && !"".equals(value)) {
    286             flag = true;
    287         }
    288     }
    289 }
    复制代码

    辅助类ExcelReaderUtil,调用ExcelXlsReader类和ExcelXlsxReader类对excel2003和excel2007两个版本进行大批量数据读取:

    复制代码
     1 package org.poi;
     2 
     3 import java.util.List;
     4 
     5 /**
     6  * @author y
     7  * @create 2018-01-19 0:13
     8  * @desc
     9  **/
    10 public class ExcelReaderUtil {
    11     //excel2003扩展名
    12     public static final String EXCEL03_EXTENSION = ".xls";
    13     //excel2007扩展名
    14     public static final String EXCEL07_EXTENSION = ".xlsx";
    15 
    16     /**
    17      * 每获取一条记录,即打印
    18      * 在flume里每获取一条记录即发送,而不必缓存起来,可以大大减少内存的消耗,这里主要是针对flume读取大数据量excel来说的
    19      * @param sheetName
    20      * @param sheetIndex
    21      * @param curRow
    22      * @param cellList
    23      */
    24     public static void sendRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
    25             StringBuffer oneLineSb = new StringBuffer();
    26             oneLineSb.append(filePath);
    27             oneLineSb.append("--");
    28             oneLineSb.append("sheet" + sheetIndex);
    29             oneLineSb.append("::" + sheetName);//加上sheet名
    30             oneLineSb.append("--");
    31             oneLineSb.append("row" + curRow);
    32             oneLineSb.append("::");
    33             for (String cell : cellList) {
    34                 oneLineSb.append(cell.trim());
    35                 oneLineSb.append("|");
    36             }
    37             String oneLine = oneLineSb.toString();
    38             if (oneLine.endsWith("|")) {
    39                 oneLine = oneLine.substring(0, oneLine.lastIndexOf("|"));
    40             }// 去除最后一个分隔符
    41 
    42             System.out.println(oneLine);
    43     }
    44 
    45     public static void readExcel(String fileName) throws Exception {
    46         int totalRows =0;
    47         if (fileName.endsWith(EXCEL03_EXTENSION)) { //处理excel2003文件
    48             ExcelXlsReader excelXls=new ExcelXlsReader();
    49             totalRows =excelXls.process(fileName);
    50         } else if (fileName.endsWith(EXCEL07_EXTENSION)) {//处理excel2007文件
    51             ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
    52             totalRows = excelXlsxReader.process(fileName);
    53         } else {
    54             throw new Exception("文件格式错误,fileName的扩展名只能是xls或xlsx。");
    55         }
    56         System.out.println("发送的总行数:" + totalRows);
    57     }
    58 
    59     public static void main(String[] args) throws Exception {
    60         String path="C:\\Users\\y****\\Desktop\\TestSample\\H_20171226_***_*****_0430.xlsx";
    61         ExcelReaderUtil.readExcel(path);
    62     }
    63 }
    复制代码

    转自:https://www.cnblogs.com/swordfall/p/8298386.html

    github地址:https://github.com/qiushangwenyue/POIExcel

    参考资料:

    https://www.cnblogs.com/huangjian2/p/6238237.html

    https://www.cnblogs.com/yfrs/p/5689347.html

    http://blog.csdn.net/lishengbo/article/details/40711769

    https://www.cnblogs.com/wshsdlau/p/5643847.html

    http://blog.csdn.net/lipinganq/article/details/78775195

    http://blog.csdn.net/lipinganq/article/details/53389501

    http://blog.csdn.net/zmx729618/article/details/72639037

    http://blog.csdn.net/daiyutage/article/details/53010491

    展开全文
  • Java POI处理大量数据导入导出xls和xlsx导入数据(大量)导出数据(大量)总结 xls和xlsx xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包, 如图: xls是以二进制的方式...
  • package org.hzjun.hlt.excel; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List;...import javax.xml.parsers.Pars
  • package skuPrice; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException;...import java.io...
  • Exception in thread "main" java.lang.NoSuchMethodError: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets.getSheetList()Ljava/util/List;...at org.apache.poi.xssf.eventusermodel.XSSFReader$Sh
  • 利用 poi技术读取大数据量的excel时,总会出现内存溢出的情况,在网上找了很多办法,很多方式都不能较好地解决。最后找到一种方法能较好的解决,修改jvm,我将其扩大一倍后就能读20列的6万多条数据。 修改tomcat的...
  • POI 提供了 好几种生成Excel的方式,查看官方的API可以发现 第一种:HSSFWorkbook  针对是 EXCEL2003 版本,扩展名为 .xls;所以 此种的局限就是 导出的行数 至多为 65535 行,此种 因为行数不足七万行 所以 一般...
  • 记录一下使用poi读取大数据excel文件踩的坑 介绍 Java 有2个jar包可以操作excel文件,分别是jxl和poi; jxl这个jar包只能读取excel2003年的文件(文件后缀为.xls),而poi这个jar包excel2003(文件后缀为.xls)和...
  • POI导出大数据量excel(注:项目源码及后续更新请点击)1、ExcelUtils类:package Utils; import com.alibaba.fastjson.JSONArray; ... import org.apache.poi.common.usermodel.Hyperlink; import org....
  • 在处理文本时,经常遇到超过1g存储的数据,直接简单的读取,可能遇到java空间不足的问题,为解决此问题,可将大文本数据按照行进行切分为很多块,并将每一块存储为一个文本public class BigDataRead { public static...
  • poi读取大数据量excel文件,避免内存溢出,行级操作 根据本网站的资源修改的。 将一些类路径错误全部进行了修正。 另外,需要自己在类路径里,放spring-context.jar和spring-beans.jar包。
  • 今天在做excel文件上传时,发现数据量超过10万条后,系统会出现内存溢出。 跟踪代码发现程序卡在如下中 ...在poi的官方文档中找到了读取大量数据的方法,但只能读数据,不能进行写操作。 代码整...
  • 使用POI能够导出大数据保证内存不溢出的一个重要原因是SXSSFWorkbook生成的EXCEL为2007版本,修改EXCEL2007文件后缀为ZIP打开可以看到,每一个Sheet都是一个xml文件,单元格格式和单元格坐标均用标签表示。...
  • 本案例采用的poi读取大数据的excel文件 usermodel模式对excel操作前需要将文件全部转入内存,对较大文件来说内存开销很大。但是其使用简单。 eventusermodel模式采用事件模型,对文件边读取边处理,内存消耗较低,...
1 2 3 4 5 ... 20
收藏数 1,078
精华内容 431