最近在工作接触到将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