Untitled

 avatar
user_0623289
csharp
2 months ago
2.5 kB
4
Indexable
Never
using ClosedXML.Excel;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Globalization;

class Program
{
    static void Main(string[] args)
    {
        string jsonData;
        try
        {
            jsonData = File.ReadAllText("data.json");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error reading JSON file: {ex.Message}");
            return;
        }

        var data = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(jsonData);

        if (data == null || data.Count == 0)
        {
            Console.WriteLine("No data found in JSON file.");
            return;
        }

        DataTable dt = new DataTable();

        foreach (var columnName in data[0].Keys)
        {
            dt.Columns.Add(columnName);
        }

        foreach (var row in data)
        {
            var newRow = dt.NewRow();
            foreach (var column in row)
            {
                if (column.Key == "serviceRate")
                {
                    newRow[column.Key] = column.Value != null ? decimal.TryParse((string)column.Value, out decimal rate) ? rate.ToString("C", CultureInfo.CurrentCulture) : "$0.00" : "$0.00";
                }
                else
                {
                    newRow[column.Key] = column.Value;
                }
            }
            dt.Rows.Add(newRow);
        }

        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("Sheet1");

            worksheet.Cell(1, 1).InsertTable(dt);

            var range = worksheet.RangeUsed();
            if (range != null)
            {
                range.Style.Font.FontSize = 11;
                range.Style.Font.FontName = "Calibri";
                range.Style.Font.Bold = false;
                range.Style.Alignment.WrapText = false;

                foreach (var cell in range.FirstRow().CellsUsed())
                {
                    cell.Style.Fill.BackgroundColor = XLColor.FromHtml("#7a7a7a");
                }
            }
            else
            {
                Console.WriteLine("Worksheet has no used range.");
            }

            string fileName = "output.xlsx";
            workbook.SaveAs(fileName);

            Console.WriteLine("Excel file saved as: " + fileName);
        }
    }
}
Leave a Comment