JAVA 中的 Excel 到 Json 转换器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35794684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-11-03 00:36:18 来源:igfitidea点击:
Excel to Json converter in JAVA
提问by user3599302
Am trying to covert the excel data into JSON format. And i am successful upto converting excel data by reading the rows and columns and set the data to list and then converting that List to JSON format.
我正在尝试将 excel 数据转换为 JSON 格式。通过读取行和列并将数据设置为列表，然后将该列表转换为 JSON 格式，我成功地转换了 excel 数据。
But, I stuck in next level of JSON where the format will be like
但是，我停留在 JSON 的下一个级别，格式将类似于
Here is the code which iam using, But not yet started how to make that nested data like 3 columns data should come in one array like the above image.
这是我使用的代码，但尚未开始如何使嵌套数据（如 3 列数据）应出现在一个数组中，如上图。
Pojo class:
Pojo 类：
public class Products implements Serializable{
@Id
@Column(name="ID")
private String id;
@Column(name="PRODUCTID")
private String productId;
@Column(name="NAMEID")
private String nameId;
@Column(name="NAME")
private String name;
@Column(name="P_DESC")
private String desc;
@Column(name="DIMENSION")
private String dimension;
@Column(name="CATEGORY")
private String category;
@Column(name="SUB_CATEGORY")
private String subcategory;
@Column(name="CATEGORYID")
private String categoryId;
@Column(name="SUBCATEGORYID")
private String subcategoryId;
@Column(name="TAGS")
private String tags;
@Column(name="DESIGNER")
private String designer;
@Column(name="CURR")
private String curr;
@Column(name="POPULARITY")
private String popularity;
@Column(name="RELEVANCE")
private String relevance;
@Column(name="SHORTLISTED")
private String shortlisted;
@Column(name="LIKES")
private String likes;
@Column(name="CREATE_String")
private String createDt;
@Column(name="PAGEID")
private String pageId;
@Column(name="STYLENAME")
private String styleName;
@Column(name="STYLEID")
private String styleId;
@Column(name="PRICERANGE")
private String priceRange;
@Column(name="PRICEID")
private String priceId;
@Column(name="DEFAULT_PRICE")
private String defaultPrice;
@Column(name="DEFAULT_MATERIAL")
private String defaultMaterial;
@Column(name="DEFAULT_FINISH")
private String defaultFinish;
/* setters and getters */
}
ExceltoJsonConverter class:
ExceltoJsonConverter 类：
public static void uploadXLS(MultipartFile file, Document doc)
throws IOException {
Products products = new Products();
List<Products> productsList = new ArrayList<Products>();
logger.info("uploadExcel method");
HSSFWorkbook wb = null;
try {
wb= new HSSFWorkbook(file.getInputStream());
System.out.println("workbook: "+wb);
HSSFSheet sheet = wb.getSheetAt(0);
System.out.println("worksheet: "+sheet);
HSSFRow row;
Iterator<Row> iterator = sheet.iterator();
while (iterator.hasNext()) {
products = new Products();
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
Cell cell = cellIterator.next();
Iterator cells = nextRow.cellIterator();
cell=(HSSFCell) cells.next();
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+" ");
}
else if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
} else
{
//U Can Handel Boolean, Formula, Errors
}
products.setId(new DataFormatter().formatCellValue(nextRow.getCell(0)));
products.setProductId(new DataFormatter().formatCellValue(nextRow.getCell(1)));
products.setNameId(new DataFormatter().formatCellValue(nextRow.getCell(2)));
products.setName(new DataFormatter().formatCellValue(nextRow.getCell(3)));
products.setDesc(new DataFormatter().formatCellValue(nextRow.getCell(4)));
products.setDimension(new DataFormatter().formatCellValue(nextRow.getCell(5)));
products.setCategory(new DataFormatter().formatCellValue(nextRow.getCell(6)));
products.setSubcategory((new DataFormatter().formatCellValue(nextRow.getCell(7))));
products.setCategoryId(new DataFormatter().formatCellValue(nextRow.getCell(8)));
products.setSubcategoryId((new DataFormatter().formatCellValue(nextRow.getCell(9))));
products.setTags((new DataFormatter().formatCellValue(nextRow.getCell(10))));
products.setDesigner((new DataFormatter().formatCellValue(nextRow.getCell(11))));
products.setCurr((new DataFormatter().formatCellValue(nextRow.getCell(12))));
products.setPopularity((new DataFormatter().formatCellValue(nextRow.getCell(13))));
products.setRelevance((new DataFormatter().formatCellValue(nextRow.getCell(14))));
products.setShortlisted((new DataFormatter().formatCellValue(nextRow.getCell(15))));
products.setLikes((new DataFormatter().formatCellValue(nextRow.getCell(16))));
products.setCreateDt((new DataFormatter().formatCellValue(nextRow.getCell(17))));
products.setPageId((new DataFormatter().formatCellValue(nextRow.getCell(18))));
products.setStyleName((new DataFormatter().formatCellValue(nextRow.getCell(19))));
products.setStyleId((new DataFormatter().formatCellValue(nextRow.getCell(20))));
products.setPriceRange((new DataFormatter().formatCellValue(nextRow.getCell(21))));
products.setPriceId((new DataFormatter().formatCellValue(nextRow.getCell(22))));
products.setDefaultPrice((new DataFormatter().formatCellValue(nextRow.getCell(23))));
products.setDefaultMaterial((new DataFormatter().formatCellValue(nextRow.getCell(24))));
products.setDefaultFinish((new DataFormatter().formatCellValue(nextRow.getCell(25))));
Map<String, String> inputMap = new HashMap<String, String>();
inputMap.put("name", "Java2Novice");
inputMap.put("site", "http://java2novice.com");
productsList.add(products);
System.out.println(productsList);
// JSON CONVERTER
ObjectMapper mapper = new ObjectMapper();
System.out.println("productsList: "+products);
DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
Date date = new Date();
String location = dateFormat.format(date);
System.out.println("productsList final: "+products);
// Convert object to JSON string and save into file directly
mapper.writeValue(new File("D:\"+location+"mygubbi.json"), productsList);
// Convert object to JSON string and save into file directly
mapper.writeValue(new File("D:\products.json"), productsList);
// Convert object to JSON string
String jsonInString = mapper.writeValueAsString(productsList);
System.out.println("JsonInString " +jsonInString);
// Convert object to JSON string and pretty print
jsonInString = mapper.writerWithDefaultPrettyPrinter()
.writeValueAsString(products);
System.out.println("Final Json" +mapper.writerWithDefaultPrettyPrinter()
.writeValueAsString(products));
mapper.writeValue(new File("D:\productsJson.json"), jsonInString);
}
} catch (JsonGenerationException e) {
e.printStackTrace();
} catch (JsonMappingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
finally {
}
}
Please suggest me how to approach to get multiple columns data in Json format as shown below
请建议我如何获取 Json 格式的多列数据，如下所示
A BIG Thank you in advance... Hoping anyone can make my day today :)
非常感谢您提前...希望今天任何人都能让我开心:)
采纳答案by user3599302
I too faced this type of scenario. You Just create A class with the fields which you having in the second sheet. For ex:
我也遇到过这种情况。您只需使用第二张表中的字段创建一个类。例如：
public class Somename{
private String abc;
private String xyz;
// getters and setters
}
In your pojo class define an array
在你的 pojo 类中定义一个数组
private Somename some[];
Goto exceltoJson converter class.Try to create Hash map and fetch the details which u need in that sheet. That's it...
转到 exceltoJson 转换器类。尝试创建哈希映射并获取该表中您需要的详细信息。而已...
public static Map getImgs(HSSFWorkbook wb) {
Map hm1 = new HashMap<String, ArrayList<Somename>>();
HSSFSheet sheet = wb.getSheetAt(2);
Iterator<Row> iterator= sheet.iterator();
ArrayList<Somename> al = new ArrayList<Somename>();
while (iterator.hasNext()) {
Row row = iterator.next();
String sno = new DataFormatter().formatCellValue(row.getCell(0));
if (hm1.get(sno) == null) {
hm1.put(sno, new ArrayList());
}
String some= new DataFormatter().formatCellValue(row
.getCell(1));
Somename img1 = new Somename(some);
System.out.println(img1);
ArrayList r = (ArrayList) hm1.get(sno);
r.add(img1);
}
return hm1;
}
Then add this uploadXLS,
然后加上这个uploadXLS，
ArrayList<Somename> al = (ArrayList<Somename>) hm1.get(id_prod);
Somename[] ar = new Images[al.size()];
products.setSome(al.toArray(imgfar));
May be this will help you.
可能这会帮助你。
