POI3.5讀取Excel2007

環境和所需包：

       1，JDK1.5

       2，poi-3.5-FINAL-20090928.jar，

            poi-contrib-3.5-FINAL-20090928.jar，

            poi-ooxml-3.5-FINAL-20090928.jar，

            poi-scratchpad-3.5-FINAL-20090928.jar，

            log4j-1.2.13.jar，

            commons-logging-1.1.jar，

            junit-3.8.1.jar，

            dom4j-1.6.1.jar，

            geronimo-stax-api_1.0_spec-1.0.jar，

            ooxml-schemas-1.0.jar，

            xmlbeans-2.3.0.jar

            注意 ：

                    1，可能有些包不需要，沒有測試，因為有些包項目中已經存在了

                    2，我開始少了最後2個包，報：

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException

Java代碼：

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POIExcelDemo {

	public static void read(String fileName) throws Exception {
		
        XSSFWorkbook wb = new XSSFWorkbook(fileName);
        read(wb);
	}
	
	public static void read(InputStream is) throws Exception {
		XSSFWorkbook wb = new XSSFWorkbook(is);
		read(wb);
	}
	
	public static void read(XSSFWorkbook xwb) throws Exception {
		try {
			
			for (int k = 0; k < xwb.getNumberOfSheets(); k++) {

				XSSFSheet sheet = xwb.getSheetAt(k);
				int rows = sheet.getPhysicalNumberOfRows();

				for (int r = 0; r < rows; r++) {
					// 定義 row
					XSSFRow row = sheet.getRow(r);
					if (row != null) {
						int cells = row.getPhysicalNumberOfCells();

						for (short c = 0; c < cells; c++) {
							XSSFCell cell = row.getCell(c);
							if (cell != null) {
								String value = null;

								switch (cell.getCellType()) {

								case XSSFCell.CELL_TYPE_FORMULA:
									value = "FORMULA ";
									break;

								case XSSFCell.CELL_TYPE_NUMERIC:
									if(HSSFDateUtil.isCellDateFormatted(cell)){
										value = "DATE value="
											+ cell.getDateCellValue();
									}else{
										value = "NUMERIC value="
												+ cell.getNumericCellValue();
									}
									
									break;

								case XSSFCell.CELL_TYPE_STRING:
									value = "STRING value="
											+ cell.getStringCellValue();
									break;
									
								case XSSFCell.CELL_TYPE_BOOLEAN:
									value = "BOOLEAN value="
											+ cell.getBooleanCellValue();
									
									
									cell.getDateCellValue();
									
									break;

								default:
								}
								
								System.out.println(value);

							}
						}
					}
				}
			}
		} catch (Exception e) {

			e.printStackTrace();
		}

	}

	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub

		File f = new File("d:/Test.xlsx");
		
		FileInputStream is = new FileInputStream(f);
		
		System.out.println(f.getName());
		
		read(is);
		
		
	}

}
           

寫完之後完把檔案改成讀取test.xls(Excel2003)，發現出現Exception in thread "main" org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'C:\DOCUME~1\CHENXI~1\LOCALS~1\Temp\poifiles\poi-ooxml-1966473540.tmp'

    at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:102)

是以為了相容讀取2003，我把代碼給成如下：

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;

public class POIExcelDemo {

	/**
	 * 
	 * @param fileName 檔案路徑
	 * @param flag 是2003還是2007 true：2003，false：2007
	 * @throws Exception
	 */
	public static void read(String fileName,boolean flag) throws Exception {
		Workbook wb = null;
		if(flag){//2003
			File f = new File(fileName);
			
			FileInputStream is = new FileInputStream(f);
			POIFSFileSystem fs = new POIFSFileSystem(is);   
			wb = new HSSFWorkbook(fs);
			is.close();
		}else{//2007
			wb = new XSSFWorkbook(fileName);
		}
        
        read(wb);
	}
	
	/**
	 * 
	 * @param is 輸入流
	 * @param flag 是2003還是2007 true：2003，false：2007
	 * @throws Exception
	 */
	public static void read(InputStream is,boolean flag) throws Exception {
		Workbook wb = null;
		
		if(flag){//2003
			wb = new HSSFWorkbook(is);
		}else{//2007
			wb = new XSSFWorkbook(is);
		}
		
		read(wb);
	}
	
	/**
	 * 具體讀取Excel
	 * @param wb
	 * @throws Exception
	 */
	public static void read(Workbook wb) throws Exception {
		try {
			
			for (int k = 0; k < wb.getNumberOfSheets(); k++) {

				//sheet
				Sheet sheet = wb.getSheetAt(k);
				int rows = sheet.getPhysicalNumberOfRows();

				for (int r = 0; r < rows; r++) {
					// 定義 row
					Row row = sheet.getRow(r);
					if (row != null) {
						int cells = row.getPhysicalNumberOfCells();

						for (short c = 0; c < cells; c++) {
							Cell cell = row.getCell(c);
							if (cell != null) {
								String value = null;

								switch (cell.getCellType()) {

								case Cell.CELL_TYPE_FORMULA:
									value = "FORMULA value=" + cell.getCellFormula();
									break;

								case Cell.CELL_TYPE_NUMERIC:
									if(HSSFDateUtil.isCellDateFormatted(cell)){
										value = "DATE value="
											+ cell.getDateCellValue();
									}else{
										value = "NUMERIC value="
												+ cell.getNumericCellValue();
									}
									
									break;

								case Cell.CELL_TYPE_STRING:
									value = "STRING value="
											+ cell.getStringCellValue();
									break;
									
								case Cell.CELL_TYPE_BOOLEAN:
									value = "BOOLEAN value="
											+ cell.getBooleanCellValue();
									
									
									cell.getDateCellValue();
									
									break;

								default:
								}
								
								System.out.println(value);

							}
						}
					}
				}
			}
		} catch (Exception e) {

			e.printStackTrace();
		}

	}

	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub

		File f = new File("d:/test.xlsx");
		
		FileInputStream is = new FileInputStream(f);
		
		System.out.println(f.getName());
		
		read(is,false);
		
		
	}

}