POI内存式读取Excel代码记录展示

/**
 * Excel xls xlsx 文件的读取
 * 非商业用途的个人代码
 */
package top.yangcourage.test.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * @author courage.yang
 * 本示例依赖的jar包如下所示
 * commons-collections4-4.1.jar
 * poi-3.16-beta2.jar
 * poi-excelant-3.16-beta2.jar
 * poi-ooxml-3.16-beta2.jar
 * poi-ooxml-schemas-3.16-beta2.jar
 * poi-scratchpad-3.16-beta2.jar
 * xmlbeans-2.6.0.jar
 */
public class PoiReadAndWrite {
	final static String filePath = "X:\\xxxx\\测试文件内容.xlsx";
	public static void main(String[] args) throws Exception {
		
		// 定义日期格式化对象
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// number内容格式化
		DecimalFormat df = new DecimalFormat("0.00");
		
		// 定义公式计算器
		FormulaEvaluator fe = null;
		InputStream input = new FileInputStream(new File(filePath));
		
		// 读取内容的工作簿
		Workbook wb = null;
		// 根据文件名初始化对应的Excel文件
		if(filePath.endsWith(".xlsx")){
			// xlsx
			wb = new XSSFWorkbook(input);
		}
		else{
			// xls
			wb = new HSSFWorkbook(input);
		}
		// 初始化当期页签的公式计算类
		fe = wb.getCreationHelper().createFormulaEvaluator();
		// 获取当前文件的表格个数
	    int sheetCount = wb.getNumberOfSheets();
		
		// 获取第一个表单
		Sheet sheet = wb.getSheetAt(0);
//		Drawing<?> draw = sheet.getDrawingPatriarch();
		XSSFDrawing drawing = (XSSFDrawing)sheet.getDrawingPatriarch();
		// 获取当前页行数
		int rowCount = sheet.getLastRowNum();
		// 逐行进行读取
		for(int i=0;i<rowCount;i++){
			// 获取当前行
			Row row = sheet.getRow(i);
			if(null == row){
				continue;
			}
			// 获取当前行的列的总数
			int columnCount = row.getLastCellNum();
			for(int j=0;j<columnCount;j++){
				Cell cell = row.getCell(j);
				XSSFCellStyle style = (XSSFCellStyle)cell.getCellStyle();
				CellRangeAddress cra =  getRangeAddress(sheet,i,j);
				if(null != cra){
					System.out.print("合并节点，数值在："+cra.getFirstRow()+":"+cra.getFirstColumn());
					continue;
				}
				if(null != cell){
					CellType type = cell.getCellTypeEnum();
					if(type == CellType.STRING){
						System.out.print("String:"+cell.getStringCellValue());
						// System.out.print("String:"+cell.getRichStringCellValue().getString());
					}
					// 公式型
					else if(type == CellType.FORMULA){
						System.out.print("公式型:"+cell.getCellFormula()+":"+df.format(fe.evaluate(cell).getNumberValue()));
					}
					// 数值型// 日期型也归为这一类
					else if(type == CellType.NUMERIC){
						// 日期型
						if(DateUtil.isCellDateFormatted(cell)){
							System.out.print("日期型："+format.format(cell.getDateCellValue()));
						}
						else if("General".equals(cell.getCellStyle().getDataFormatString())){
							System.out.print("文本型："+df.format(cell.getNumericCellValue()));
						}
						else{
							System.out.print("未解析类型："+cell.getRichStringCellValue().toString());
						}
					}
					else if(type == CellType.BOOLEAN){
						System.out.print(cell.getBooleanCellValue());
					}
					// 错误类型
					else if(type == CellType.ERROR){
						System.out.print("错误值为:"+cell.getErrorCellValue());
					}
					// 空值类型
					else if(type == CellType.BLANK){
						System.out.print("空白内容");
					}
					// 
					else if(type == CellType._NONE){
						System.out.print("null");
					}
					else{
						System.out.print("Read Error");
					}
				}
			}
			System.out.println();
		}
		
	}
	
	public static CellRangeAddress getRangeAddress(Sheet sheet,int row,int column){
		int len = sheet.getNumMergedRegions();
		for(int i=0;i<len;i++){
			CellRangeAddress cra = sheet.getMergedRegion(i);
			boolean rowInBool = row >= cra.getFirstRow() && row <= cra.getLastRow();
			boolean colInBool = column >= cra.getFirstColumn() && column <= cra.getLastColumn();
			if(rowInBool && colInBool){
				return cra;
			}
		}
		return null;
	}
}

/**
 * Java POI 流式读取代码示例记录
 * 非商业用途的个人代码
 */
package top.yangcourage.test.poi;

import java.io.InputStream;
import java.util.Iterator;

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.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import org.xml.sax.SAXException;

/**
 *
 */
public class PoiReadByStream {
	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);

        
        // 获得第一个sheet
        InputStream sheet2 = r.getSheet("rId1");
        InputSource sheetSource = new InputSource(sheet2);
        parser.parse(sheetSource);
        sheet2.close();
    }

	public void processFirstSheet(String filename) throws Exception {
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader r = new XSSFReader( pkg );
        SharedStringsTable sst = r.getSharedStringsTable();

        XMLReader parser = fetchSheetParser(sst);

        // 获得第一个sheet
        InputStream sheet2 = null;
        
        Iterator< InputStream > sheets = r.getSheetsData();
        if(sheets.hasNext()) sheet2  = sheets.next();
        
        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;
    }

    /**
     * 处理sax的handler
     */
    private static class SheetHandler extends DefaultHandler {
        private SharedStringsTable sst;
        private String lastContents;
        private boolean nextIsString;

        private SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }

        //元素开始时的handler
        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {
        	 if ( "inlineStr".equals( name ) || "v".equals( name ) )
             {
                // Clear contents cache
        		// 获取单元格类型
                 String cellType = attributes.getValue("t");
                 if(cellType != null && cellType.equals("s")) {
                     nextIsString = true;
                 } else {
                     nextIsString = false;
                 }
             }
             else
        	// c => 单元格
            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;
                }
                
            	/*String cellType = attributes.getValue( "t" );
                String rowStr = attributes.getValue( "r" );
                String cellStyleStr = attributes.getValue( "s" );

                int i=0;
                if ( "b".equals( cellType ) ) {
                	i++;
                }
                else if ( "e".equals( cellType ) ) {
                	i++;
                }
                else if ( "inlineStr".equals( cellType ) ){
                	i++;
                }
                else if ( "s".equals( cellType ) ){
                	i++;
                }
                else if ( "str".equals( cellType ) ){
                	i++;
                }
                else if ( cellStyleStr != null ){
                	i++;
                }*/
            }
            lastContents = "";
        }

        //元素结束时的handler
        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;
            }

            // v => 单元格内容
            if(name.equals("v")) {
                System.out.println(lastContents);
            }
        }

        //读取元素间内容时的handler
        public void characters(char[] ch, int start, int length)
                throws SAXException {
            lastContents += new String(ch, start, length);
        }
    }

    public static void main(String[] args) {
    	PoiReadByStream example = new PoiReadByStream();
        try {
			example.processFirstSheet("X:\\xxx\xxx.xlsx");
		} catch (Exception e) {
			e.printStackTrace();
		}
    }
}