读取指定目录下的excel表格

150 阅读 0 评论 99 点赞

我是靠谱客的博主复杂牛排，这篇文章主要介绍读取指定目录下的excel表格，现在分享给大家，希望可以做个参考。

接到一个小需求,读取指定目录下的excel表格

我们这边用的是springboot来创建工程

复制代码

1
2
3
4
5
6
7
public static void main(String[] args) throws IOException {
ConfigurableApplicationContext context = SpringApplication.run(ReadExcelApplication.class, args);
String excelPath = context.getEnvironment().getProperty("excel");
String outPath = context.getEnvironment().getProperty("outFile");
ReadExcel.getXlsxFile(excelPath,outPath);
}

然后我们在properties文件中配置地址

复制代码

1
2
3
4
excel=D:\readFile
# 四个反斜杠转义过来就是两个反斜杠
outFile=D:\\aaa

然后是获取指定目录下的文件夹

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

//从properties中获取的文件路径excelPath
File file = new File(excelPath);
if (!file.exists()){
System.out.println("文件不存在");
}
ArrayList<String> arrayList = new ArrayList<>();
File[] listFiles = file.listFiles();
//excel文件路径
String excelPaths = null;
//文件夹名称
String filePath = null;
for (int i = 0; i < listFiles.length; i++) {
if (listFiles[i].isFile()){
excelPaths = listFiles[i].getPath();
}
if (listFiles[i].isDirectory()){
filePath = listFiles[i].getPath();
}
}
File readFile = new File(filePath);
File[] files = readFile.listFiles();
for (int i = 0; i < files.length; i++) {
String path = files[i].getPath();
//这是文件夹中的数据
arrayList.add(path);
}
//文件后缀
String[] split = excelPaths.split("\.");
String fileSuffix = split[1];
InputStream inputStream = null;
try {
inputStream = new FileInputStream(excelPaths);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
Workbook workBook = getWorkBook(inputStream, fileSuffix);
//接下来就是读excel表格

判断excel的类型,是xsl还是xlsx类型

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public static Workbook getWorkBook(InputStream inputStream, String fileSuffix) {
//创建工作簿
Workbook workbook = null;
try {
workbook = WorkbookFactory.create(inputStream);
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
if ("xls".equals(fileSuffix)){
try {
workbook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}else if ("xlsx".equals(fileSuffix)){
try {
workbook = new XSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
return workbook;
}

接下来开始读excel表格

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
//开始读excel表格
List<String> strings = new ArrayList<>();
if (workBook != null){
for (int sheetNum
= 0; sheetNum
< workBook.getNumberOfSheets(); sheetNum ++) {
//获得当前的sheet工作表
Sheet sheet = workBook.getSheetAt(sheetNum);
if (sheet == null){
continue;
}
//获得当前sheet开始行
int firstRowNum = sheet.getFirstRowNum();
//获取当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//循环所有的行
for (int rowNum = firstRowNum ;rowNum <= lastRowNum; rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if (row == null){
continue;
}
//获得当前行的开始列
short firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
//循环当前行
for (int cellNum = firstCellNum;cellNum <= lastCellNum; cellNum++){
Cell cell = row.getCell(cellNum);
//数据的处理
String cellValue = getCellValue(cell);
if (cellValue != ""){
//这是excel中的数据
strings.add(cellValue);
}
}
}
}
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}

读的时候还要对数据进行处理

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
public static String getCellValue(Cell cell){
String cellValue = "";
if (cell == null){
return cellValue;
}
//如果当前单元格内容为日期类型,需要特殊处理
String dataFormatString = cell.getCellStyle().getDataFormatString();
if (dataFormatString.equals("m/d/yy")){
cellValue = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
return cellValue;
}
//把数字当字符串来读
if (cell.getCellType() == CELL_TYPE_NUMERIC){
cell.setCellType(CELL_TYPE_STRING);
}
//判断数据类型
switch (cell.getCellType()){
case CELL_TYPE_NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case CELL_TYPE_STRING :
cellValue = String.valueOf(cell.getStringCellValue());
break;
case CELL_TYPE_BOOLEAN :
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case CELL_TYPE_FORMULA:
cellValue = String.valueOf(cell.getCellFormula());
break;
case CELL_TYPE_BLANK:
cellValue = "";
break;
case CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}