maven 依赖
|
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
|
<
dependency
>
<
groupId
>com.fasterxml.jackson.core</
groupId
>
<
artifactId
>jackson-core</
artifactId
>
<
version
>2.0.0</
version
>
</
dependency
>
<
dependency
>
<
groupId
>org.apache.poi</
groupId
>
<
artifactId
>poi</
artifactId
>
<
version
>3.15</
version
>
</
dependency
>
<
dependency
>
<
groupId
>org.apache.poi</
groupId
>
<
artifactId
>poi-ooxml</
artifactId
>
<
version
>3.15</
version
>
</
dependency
>
<
dependency
>
<
groupId
>org.apache.poi</
groupId
>
<
artifactId
>poi-ooxml-schemas</
artifactId
>
<
version
>3.15</
version
>
</
dependency
>
<
dependency
>
<
groupId
>org.apache.xmlbeans</
groupId
>
<
artifactId
>xmlbeans</
artifactId
>
<
version
>2.6.0</
version
>
</
dependency
>
<
dependency
>
<
groupId
>com.fasterxml.jackson.dataformat</
groupId
>
<
artifactId
>jackson-dataformat-xml</
artifactId
>
<
version
>2.0.0</
version
>
</
dependency
>
|
|
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
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
88
89
90
91
92
93
94
95
96
97
98
99
100
|
package
excel;
import
org.apache.poi.ss.usermodel.CellType;
import
org.apache.poi.xssf.usermodel.XSSFCell;
import
org.apache.poi.xssf.usermodel.XSSFRow;
import
org.apache.poi.xssf.usermodel.XSSFSheet;
import
org.apache.poi.xssf.usermodel.XSSFWorkbook;
import
java.io.InputStream;
import
java.text.SimpleDateFormat;
import
java.util.ArrayList;
import
java.util.HashMap;
import
java.util.List;
import
java.util.Map;
/**
* Created by xiaominzh on 2016/11/14.
*/
public
class
ExcelExportNew {
private
static
String getCellValue(XSSFCell cell,String columnType)
throws
Exception{
if
(
"i"
.equals(columnType)){
return
String.valueOf(cell.getNumericCellValue());
}
if
(
"d"
.equals(columnType)){
SimpleDateFormat sdf =
new
SimpleDateFormat(
"yyyy-MM-dd"
);
return
sdf.format(cell.getDateCellValue());
}
cell.setCellType(CellType.STRING);
return
cell.getRichStringCellValue().toString();
}
private
static
List<String> loadColumnNames(XSSFRow row){
List<String> array =
new
ArrayList<String>();
int
maxCellNum = row.getLastCellNum();
for
(
int
i=
0
;i<maxCellNum;i++){
XSSFCell cell = row.getCell(i);
String name = cell.getStringCellValue();
array.add(name);
}
return
array;
}
private
static
List<String> loadColumnTypes(XSSFRow row){
List<String> array =
new
ArrayList<String>();
int
maxCellNum = row.getLastCellNum();
for
(
int
i=
0
;i<maxCellNum;i++){
XSSFCell cell = row.getCell(i);
String name = cell.getStringCellValue();
array.add(name);
}
return
array;
}
private
static
void
convertExcelToJSON(String fileName,String sheetName)
throws
Exception {
InputStream is = ExcelExportNew.
class
.getResourceAsStream(fileName);
XSSFWorkbook hssfWorkbook =
new
XSSFWorkbook(is);
XSSFSheet sheet = hssfWorkbook.getSheet(sheetName);
int
rows = sheet.getLastRowNum();
List<Map<String, Object>> result =
new
ArrayList<Map<String, Object>>();
List<String> columnTypes = loadColumnTypes(sheet.getRow(
0
));
List<String> columnNames = loadColumnNames(sheet.getRow(
1
));
for
(
int
i =
3
; i <= rows; i++) {
XSSFRow row = sheet.getRow(i);
int
maxCellNum = row.getLastCellNum();
Map<String, Object> item =
new
HashMap<String, Object>();
for
(
int
cellIndex =
0
; cellIndex < maxCellNum; cellIndex++) {
XSSFCell cell = row.getCell(cellIndex);
String columnName = columnNames.get(cellIndex);
String columnType = columnTypes.get(cellIndex);
String value =
null
;
try
{
// cell.setCellType(CellType.STRING);
value = getCellValue(cell,columnType);
}
catch
(Exception e) {
System.err.println(
"row:"
+i+
",column:"
+cellIndex);
System.err.println(e.getMessage());
break
;
}
item.put(columnName, value);
}
result.add(item);
}
System.out.println(JSONUtil.getJSONString(result));
}
public
static
void
main(String[] args)
throws
Exception {
convertExcelToJSON(
"shop_sale_type_list.xlsx"
,
"shop_sale_type_list"
);
}
}
|
测试excel文件
输出
本文转自 antlove 51CTO博客，原文链接:http://blog.51cto.com/antlove/1874907
