java使用poi操作Excel

package com.ykp.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;

/**
 * @function:Excel数据读取工具类，POI实现，兼容Excel2003，及Excel2007
 * @date:2015年8月21日 上午11:19:53
 * @Email:yan095650@163.com
 * @Author:yankunpeng
 */
public class ExcelUtils {
    private static Logger log = Logger.getLogger(ExcelUtils.class);

    Workbook wb = null;
    List<String[]> dataList = new ArrayList<String[]>(100);

    public ExcelUtils(String path) {
        try {
            InputStream inp = new FileInputStream(path);
            wb = WorkbookFactory.create(inp);
        } catch (FileNotFoundException e) {
            log.error(e);
        } catch (InvalidFormatException e) {
            log.error(e);
        } catch (IOException e) {
            log.error(e);
        }

    }

    public ExcelUtils(InputStream inp) {
        try {
            wb = WorkbookFactory.create(inp);
        } catch (FileNotFoundException e) {
            log.error(e);
        } catch (InvalidFormatException e) {
            log.error(e);
        } catch (IOException e) {
            log.error(e);
        }

    }

    /**
     * @function:取Excel所有数据，包含header
     * @return_type:List<String[]>
     * @date:2015年8月21日 上午11:21:54
     * @Email:yan095650@163.com
     * @Author:yankunpeng
     */
    public List<String[]> getAllData(int sheetIndex) {
        if (wb != null) {
            int columnNum = 0;
            Sheet sheet = wb.getSheetAt(sheetIndex);
            if (sheet.getRow(0) != null) {
                columnNum = sheet.getRow(0).getLastCellNum() - sheet.getRow(0).getFirstCellNum();
            }
            if (columnNum > 0) {
                for (Row row : sheet) {
                    String[] singleRow = new String[columnNum];
                    int n = 0;
                    for (int i = 0; i < columnNum; i++) {
                        Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BLANK:
                                singleRow[n] = "";
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                singleRow[n] = Boolean.toString(cell.getBooleanCellValue());
                                break;
                            // 数值
                            case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    singleRow[n] = String.valueOf(cell.getDateCellValue());
                                } else {
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    String temp = cell.getStringCellValue();
                                    // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                                    if (temp.indexOf(".") > -1) {
                                        singleRow[n] = String.valueOf(new Double(temp)).trim();
                                    } else {
                                        singleRow[n] = temp.trim();
                                    }
                                }
                                break;
                            case Cell.CELL_TYPE_STRING:
                                singleRow[n] = cell.getStringCellValue().trim();
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                singleRow[n] = "";
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                singleRow[n] = cell.getStringCellValue();
                                if (singleRow[n] != null) {
                                    singleRow[n] = singleRow[n].replaceAll("#N/A", "").trim();
                                }
                                break;
                            default:
                                singleRow[n] = "";
                                break;
                        }
                        n++;
                    }
                    if ("".equals(singleRow[0])) {
                        continue;
                    }// 如果第一行为空，跳过
                    dataList.add(singleRow);
                }
            }
        }

        return dataList;
    }

    /**
     * @function:返回Excel最大行index值，实际行数要加1
     * @return_type:int
     * @date:2015年8月21日 上午11:24:19
     * @Email:yan095650@163.com
     * @Author:yankunpeng
     */
    public int getRowNum(int sheetIndex) {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        return sheet.getLastRowNum();
    }

    /**
     * @function:返回数据的列数
     * @return_type:int
     * @date:2015年8月21日 上午11:24:06
     * @Email:yan095650@163.com
     * @Author:yankunpeng
     */
    public int getColumnNum(int sheetIndex) {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = sheet.getRow(0);
        if (row != null && row.getLastCellNum() > 0) {
            return row.getLastCellNum();
        }
        return 0;
    }

    /**
     * @function:获取某一行数据,rowIndex为0代表header行
     * @return_type:String[]
     * @date:2015年8月21日 上午11:23:27
     * @Email:yan095650@163.com
     * @Author:yankunpeng
     */
    public String[] getRowData(int sheetIndex, int rowIndex) {
        String[] dataArray = null;
        if (rowIndex > this.getColumnNum(sheetIndex)) {
            return dataArray;
        } else {
            dataArray = new String[this.getColumnNum(sheetIndex)];
            return this.dataList.get(rowIndex);
        }

    }

    /**
     * @function:获取某一列数据
     * @return_type:String[]
     * @date:2015年8月21日 上午11:22:23
     * @Email:yan095650@163.com
     * @Author:yankunpeng
     */
    public String[] getColumnData(int sheetIndex, int colIndex) {
        String[] dataArray = null;
        if (colIndex > this.getColumnNum(sheetIndex)) {
            return dataArray;
        } else {
            if (this.dataList != null && this.dataList.size() > 0) {
                dataArray = new String[this.getRowNum(sheetIndex) + 1];
                int index = 0;
                for (String[] rowData : dataList) {
                    if (rowData != null) {
                        dataArray[index] = rowData[colIndex];
                        index++;
                    }
                }
            }
        }
        return dataArray;

    }

    public static void main(String[] args) {
        ExcelUtils excelReader = new ExcelUtils("D:/Users/T007113/Desktop/test.xls");
        List<String[]> list = excelReader.getAllData(0);
        for (String[] strings : list) {
            for (String string : strings) {
                System.out.print(string + " ");
            }
            System.out.println("");
        }

    }
}