一. 思路
今天接到個小任務,讓把json檔案轉換成excel檔案,按照列展開.
思路:既然json已經都已經是現成的,那直接將json檔案做讀操作,在通過不同的key,找到對應的資訊,在存到單元格中,在寫操作,生成excel文件
二.jar包
涉及到的jar包,阿里的fastjson和poi的jar包
三.程式碼
我的json文件裡資料的格式是這樣的
[ { "total": 1, "name": "規則限制:XXXX", "timeStr": 1619242800000, "message": "XXX", "hehe": "" }, { "total": 2, "name": "服務異常:XXXX", "timeStr": 1619240400000, "message": "XXX！", "hehe": "" } ]
1.先對json檔案進行讀操作,提取String物件,在將String物件轉換為JsonArray
public static String readJsonFile(String path) { String jsonString = ""; try { File file = new File(path); FileReader fileReader = new FileReader(file); Reader reader = new InputStreamReader(new FileInputStream(file),"utf-8"); int ch = 0; StringBuffer sb = new StringBuffer(); while ((ch = reader.read()) != -1) { sb.append((char) ch); } fileReader.close(); reader.close(); jsonString = sb.toString(); return jsonString; } catch (Exception e) { e.printStackTrace(); return null; } }
我試過直接讀檔案,出現中文亂碼,所以記得用UTF-8編碼,否則會是亂碼
2.檔案內容以String的形式獲取到,這時建立excel檔案,在將String轉換為jsonArray形式遍歷,分別插入到excel檔案的單元格cell中,在做寫操作
public static void main(String[] args) { String json = ToJson.readJsonFile("C:\\Users\\yu\\Desktop\\new.json"); //System.out.println(json); //JSONObject object = JSON.parseObject(json); try { //生成excel檔案存放的地址 String uploadFile = "D:/test.xlsx"; OutputStream excel = new FileOutputStream(uploadFile); XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet sheet = workBook.createSheet(); XSSFRow row = null;//行 XSSFCell cell = null;//單元格 row = sheet.createRow(0); //這是建立excel上邊的標題頭 String[] names = { "total", "異常", "頁面名稱", "資訊","時間","工號"}; for (int index = 0; index < 5; index++) { cell = row.createCell(index); cell.setCellValue(names[index]); } int count = 1; JSONArray dataArray = JSONArray.parseArray(json); for(int i = 0; i < dataArray.size();i++){ JSONObject dataObj = dataArray.getJSONObject(i); //獲取不同key中的值 String total = dataObj.getString("total"); String name = dataObj.getString("name"); String[] nameArray = name.split(":");//這個是通過分號獲得兩個值,分別寫在excel中 String name1 = nameArray[0]; String name2 = nameArray[1]; String timeStr = dataObj.getString("timeStr"); String time = ToJson.stampToTime(timeStr);//這個根據時間戳轉換為正常年月日,時分秒 String message = dataObj.getString("message"); String staffId = dataObj.getString("hehe"); row = sheet.createRow(count); cell = row.createCell(0); cell.setCellValue(total); cell = row.createCell(1); cell.setCellValue(name1); cell = row.createCell(2); cell.setCellValue(name2); cell = row.createCell(3); cell.setCellValue(message); cell = row.createCell(4); cell.setCellValue(time); cell = row.createCell(5); cell.setCellValue(staffId); count++; } workBook.write(excel); } catch (Exception e) { e.printStackTrace(); } }
時間戳的轉換方法:
public static String stampToTime(String stamp) { String sd = ""; Date d = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); sd = sdf.format(new Date(Long.parseLong(stamp))); // 時間戳轉換日期 return sd; }
執行即可獲得excel檔案
全部程式碼:
package com.china.excelToJson; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; 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 java.io.*; import java.text.SimpleDateFormat; import java.util.Date; public class ToJson { public static void main(String[] args) { String json = ToJson.readJsonFile("C:\\Users\\yu\\Desktop\\new.json"); //System.out.println(json); //JSONObject object = JSON.parseObject(json); try { //生成excel檔案存放的地址 String uploadFile = "D:/test.xlsx"; OutputStream excel = new FileOutputStream(uploadFile); XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet sheet = workBook.createSheet(); XSSFRow row = null;//行 XSSFCell cell = null;//單元格 row = sheet.createRow(0); //這是建立excel上邊的標題頭 String[] names = { "total", "異常", "頁面名稱", "資訊","時間","工號"}; for (int index = 0; index < 5; index++) { cell = row.createCell(index); cell.setCellValue(names[index]); } int count = 1; JSONArray dataArray = JSONArray.parseArray(json); for(int i = 0; i < dataArray.size();i++){ JSONObject dataObj = dataArray.getJSONObject(i); //獲取不同key中的值 String total = dataObj.getString("total"); String name = dataObj.getString("name"); String[] nameArray = name.split(":");//這個是通過分號獲得兩個值,分別寫在excel中 String name1 = nameArray[0]; String name2 = nameArray[1]; String timeStr = dataObj.getString("timeStr"); String time = ToJson.stampToTime(timeStr);//這個根據時間戳轉換為正常年月日,時分秒 String message = dataObj.getString("message"); String staffId = dataObj.getString("hehe"); row = sheet.createRow(count); cell = row.createCell(0); cell.setCellValue(total); cell = row.createCell(1); cell.setCellValue(name1); cell = row.createCell(2); cell.setCellValue(name2); cell = row.createCell(3); cell.setCellValue(message); cell = row.createCell(4); cell.setCellValue(time); cell = row.createCell(5); cell.setCellValue(staffId); count++; } workBook.write(excel); } catch (Exception e) { e.printStackTrace(); } } public static String stampToTime(String stamp) { String sd = ""; Date d = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); sd = sdf.format(new Date(Long.parseLong(stamp))); // 時間戳轉換日期 return sd; } public static String readJsonFile(String fileName) { String jsonStr = ""; try { File jsonFile = new File(fileName); FileReader fileReader = new FileReader(jsonFile); Reader reader = new InputStreamReader(new FileInputStream(jsonFile),"utf-8"); int ch = 0; StringBuffer sb = new StringBuffer(); while ((ch = reader.read()) != -1) { sb.append((char) ch); } fileReader.close(); reader.close(); jsonStr = sb.toString(); return jsonStr; } catch (Exception e) { e.printStackTrace(); return null; } } }