json 生成 excel 文件和下载

前端生成

使用 依赖 xlsx

import * as XLSX from 'xlsx';

const jsonData = [
    {"Name": "Alice", "Age": 30, "City": "New York"},
    {"Name": "Bob", "Age": 25, "City": "Los Angeles"},
    {"Name": "Charlie", "Age": 35, "City": "Chicago"}
];

const wb = XLSX.utils.book_new();
const ws = XLSX.utils.json_to_sheet(jsonData);
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "jsonData.xlsx"); // 如果在浏览器端 这一步就会开始弹出下载.

使用 依赖 exceljs

const ExcelJS = require('exceljs');

// Sample JSON data
const jsonData = [
    {"Name": "Alice", "Age": 30, "City": "New York"},
    {"Name": "Bob", "Age": 25, "City": "Los Angeles"},
    {"Name": "Charlie", "Age": 35, "City": "Chicago"}
];

// Create a new Excel workbook
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Sheet1');

// Add JSON data to worksheet
worksheet.columns = [
    { header: 'Name', key: 'name' },
    { header: 'Age', key: 'age' },
    { header: 'City', key: 'city' }
];

jsonData.forEach(row => {
    worksheet.addRow(row);
});

// Save workbook to file
workbook.xlsx.writeFile('output.xlsx')
    .then(() => {
        console.log('Excel file created successfully');
    })
    .catch(err => {
        console.error('Error creating Excel file:', err);
    });

后端生成

使用 python 生成

后端 使用python ,依赖 pandas

import pandas as pd

# Sample JSON data
json_data = [
    {"Name": "Alice", "Age": 30, "City": "New York"},
    {"Name": "Bob", "Age": 25, "City": "Los Angeles"},
    {"Name": "Charlie", "Age": 35, "City": "Chicago"}
]

# Convert JSON data to a pandas DataFrame
df = pd.DataFrame(json_data)

# Write DataFrame to an Excel file
df.to_excel("output.xlsx", index=False)

输出到流, 让浏览器下载

from cloudoll import get
import pandas as pd

@get('/download_excel')
def download_excel():
    # 示例JSON数据
    json_data = [
        {"Name": "Alice", "Age": 30, "City": "New York"},
        {"Name": "Bob", "Age": 25, "City": "Los Angeles"},
        {"Name": "Charlie", "Age": 35, "City": "Chicago"}
    ]

    # 将JSON数据转换为DataFrame
    df = pd.DataFrame(json_data)

    # 创建Excel文件的二进制数据
    excel_bytes = df.to_excel(index=False)

    # 创建响应对象
    response = make_response(excel_bytes)

    # 设置响应头
    response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    response.headers['Content-Disposition'] = 'attachment; filename=output.xlsx'

    return response

//cloudoll start  to test

使用 java

添加maven 依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version> <!-- 或者使用最新版本 -->
</dependency>

示例 :

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

import java.io.FileOutputStream;
import java.io.IOException;

public class JsonToExcelConverter {
    public static void main(String[] args) {
        // 示例JSON数据
        String jsonData = "[{\"Name\":\"Alice\",\"Age\":30,\"City\":\"New York\"},{\"Name\":\"Bob\",\"Age\":25,\"City\":\"Los Angeles\"},{\"Name\":\"Charlie\",\"Age\":35,\"City\":\"Chicago\"}]";

        // 创建一个新的Excel工作簿
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");

            JSONArray jsonArray = new JSONArray(jsonData);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < jsonArray.getJSONObject(0).length(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue((String) jsonArray.getJSONObject(0).names().get(i));
            }

            // 填充数据行
            for (int i = 0; i < jsonArray.length(); i++) {
                Row row = sheet.createRow(i + 1);
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                for (int j = 0; j < jsonObject.length(); j++) {
                    Cell cell = row.createCell(j);
                    String key = (String) jsonObject.names().get(j);
                    cell.setCellValue(jsonObject.get(key).toString());
                }
            }

            // 将工作簿写入文件
            try (FileOutputStream fileOut = new FileOutputStream("output.xlsx")) {
                workbook.write(fileOut);
                System.out.println("Excel文件创建成功。");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}