C
C#•9mo ago
bᥲkᥙg᥆

excel modification

https://pastecode.io/s/ndzcjm6s can i get some tips how can i improve my code i am a complete beginner in c#
No description
59 Replies
Mango
Mango•9mo ago
What code are you trying to improve? I just see an excel sheet Edit: I see you added a pastebin link to the post now 👍
Salman
Salman•9mo ago
code looks fine already
maxmahem
maxmahem•9mo ago
You don't need the JsonPropertyName attributes if the property name matches the json name. You have a lot of nullable properties. I would make these required if they are, well, required in the JSON. Only leave them nullable if the property is optional. You can use this syntax to avoid having to nest using: using var workbook = new XLWorkbook(); oooh I see you are using the properties for headers. Mmmmm I'd have to ponder that. I don't think relying on property order is a great strategy, but I don't know of a better one offhand.
maxmahem
maxmahem•9mo ago
I guess I would use the DisplayAttribute for that instead. https://learn.microsoft.com/en-us/dotnet/api/system.componentmodel.dataannotations.displayattribute?view=net-8.0
DisplayAttribute Class (System.ComponentModel.DataAnnotations)
Provides a general-purpose attribute that lets you specify localizable strings for types and members of entity partial classes.
maxmahem
maxmahem•9mo ago
so all this code here: 
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")}";
                        }
                    }
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")}";
                        }
                    }
Could be probably handled better with another property instead. in fact you might consider moving all the properties to a DTO object and then having another object that wraps it that fomats the data for display. This is called the decorator pattern. since you aren't doing anything fancy with the exceptions other than logging and exiting, you might simply move all the code out of main into a seperate function, and then handle the catch there.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
https://pastecode.io/s/m4n86vem how about this one guys since i reduced lots of condition statement i was trying to name them in first letter capital that why i definaed json property to give them new name
maxmahem
maxmahem•9mo ago
Yeah, you don't need to bother with that if you set the PropertyNameCaseInsensitive property, which you did. But actually I think that is the default, so you don't even need to bother with that.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
https://pastecode.io/s/d3nfzmk1 json data is this good enough as beginner
maxmahem
maxmahem•9mo ago
I mean if it does what you want, sure. The only major thing I would point out is the nullable type issue.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
so how should i ressolve it required?
maxmahem
maxmahem•9mo ago
mark properties that should not be null as required stj will then throw if the property is not present
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
should not be null or that is not null in my json data?
maxmahem
maxmahem•9mo ago
if it is required it should not be nullable
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
like documentId
maxmahem
maxmahem•9mo ago
values that should always have a value in your json data should probably all be marked required even struct ones this sill cause STJ to throw if they are not present during deserialization.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
one of the problem is i am deleting some colums like firsta nd second name
maxmahem
maxmahem•9mo ago
so, is the composite name present in your json?
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
what do u mean if are talking about clientfirst and last name them yes they r present in json data
maxmahem
maxmahem•9mo ago
no I mean properties like clientName
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
no i added them
maxmahem
maxmahem•9mo ago
I would then consider doing a two-object approach like I said.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
clientname and duration
maxmahem
maxmahem•9mo ago
a JSON based DTO object and another object that presents the data formated. which wraps the DTO object
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
can u give me example any example
maxmahem
maxmahem•9mo ago
something like... 
class DocumentDTO {
    public required int DocumentId { get; set; }
    public required string SiteName { get; set; }
    public required DateTime ServiceDate { get; set; }
    public required string TemplateName { get; set; }
    public required string ClientFName { get; set; }
    public string ClientMName { get; set; } = "";
    public required string ClientLName { 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.Format("YY-mm-dd");

    [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}";
}
class DocumentDTO {
    public required int DocumentId { get; set; }
    public required string SiteName { get; set; }
    public required DateTime ServiceDate { get; set; }
    public required string TemplateName { get; set; }
    public required string ClientFName { get; set; }
    public string ClientMName { get; set; } = "";
    public required string ClientLName { 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.Format("YY-mm-dd");

    [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}";
}
This gives you a deterministic order as well.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
DocumentDTO should be matching with json data keys like in my json its documentId:10
maxmahem
maxmahem•9mo ago
if you set that property, stj will figure it out.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
PropertyNameCaseInsensitive will turn off this one
maxmahem
maxmahem•9mo ago
it works just fine
MODiX
MODiX•9mo ago
maxmahem
sharplab.io (click here) 
string json =
"""
[ {
    "documentId": 2461,
    "documentTemplateId": 1119,
    "templateTypeId": 1,
    "clientId": "8FB1074D-D5C7-4A00-A80A-E645768D7C00",
    "serviceId": 0,
    "siteId": 491,
    "siteName": "SmartData",
// 208 more lines. Follow the link to view.
string json =
"""
[ {
    "documentId": 2461,
    "documentTemplateId": 1119,
    "templateTypeId": 1,
    "clientId": "8FB1074D-D5C7-4A00-A80A-E645768D7C00",
    "serviceId": 0,
    "siteId": 491,
    "siteName": "SmartData",
// 208 more lines. Follow the link to view.
React with ❌ to remove this embed.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
thanks how can i access display names [Display(Name = "Document Id", Order = 0)]
maxmahem
maxmahem•9mo ago
reflection
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
what
maxmahem
maxmahem•9mo ago
something like... 
var properties = from property in typeof(DocumentDisplay).GetProperties()
                 where Attribute.IsDefined(property, typeof(DisplayAttribute))
                 let displayAttribute = (DisplayAttribute) property.GetCustomAttributes(typeof(DisplayAttribute), false).Single()
                 orderby displayAttribute.Order
                 select displayAttribute.Name;
var properties = from property in typeof(DocumentDisplay).GetProperties()
                 where Attribute.IsDefined(property, typeof(DisplayAttribute))
                 let displayAttribute = (DisplayAttribute) property.GetCustomAttributes(typeof(DisplayAttribute), false).Single()
                 orderby displayAttribute.Order
                 select displayAttribute.Name;
you would probably want to hydrate this together with the property object though. 
var propertyNameEnum = from property in typeof(DocumentDisplay).GetProperties()
                 where Attribute.IsDefined(property, typeof(DisplayAttribute))
                 let displayAttribute = (DisplayAttribute) property.GetCustomAttributes(typeof(DisplayAttribute), false).Single()
                 orderby displayAttribute.Order
                 select (Property: property, Name: displayAttribute.Name);
var propertyNameArr = propertyNameEnum.ToImmutableArray();
var propertyNameEnum = from property in typeof(DocumentDisplay).GetProperties()
                 where Attribute.IsDefined(property, typeof(DisplayAttribute))
                 let displayAttribute = (DisplayAttribute) property.GetCustomAttributes(typeof(DisplayAttribute), false).Single()
                 orderby displayAttribute.Order
                 select (Property: property, Name: displayAttribute.Name);
var propertyNameArr = propertyNameEnum.ToImmutableArray();
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
let me send u my code in few min
maxmahem
maxmahem•9mo ago
Then you would access them via like... 
foreach(var name in propertyNameArr.Select(element => element.Name)) {
    // set name into header
}

foreach(var entry in dtoList.Select(dto => new DocumentDisplay(dto)) {
    foreach(var prop in propertyNameArr.Select(element => element.Property)) {
      var propValue = prop.GetValue(entry);
      // set propValue into cell
    }
}
foreach(var name in propertyNameArr.Select(element => element.Name)) {
    // set name into header
}

foreach(var entry in dtoList.Select(dto => new DocumentDisplay(dto)) {
    foreach(var prop in propertyNameArr.Select(element => element.Property)) {
      var propValue = prop.GetValue(entry);
      // set propValue into cell
    }
}
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
this look lethal
maxmahem
maxmahem•9mo ago
lethal bad? Good? Your using reflection either way here. Adding the order property just makes order deterministic.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
this too good for me even if i understand ill just use properties let me send u because i have given this work as a beginner and they probably think i copied from somewhere good can i iterate through DocumentDisplay foreach(var entry in dtoList.Select(dto => new DocumentDisplay(dto)) { foreach(var prop in propertyNameArr.Select(element => element.Property)) { var propValue = prop.GetValue(entry); // set propValue into cell } } not like this something more simpler 
foreach (var documentDTO in data)
        {
            var documentDisplay = new DocumentDisplay(documentDTO);
            var properties=typeof(DocumentDisplay).GetProperties();
           Console.WriteLine($"Document Id: {documentDisplay.DocumentId}");
            Console.WriteLine($"Site Name: {documentDisplay.SiteName}");
            Console.WriteLine($"Service Date: {documentDisplay.ServiceDate}");
            Console.WriteLine($"Template Name: {documentDisplay.TemplateName}");
            Console.WriteLine($"Client Name: {documentDisplay.ClientName}");

        }
    }
foreach (var documentDTO in data)
        {
            var documentDisplay = new DocumentDisplay(documentDTO);
            var properties=typeof(DocumentDisplay).GetProperties();
           Console.WriteLine($"Document Id: {documentDisplay.DocumentId}");
            Console.WriteLine($"Site Name: {documentDisplay.SiteName}");
            Console.WriteLine($"Service Date: {documentDisplay.ServiceDate}");
            Console.WriteLine($"Template Name: {documentDisplay.TemplateName}");
            Console.WriteLine($"Client Name: {documentDisplay.ClientName}");

        }
    }
in this case i have to use .
maxmahem
maxmahem•9mo ago
sure. You used reflection earlier, so I gave a reflection based solution
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
can we just iterate through it
maxmahem
maxmahem•9mo ago
well, that's what I showed. The issue with the code you had earlier is that iterating properties in the way you were is not (AFAIK) deterministic. That is, the order the properties are enumerated is not guranteed to be the same. The way to avoid this is to either A. Order them by a deterministic attribute, such as order, which I showed above. B. Manually order them.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
https://pastecode.io/s/0iraj2qk i am very proud of code since i am not even using display name that you told me but it does helped me solve addind and removing unnecessary columns but its too hasrd for me to get can i remove Display since i am using it?
maxmahem
maxmahem•9mo ago
looks fine, though you could have ServiceRate just be a decimal and avoid parsing it.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
i was thinking if i can just get all the values through iteration but this is just too much for me understanf rn
maxmahem
maxmahem•9mo ago
this is very similar to what you are doing, but just pulls values from a property attribute as well. I can go over what it's doing if you like.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
Unhandled exception. System.Text.Json.JsonException: The JSON value could not be converted to System.Decimal. Path: $[0].serviceRate | LineNumber: 49 | BytePositionInLine: 23. public decimal ServiceRate { get; set; } if i did that i am getting error sure if i can understand
maxmahem
maxmahem•9mo ago
ahh, looks like ServiceRate is sometimes null in your data. Unfortunate.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
yeah it is
maxmahem
maxmahem•9mo ago
You might try making it nullable. But I'm not 100% on how STJ will handle that with a struct type.
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
that's why i was using it as string
maxmahem
maxmahem•9mo ago
let me check
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
using ?
maxmahem
maxmahem•9mo ago
yeah. Works. but nullable struct types have some different properties
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
iil just use string explain
maxmahem
maxmahem•9mo ago
how familiar are you with linq?
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
noob just started c# yesterday
maxmahem
maxmahem•9mo ago
mmm.... might be to much to explain then what is this for btw?
bᥲkᥙg᥆
bᥲkᥙg᥆OP•9mo ago
nah its fine i'll just use this my friend introduce me to some company they were looking for backend and ui dev i am new in c# so they gave me this json data and told me to convert into excel with my basic knowledge i somehow managed to make it i am just now improving
Want results from more Discord servers?
Add your server