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);
		
		
	}

}