Java读取Excel文件(支持xls,xlsx,多sheet)
程序员文章站
2022-07-13 12:57:57
...
Java读取Excel文件(支持xls,xlsx,多sheet)
1. pom.xml依赖
<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. 工具类封装
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;
}
/**
* 读取Excel xlsx后缀名文件数据
*
* @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.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;
}
/**
* 读取excel xls后缀名文件
*
* @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.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
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);
});
});
}
}
上一篇: Excel导出多sheet
推荐阅读
-
Java读取Excel数据内容,兼容excel2003和excel2007版本/xls后缀,xlsx后缀
-
java导出多sheet的excel文件
-
java编程EXCEL导出,支持多sheet页导出
-
Java读取Excel文件(支持xls,xlsx,多sheet)
-
java导入Excel包含多张sheet表的.xls .xslx .txt三种后缀格式的文件
-
java导入Excel包含多张sheet表的.xls .xslx .txt三种后缀格式的文件
-
java使用poi(XSSFWorkbook)读取excel(.xlsx)文件
-
Java中通过POI读取Excel 2003 - 2007的xls,xlsx格式
-
Java读取Excel的方法,解析xls、xlsx两种格式
-
Java读取Excel的方法,解析xls、xlsx两种格式