我有一些数据是使用Java、Apache POI和Jackson从Excel文件中提取到JSON结构中的。生成的JSON数据结构如下所示：
{
"fileName" : "C:\Users\jgagnon\sample_data\PDM_BOM.xlsx",
"sheets" : [ {
"name" : "PDM_BOM",
"data" : [ [ "BRANCH", "PARENT ITEM NUMBER", "2ND ITEM NUMBER", "QUANTITY REQUIRED", "UNIT OF MEASURE", "ISSUE TYPE CODE", "LINE TYPE", "STOCK TYPE", "TYPE BOM", "LINE NUMBER", "OPERATING SEQUENCE", "EFFECTIVE FROM DATE", "EFFECTIVE THRU DATE", "DRAWING NUMBER", "UNIT COST", "SCRAP PERCENT" ],
[ "B20", "208E8840040", "5P884LPFSR2", 0.32031, "LB", "I", "S", "M", "M", 1.0, 10.0, "09/11/13", "12/31/40", null, 0.0, 0.0 ],
[ "B20", "208E8840168", "5P884LPFSR2", 1.36, "LB", "I", "S", "M", "M", 1.0, 10.0, "02/26/08", "12/31/40", null, 0.0, 0.0 ],
[ "B20", "208E8840172", "5P884LPFSR2", 1.3924, "LB", "I", "S", "M", "M", 1.0, 10.0, "02/26/08", "12/31/40", null, 0.0, 0.0 ],
[ "B20", "208E8840180", "5P884LPFSR2", 1.4565, "LB", "I", "S", "P", "M", 1.0, 10.0, "03/04/09", "12/31/40", null, 0.0, 0.0 ],
[ "B20", "21PPH150166", "8P315TPMRG", 1.39629, "LB", "I", "S", "M", "M", 1.0, 10.0, "03/05/18", "12/31/40", null, 0.0, 0.0 ] ],
"maxCols" : 16,
"maxRows" : 14996
} ]
}
在data元素中，本质上有一个数组数组，表示工作表中的所有行。第一个行数组是后面数据行的列标题。
我希望能够重构data，使其成为对象映射的形式，其中每个键都是(对于本例(PARENT ITEM NUMBER列中的值。与键关联的映射值将是一个JSON对象，该对象由当前数据行的列标题和列值的键/值对组成。
所以，使用上面的例子，我会得到这样的结果：(我可能有JSON语法/结构不正确(
{
"data": {
"208E8840040": {
"BRANCH": "B20",
"PARENT ITEM NUMBER": "208E8840040",
"2ND ITEM NUMBER": "5P884LPFSR2",
"QUANTITY REQUIRED": 0.32031,
"UNIT OF MEASURE": "LB",
"ISSUE TYPE CODE": "I",
"LINE TYPE": "S",
"STOCK TYPE": "M",
"TYPE BOM": "M",
"LINE NUMBER": 1.0,
"OPERATING SEQUENCE": 10.0,
"EFFECTIVE FROM DATE": "09/11/13",
"EFFECTIVE THRU DATE": "12/31/40",
"DRAWING NUMBER": null,
"UNIT COST": 0.0,
"SCRAP PERCENT": 0.0
},
"208E8840168": {
"BRANCH": "B20",
"PARENT ITEM NUMBER": "208E8840168",
"2ND ITEM NUMBER": "5P884LPFSR2",
"QUANTITY REQUIRED": 1.36,
...
},
...
}
}
我正在寻找一种将前者转化为后者的方法。
更新：
我刚刚意识到我遗漏了一个重要的细节。
此工作表(表(中的数据基本上由PARENT ITEM NUMBER列键控。但是，尽管该列是主要标识符，但它在表中并不总是唯一的。
通常情况下，存在多个具有相同PARENT ITEM NUMBER值的行。这些行中的每一行包含关于组成"行"的元素的信息；"父"；(把它们看作是儿童用品(。这些子项目由2ND ITEM NUMBER列标识。
此外，许多子项将在表中具有自己的行，其中PARENT ITEM NUMBER是由父项的2ND ITEM NUMBER标识的子项编号。正如你可能已经猜到的，这些子项目可以有自己的子项目，等等
基本上，这是相关数据的多个层次结构的表格表示。某些子项目将显示(重复使用(多个父项目。
我不知道这会以什么方式使我想做的事情复杂化。
更新：
感谢https://stackoverflow.com/users/51591/micha%c5%82-ziober最初的想法。我稍微调整了一下，生成了一个子项目列表的映射。修改后的代码如下：
public String convertToJson(File jsonFile) throws IOException {
ArrayNode arrayNode = readDataArray(jsonFile);
List<Map<String, JsonNode>> rowMaps = convertArrayToMaps(arrayNode);
Map<Object, List<Map<String, JsonNode>>> dataMap = rowMaps.stream()
.collect(Collectors.groupingBy(map -> map.get("PARENT ITEM NUMBER").textValue()));
return jsonMapper.writeValueAsString(Collections.singletonMap("data", dataMap));
}
下面是一个输出示例：
{
"data" : {
"MTDMN97PJ1A9" : [ { <- 1 child
"BRANCH" : "B70",
"PARENT ITEM NUMBER" : "MTDMN97PJ1A9",
"2ND ITEM NUMBER" : "MTDMN970144XO",
"QUANTITY REQUIRED" : 12.0,
"UNIT OF MEASURE" : "SY",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 1.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "01/18/19",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
} ],
"ZCP723A1152" : [ { <- 4 children
"BRANCH" : "B70",
"PARENT ITEM NUMBER" : "ZCP723A1152",
"2ND ITEM NUMBER" : "5P587UMFSD2",
"QUANTITY REQUIRED" : 2.32222,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 3.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "05/15/17",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
}, {
"BRANCH" : "B70",
"PARENT ITEM NUMBER" : "ZCP723A1152",
"2ND ITEM NUMBER" : "8P550ZPPOOLE",
"QUANTITY REQUIRED" : 2.32222,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 1.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "05/15/17",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
}, {
"BRANCH" : "B70",
"PARENT ITEM NUMBER" : "ZCP723A1152",
"2ND ITEM NUMBER" : "8P906WPPA3077",
"QUANTITY REQUIRED" : 4.64444,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 2.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "05/15/17",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
}, {
"BRANCH" : "B70",
"PARENT ITEM NUMBER" : "ZCP723A1152",
"2ND ITEM NUMBER" : "8U910LKSHBL",
"QUANTITY REQUIRED" : 2.32222,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 4.01,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "12/13/17",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
} ],
... many more entries
}
您可以将整个JSON负载读取为JsonNode，并检索作为数组的data属性。如果我们可以假设第一个元素始终是名称数组，并且所有其他元素的大小相等，那么您可以手动创建Map并将其串行化回JSON。一个棘手的部分是如何为结果JSON生成唯一的键值。示例代码如下所示：
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.node.ArrayNode;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
public class Excel2JonsApp {
public static void main(String[] args) throws IOException {
File jsonFile = new File("./resource/test.json").getAbsoluteFile();
Excel2JsonMapper mapper = new Excel2JsonMapper();
String json = mapper.convertToJson(jsonFile);
System.out.println(json);
}
}
class Excel2JsonMapper {
private final ObjectMapper jsonMapper;
Excel2JsonMapper() {
this.jsonMapper = createJsonMapper();
}
private ObjectMapper createJsonMapper() {
ObjectMapper mapper = new ObjectMapper();
mapper.enable(SerializationFeature.INDENT_OUTPUT);
return mapper;
}
String convertToJson(File jsonFile) throws IOException {
ArrayNode arrayNode = readDataArray(jsonFile);
Map<Object, Map<String, JsonNode>> dataMap = convertArrayToMaps(arrayNode)
.stream()
.collect(Collectors.toMap(
map -> map.get("PARENT ITEM NUMBER").textValue(), //key generator function
Function.identity()));
return jsonMapper.writeValueAsString(Collections.singletonMap("data", dataMap));
}
private List<Map<String, JsonNode>> convertArrayToMaps(ArrayNode arrayNode) {
ArrayNode names = (ArrayNode) arrayNode.get(0);
Iterator<JsonNode> iterator = arrayNode.iterator();
iterator.next();// skip names
List<Map<String, JsonNode>> list = new ArrayList<>();
while (iterator.hasNext()) {
ArrayNode values = (ArrayNode) iterator.next();
Map<String, JsonNode> map = new LinkedHashMap<>();
for (int i = 0; i < names.size(); i++) {
map.put(names.get(i).textValue(), values.get(i));
}
list.add(map);
}
return list;
}
private ArrayNode readDataArray(File jsonFile) throws IOException {
JsonNode root = jsonMapper.readTree(jsonFile);
return (ArrayNode) root.at("/sheets/0/data");
}
}
以上代码打印：
{
"data" : {
"208E8840168" : {
"BRANCH" : "B20",
"PARENT ITEM NUMBER" : "208E8840168",
"2ND ITEM NUMBER" : "5P884LPFSR2",
"QUANTITY REQUIRED" : 1.36,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 1.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "02/26/08",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
},
"21PPH150166" : {
"BRANCH" : "B20",
"PARENT ITEM NUMBER" : "21PPH150166",
"2ND ITEM NUMBER" : "8P315TPMRG",
"QUANTITY REQUIRED" : 1.39629,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 1.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "03/05/18",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
},
"208E8840180" : {
"BRANCH" : "B20",
"PARENT ITEM NUMBER" : "208E8840180",
"2ND ITEM NUMBER" : "5P884LPFSR2",
"QUANTITY REQUIRED" : 1.4565,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "P",
"TYPE BOM" : "M",
"LINE NUMBER" : 1.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "03/04/09",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
},
"208E8840172" : {
"BRANCH" : "B20",
"PARENT ITEM NUMBER" : "208E8840172",
"2ND ITEM NUMBER" : "5P884LPFSR2",
"QUANTITY REQUIRED" : 1.3924,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 1.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "02/26/08",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
},
"208E8840040" : {
"BRANCH" : "B20",
"PARENT ITEM NUMBER" : "208E8840040",
"2ND ITEM NUMBER" : "5P884LPFSR2",
"QUANTITY REQUIRED" : 0.32031,
"UNIT OF MEASURE" : "LB",
"ISSUE TYPE CODE" : "I",
"LINE TYPE" : "S",
"STOCK TYPE" : "M",
"TYPE BOM" : "M",
"LINE NUMBER" : 1.0,
"OPERATING SEQUENCE" : 10.0,
"EFFECTIVE FROM DATE" : "09/11/13",
"EFFECTIVE THRU DATE" : "12/31/40",
"DRAWING NUMBER" : null,
"UNIT COST" : 0.0,
"SCRAP PERCENT" : 0.0
}
}
}