.NET JSON / Excel 轉換範例

被問到有沒有 JSON 轉 Excel 的 .NET 範例，雖然是柴米油鹽級的簡單任務，一查還真沒寫過，念在這也算常遇到的應用場景，順手補上唄~

假設有 JSON 如下：

[
  {
    "Name": "John Doe",
    "Age": 28,
    "Country": "USA",
    "Email": "johndoe@example.com",
    "RegDate": "2023-10-04T08:23:55Z"
  },
  {
    "Name": "Jane Smith",
    "Age": 35,
    "Country": "UK",
    "Email": "janesmith@example.com",
    "RegDate": "2023-10-04T11:46:30Z"
  },
  {
    "Name": "Alice Jones",
    "Age": 29,
    "Country": "Australia",
    "Email": "alicej@example.com",
    "RegDate": "2023-10-04T15:02:43Z"
  },
  {
    "Name": "Bob Brown",
    "Age": 46,
    "Country": "Canada",
    "Email": "bobbrown@example.com",
    "RegDate": "2023-10-04T22:19:17Z"
  },
  {
    "Name": "Eve White",
    "Age": 32,
    "Country": "Germany",
    "Email": "evew@example.com",
    "RegDate": "2023-10-04T03:58:29Z"
  },
  {
    "Name": "Charlie Black",
    "Age": 22,
    "Country": "Japan",
    "Email": "charlieb@example.com",
    "RegDate": "2023-10-04T17:34:08Z"
  }
]

轉 Excel 的重責大任當然是交給 ClosedXML 了。先示範 .NET Framework + Json.NET 版本：

using ClosedXML.Excel;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.IO;
using System.Linq;

namespace NetFxDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            var json = File.ReadAllText("sample.json");
            var data = JsonConvert.DeserializeObject<JObject[]>(json);

            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("sample");
            var headers = data.First().Properties().Select(p => p.Name).ToList();
            for (int i = 0; i < headers.Count; i++)
            {
                ws.Cell(1, i + 1).Value = headers[i];
            }

            var headerRow = ws.Row(1);
            headerRow.Style.Font.Bold = true;
            headerRow.Style.Fill.BackgroundColor = XLColor.LightGray;
            headerRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;


            for (int i = 0; i < data.Length; i++)
            {
                var properties = data[i].Properties();
                int j = 1;
                foreach (var property in properties)
                {
                    var valueType = property.Value.Type;
                    var value = property.Value;
                    switch (valueType)
                    {
                        case JTokenType.Integer:
                        case JTokenType.Float:
                            ws.Cell(i + 2, j++).Value = value.ToObject<double>();
                            break;
                        case JTokenType.Date:
                            ws.Cell(i + 2, j++).Value = value.ToObject<DateTime>().ToString("yyyy-MM-dd HH:mm:ss");
                            break;
                        default:
                            ws.Cell(i + 2, j++).Value = value.ToString();
                            break;
                    }   
                }   
            }

            ws.Columns().AdjustToContents();
            ws.Columns().ToList().ForEach(c => c.Width = c.Width + 1);

            wb.SaveAs("output.xlsx");
        }
    }
}

針對數字及日期型別我加了一些額外邏輯，比起一律轉字串，Excel 顯示樣式會更精緻一些，這部分可依實際需求調整以符合期望結果。

再示範 .NET 6+ 搭配 System.Text.Json 如何完成。

using ClosedXML.Excel;
using System.Text.Json;

var json = File.ReadAllText("sample.json");
var data = JsonSerializer.Deserialize<JsonElement[]>(json);

var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("sample");
var headers = data.First().EnumerateObject().Select(p => p.Name).ToList();
for (int i = 0; i < headers.Count; i++)
{
    ws.Cell(1, i + 1).Value = headers[i];
}
var headerRow = ws.Row(1);
headerRow.Style.Font.Bold = true;
headerRow.Style.Fill.BackgroundColor = XLColor.LightGray;
headerRow.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

for (int i = 0; i < data.Length; i++)
{
    var properties = data[i].EnumerateObject();
    int j = 1;
    foreach (var property in properties)
    {
        var valueKind = property.Value.ValueKind;
        if (valueKind == JsonValueKind.Number)
        {
            ws.Cell(i + 2, j++).Value = property.Value.GetDouble();

        }
        else
        {
            string valueString = property.Value.ToString();
            if (valueKind == JsonValueKind.String &&
                DateTime.TryParse(valueString, out DateTime date))
                valueString = date.ToString("yyyy-MM-dd HH:mm:ss");
            ws.Cell(i + 2, j++).Value = valueString;
        }
    }
}
ws.Columns().AdjustToContents();
ws.Columns().ToList().ForEach(c => c.Width = c.Width + 1);
wb.SaveAs("output.xlsx");

執行結果。

【2024-05-14 補充】

因有讀者敲碗，再補上 Excel 轉 JSON 範例。.NET Framework + Json.NET 版本：

static void Main(string[] args)
{
    var wb = new XLWorkbook("sample.xlsx");
    var ws = wb.Worksheet(1);

    var headerRow = ws.Row(1);
    var headers = headerRow.Cells().Select(cell => cell.Value.ToString()).ToList();

    var data = new JArray();
    var rows = ws.RowsUsed().Skip(1);
    foreach (var row in rows)
    {
        var rowData = new JObject();
        for (int i = 0; i < headers.Count; i++)
        {
            var cell = row.Cell(i + 1);
            if (cell.Value.IsNumber)
                rowData[headers[i]] = cell.Value.GetNumber();
            else if (cell.Value.IsDateTime)
                rowData[headers[i]] = cell.Value.GetDateTime();
            else
                rowData[headers[i]] = cell.Value.ToString();
        }
        data.Add(rowData);
    }

    var json = JsonConvert.SerializeObject(data, Formatting.Indented);
    File.WriteAllText("output.json", json);
}

.NET 6+ + System.Text.Json 版本。

using ClosedXML.Excel;
using System.Text.Json;
using System.IO;
using System.Text.Json.Nodes;

var wb = new XLWorkbook("sample.xlsx");
var ws = wb.Worksheet(1);

var headerRow = ws.Row(1);
var headers = headerRow.Cells().Select(cell => cell.Value.ToString()).ToList();

var data = new JsonArray();
var rows = ws.RowsUsed().Skip(1);
foreach (var row in rows)
{
    var rowData = new JsonObject();
    for (int i = 0; i < headers.Count; i++)
    {
        var cell = row.Cell(i + 1);
        if (cell.Value.IsNumber)
            rowData[headers[i]] = cell.Value.GetNumber();
        else if (cell.Value.IsDateTime)
            rowData[headers[i]] = cell.Value.GetDateTime();
        else
            rowData[headers[i]] = cell.Value.ToString();
    }
    data.Add(rowData);
}

var options = new JsonSerializerOptions
{
    WriteIndented = true
};

var json = JsonSerializer.Serialize(data, options);
File.WriteAllText("output.json", json);

.NET Framework and .NET 6+ examples to convert JSON to Excel with ClosedXML.