java实现：将Json转为Excel

先来看效果：

json = [{"name":"Alice","age":25,"city":"New York"},{"name":"Bob","age":30,"city":"London"}]

输出excel文件内容：

一. 添加依赖

这里使用阿里云镜像来下载依赖项

将以下内容复制到pom.xml文件中，也可复制Apache POI和JSON库添加到已有的pom.xml中。

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>json-to-excel</artifactId>
    <version>1.0.0</version>

    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <repositories>
        <repository>
            <id>aliyun</id>
            <url>https://maven.aliyun.com/repository/public</url>
        </repository>
    </repositories>

    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20210307</version>
        </dependency>
    </dependencies>

</project>

二. 实现代码

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

import java.io.FileOutputStream;

public class JsonToExcelExporter {

    public static void main(String[] args) {

        // 示例JSON数据
        String jsonString = "[{\"name\":\"Alice\",\"age\":25,\"city\":\"New York\"},{\"name\":\"Bob\",\"age\":30,\"city\":\"London\"}]";
        JSONArray jsonArray = new JSONArray(jsonString);

        try {

            exportJsonToExcel(jsonArray, "D:/output-" + String.valueOf(System.currentTimeMillis()) + ".xlsx");
            System.out.println("json转excel成功");

        } catch (Exception e) {

            e.printStackTrace();
        }
    }

    public static void exportJsonToExcel(JSONArray jsonArray, String filePath) throws Exception {

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        JSONObject firstObject = jsonArray.getJSONObject(0);
        int columnIndex = 0;

        for (String key : firstObject.keySet()) {

            Cell cell = headerRow.createCell(columnIndex++);
            cell.setCellValue(key);
        }

        // 填充数据
        int rowIndex = 1;

        for (int i = 0; i < jsonArray.length(); i++) {

            JSONObject jsonObject = jsonArray.getJSONObject(i);
            Row dataRow = sheet.createRow(rowIndex++);
            columnIndex = 0;

            for (String key : jsonObject.keySet()) {

                Cell cell = dataRow.createCell(columnIndex++);

                Object keyObject = jsonObject.get(key);

                // 判断jsonObject中的类型
                if (keyObject instanceof Integer) {

                    cell.setCellValue(jsonObject.getInt(key));

                } else if (keyObject instanceof String) {

                    cell.setCellValue(jsonObject.getString(key));

                } else if (keyObject instanceof JSONArray) {

                    cell.setCellValue(jsonObject.getJSONArray(key).toString());

                } else if (keyObject instanceof JSONObject) {

                    cell.setCellValue(jsonObject.getJSONObject(key).toString());
                }
            }
        }

        // 调整列宽
        for (int i = 0; i < firstObject.length(); i++) {

            sheet.autoSizeColumn(i);
        }

        // 保存Excel文件
        FileOutputStream fileOut = new FileOutputStream(filePath);
        workbook.write(fileOut);
        fileOut.close();

        workbook.close();
    }
}