一、導(dǎo)入Jar包、引入MAVEN依賴

1.涉及的jar包: poi、poi-ooxml、poi-ooxml-schemas、xmlbeans、dom4j
2.maven庫(kù): https://mvnrepository.com/
3.依賴引入/Jar包導(dǎo)入

二、文件格式校驗(yàn)

1）判斷Excel文件是否存在

/**
     * 檢查文件是否為excel或者為空
     *
     * @param filePath 文件路徑
     * @return boolean
     */
    public boolean validateExcel(String filePath) {
        //檢查文件格式
        if (!(ReadExcel.isExcel2003(filePath) || ReadExcel.isExcel2007(filePath))) {
            errorInfo = "不是excel格式";
            return false;
        }
        //檢查文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            errorInfo = "文件不存在";
            return false;
        }
        return true;
    }

2）判斷Excel文件類型

/**
     * 判斷是否xls格式
     *
     * @param filePath
     * @return boolean
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 判斷是否xlsx格式
     *
     * @param filePath 文件路徑
     * @return boolean
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

三、Excel解析

1）Excel文件流解析

/**
     * 文件及類型解析
     *
     * @param filePath 文件路徑
     * @return List<List < String>>
     */
    public List<List<String>> read(String filePath) {
        List<List<String>> dataList = new ArrayList<>();
        InputStream is = null;

        if (!validateExcel(filePath)) {
            return null;
        } else {
            try {
                //判斷文件類型
                boolean isExcel2003 = true;
                if (ReadExcel.isExcel2007(filePath)) {
                    isExcel2003 = false;
                }

                //調(diào)用讀取方法
                File file = new File(filePath);
                is = new FileInputStream(file);
                dataList = readStream(is, isExcel2003);
                is.close();
            } catch (Exception e) {
                errorInfo = "文件上傳失敗，請(qǐng)重新上傳";
            }
            return dataList;
        }
    }
/**
     * 文件及類型解析
     *
     * @param inputStream 文件輸入流
     * @param isExcel2003 是否是xls
     * @return List<List < String>>
     * @throws Exception 拋出
     */
    public List<List<String>> readStream(InputStream inputStream, boolean isExcel2003) throws Exception {
        List<List<String>> dataList = null;

        Workbook wb = null;
        if (isExcel2003) {
            //HSSF是POI工程對(duì)Excel 97(-2007)文件操作
            wb = new HSSFWorkbook(inputStream);
        } else {
            //XSSF是POI工程對(duì)Excel 2007 OOXML (.xlsx)文件操作
            wb = new XSSFWorkbook(inputStream);
        }

        dataList = readWork(wb);
        return dataList;
    }

2）Excel解析主方法

/**
     * Excel解析主方法
     *
     * @param wb excel的對(duì)象
     * @return List<List < String>>
     */
    public List<List<String>> readWork(Workbook wb) {
        List<List<String>> dataList = new ArrayList<>();
        //用于記錄不匹配列
        StringBuffer stringBuffer = new StringBuffer();
        //得到第一個(gè)shell
        Sheet sheet = wb.getSheetAt(0);
        this.totalRows = sheet.getPhysicalNumberOfRows();

        if (this.totalRows >= 2 && sheet.getRow(1) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            //校驗(yàn)?zāi)０迨欠裾_
            if (totalCells != title.length) {
                errorInfo = "Excel模板列數(shù)與導(dǎo)入Excel列數(shù)不匹配?。?！";
            } else {
                for (int i = 0; i < title.length; i++) {
                    //第一行列取值
                    String topTitle = String.valueOf(sheet.getRow(0).getCell(i));
                    if (!title[i].equals(topTitle)) {
                        stringBuffer.append("《");
                        stringBuffer.append(topTitle);
                        stringBuffer.append("》");
                        stringBuffer.append(",");
                    }
                }
                if (stringBuffer.length() > 0) {
                    stringBuffer.deleteCharAt(stringBuffer.length() - 1);
                    errorInfo = "Excel模板不存在" + stringBuffer + "列標(biāo)題?。?！";
                }
            }
            //解析Excel
            if (errorInfo == null) {
                //循環(huán)excel的行
                for (int r = 1; r < this.getTotalRows(); r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }
                    //rowList集合對(duì)應(yīng)每一行數(shù)據(jù)
                    List<String> rowList = new ArrayList<>();
                    //循環(huán)excel的列
                    for (int c = 0; c < this.getTotalCells(); c++) {
                        Cell cell = row.getCell(c);
                        String cellValue = "";
                        if (cell != null) {
                            //判斷數(shù)據(jù)類型
                            switch (cell.getCellType()) {
                                //數(shù)字
                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    cellValue = cell.getNumericCellValue() + "";
                                    break;
                                //字符串
                                case HSSFCell.CELL_TYPE_STRING:
                                    cellValue = cell.getStringCellValue();
                                    break;
                                //布爾
                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    cellValue = cell.getBooleanCellValue() + "";
                                    break;
                                //公式
                                case HSSFCell.CELL_TYPE_FORMULA:
                                    cellValue = cell.getCellFormula() + "";
                                    break;
                                //空
                                case HSSFCell.CELL_TYPE_BLANK:
                                    cellValue = "";
                                    break;
                                //故障
                                case HSSFCell.CELL_TYPE_ERROR:
                                    cellValue = "非法字符";
                                    break;
                                default:
                                    cellValue = "未知類型";
                                    break;
                            }
                        }
                        rowList.add(cellValue);
                    }
                    dataList.add(rowList);
                }
                return dataList;
            } else {
                return null;
            }
        } else {
            errorInfo = "請(qǐng)核查Excel是否存在數(shù)據(jù)！??！";
            return null;
        }
    }

四、方法調(diào)用

package com.work.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ReadExcel {
/**
     * 總行數(shù)
     */
    private int totalRows = 0;
    /**
     * 總列數(shù)
     */
    private int totalCells = 0;
    /**
     * 錯(cuò)誤信息
     */
    private String errorInfo = null;
    /**
     * 模板標(biāo)題列
     */
    String[] title = {"姓名", "年齡", "學(xué)歷", "手機(jī)號(hào)", "郵箱", "地址"};

    private ReadExcel() {

    }

    public int getTotalRows() {
        return totalRows;
    }

    public int getTotalCells() {
        return totalCells;
    }

    public String getErrorInfo() {
        return errorInfo;
    }
/**
     * 方法調(diào)用
     */
    public void mainMothd(){
        ReadExcel t = new ReadExcel();
        //地址范例
        String filePath = "C:\\Users\\Administrator\\Desktop\\模板.xlsx";
        List<List<String>> list = t.read(filePath);
        if (list != null) {
            System.out.println("********************正在解析***********************");
            for (int i = 0; i < list.size(); i++) {
                System.out.print("第" + (i + 1) + "行：");
                List<String> cellList = list.get(i);
                for (String s : cellList) {
                    System.out.print("   " + s);
                }
                System.out.println();
            }
            System.out.println("********************解析完成***********************");
        } else {
            System.out.println(t.getErrorInfo());
        }
    }

    /**
     * main方法
     * @param args
     */
    public static void main(String[] args) {
        ReadExcel t = new ReadExcel();
        t.mainMothd();
    }
}