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
        {
            if (DateTime.TryParse(StartTime, out DateTime startTime) && DateTime.TryParse(EndTime, out DateTime endTime))
            {
                return $"{startTime.ToString("hh:mm tt")} - {endTime.ToString("hh:mm tt")}";
            }
            return "";
        }
    }

    [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++)
            {

                headerRow.Cell(i + 1).Value = properties[i].Name;
            }

            int rowIndex = 2;
            foreach (var item in data)
            {
                int columnIndex = 1;
                var row = worksheet.Row(rowIndex);

                row.Cell(columnIndex++).Value = item.DocumentId;
                row.Cell(columnIndex++).Value = item.SiteName;
                row.Cell(columnIndex++).Value = item.ServiceDate.ToString("dd-MM-yyyy");
                row.Cell(columnIndex++).Value = item.TemplateName;
                row.Cell(columnIndex++).Value = string.Join(" ", item.ClientFirstName, item.ClientMiddleName, item.ClientLastName);
                row.Cell(columnIndex++).Value = item.ClientFirstName;
                row.Cell(columnIndex++).Value = item.ClientMiddleName;
                row.Cell(columnIndex++).Value = item.ClientLastName;
                row.Cell(columnIndex++).Value = item.DateOfBirth;
                row.Cell(columnIndex++).Value = item.StartTime;
                row.Cell(columnIndex++).Value = item.EndTime;
                row.Cell(columnIndex++).Value = item.Duration;
                row.Cell(columnIndex++).Value = item.ShiftName;
                row.Cell(columnIndex++).Value = item.DocStatus;
                row.Cell(columnIndex++).Value = string.Join(", ", item.StaffFirstName, item.StaffLastName);
                row.Cell(columnIndex++).Value = item.StaffFirstName;
                row.Cell(columnIndex++).Value = item.StaffLastName;
                row.Cell(columnIndex++).Value = decimal.TryParse(item.ServiceRate, out decimal rate) ? rate : "";
                row.Cell(columnIndex - 1).Style.NumberFormat.Format = "$ #,##0.00";
                row.Cell(columnIndex++).Value = item.ClientDiagnosisName;
                row.Cell(columnIndex++).Value = item.Signed;
                row.Cell(columnIndex++).Value = item.Locked;
                row.Cell(columnIndex++).Value = item.AuthId;
                row.Cell(columnIndex++).Value = item.BillingStatusName;

                rowIndex++;
            }

            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}");
            }
        }
    }
}