Excel(7) : 读取xls或者xlxs及含有公式的xls

参考 :

    Java解析Excel文件（.xlsx和.xls格式均适用）_gxx_csdn的博客-优快云博客_java 解析excel

    https://chenpenghui.iteye.com/blog/1546125

注 : 读取xlxs未测试

package com.aliyun.et.industry.pangang.common.util.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.util.LinkedList;
import java.util.List;

/**
 * @Auther: liyue
 * @Date: 2019/5/30 16:53
 * @Description:
 */
public class ExcelUtil {

    /**
     * 读取xls或者xlxs
     *
     * @return
     */
    public static List<List<String>> readXlsOrXlxs(String path) {
        try {
            File excel = new File(path);
            if (excel.isFile() && excel.exists()) {   //判断文件是否存在

                String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
                Workbook wb;
                //根据文件后缀（xls/xlsx）进行判断
                FileInputStream fis = new FileInputStream(excel);   //文件流对象
                if ("xls".equals(split[1])) {
                    wb = new HSSFWorkbook(fis);
                } else if ("xlsx".equals(split[1])) {
                    wb = new XSSFWorkbook(fis);
                } else {
                    System.out.println("文件类型错误!");
                    return null;
                }

                //开始解析
                //读取sheet 0
                Sheet sheet = wb.getSheetAt(0);

                //第一行是列名，所以不读
                int firstRowIndex = sheet.getFirstRowNum() + 1;
                int lastRowIndex = sheet.getLastRowNum();

                List<List<String>> contents = new LinkedList<>();
                //遍历行
                for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {
                        int firstCellIndex = row.getFirstCellNum();
                        int lastCellIndex = row.getLastCellNum();
                        List<String> hang = new LinkedList<>();
                        //遍历列
                        for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
                            Cell cell = row.getCell(cIndex);
                            if (cell != null) {
                                hang.add(cell.toString());
                            }
                        }
                        contents.add(hang);
                    }
                }
                return contents;
            } else {
                System.out.println("找不到指定的文件");
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 读取xls,支持读取带公式的单元格
     *
     * @param path
     * @return
     */
    public static List<List<String>> readXls(String path) {
        try {
            File excel = new File(path);
            //判断文件是否存在
            if (excel.isFile() && excel.exists()) {
                HSSFWorkbook wb;
                //文件流对象
                FileInputStream fis = new FileInputStream(excel);
                wb = new HSSFWorkbook(fis);
                // 判断第二行是否含有公式
                Boolean havaFuction = isHavaFuction(wb);
                HSSFFormulaEvaluator evaluator = null;
                if (havaFuction) {
                    evaluator = new HSSFFormulaEvaluator(wb);

                }
                //开始解析
                //读取sheet 0
                Sheet sheet = wb.getSheetAt(0);
                //第一行是列名，所以不读
                int firstRowIndex = sheet.getFirstRowNum() + 1;
                int lastRowIndex = sheet.getLastRowNum();
                List<List<String>> contents = new LinkedList<>();
                //遍历行
                for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
                    Row row = sheet.getRow(rIndex);
                    if (row != null) {
                        int firstCellIndex = row.getFirstCellNum();
                        int lastCellIndex = row.getLastCellNum();
                        List<String> hang = new LinkedList<>();
                        //遍历列
                        for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
                            Cell cell = row.getCell(cIndex);
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        String value = evaluator.evaluate(cell).getStringValue();
                                        hang.add(value != null ? value : "");
                                        break;
                                    default:
                                        hang.add(cell.toString());
                                        break;
                                }
                            } else {
                                hang.add("");
                            }
                        }
                        contents.add(hang);
                    }
                }
                return contents;
            } else {
                System.out.println("找不到指定的文件");
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 判断第二行是否含有公式
     *
     * @param wb
     * @return
     */
    private static Boolean isHavaFuction(HSSFWorkbook wb) {
        try {
            //读取sheet 0
            Sheet sheet = wb.getSheetAt(0);
            //第一行是列名，所以不读
            int firstRowIndex = sheet.getFirstRowNum() + 1;
            int lastRowIndex = sheet.getLastRowNum();
            //遍历行
            for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
                Row row = sheet.getRow(rIndex);
                if (row != null) {
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    //遍历列
                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
                        Cell cell = row.getCell(cIndex);
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_FORMULA:
                                return Boolean.TRUE;
                            default:
                                break;
                        }
                    }
                    return Boolean.FALSE;
                }
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>

END。