【第03篇】利用POI框架的SAX方式之获取Excel中数据总行数

忘是亡心i 2022-02-04 19:49 398阅读 0赞

已经写过读取Excel的方式，这次贴个常用的业务功能：获取总行数。

原理和上2篇差不多，直接贴代码：

package com.fulong.utils.v2.poi;
    
    import java.io.File;
    import java.io.IOException;
    
    /* ====================================================================
       Licensed to the Apache Software Foundation (ASF) under one or more
       contributor license agreements.  See the NOTICE file distributed with
       this work for additional information regarding copyright ownership.
       The ASF licenses this file to You under the Apache License, Version 2.0
       (the "License"); you may not use this file except in compliance with
       the License.  You may obtain a copy of the License at
    
           http://www.apache.org/licenses/LICENSE-2.0
    
       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.
    ==================================================================== */
    
    
    import java.io.InputStream;
    import java.util.regex.Pattern;
    
    import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.xssf.eventusermodel.XSSFReader;
    import org.apache.poi.xssf.model.SharedStringsTable;
    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;
    
    
    /**
     * XSSF and SAX (Event API) basic example.
     * See {@link XLSX2CSV} for a fuller example of doing
     *  XSLX processing with the XSSF Event code.
     */
    public class MyExcel2007ForMaxRow {
    	/**
    	 * 代表Excel中必须有值的起始列(A、B、C....AA、AB...)
    	 */
    	private static final String INDEX_COL_DATA = "A";
    	public static final String NEW_ROW_REGEX = "^"+INDEX_COL_DATA+"[0-9]+$";
    	//new add
    	public long maxRow = 0;//记录总行数
    	
    	/**
    	 * 带路径的文件名
    	 */
    	//private String filename = null;
    	private File f = null;
    	/*public MyExcel2007ForMaxRow(String path) throws Exception{
    		if(StringUtils.isBlank(path)) throw new Exception("文件名不能空");
    		this.f = new File(path);
    		
    		if(!f.exists()) throw new Exception("文件不存在");
    		processFirstSheet();
    	}*/
    	public MyExcel2007ForMaxRow(File f) throws IOException, SAXException, OpenXML4JException{
    		this.f = f;
    		processFirstSheet();
    	}
    	/**
    	 * 指定获取第一个sheet
    	 * @param filename
    	 * @throws IOException 
    	 * @throws SAXException 
    	 * @throws OpenXML4JException 
    	 * @throws Exception
    	 */
    	private void processFirstSheet() throws IOException, SAXException, OpenXML4JException  {
    		//OPCPackage pkg = OPCPackage.open(filename);
    		OPCPackage pkg = OPCPackage.open(f);
    		XSSFReader r = new XSSFReader( pkg );
    		SharedStringsTable sst = r.getSharedStringsTable();
    
    		XMLReader parser = fetchSheetParser(sst);
    
    		// To look up the Sheet Name / Sheet Order / rID,
    		//  you need to process the core Workbook stream.
    		// Normally it's of the form rId# or rSheet#
    		InputStream sheet2 = r.getSheet("rId1");
    		InputSource sheetSource = new InputSource(sheet2);
    		parser.parse(sheetSource);
    		sheet2.close();//关闭流
    		pkg.close();//关闭POI的sax流
    	}
    	
    	private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
    		XMLReader parser =
    			XMLReaderFactory.createXMLReader(
    					"org.apache.xerces.parsers.SAXParser"
    			);
    		ContentHandler handler = new MaxRowHandler();
    		parser.setContentHandler(handler);
    		return parser;
    	}
    
    	/** 
    	 * See org.xml.sax.helpers.DefaultHandler javadocs 
    	 */
    	private  class MaxRowHandler extends DefaultHandler {
    
    		@Override
    		public void startElement(String uri, String localName, String name,
    				Attributes attributes) throws SAXException {
    			// c => cell
    			if("c".equals(name)) {
    				String index = attributes.getValue("r");
    				if(Pattern.compile(NEW_ROW_REGEX).matcher(index).find()){
    					//System.out.println(index);
    					maxRow++;
    				}
    		
    			}
    
    		}
    	
    	}
    	public static void main(String[] args) throws IOException, SAXException, OpenXML4JException {
    		System.out.println(System.currentTimeMillis());
    		MyExcel2007ForMaxRow reader = new MyExcel2007ForMaxRow(new File("e:/wl.xlsx"));
    		System.out.println(System.currentTimeMillis());
    		System.out.println(reader.maxRow);
    	}
    }