ExcelUtils

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class ExcelUtils {
public static List readExcel(File file, Integer rowCount, Integer columnCount)
throws FileNotFoundException, IOException {

    // 根据后缀名称判断excel的版本
    String fileName = file.getName();
    String extName = fileName.substring(fileName.lastIndexOf("."));
    Workbook wb = null;
    if (".xls".equals(extName)) {
        wb = new HSSFWorkbook(new FileInputStream(file));
    } else if (".xlsx".equals(extName)) {
        wb = new XSSFWorkbook(new FileInputStream(file));
    } else {
        // 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式
        throw new IllegalArgumentException("Invalid excel version");
    }
    // 开始读取数据
    List<ExcelSheet> sheetPOs = new ArrayList<>();
    // 解析sheet
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        List<List<Object>> dataList = new ArrayList<>();
        ExcelSheet sheetPO = new ExcelSheet();
        sheetPO.setSheetName(sheet.getSheetName());
        sheetPO.setDataList(dataList);
        int readRowCount = 0;
        if (rowCount == null || rowCount > sheet.getPhysicalNumberOfRows()) {
            readRowCount = sheet.getPhysicalNumberOfRows();
        } else {
            readRowCount = rowCount;
        }
        // 解析sheet 的行
        for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
            Row row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            if (row.getFirstCellNum() < 0) {
                continue;
            }
            int readColumnCount = 0;
            if (columnCount == null || columnCount > row.getLastCellNum()) {
                readColumnCount = (int) row.getLastCellNum();
            } else {
                readColumnCount = columnCount;
            }
            List<Object> rowValue = new LinkedList<Object>();
            // 解析sheet 的列
            for (int k = 0; k < readColumnCount; k++) {
                Cell cell = row.getCell(k);
                rowValue.add(getCellValue(wb, cell));
            }
            dataList.add(rowValue);
        }
        sheetPOs.add(sheetPO);
    }
    return sheetPOs;
}
private static Object getCellValue(Workbook wb, Cell cell) {
    Object columnValue = null;
    if (cell != null) {
        DecimalFormat df = new DecimalFormat("0");// 格式化 number
        // String
        // 字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
        DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                columnValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    columnValue = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    columnValue = nf.format(cell.getNumericCellValue());
                } else {
                    columnValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                columnValue = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                columnValue = "";
                break;
            case Cell.CELL_TYPE_FORMULA:
                // 格式单元格
                FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
                evaluator.evaluateFormulaCell(cell);
                CellValue cellValue = evaluator.evaluate(cell);
                columnValue = cellValue.getNumberValue();
                break;
            default:
                columnValue = cell.toString();
        }
    }
    return columnValue;
}

}