Java 操作 Excel (读取Excel2003 2007，Poi实现)

一. Apache POI 简介（ http://poi.apache.org/）

使用Java程序读写Microsoft Office，提供了下面这几种类型：

HSSF－提供读写Microsoft Excel XLS格式档案的功能。
XSSF－提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF－提供读写Microsoft Word DOC格式档案的功能。
HSLF－ 供读写Microsoft PowerPoint格式档案的功能。
HDGF－提供读Microsoft Visio格式档案的功能。
HPBF－提供读Microsoft Publisher格式档案的功能。


二、POI操作Excel


1. 官方快速帮助：http://poi.apache.org/spreadsheet/quick-guide.html

2. 导入包：poi-3.6.jar


参考：



1. http://www.blogjava.net/vwpolo/archive/2009/09/16/295243.html

2. http://hacker-zxf.iteye.com/blog/746546

3. http://zmx.iteye.com/blog/622536

4. http://canfly2010.iteye.com/blog/701726

package excel.poi.input;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.POITextExtractor;
import org.apache.poi.extractor.ExtractorFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.XmlException;
public class ReadExcel {
	/**
	 * 读取office 2003 xls
	 * @param filePath
	 */
 @SuppressWarnings({ "unchecked", "deprecation" })
public void loadXls(String filePath){
	  try {
		   InputStream input = new FileInputStream("D:\\test.xls");
		   POIFSFileSystem fs = new POIFSFileSystem(input);
		   HSSFWorkbook wb = new HSSFWorkbook(fs);
		   HSSFSheet sheet = wb.getSheetAt(0);
		   // Iterate over each row in the sheet
		   Iterator rows = sheet.rowIterator();
		   while (rows.hasNext()) {
		    HSSFRow row = (HSSFRow) rows.next();
		    System.out.println("Row #" + row.getRowNum());
		    // Iterate over each cell in the row and print out the cell"s
		    // content
		    Iterator cells = row.cellIterator();
		    while (cells.hasNext()) {
		     HSSFCell cell = (HSSFCell) cells.next();
		     System.out.println("Cell #" + cell.getCellNum());
		     switch (cell.getCellType()) {
		     case HSSFCell.CELL_TYPE_NUMERIC:
		      System.out.println(cell.getNumericCellValue());
		      break;
		     case HSSFCell.CELL_TYPE_STRING:
		      System.out.println(cell.getStringCellValue());
		      break;
		     case HSSFCell.CELL_TYPE_BOOLEAN:
		      System.out.println(cell.getBooleanCellValue());
		      break;
		     case HSSFCell.CELL_TYPE_FORMULA:
		      System.out.println(cell.getCellFormula());
		      break;
		     default:
		      System.out.println("unsuported sell type");
		      break;
		     }
		    }
		   }
		  } catch (IOException ex) {
		   ex.printStackTrace();
		  }
 }
 /**
  * 读取xlsx文本
  * @param filePath
  */
 public void loadXlsxText(String filePath){
	 File inputFile = new File("D:\\test.xlsx");   
	 try {
		POITextExtractor extractor = ExtractorFactory.createExtractor(inputFile);
		System.out.println(extractor.getText());
	} catch (InvalidFormatException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	} catch (OpenXML4JException e) {
		e.printStackTrace();
	} catch (XmlException e) {
		e.printStackTrace();
	}   
 }
 /**
  * 读取office 2007 xlsx
  * @param filePath
  */
 public void loadXlsx(String filePath){
	 // 构造 XSSFWorkbook 对象，strPath 传入文件路径   
	XSSFWorkbook xwb = null;
	try {
		xwb = new XSSFWorkbook("D:\\test.xlsx");
	} catch (IOException e) {
		System.out.println("读取文件出错");
		e.printStackTrace();
	}   
	 // 读取第一章表格内容   
	 XSSFSheet sheet = xwb.getSheetAt(0);   
	 xwb.getSheetAt(1);
	// 定义 row、cell   
	 XSSFRow row;   
	 String cell;   
	 // 循环输出表格中的内容   
	 for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {   
	     row = sheet.getRow(i);   
	     
	     for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {   
	        // 通过 row.getCell(j).toString() 获取单元格内容，   
	        cell = row.getCell(j).toString();  
	        System.out.print(cell + "\t");   
	    }   
	     System.out.println("");   
	 }   
 }
 public static void main(String[] args) {
	 ReadExcel readExcel =new ReadExcel();
	 readExcel.loadXlsx("");
 }
}

但是：Workbook wb = WorkbookFactory.create(new FileInputStream(FILE_URL));

查看Create函数的源代码：

/**
 * Creates the appropriate HSSFWorkbook / XSSFWorkbook from
 * the given InputStream.
 * Your input stream MUST either support mark/reset, or
 * be wrapped as a {@link PushbackInputStream}!
 */
public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
// If clearly doesn't do mark/reset, wrap up
if(! inp.markSupported()) {
inp = new PushbackInputStream(inp, 8);
}

if(POIFSFileSystem.hasPOIFSHeader(inp)) {
return new HSSFWorkbook(inp);
}
if(POIXMLDocument.hasOOXMLHeader(inp)) {
return new XSSFWorkbook(OPCPackage.open(inp));
}
throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
}

你会发现，这里，已经给予Office2003,2007有了判断，对应不用的版本，调用哪个不同的方法，所以，建议，用这种，不过也可以分开写

，如上面的，分成两个函数写，也可以！！

评论

1 楼 wdhxinxin 2011-10-08