Untitled

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



public class Document
{
    [JsonPropertyName("documentId")]
    public int DocumentId { get; set; }

    [JsonPropertyName("siteName")]
    public string? SiteName { get; set; }

    [JsonPropertyName("serviceDate")]
    public DateTime ServiceDate { get; set; }

    [JsonPropertyName("templateName")]
    public string? TemplateName { get; set; }

    [JsonPropertyName("clientName")]
    public string? ClientName { get; set; }="";

    [JsonPropertyName("clientFName")]
    public string? ClientFirstName { get; set; }

    [JsonPropertyName("clientMName")]
    public string? ClientMiddleName { get; set; }

    [JsonPropertyName("clientLName")]
    public string? ClientLastName { get; set; }

    [JsonPropertyName("dateOfBirth")]
    public string? DateOfBirth { get; set; }

    [JsonPropertyName("startTime")]
    public string? StartTime { get; set; }

    [JsonPropertyName("endTime")]
    public string? EndTime { get; set; }

    [JsonPropertyName("duration")]
    public string? Duration { get; set; }="";

    [JsonPropertyName("shiftName")]
    public string? ShiftName { get; set; }

    [JsonPropertyName("docStatus")]
    public string? DocStatus { get; set; }

    [JsonPropertyName("staffName")]
    public string? StaffName { get; set; }="";

    [JsonPropertyName("staffFName")]
    public string? StaffFirstName { get; set; }

    [JsonPropertyName("staffLName")]
    public string? StaffLastName { get; set; }

    [JsonPropertyName("serviceRate")]
    public string? ServiceRate { get; set; }

    [JsonPropertyName("clientDiagnosisName")]
    public string? ClientDiagnosisName { get; set; }

    [JsonPropertyName("isSigned")]
    public bool Signed { get; set; }

    [JsonPropertyName("isLocked")]
    public bool Locked { get; set; }

    [JsonPropertyName("authId")]
    public int AuthId { get; set; }

    [JsonPropertyName("billingStatusName")]
    public string? BillingStatusName { get; set; }
}

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 options = new JsonSerializerOptions
        {
            PropertyNameCaseInsensitive = true
        };

        var data = JsonSerializer.Deserialize<List<Document>>(jsonData, options);


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

        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("clientData");
            var headerRow = worksheet.Row(1);
            var properties = typeof(Document).GetProperties().ToList();
            for (int i = 0; i < properties.Count; i++)
            {
                // Console.WriteLine(properties[i].Name);
                headerRow.Cell(i + 1).Value = properties[i].Name;
            }

            for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
            {
                var item = data[rowIndex];
                var currentRow = worksheet.Row(rowIndex + 2);
                for (int colIndex = 0; colIndex < properties.Count; colIndex++)
                {
                    var propertyValue = properties[colIndex].GetValue(item);
                    var cell = currentRow.Cell(colIndex + 1);

                    if (propertyValue != null)
                    {

                        if (properties[colIndex].PropertyType == typeof(DateTime))
                        {

                            cell.Value = ((DateTime)propertyValue).ToString("dd-MM-yyyy");
                        }
                        else if (properties[colIndex].Name == "ServiceRate")
                        {
                            if (decimal.TryParse(propertyValue.ToString(), out decimal rate))
                            {
                                cell.Value = rate;
                                cell.Style.NumberFormat.Format = "$ #,##0.00";
                            }
                        }else if (properties[colIndex].Name=="ClientName"){
                            cell.Value = string.Join(" ", item.ClientFirstName, item.ClientMiddleName, item.ClientLastName);
                        }else if (properties[colIndex].Name=="StaffName"){
                            cell.Value = string.Join(", ", item.StaffFirstName, item.StaffLastName);
                        }else if(properties[colIndex].Name=="Duration"){
                            DateTime startTime;
                            DateTime endTime;
                            if (DateTime.TryParse(item.StartTime, out startTime) && DateTime.TryParse(item.EndTime, out endTime))
                            {
                                cell.Value = $"{startTime.ToString("hh:mm tt")} - {endTime.ToString("hh:mm tt")}";
                            }
                        }
                        else
                        {

                            cell.Value = propertyValue.ToString();
                        }
                    }
                    else
                    {

                        cell.Value = string.Empty;
                    }
                }

                
                
            }

            worksheet.Columns("Q").Delete();
            worksheet.Columns("P").Delete();
            worksheet.Columns("K").Delete();
            worksheet.Columns("J").Delete();
            worksheet.Columns("H").Delete();
            worksheet.Columns("G").Delete(); 
            worksheet.Columns("F").Delete();


            try
            {
                worksheet.Columns().AdjustToContents();
                workbook.SaveAs("output.xlsx");
                Console.WriteLine("Excel file saved as: output.xlsx");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error saving Excel file: {ex.Message}");
            }
        }
    }
}