Untitled

using System.Data;
using System.Text.Json;
using System.Text.Json.Serialization;
using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;




public class DocumentDTO
{
  public required int DocumentId { get; set; }

  public string SiteName { get; set; }="";

  public DateTime ServiceDate { get; set; }

  public string TemplateName { get; set; }="";

  public required string ClientFName { get; set; }

  public required string ClientMName { get; set; }="";

  public required string ClientLName { get; set; }

  public required string DateOfBirth { get; set; }

  public required DateTime StartTime { get; set; }

  public required DateTime EndTime { get; set; }

  public string ShiftName { get; set; }="";

  public string DocStatus { get; set; }="";

  public string StaffName { get; set; } = "";


  public required string StaffFName { get; set; }

  public required string StaffLName { get; set; }

  public string ServiceRate { get; set; }="";

  public string ClientDiagnosisName { get; set; }="";

  public bool Signed { get; set; }

  public bool Locked { get; set; }

  public int AuthId { get; set; }

  public string BillingStatusName { get; set; }="";
}


class DocumentDisplay(DocumentDTO document) {
    [Display(Name = "Document Id", Order = 0)]
    public int DocumentId => document.DocumentId;

    [Display(Name = "Site Name", Order = 1)]
    public string SiteName => document.SiteName;

    [Display(Name = "Service Date", Order = 2)]
    public string ServiceDate => document.ServiceDate.ToString("dd-MM-yyyy");

    [Display(Name = "Template Name", Order = 3)]
    public string TemplateName => document.TemplateName;

    [Display(Name = "Client Name", Order = 4)]
    public string ClientName => $"{document.ClientFName} {document.ClientMName} {document.ClientLName}";

    [Display(Name = "Date of Birth", Order = 6)]
    public string DateOfBirth => document.DateOfBirth;

    // [Display(Name = "Start Time", Order = 7)]
    // public string StartTime => document.StartTime;

    // [Display(Name = "End Time", Order = 8)]
    // public string EndTime => document.EndTime;

    [Display(Name = "Shift Name", Order = 9)]
    public string ShiftName => document.ShiftName;

    [Display(Name = "Duration", Order = 9)]
    public string Duration => $"{document.StartTime.ToString("hh:mm tt")} - {document.EndTime.ToString("hh:mm tt")}";

    [Display(Name = "Document Status", Order = 10)]
    public string DocStatus => document.DocStatus;

    [Display(Name = "Staff Name", Order = 11)]
    public string StaffName => $"{document.StaffFName} {document.StaffLName}";

    [Display(Name = "Service Rate", Order = 13)]
    public string ServiceRate => document.ServiceRate;

    [Display(Name = "Client Diagnosis Name", Order = 15)]
    public string ClientDiagnosisName => document.ClientDiagnosisName;

    [Display(Name = "Signed", Order = 16)]
    public bool Signed => document.Signed;

    [Display(Name = "Locked", Order = 17)]
    public bool Locked => document.Locked;

    [Display(Name = "Authorization Id", Order = 18)]
    public int AuthId => document.AuthId;

    [Display(Name = "Billing Status Name", Order = 19)]
    public string BillingStatusName => document.BillingStatusName;
}



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<DocumentDTO>>(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(DocumentDisplay).GetProperties().ToList();
            for (int i = 0; i < properties.Count; i++)
            {

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

            int rowIndex = 2;
            foreach (var documentDTO in data)
            {
                var item = new DocumentDisplay(documentDTO);
                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;
                row.Cell(columnIndex++).Value = item.TemplateName;
                row.Cell(columnIndex++).Value = item.ClientName;
                row.Cell(columnIndex++).Value = item.DateOfBirth;
                row.Cell(columnIndex++).Value = item.ShiftName;
                row.Cell(columnIndex++).Value = item.Duration;
                row.Cell(columnIndex++).Value = item.DocStatus;
                row.Cell(columnIndex++).Value = item.StaffName;
                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++;
            }


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