java-将xlsx（excel）文件转换成json

      来源：http://blog.csdn.net/Cassy_Wu/article/details/54577720

最近在工作接触到将xlsx(excel)转换成Json文件的需求。特此写个文章总结学习。以供以后参考。

首先了解一下Json的语法 
摘抄自http://www.w3school.com.cn/json/json_syntax.asp

JSON 语法是 JavaScript 对象表示法语法的子集。

• 数据在名称/值对中
• 数据由逗号分隔
• 花括号保存对象
• 方括号保存数组

JSON 值可以是：

• 数字（整数或浮点数）
• 字符串（在双引号中）
• 逻辑值（true 或 false）
• 数组（在方括号中）
• 对象（在花括号中）
• null

JSON 对象 
{ “firstName”:”John” , “lastName”:”Doe” } 
等价于js中 
firstName = “John”； 
lastName = “Doe”；

JSON 数组 
[ 
{ “firstName”:”Bill” , “lastName”:”Gates” }, 
{ “firstName”:”George” , “lastName”:”Bush” }, 
{ “firstName”:”Thomas” , “lastName”: “Carter” } 
]

JSON 文件 
JSON 文件的文件类型是 “.json” 
JSON 文本的 MIME 类型是 “application/json”

以下是转换代码。 
excel截图如下 

package XlsxToJson;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 org.apache.poi.ss.usermodel.Cell;import org.apache.sling.commons.json.JSONArray;import org.apache.sling.commons.json.JSONObject;import java.io.FileInputStream;import java.io.FileWriter;import java.util.HashMap;import java.util.Map;public class XlsxToJson {    public static void main(String[] args) {        FileInputStream inputStream = null;        FileWriter writer = null;        try{            inputStream = new FileInputStream("D:\\excel\\table.xlsx");            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);            writer = new FileWriter("D:\\excel\\test.json");            JSONObject jsonData = transferXlsxToJson(workbook);            jsonData.write(writer);        }catch (Exception e){            e.printStackTrace();        }finally {            if (inputStream!=null){                try {                    inputStream.close();                }catch (Exception e){                    e.printStackTrace();                }            }            if (writer != null){                try{                    writer.flush();                    writer.close();                }catch (Exception e){                    e.printStackTrace();                }            }        }    }    private static JSONObject transferXlsxToJson (XSSFWorkbook workbook) throws Exception{        //获取sheet数目，判断excel是否可用        int sheetCount = workbook.getNumberOfSheets();        if (sheetCount < 1) {            throw new Exception ("Not any available sheet");        }        //获取第一个sheet对象        XSSFSheet sheet = workbook.getSheetAt(0);        //获取Upper列的值        XSSFRow tRow = sheet.getRow(1);//索引是从0开始        //记录索引位置        Map<String, Integer> indexMap = new HashMap<>();        for (Cell cell: tRow) {            String cellValue = cell.getStringCellValue();            if (!(cellValue == null)|| cellValue==""){                indexMap.put(cellValue, cell.getColumnIndex());            }        }        int lastRowNum = sheet.getLastRowNum();        JSONArray dataArr = new JSONArray();        for (int rowIndex = 3; rowIndex <= lastRowNum; rowIndex++) {            String incomeThreholdUpper = "";            XSSFRow xRow = sheet.getRow(rowIndex);            XSSFCell upperCell =  xRow.getCell(1);            /*            //判断值的类型是不是字符串            if (upperCell.getCellType()==0){                incomeThreholdUpper = String.valueOf(Math.round(upperCell.getNumericCellValue()));                //判断值的类型是不是字符串            }else if(upperCell.getCellType()==1){                if (upperCell.getStringCellValue().equals("+")){                    incomeThreholdUpper = "infinity";                }            }            //获取Couple下所有列的值            int lastCellNum = xRow.getLastCellNum();            JSONArray CoupleArr = new JSONArray();            int dependents = 0;            for (int cellIndex = indexMap.get("Couple"); cellIndex <indexMap.get("Single") ; cellIndex++) {                XSSFCell xCell = xRow.getCell(cellIndex);                                JSONObject jsonObj = new JSONObject().accumulate("dependents", dependents).accumulate("value", Math.round(xCell.getNumericCellValue()));                CoupleArr.put(jsonObj);                dependents++;            }            JSONObject CoupleObj = new JSONObject().accumulate("type","Couple").accumulate("this type", CoupleArr);            //            JSONArray SingleArr = new JSONArray();            dependents = 0;            for (int cellIndex = indexMap.get("Single"); cellIndex <lastCellNum ; cellIndex++) {                XSSFCell xCell = xRow.getCell(cellIndex);                JSONObject jsonObj = new JSONObject().accumulate("dependents", dependents).accumulate("value", Math.round(xCell.getNumericCellValue()));                SingleArr.put(jsonObj);                dependents++;            }            JSONObject SingleObj = new JSONObject().accumulate("type", "Single").accumulate("thisType", SingleArr);            JSONObject rowObj = new JSONObject().accumulate("incomeThreholdUpper",incomeThreholdUpper).accumulate("thisIncomeLevel", new JSONArray().put(SingleObj).put(CoupleObj));            dataArr.put(rowObj);        }        JSONObject Table = new JSONObject().accumulate("config", new JSONObject().accumulate("currency","$").accumulate("data", dataArr));        return Table;    }}
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

所需的jar包如下 

jar包下载地址 
http://commons.apache.org/proper/commons-collections/download_collections.cgi 
http://sling.apache.org/downloads.cgi 
http://www.java2s.com/Code/Jar/o/Downloadorgapacheslingcommonsjson204incubatorjar.htm

如果遇到如下截图错误，是缺少commons-collections4-4.1.jar包的原因 
Exception in thread “main” java.lang.NoClassDefFoundError:org/apache/commons/collections4/ListValuedMap 
 
生成的json文件如下： 
{ 
“config”: { 
“currency”: “$”, 
“data”: [ 
{ 
“incomeThreholdUpper”: “6710”, 
“thisIncomeLevel”: [ 
{ 
“type”: “Single”, 
“thisType”: [ 
{ 
“dependents”: 0, 
“value”: 750 
}, 
{ 
“dependents”: 1, 
“value”: 1023 
}, 
{ 
“dependents”: 2, 
“value”: 2056 
}, 
{ 
“dependents”: 3, 
“value”: 3054 
}, 
{ 
“dependents”: 4, 
“value”: 4012 
}, 
{ 
“dependents”: 5, 
“value”: 5026 
}, 
{ 
“dependents”: 6, 
“value”: 6025 
}, 
{ 
“dependents”: 7, 
“value”: 7025 
}, 
{ 
“dependents”: 8, 
“value”: 8021 
}, 
{ 
“dependents”: 9, 
“value”: 9021 
}, 
{ 
“dependents”: 10, 
“value”: 10100 
} 
] 
}, 
{ 
“type”: “Couple”, 
“this type”: [ 
{ 
“dependents”: 0, 
“value”: 975 
}, 
{ 
“dependents”: 1, 
“value”: 1459 
}, 
{ 
“dependents”: 2, 
“value”: 2248 
}, 
{ 
“dependents”: 3, 
“value”: 3158 
}, 
{ 
“dependents”: 4, 
“value”: 4126 
}, 
{ 
“dependents”: 5, 
“value”: 5458 
}, 
{ 
“dependents”: 6, 
“value”: 6245 
}, 
{ 
“dependents”: 7, 
“value”: 7569 
}, 
{ 
“dependents”: 8, 
“value”: 8125 
}, 
{ 
“dependents”: 9, 
“value”: 9506 
}, 
{ 
“dependents”: 10, 
“value”: 10256 
} 
] 
} 
] 
}, 
………….

学习参考网址： 
JAVA入门之如何通过 POI 读取并修改 Excel 
http://jingyan.baidu.com/article/fdbd4277cbaccab89e3f48a3.html 
POI读取Excel常见问题 
http://www.cnblogs.com/qingxinblog/articles/3647483.html 
POI操作Excel常用方法总结 
http://blog.csdn.net/educast/article/details/50454810