欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-07-13 12:51:37
...

一. 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有了判断,对应不用的版本,调用哪个不同的方法,所以,建议,用这种,不过也可以分开写
,如上面的,分成两个函数写,也可以!!