Untitled
user_0623289
csharp
a year ago
9.8 kB
10
Indexable
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 placeserviceL { [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<placeserviceL> placeserviceL { 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"); dt.Columns.Add("PlaceOfServiceList"); 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 if (column.ColumnName == "placeserviceL") { var placeOfServiceList = value as List<placeserviceL>; if (placeOfServiceList != null) { string placeOfServiceListString = JsonSerializer.Serialize(placeOfServiceList); // Console.WriteLine(placeOfServiceListString); row["PlaceOfServiceList"] = placeOfServiceListString; } } else { row[column.ColumnName] = value; } } row["ClientName"] = string.Join(" ", row["ClientFirstName"], row["ClientMiddleName"], row["ClientLastName"]); 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("placeserviceL"); dt.Columns.Remove("StaffLastName"); try { using (var workbook = new XLWorkbook()) { var worksheet = workbook.Worksheets.Add("Sheet1"); var tableRange = 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}"); } } }
Editor is loading...
Leave a Comment