一.思路

今天接到个小任务,让把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.printStackT3ff8race();return null;}}

View Code

我试过直接读文件,出现中文乱码,所以记得用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();}}

View Code

时间戳的转换方法:

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;}

View Code

运行即可获得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;}}}

View Code