一. 思路
今天接到个小任务,让把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; } } }