基于Java實現Json文件轉換為Excel文件

更新時間：2022年12月01日 16:23:05 作者：菜鳥小于

這篇文章主要為大家詳細介紹了如何利用Java實現Json文件轉換為Excel文件，文中的示例代碼講解詳細，具有一定的借鑒價值，需要的可以參考一下

一. 思路

今天接到個小任務,讓把json文件轉換成excel文件,按照列展開.

思路:既然json已經都已經是現成的,那直接將json文件做讀操作,在通過不同的key,找到對應的信息,在存到單元格中,在寫操作,生成excel文檔

二.jar包

涉及到的jar包,阿里的fastjson和poi的jar包

三.代碼

我的json文檔里數據的格式是這樣的

[ 
{
        "total": 1,
        "name": "規(guī)則限制: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的形式獲取到,這時創(chuàng)建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);
            //這是創(chuàng)建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);
            //這是創(chuàng)建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內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: