基於Java實現Json文件轉換為Excel文件
一. 思路
今天接到個小任務,讓把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;
}
}
}
到此這篇關於基於Java實現Json文件轉換為Excel文件的文章就介紹到這瞭,更多相關Java Json轉Excel內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- Apache POI的基本使用詳解
- 教你如何使用JAVA POI
- 一文帶你搞懂Go如何讀寫Excel文件
- Java實現超大Excel文件解析(XSSF,SXSSF,easyExcel)
- java導出excel 瀏覽器直接下載或者或以文件形式導出