用POI解析EXCEL文件

需要POI3.2支持，支持日期和数字解析:

 

package cn.cnduyi.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;

/**
 * @Title: ExcelReader.java
 * @Package cn.cnduyi.test
 * @Description: TODO(添加描述)
 * @author 杜逸 www.cnduyi.cn
 * @date Sep 6, 2009 3:07:26 PM
 * @version V1.0
 */
public class ExcelReader {
 private HSSFWorkbook wb = null;// book [includes sheet]

 private HSSFSheet sheet = null;

 private HSSFRow row = null;

 private int sheetNum = 0; // 第sheetnum个工作表

 private int rowNum = 0;

 private FileInputStream fis = null;

 private File file = null;

 private int num = 0;

 public ExcelReader() {
 }

 public ExcelReader(File file, Integer num) {
  this.file = file;
  this.num = num;
 }

 public void setRowNum(int rowNum) {
  this.rowNum = rowNum;
 }

 public void setSheetNum(int sheetNum) {
  this.sheetNum = sheetNum;
 }

 public void setFile(File file) {
  this.file = file;
 }

 /**
  * 读取excel文件获得HSSFWorkbook对象
  */
 public void open() throws IOException {
  fis = new FileInputStream(file);
  wb = new HSSFWorkbook(new POIFSFileSystem(fis));
  fis.close();
 }

 /**
  * 返回sheet表数目
  *
  * @return int
  */
 public int getSheetCount() {
  int sheetCount = -1;
  sheetCount = wb.getNumberOfSheets();
  return sheetCount;
 }

 /**
  * sheetNum下的记录行数
  *
  * @return int
  */
 public int getRowCount() {
  if (wb == null)
   System.out.println("=============>WorkBook为空");
  HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
  int rowCount = -1;
  rowCount = sheet.getLastRowNum();
  return rowCount;
 }

 /**
  * 读取指定sheetNum的rowCount
  *
  * @param sheetNum
  * @return int
  */
 public int getRowCount(int sheetNum) {
  HSSFSheet sheet = wb.getSheetAt(sheetNum);
  int rowCount = -1;
  rowCount = sheet.getLastRowNum();
  return rowCount;
 }

 /**
  * 得到指定行的内容
  *
  * @param lineNum
  * @return String[]
  */
 public String[] readExcelLine(int lineNum) {
  return readExcelLine(this.sheetNum, lineNum);
 }

 /**
  * 指定工作表和行数的内容
  *
  * @param sheetNum
  * @param lineNum
  * @return String[]
  */
 public String[] readExcelLine(int sheetNum, int lineNum) {
  if (sheetNum < 0 || lineNum < 0)
   return null;
  String[] strExcelLine = null;
  try {
   sheet = wb.getSheetAt(sheetNum);
   row = sheet.getRow(lineNum);

   int cellCount = row.getLastCellNum();
   strExcelLine = new String[cellCount + 1];
   for (int i = 0; i <= cellCount; i++) {
    strExcelLine[i] = readStringExcelCell(lineNum, i);
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return strExcelLine;
 }

 /**
  * 读取指定列的内容
  *
  * @param cellNum
  * @return String
  */
 public String readStringExcelCell(int cellNum) {
  return readStringExcelCell(this.rowNum, cellNum);
 }

 /**
  * 指定行和列编号的内容
  *
  * @param rowNum
  * @param cellNum
  * @return String
  */
 public String readStringExcelCell(int rowNum, int cellNum) {
  return readStringExcelCell(this.sheetNum, rowNum, cellNum);
 }

 /**
  * 指定工作表、行、列下的内容
  *
  * @param sheetNum
  * @param rowNum
  * @param cellNum
  * @return String
  */
 public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
  if (sheetNum < 0 || rowNum < 0)
   return "";
  String strExcelCell = "";
  try {
   sheet = wb.getSheetAt(sheetNum);
   row = sheet.getRow(rowNum);

   if (row.getCell(cellNum) != null) { // add this condition
    // judge
    switch (row.getCell(cellNum).getCellType()) {
    case HSSFCell.CELL_TYPE_FORMULA:
     strExcelCell = "FORMULA ";
     break;
    case HSSFCell.CELL_TYPE_NUMERIC:
     if (HSSFDateUtil.isCellDateFormatted(row.getCell(cellNum))) {
      double d = row.getCell(cellNum).getNumericCellValue();
      Date date = HSSFDateUtil.getJavaDate(d);
      Timestamp timestamp = new Timestamp(date.getTime());
      String temp = timestamp.toString();
      if (temp.endsWith("00:00:00.0")) {
       strExcelCell = temp.substring(0, temp
         .lastIndexOf("00:00:00.0"));
      } else if (temp.endsWith(".0")) {
       strExcelCell = temp.substring(0, temp
         .lastIndexOf(".0"));
      } else {
       strExcelCell = timestamp.toString();
      }
     } else {
      long temp = (long) row.getCell(cellNum)
        .getNumericCellValue();
      strExcelCell = String.valueOf(temp);
     }
     break;
    case HSSFCell.CELL_TYPE_STRING:
     strExcelCell = row.getCell(cellNum)
       .getRichStringCellValue().getString();
     break;
    case HSSFCell.CELL_TYPE_BLANK:
     strExcelCell = "";
     break;
    default:
     strExcelCell = "";
     break;
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return strExcelCell;
 }

 // 主函数用于测试
 public static void main(String args[]) {
  File file = new File("e://test2.xls");
  ExcelReader excelReader = null;
  try {
   Class c = Class.forName("cn.cnduyi.test.ExcelReader");
   Integer num = 0;
   Constructor<ExcelReader> constructor = c
     .getConstructor(new Class[] { file.getClass(),
       num.getClass() });
   excelReader = constructor.newInstance(file, num);
  } catch (ClassNotFoundException e1) {
   // TODO Auto-generated catch block
   e1.printStackTrace();
  } catch (SecurityException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (NoSuchMethodException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IllegalArgumentException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (InstantiationException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (InvocationTargetException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  ExcelReader readExcel = excelReader;
  try {
   readExcel.open();
  } catch (IOException e) {
   e.printStackTrace();
  }
  readExcel.setSheetNum(0); // 设置读取索引为0的工作表
  // 总行数
  int count = readExcel.getRowCount();
  for (int i = 0; i <= count; i++) {
   String[] rows = readExcel.readExcelLine(i);
   for (int j = 0; j < rows.length; j++) {
    System.out.print(rows[j] + " ");
   }
   System.out.print("/n");
  }
 }
}