读Excel工具类

package com.supermarket.goods.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.util.*;

/**
 * @author wp
 * @version 1.0
 * @des read excel
 * @date 2020/11/5 9:46
 */
public class ExcelUtil {

    public static List<String> getColumns(String filePath, String sheetName) {
        try {
            if (filePath.endsWith(".xls")) {
                return getColumnsFromXLS(filePath, sheetName);
            } else if (filePath.endsWith(".xlsx")) {
                return getColumnsFromXLSX(filePath, sheetName);
            } else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 根据sheet名称转为map
     * @param filePath
     * @return
     */
    public static Map<String, List<String>> getColumns(String filePath) {
        try {
            if (filePath.endsWith(".xls")) {
                return getColumnsFromXLS(filePath);
            } else if (filePath.endsWith(".xlsx")) {
                return getColumnsFromXLSX(filePath);
            } else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 根据sheet名称转为map  xls类型
     * @param filePath
     * @return
     */
    private static Map<String, List<String>> getColumnsFromXLS(String filePath) throws IOException {

        Map<String, List<String>> columns = new LinkedHashMap<>();

        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));
        Iterator<Sheet> sheets = wb.sheetIterator();

        while (sheets.hasNext()) {

            HSSFSheet sheet = (HSSFSheet) sheets.next();
            String sheetName = sheet.getSheetName();
            List<String> sheetColumns = new ArrayList<>();
            HSSFRow row;
            HSSFCell cell;
            Iterator<Row> rows = sheet.rowIterator();
            if (rows.hasNext()) {
                row = (HSSFRow) rows.next();
                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    cell = (HSSFCell) cells.next();
                    if (cell.getCellType() == CellType.STRING) {
                        String column = cell.getStringCellValue();
                        sheetColumns.add(column);
                    } else {
                        continue;
                    }
                }
            }
            columns.put(sheetName, sheetColumns);
        }
        if (wb != null)
            wb.close();

        return columns;
    }

    /**
     * 根据sheet名称转为map  xlsx类型
     * @param filePath
     * @return
     */
    private static Map<String, List<String>> getColumnsFromXLSX(String filePath) throws IOException {

        Map<String, List<String>> columns = new LinkedHashMap<>();

        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
        Iterator<Sheet> sheets = wb.sheetIterator();

        while (sheets.hasNext()) {

            XSSFSheet sheet = (XSSFSheet) sheets.next();
            String sheetName = sheet.getSheetName();
            List<String> sheetColumns = new ArrayList<>();
            XSSFRow row;
            XSSFCell cell;
            Iterator<Row> rows = sheet.rowIterator();
            if (rows.hasNext()) {
                row = (XSSFRow) rows.next();
                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    cell = (XSSFCell) cells.next();
                    if (cell.getCellType() == CellType.STRING) {
                        String column = cell.getStringCellValue();
                        sheetColumns.add(column);
                    } else {
                        continue;
                    }
                }
            }
            columns.put(sheetName, sheetColumns);
        }
        if (wb != null)
            wb.close();

        return columns;
    }

    /**
     * 读取文件某个sheet下的数据  xlsx类型
     * @param filePath
     * @param sheetName
     * @return
     * @throws IOException
     */
    private static List<String> getColumnsFromXLSX(String filePath, String sheetName) throws IOException {

        List<String> columns = new ArrayList<>();

        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
        Iterator<Sheet> sheets = wb.sheetIterator();

        while (sheets.hasNext()) {

            XSSFSheet sheet = (XSSFSheet) sheets.next();
            if(sheet.getSheetName().equals(sheetName)) {
                XSSFRow row;
                XSSFCell cell;
                Iterator<Row> rows = sheet.rowIterator();
                if (rows.hasNext()) {
                    row = (XSSFRow) rows.next();
                    Iterator<Cell> cells = row.cellIterator();
                    while (cells.hasNext()) {
                        cell = (XSSFCell) cells.next();
                        if (cell.getCellType() == CellType.STRING) {
                            String column = cell.getStringCellValue();
                            columns.add(column);
                        } else {
                            continue;
                        }
                    }
                }
            }
        }
        if (wb != null)
            wb.close();

        return columns;
    }

    /**
     * 读取文件某个sheet下的数据  xls类型
     * @param filePath
     * @param sheetName
     * @return
     * @throws IOException
     */
    private static List<String> getColumnsFromXLS(String filePath, String sheetName) throws IOException {

        List<String> columns = new ArrayList<>();

        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));
        Iterator<Sheet> sheets = wb.sheetIterator();

        while (sheets.hasNext()) {

            HSSFSheet sheet = (HSSFSheet) sheets.next();
            if (sheet.getSheetName().equals(sheetName)) {
                HSSFRow row;
                HSSFCell cell;
                Iterator<Row> rows = sheet.rowIterator();
                if (rows.hasNext()) {
                    row = (HSSFRow) rows.next();
                    Iterator<Cell> cells = row.cellIterator();
                    while (cells.hasNext()) {
                        cell = (HSSFCell) cells.next();
                        if (cell.getCellType() == CellType.STRING) {
                            String column = cell.getStringCellValue();
                            columns.add(column);
                        } else {
                            continue;
                        }
                    }
                }
            }
        }
        if (wb != null)
            wb.close();

        return columns;
    }


}