1. pom.xml dependency
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
2. Tool class packaging
public class ExcelReadUtil {
private static Logger logger = LoggerFactory.getLogger(ExcelReadUtil.class);
public static HashMap<String, ArrayList<ArrayList<String>>> readExcel(File file, int ignoreRow) {
if (file.getName().toLowerCase().endsWith(".xlsx")) {
return readExcelForXlsx(file, ignoreRow);
} else if (file.getName().toLowerCase().endsWith(".xls")) {
return readExcelForXls(file, ignoreRow);
}
return null;
}
/**
* Read Excel xlsx suffix file data
*
* @param file
*/
private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXlsx(File file, int ignoreRow) {
HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>();
if (!file.exists()) {
logger.error("{} file does not exist", file.getName());
return null;
}
int rowSize = 0;
try (BufferedInputStream in = new BufferedInputStream(new FileInputStream(file))) {
XSSFWorkbook workbook = null;
try {
workbook = new XSSFWorkbook(in);
} catch (IOException e) {
e.printStackTrace();
}
XSSFCell cell = null;
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex + + ) {
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
ArrayList<ArrayList<String>> lists = new ArrayList<>();
for (int rowIndex = ignoreRow; rowIndex <= sheet.getLastRowNum(); rowIndex + + ) {
XSSFRow row = sheet.getRow(rowIndex);
if (null == row) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
ArrayList<String> list = new ArrayList<>();
int col = 0;
for (int colIndex = 0; colIndex <= row.getLastCellNum(); colIndex + + ) {
cell = row.getCell(colIndex);
String value = "";
if (cell != null) {
CellType cellType = cell.getCellType();
switch (cellType) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = String.valueOf(cell.getDateCellValue());
} else {
value = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue()));
}
break;
case STRING:
value = String.valueOf(cell.getStringCellValue());
break;
case FORMULA:
value = String.valueOf(cell.getCellFormula());
break;
case BLANK:
value = "";
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
value = String.valueOf(cell.getErrorCellValue());
break;
default:
value = "";
}
if (StringUtils.isNotBlank(value)) {
list.add(value);
} else {
col + + ;
}
}
}
if (col == row.getRowNum()) {
continue;
}
if (list.size() > 0) {
lists.add(list);
}
}
map.put("sheet" + sheetIndex, lists);
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
/**
* Read excel xls suffix file
*
* @param file
* @param ignoreRow
* @return
*/
private static HashMap<String, ArrayList<ArrayList<String>>> readExcelForXls(File file, int ignoreRow) {
HashMap<String, ArrayList<ArrayList<String>>> map = new HashMap<>();
if (!file.exists()) {
logger.error("{} file does not exist", file.getName());
return null;
}
int rowSize = 0;
try {
BufferedInputStream bufferedInputStream = new BufferedInputStream(new FileInputStream(file));
HSSFWorkbook workbook = new HSSFWorkbook(bufferedInputStream);
HSSFCell cell = null;
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex + + ) {
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
ArrayList<ArrayList<String>> lists = new ArrayList<>();
for (int rowIndex = ignoreRow; rowIndex < sheet.getLastRowNum(); rowIndex + + ) {
HSSFRow row = sheet.getRow(rowIndex);
if (null == row) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
ArrayList<String> list = new ArrayList<>();
int col = 0;
for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex + + ) {
cell = row.getCell(colIndex);
String value = "";
if (cell != null) {
CellType cellType = cell.getCellType();
switch (cellType) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = String.valueOf(cell.getDateCellValue());
} else {
value = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue()));
}
break;
case STRING:
value = String.valueOf(cell.getStringCellValue());
break;
case FORMULA:
value = String.valueOf(cell.getCellFormula());
break;
case BLANK:
value = "";
break;
case BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
value = String.valueOf(cell.getErrorCellValue());
break;
default:
value = "";
}
if (StringUtils.isNotBlank(value)) {
list.add(value);
} else {
col + + ;
}
}
}
if (col == row.getRowNum()) {
continue;
}
if (list.size() > 0) {
lists.add(list);
}
}
map.put("sheet" + sheetIndex, lists);
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
}
3. Test class
@Test
public void testExcelRead(){
HashMap<String, ArrayList<ArrayList<String>>> excelReadMap = ExcelReadUtil.readExcel(new File(excelFilePath), 1);
if(excelReadMap != null){
excelReadMap.entrySet().stream().forEach(entry -> {
entry.getValue().stream().forEach(col -> {
col.stream().forEach(System.out::println);
});
});
}
}
Original link:
https://blog.csdn.net/superbeyone/article/details/89158908
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 132039 people are learning the system