Untitled

using System;
using System.Data;
using System.Text.Json;
using System.Text.Json.Serialization;
using ClosedXML.Excel;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Globalization;

public class PlaceOfServiceList
{
    [JsonPropertyName("id")]
    public int Id { get; set; }

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

    [JsonPropertyName("locCode")]
    public string LocCode { get; set; }

    [JsonPropertyName("placeOfServiceName")]
    public string PlaceOfServiceName { get; set; }
}

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

    [JsonPropertyName("documentTemplateId")]
    public int DocumentTemplateId { get; set; }

    [JsonPropertyName("templateTypeId")]
    public int TemplateTypeId { get; set; }

    [JsonPropertyName("clientId")]
    public Guid ClientId { get; set; }

    [JsonPropertyName("serviceId")]
    public int ServiceId { get; set; }

    [JsonPropertyName("siteId")]
    public int SiteId { get; set; }

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

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

    [JsonPropertyName("utcDateCreated")]
    public string? UtcDateCreated { get; set; }

    [JsonPropertyName("templateName")]
    public string TemplateName { 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("recordingMethodId")]
    public int RecordingMethodId { get; set; }

    [JsonPropertyName("shiftId")]
    public int ShiftId { get; set; }

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

    [JsonPropertyName("totalMinutes")]
    public float TotalMinutes { get; set; }

    [JsonPropertyName("service")]
    public string Service { get; set; }

    [JsonPropertyName("totalRecords")]
    public int TotalRecords { get; set; }

    [JsonPropertyName("docStatusId")]
    public int DocStatusId { get; set; }

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

    [JsonPropertyName("placeOfServiceList")]
    public List<PlaceOfServiceList> PlaceOfServiceList { get; set; }

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

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

    [JsonPropertyName("serviceCode")]
    public string ServiceCode { get; set; }

    [JsonPropertyName("modifier")]
    public string? Modifier { get; set; }

    [JsonPropertyName("secModifier")]
    public string? SecModifier { get; set; }

    [JsonPropertyName("thirdModifier")]
    public string? ThirdModifier { get; set; }

    [JsonPropertyName("fourthModifier")]
    public string? FourthModifier { get; set; }

    [JsonPropertyName("ageModifier")]
    public string? AgeModifier { get; set; }

    [JsonPropertyName("isActive")]
    public bool IsActive { get; set; }

    [JsonPropertyName("createdByStaff")]
    public string CreatedByStaff { get; set; }

    [JsonPropertyName("isFaceToFace")]
    public bool IsFaceToFace { get; set; }

    [JsonPropertyName("isIndirectVisit")]
    public bool IsIndirectVisit { get; set; }

    [JsonPropertyName("isTelehealth")]
    public bool IsTelehealth { get; set; }

    [JsonPropertyName("diagnosis")]
    public string? Diagnosis { get; set; }

    [JsonPropertyName("primaryInsurance")]
    public string PrimaryInsurance { get; set; }

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

    [JsonPropertyName("numUnits")]
    public long NumUnits { get; set; }

    [JsonPropertyName("amtBilled")]
    public string? AmtBilled { get; set; }

    [JsonPropertyName("custAuthId")]
    public string? CustAuthId { get; set; }

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

    [JsonPropertyName("lockedByStaff")]
    public string LockedByStaff { get; set; }

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

    [JsonPropertyName("createdBy")]
    public int CreatedBy { get; set; }

    [JsonPropertyName("clientDiagnosisId")]
    public int ClientDiagnosisId { get; set; }

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

    [JsonPropertyName("isNoteReviewed")]
    public bool IsNoteReviewed { get; set; }

    [JsonPropertyName("billingStatusId")]
    public int BillingStatusId { get; set; }

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

    [JsonPropertyName("supervisorStaffId")]
    public int SupervisorStaffId { get; set; }

    [JsonPropertyName("supervisorName")]
    public string? SupervisorName { get; set; }

    [JsonPropertyName("payrollDate")]
    public string  PayrollDate { get; set; }

    [JsonPropertyName("payrollPaid")]
    public bool PayrollPaid { get; set; }

    [JsonPropertyName("isSignedByClient")]
    public bool IsSignedByClient { get; set; }

    [JsonPropertyName("isSignedByReviewer")]
    public bool IsSignedByReviewer { get; set; }

    [JsonPropertyName("isSignedByAuthor")]
    public bool IsSignedByAuthor { get; set; }

    [JsonPropertyName("isSignedBySupervisor")]
    public bool IsSignedBySupervisor { get; set; }

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

    [JsonPropertyName("lastBilledDate")]
    public string LastBilledDate { get; set; }

    [JsonPropertyName("batchId")]
    public int BatchId { get; set; }

    [JsonPropertyName("transId")]
    public int TransId { 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 data = JsonSerializer.Deserialize<List<Document>>(jsonData);

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

        DataTable dt = new DataTable();
        dt.Columns.Add("ClientName");
        dt.Columns.Add("StaffName");
        foreach (var property in typeof(Document).GetProperties())
        {
            dt.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
        }

        foreach (var item in data)
        {
            DataRow row = dt.NewRow();


            foreach (DataColumn column in dt.Columns)
            {
                var property = typeof(Document).GetProperty(column.ColumnName);
                var value = property?.GetValue(item);
                if (column.ColumnName == "ServiceRate")
                        {
                            row[column.ColumnName] = value != null ? decimal.TryParse(value.ToString(), out decimal rate) ? rate.ToString("C", CultureInfo.CurrentCulture) : "$0.00" : "$0.00";
                }
                else{
                    row[column.ColumnName] = value;
                }
                
                
            }
            row["ClientName"] = string.Join(" ", row["ClientFirstName"], row["ClientMiddleName"], row["ClientLastName"]);
            //Console.WriteLine(row["ClientFirstName"]);
            row["StaffName"] = string.Join(" ", row["StaffFirstName"], row["StaffLastName"]);
            dt.Rows.Add(row);
        }
        dt.Columns.Remove("ClientFirstName");
        dt.Columns.Remove("ClientMiddleName");
        dt.Columns.Remove("ClientLastName");
        dt.Columns.Remove("StaffFirstName");
        //dt.Columns.Remove("staffMName");
        dt.Columns.Remove("StaffLastName");
        
        try
{
    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);
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Error saving Excel file: {ex.Message}");
}

    }
}