java读取xlsx并转化json数据

最新推荐文章于 2025-10-16 17:19:58 发布

原创最新推荐文章于 2025-10-16 17:19:58 发布 · 2.0k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#解析xlsx #POI #list转json

java 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个将Excel文件（xlsx格式）转换为JSON格式的具体实现案例。利用POI和fastjson库，通过Java代码实现了从Excel中读取数据，并将数据按类别进行归类整理后输出为JSON文件的过程。

今天因为工作需要，需要写一个demo将xlsx转换成json数据。将store绑定在category里面。

数据如图：

然后就找到POI和fastjson的jar包。

demo:

	public static void main(String[] args) throws IOException {
		Xlsx xlsxMain = new Xlsx();
		List<Category> list = new ArrayList<Category>();
		HashMap<Integer, Category> map = xlsxMain.readXlsx();//读取xlsx
		Set<Integer> setIds = map.keySet();
		Iterator<Integer> i = setIds.iterator();
		while(i.hasNext()){
			int id = i.next();
			Category category = map.get(id);
			list.add(category);
		}
		Collections.sort(list);//排序
		String json = JSON.toJSONString(list);//将list转换成json
		File file = new File("D:\\json.txt");
		FileOutputStream out = new FileOutputStream(file);//写入文件
		byte[] bytes = json.getBytes();
		out.write(bytes);
		out.flush();
		out.close();
	}

	private HashMap<Integer,Category> readXlsx() throws IOException {
		String fileName = "D:\\m.xlsx";
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileName);
		HashMap<Integer,Category> map = new HashMap<Integer,Category>();
		//读取sheet
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			//读取行
			for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
				XSSFRow xssfRow = xssfSheet.getRow(rowNum);
				if (xssfRow == null) {
					continue;
				}
				if (rowNum == 0) {
					continue;
				}
				Store store = new Store();
				Category category = null;
				//读取列
				for (int cellNum = 0; cellNum <= xssfRow.getLastCellNum(); cellNum++) {
					XSSFCell xssfCell = xssfRow.getCell(cellNum);
					if(xssfCell == null){
						continue;
					}
					String xssCell = getValue(xssfCell);
					if(cellNum == 0){
						store.setId((int)Float.parseFloat(xssCell));
					}else if(cellNum == 1){
						store.setName(xssCell);
					}else if(cellNum == 4){
						int categoryId = Integer.parseInt(xssCell);
						if(map.containsKey(categoryId)){
							category = map.get(categoryId);
							category.setCategoryId(categoryId);
							category.addStore(store);
						}else{
							category = new Category();
							category.setCategoryId(categoryId);
							category.addStore(store);
							map.put(categoryId, category);
						}
					}else if(cellNum == 5){
						int p  = xssCell.indexOf("/");
						String b = p > 0? xssCell.substring(0, p) : xssCell;
						b = b.toUpperCase();
						category.setCategoryName(b);
					}else if(cellNum ==6){
						store.setBanner(xssCell);
					}

				}
				
			}
		}
		return map;
	}
	//得到值 
	private String getValue(XSSFCell xssfCell) {
		if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfCell.getBooleanCellValue());
		} else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfCell.getNumericCellValue());
		} else {
			return String.valueOf(xssfCell.getStringCellValue());
		}
	}

}

输出文本：

[{"categoryId":1,"categoryName":"DEPARTMENT STORES","stores":[{"banner":"http://upload.cheaperseeker.com/2014/02/5746e15389.png","id":2612,"name":"Kohls"},{"banner":"http://upload.cheaperseeker.com/2012/04/Macys.jpg","id":2845,"name":"Macy's"},{"banner":"http://upload.cheaperseeker.com/2012/08/logo_bbb.gif","id":557,"name":"Bed Bath & Beyond"},...}]