Untitled

 avatar
user_0623289
csharp
2 months ago
3.3 kB
6
Indexable
Never
using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Globalization;
using System.Text.Json;

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 = JsonSerializer.Deserialize<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);
        }

        dt.Columns.Add("ClientName");
        dt.Columns.Add("StaffName");

        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(column.Value.ToString(), out decimal rate) ? rate.ToString("C", CultureInfo.CurrentCulture) : "$0.00" : "$0.00";
                }
                else if (column.Key == "clientFName" || column.Key == "clientMName" || column.Key == "clientLName" ||
                         column.Key == "staffFName" || column.Key == "staffMName" || column.Key == "staffLName")
                {
                    continue;
                }
                else
                {
                    newRow[column.Key] = column.Value;
                }
            }

            newRow["ClientName"] = string.Join(" ", row["clientFName"], row["clientMName"], row["clientLName"]);

            newRow["StaffName"] = string.Join(" ", row["staffFName"], row["staffLName"]);

            dt.Rows.Add(newRow);
        }

        dt.Columns.Remove("clientFName");
        dt.Columns.Remove("clientMName");
        dt.Columns.Remove("clientLName");
        dt.Columns.Remove("staffFName");
        //dt.Columns.Remove("staffMName");
        dt.Columns.Remove("staffLName");

        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