需要引用到的包:
<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 "";
}
}
}