需要的jar:
[XML] 纯文本查看 复制代码
1
2
3
4
5
|
< dependency >
< groupId >org.apache.poi</ groupId >
< artifactId >poi-ooxml</ artifactId >
< version >4.1.0</ version >
</ dependency >
|
java代码:
[Java] 纯文本查看 复制代码
01
02
03
04
05
06
07
08
09
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
public static String importExcel(String filePath) throws Exception {
//判断文件
if (filePath != null && ! "" .equals(filePath)) {
File file = new File(filePath);
//判断格式
if (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)) {
//创建输入流对象
InputStream is = new FileInputStream(file);
Workbook workbook = null ;
//判断excel版本号
if (file.getName().endsWith(EXCEL_XLS)) {
workbook = new HSSFWorkbook(is);
} else if (file.getName().endsWith(EXCEL_XLSX)) {
workbook = new XSSFWorkbook(is);
}
Map<String, Object> objectMap= new HashMap<>();
//循环表格(sheet)
for ( int i = 0 ; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
//判断sheet是否有数据
if (sheet.getPhysicalNumberOfRows() <= 0 ) {
continue ;
}
//存放集合
List<Map<String, Object>> list = new ArrayList<>();
//存放表头名字(第一行的数据)
List<String> header = new ArrayList<>();
for ( int x = 0 ; x < sheet.getRow( 0 ).getLastCellNum(); x++) {
Cell cell = sheet.getRow( 0 ).getCell(x);
String value = cell.getStringCellValue();
header.add(value);
}
//获取行并进行循环
for ( int j = 1 ; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
//判断row是否有数据
if (row.getPhysicalNumberOfCells() <= 0 ) {
continue ;
}
//存放数据
Map<String, Object> map = new HashMap<>();
//获取单元格并进行循环
for ( int k = 0 ; k < row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
if (cell == null || cell.toString().trim().equals( "" )) {
continue ;
}
CellType cellType = cell.getCellTypeEnum();
//存放值
String cellValue = "" ;
//字符串
if (cellType == CellType.STRING) {
cellValue = cell.getStringCellValue().trim();
cellValue = StringUtil.isNullOrEmpty(cellValue) ? "" : cellValue;
}
//数据格式
if (cellType == CellType.NUMERIC) {
//判断日期类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat dff = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
String date1 = dff.format(cell.getDateCellValue());
cellValue = date1;
} else {
//设置数据格式("#.######"是几位小数)
cellValue = new DecimalFormat( "#.######" ).format(cell.getNumericCellValue());
}
}
if (cellType == CellType.BOOLEAN) {
cellValue = String.valueOf(cell.getBooleanCellValue());
}
//添加数据到map
map.put(header.get(k), cellValue);
}
//把map数据添加到list
list.add(map);
}
objectMap.put(sheet.getSheetName(),list);
}
return objectMap.toString();
} else {
return "文件不是excel" ;
}
} else {
return "文件不存在" ;
}
}
|