JAVA 读取EXCEL文件为List结构

需要引用到的包：

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

    </dependencies>

直接上代码：可以读取带密码的

package com.nece001;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.security.GeneralSecurityException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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;

public class Main {

    public static void main(String[] args) throws FileNotFoundException, IOException, GeneralSecurityException {
        // 设定Excel文件所在路径
        //String excelFileName = "C:\\Users\\nece001\\Desktop\\test.xls";
        //String excelFileName = "C:\\Users\\nece001\\Desktop\\Book1.xlsx";
        String excelFileName = "C:\\Users\\nece001\\Desktop\\ExportOrderList6448257131.xlsx";

        String passwrod = "zxQn1qaO";

        ExcelReader reader = new ExcelReader();
        //reader.setFile(excelFileName);
        reader.setFile(excelFileName, passwrod);
        //List<List<String>> list = reader.getArrayListBySheetIndex(0);
        List<Map<String, String>> list = reader.getKvListBySheetIndex(0);
        if (!reader.isEmpty()) {
            System.out.println(list);
        }
    }

}

class ExcelReader {

    private String password;
    private boolean empty;
    private Workbook workbook;

    public boolean isEmpty() {
        return empty;
    }

    public void setFile(String filename) throws IOException, FileNotFoundException, GeneralSecurityException {
        File file = new File(filename);
        if (!file.exists()) {
            throw new IOException(filename + " not found.");
        }

        workbook = loadWrokbook(file);
    }

    public void setFile(String filename, String password) throws IOException, FileNotFoundException, GeneralSecurityException {
        this.password = password;
        this.setFile(filename);
    }

    public List<Map<String, String>> getKvListBySheetIndex(int index) {
        Sheet sheet = workbook.getSheetAt(index);
        return sheetToKvList(sheet);
    }

    public List<Map<String, String>> getKvListBySheetName(String name) {
        Sheet sheet = workbook.getSheet(name);
        return sheetToKvList(sheet);
    }

    public List<List<String>> getArrayListBySheetIndex(int index) {
        Sheet sheet = workbook.getSheetAt(index);
        return sheetToArrayList(sheet);
    }

    public List<List<String>> getArrayListBySheetName(String name) {
        Sheet sheet = workbook.getSheet(name);
        return sheetToArrayList(sheet);
    }

    private List<List<String>> sheetToArrayList(Sheet sheet) {
        empty = true;
        int firstRowNo = sheet.getFirstRowNum();
        int lastRowNo = sheet.getLastRowNum();
        int phyNo = sheet.getPhysicalNumberOfRows();

        if (phyNo > 0) {
            empty = false;
            List<List<String>> list = new ArrayList<>();
            List<String> array;
            Row row;
            Cell cell;

            boolean firstRow = true;
            int firstCellNo;
            int lastCellNo;
            int phyCellNo;
            String cellValue;
            for (int i = firstRowNo; i < lastRowNo; i++) {
                row = sheet.getRow(i);

                if (null != row) {
                    firstCellNo = row.getFirstCellNum();
                    lastCellNo = row.getLastCellNum();
                    phyCellNo = row.getPhysicalNumberOfCells();

                    array = new ArrayList<>();

                    for (int j = firstCellNo; j < lastCellNo; j++) {
                        cell = row.getCell(j);
                        if (null == cell) {
                            cellValue = "";
                        } else {
                            phyCellNo--;
                            cellValue = getCellStringValue(cell);
                        }

                        array.add(cellValue);

                        if (phyCellNo == 0) {
                            break;
                        }
                    }

                    if (!firstRow) {
                        list.add(array);
                    }
                }

                firstRow = false;
                phyNo--;
                if (phyNo == 0) {
                    break;
                }
            }
            return list;
        }
        return null;
    }

    private List<Map<String, String>> sheetToKvList(Sheet sheet) {
        empty = true;
        int firstRowNo = sheet.getFirstRowNum();
        int lastRowNo = sheet.getLastRowNum();
        int phyNo = sheet.getPhysicalNumberOfRows();

        if (phyNo > 0) {
            empty = false;
            List<Map<String, String>> list = new ArrayList<>();
            Map<String, String> map;
            Row row;
            Cell cell;

            boolean firstRow = true;
            int firstCellNo;
            int lastCellNo;
            int phyCellNo;
            String cellValue;
            List<String> keys = new ArrayList<>();
            int k;
            for (int i = firstRowNo; i < lastRowNo; i++) {
                row = sheet.getRow(i);

                if (null != row) {
                    firstCellNo = row.getFirstCellNum();
                    lastCellNo = row.getLastCellNum();
                    phyCellNo = row.getPhysicalNumberOfCells();

                    map = new HashMap<>();
                    k = 0;

                    for (int j = firstCellNo; j < lastCellNo; j++) {
                        cell = row.getCell(j);
                        if (null == cell) {
                            cellValue = "";
                        } else {
                            phyCellNo--;
                            cellValue = getCellStringValue(cell);
                        }

                        if (firstRow) {
                            keys.add(cellValue);
                        } else {
                            if (k < keys.size()) {
                                map.put(keys.get(k), cellValue);
                                k++;
                            }
                        }

                        if (phyCellNo == 0) {
                            break;
                        }
                    }

                    if (!firstRow) {
                        list.add(map);
                    }
                    firstRow = false;
                }

                phyNo--;
                if (phyNo == 0) {
                    break;
                }
            }
            return list;
        }
        return null;
    }

    private Workbook loadWrokbook(File file) throws FileNotFoundException, IOException, GeneralSecurityException {
        String filename = file.getName();
        String ext = filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();

        FileInputStream inputStream = new FileInputStream(file);
        if (null != password) {
            POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
            inputStream.close();
            EncryptionInfo encInfo = new EncryptionInfo(pfs);
            Decryptor decryptor = Decryptor.getInstance(encInfo);
            decryptor.verifyPassword(password);

            if (ext.equals("xls")) {
                return new HSSFWorkbook(decryptor.getDataStream(pfs));
            } else {
                return new XSSFWorkbook(decryptor.getDataStream(pfs));
            }
        } else {
            if (ext.equals("xls")) {
                return new HSSFWorkbook(inputStream);
            } else {
                return new XSSFWorkbook(inputStream);
            }
        }
    }

    private String getCellStringValue(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                Double d = cell.getNumericCellValue();
                return d.toString();
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                Boolean b = cell.getBooleanCellValue();
                return b.toString();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}