需要读取excel数据转换成json数据，写了个测试功能，转换正常：
JSON转换：org.json.jar
测试类： importFile.java:
|
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
package com.siemens.util; import java.util.ArrayList; import java.util.List; import org.json.JSONException; import org.json.JSONObject; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; //import com.siemens.entity.master; //import com.siemens.service.masterService; //import com.siemens.serviceImpl.masterServiceImpl; //import com.siemens.serviceImpl.webServiceImpl; public class importFile { public static void main(String[] args) throws JSONException{ // master masters = new master(); // ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml"); // masterService ms = (masterService)ac.getBean("masterservice"); Workbook wb =null; Sheet sheet = null; Row row = null; String cellData = null; //文件路径， String filePath = "f:/haoxy/Worktable222.xls"; wb =EXCELBean.readExcel(filePath); if(wb != null){ //用来存放表中数据 List<JSONObject> listMap = new ArrayList<JSONObject>(); //获取第一个sheet sheet = wb.getSheetAt(0); //获取最大行数 int rownum = sheet.getPhysicalNumberOfRows(); //获取第一行 row = sheet.getRow(0); //获取最大列数 int colnum = row.getPhysicalNumberOfCells(); //这里创建json对象，实测用map的话，json数据会有问题 JSONObject jsonMap = new JSONObject(); //循环行 for (int i = 1; i < rownum; i++) { row = sheet.getRow(i); if(row !=null){ //创建list对象接收读出的excel数据 List<String> list = new ArrayList<String>(); //循环列 for (int j=0;j<colnum;j++){ cellData = (String) EXCELBean.getCellFormatValue(row.getCell(j)); list.add(cellData); } //System.out.println(list.get(59)); //下面具体是本人对数据按需求进行格式处理 ---创建json对象会报异常，捕捉一下。 JSONObject jsonObject2 = new JSONObject(); jsonObject2.put("skvDorCode",list.get(0)); jsonObject2.put("description", list.get(1)); jsonObject2.put("discipline", list.get(2)); jsonObject2.put("prefabricatedSkids", list.get(3)); jsonObject2.put("onRack", list.get(4)); jsonObject2.put("offRack", list.get(5)); jsonObject2.put("yard", list.get(6)); jsonObject2.put("hsrg", list.get(7)); JSONObject jsonPptData = new JSONObject(); jsonPptData.put("SC FF",list.get(8)); jsonPptData.put("CC SS CT",list.get(9)); jsonPptData.put("CC SS OCC",list.get(10)); jsonPptData.put("CC SS ACC",list.get(11)); jsonPptData.put("CC MS CT",list.get(12)); jsonPptData.put("CC MS OCC",list.get(13)); jsonPptData.put("CC MS ACC",list.get(14)); //turnkey JSONObject jsonTurnkey = new JSONObject(); jsonTurnkey.put("plantDesign", list.get(26)); jsonTurnkey.put("basicDesign", list.get(27)); jsonTurnkey.put("detailDesign", list.get(28)); jsonTurnkey.put("supplier", list.get(29)); jsonTurnkey.put("errection", list.get(30)); jsonTurnkey.put("commissioning", list.get(31)); jsonTurnkey.put("blackBox", list.get(32)); jsonTurnkey.put("optionalScope", list.get(33)); jsonTurnkey.put("remark", list.get(34)); jsonTurnkey.put("internalRemark", list.get(35)); jsonTurnkey.put("revision", list.get(36)); //PowerCore JSONObject jsonPowerCore = new JSONObject(); jsonPowerCore.put("plantDesign", list.get(37)); jsonPowerCore.put("basicDesign", list.get(38)); jsonPowerCore.put("detailDesign", list.get(39)); jsonPowerCore.put("supplier", list.get(40)); jsonPowerCore.put("errection", list.get(41)); jsonPowerCore.put("commissioning", list.get(42)); jsonPowerCore.put("blackBox", list.get(43)); jsonPowerCore.put("optionalScope", list.get(44)); jsonPowerCore.put("remark", list.get(45)); jsonPowerCore.put("internalRemark", list.get(46)); jsonPowerCore.put("revision", list.get(47)); //PowerIsland JSONObject jsonPowerIsland = new JSONObject(); jsonPowerIsland.put("plantDesign", list.get(48)); jsonPowerIsland.put("basicDesign", list.get(49)); jsonPowerIsland.put("detailDesign", list.get(50)); jsonPowerIsland.put("supplier", list.get(51)); jsonPowerIsland.put("errection", list.get(52)); jsonPowerIsland.put("commissioning", list.get(53)); jsonPowerIsland.put("blackBox", list.get(54)); jsonPowerIsland.put("optionalScope", list.get(55)); jsonPowerIsland.put("remark", list.get(56)); jsonPowerIsland.put("internalRemark", list.get(57)); jsonPowerIsland.put("revision", list.get(58)); //创建jsonBmt对象，进一步把以上对象嵌套 JSONObject jsonBmt = new JSONObject(); jsonBmt.put("Turnkey", jsonTurnkey); jsonBmt.put("PowerCore", jsonPowerCore); jsonBmt.put("PowerIsland", jsonPowerIsland); //把以上几个嵌套入第一层对象中 jsonObject2.put("powerPlantTypes",jsonPptData); jsonObject2.put("businessMixTypes",jsonBmt); jsonObject2.put("treeDepth",Integer.parseInt(list.get(59).substring(0,list.get(59).indexOf('.')))); if(Integer.parseInt(list.get(59).substring(0,list.get(59).indexOf('.')))<=2){ List<String> list3 = new ArrayList<String>(); list3.add("non-empty-placeholder"); jsonObject2.put("children",list3); } listMap.add(jsonObject2); }else{ break; } }// end for row //最外层加个key-gridData jsonMap.put("gridData", listMap); System.out.println(jsonMap); } } } |
读取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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
package com.siemens.util; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class EXCELBean { //读取excel public static Workbook readExcel(String filePath){ Workbook wb = null; if(filePath==null){ return null; } String extString = filePath.substring(filePath.lastIndexOf(".")); InputStream is = null; try { is = new FileInputStream(filePath); if(".xls".equals(extString)){ return wb = new HSSFWorkbook(is); }else if(".xlsx".equals(extString)){ return wb = new XSSFWorkbook(is); }else{ return wb = null; } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return wb; } public static Object getCellFormatValue(Cell cell){ Object cellValue = null; if(cell!=null){ //判断cell类型 switch(cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC:{ cellValue = String.valueOf(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_FORMULA:{ //判断cell是否为日期格式 if(DateUtil.isCellDateFormatted(cell)){ //转换为日期格式YYYY-mm-dd cellValue = cell.getDateCellValue(); }else{ //数字 cellValue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING:{ cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } }else{ cellValue = ""; } return cellValue; } } |
总结
以上所述是小编给大家介绍的java 读取excel文件转换成json格式 ，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！
原文链接：https://blog.csdn.net/qq_27985863/article/details/79917636
