apache POI学习（五）——读取Excel文件

apache POI学习（五）——读取Excel文件

根据官网上的资料和代码，自己改造了一下代码，方便使用：

import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RowRecord;
import org.apache.poi.hssf.record.SSTRecord;

/**
 * This example shows how to use the event API for reading a file.
 */
public class EventExample implements HSSFListener {
	private SSTRecord sstrec;
	private int rowNum = 0;
	private List<String> sheetNames = new ArrayList<>();

	/**
	 * This method listens for incoming records and handles them as required.
	 * 
	 * @param record
	 *            The record that was found while reading.
	 */
	public void processRecord(Record record) {
		switch (record.getSid()) {
		// the BOFRecord can represent either the beginning of a sheet or the
		// workbook
		case BOFRecord.sid:
			BOFRecord bof = (BOFRecord) record;
			if (bof.getType() == BOFRecord.TYPE_WORKBOOK) {
				System.out.println("Encountered workbook");
				// assigned to the class level member
			} else if (bof.getType() == BOFRecord.TYPE_WORKSHEET) {
				System.out.println("Encountered sheet reference ==>" + sheetNames.get(0));
				rowNum = 0;
				sheetNames.remove(0);
			}
			break;
		case BoundSheetRecord.sid:
			BoundSheetRecord bsr = (BoundSheetRecord) record;
			sheetNames.add(bsr.getSheetname());
			System.out.println("New sheet named: " + bsr.getSheetname());
			break;
		case RowRecord.sid:
			/*RowRecord rowrec = (RowRecord) record;
			System.out.println(
					"Row found, first column at " + rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());*/
			rowNum++;
			break;
		// SSTRecords store a array of unique strings used in
		// Excel.不依赖于row和col，直接读取出数据，并且放入数组中
		case SSTRecord.sid:
			sstrec = (SSTRecord) record;
			/*
			 * for (int k = 0; k < sstrec.getNumUniqueStrings(); k++) {
			 * System.out.println("String table value " + k + " = " +
			 * sstrec.getString(k)); }
			 */
			break;
		case LabelSSTRecord.sid:
			LabelSSTRecord lrec = (LabelSSTRecord) record;
			System.out.println("String cell found with value " + sstrec.getString(lrec.getSSTIndex()) + " -->row:"
					+ rowNum + "col:" + new Character((char) (lrec.getColumn() + 65)) + " colNum:" + lrec.getColumn());
			break;
		}
	}
}

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Main {
	public static void main(String[] args){
		// TODO Auto-generated method stub
		// create a new file input stream with the input file specified
        // at the command line
        FileInputStream fin = null;
		try {
			fin = new FileInputStream("d:/123/workbook.xls");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        // create a new org.apache.poi.poifs.filesystem.Filesystem
        POIFSFileSystem poifs = null;
		try {
			poifs = new POIFSFileSystem(fin);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        // get the Workbook (excel part) stream in a InputStream
        InputStream din = null;
		try {
			din = poifs.createDocumentInputStream("Workbook");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        // construct out HSSFRequest object
        HSSFRequest req = new HSSFRequest();
        // lazy listen for ALL records with the listener shown above
        req.addListenerForAllRecords(new EventExample());
        // create our event factory
        HSSFEventFactory factory = new HSSFEventFactory();
        // process our events based on the document input stream
        factory.processEvents(req, din);
        // once all the events are processed close our file input stream
        try {
			fin.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        // and our document input stream (don't want to leak these!)
        try {
			din.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        System.out.println("done.");
	}

}

运行的效果：

下面是测试时使用的Excel文件：

这样，就可以方便的进行Excel的读取了。