C#•3mo ago

Excel modify

i am trying to make this excel looks like this but not working can anyone help https://pastecode.io/s/ew5iwzjn

101 Replies

start by not doing this JsonSerializer.Deserialize<List<Dictionary<string, object>>>(jsonData); make a proper model to deserialize to you can add filters with https://github.com/ClosedXML/ClosedXML/wiki/Adding-an-AutoFilter-to-a-Range

bᥲkᥙg᥆•3mo ago

so i use record?

leowest•3mo ago

or a plain class either works for alternating the line colors u can do something like https://github.com/ClosedXML/ClosedXML/wiki/Using-Colors can u $paste the json?

MODiX•3mo ago

If your code is too long, you can post to https://paste.mod.gg/ and copy the link into chat for others to see your shared code!

bᥲkᥙg᥆•3mo ago

yes

leowest•3mo ago

or a sample of what it looks like

bᥲkᥙg᥆•3mo ago

https://pastecode.io/s/cza10o9m

using System;
using System.Collections.Generic;

public class PlaceOfService
{
    public int Id { get; set; }
    public int DocumentId { get; set; }
    public string LocCode { get; set; }
    public string PlaceOfServiceName { get; set; }
}

public class ServiceRecord
{
    public int DocumentId { get; set; }
    public int DocumentTemplateId { get; set; }
    public int TemplateTypeId { get; set; }
    public string ClientId { get; set; }
    public int ServiceId { get; set; }
    public int SiteId { get; set; }
    public string SiteName { get; set; }
    public DateTime ServiceDate { get; set; }
    public DateTime UtcDateCreated { get; set; }
    public string TemplateName { get; set; }
    public string ClientFName { get; set; }
    public string ClientMName { get; set; }
    public string ClientLName { get; set; }
    public string DateOfBirth { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
    public int RecordingMethodId { get; set; }
    public int ShiftId { get; set; }
    public string ShiftName { get; set; }
    public double TotalMinutes { get; set; }
    public string Service { get; set; }
    public int TotalRecords { get; set; }
    public int DocStatusId { get; set; }
    
}

using System;
using System.Collections.Generic;

public class PlaceOfService
{
    public int Id { get; set; }
    public int DocumentId { get; set; }
    public string LocCode { get; set; }
    public string PlaceOfServiceName { get; set; }
}

public class ServiceRecord
{
    public int DocumentId { get; set; }
    public int DocumentTemplateId { get; set; }
    public int TemplateTypeId { get; set; }
    public string ClientId { get; set; }
    public int ServiceId { get; set; }
    public int SiteId { get; set; }
    public string SiteName { get; set; }
    public DateTime ServiceDate { get; set; }
    public DateTime UtcDateCreated { get; set; }
    public string TemplateName { get; set; }
    public string ClientFName { get; set; }
    public string ClientMName { get; set; }
    public string ClientLName { get; set; }
    public string DateOfBirth { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
    public int RecordingMethodId { get; set; }
    public int ShiftId { get; set; }
    public string ShiftName { get; set; }
    public double TotalMinutes { get; set; }
    public string Service { get; set; }
    public int TotalRecords { get; set; }
    public int DocStatusId { get; set; }
    
}

like that? +.

leowest•3mo ago

https://paste.mod.gg/vettabquhzcg/0

BlazeBin - vettabquhzcg

A tool for sharing your source code with the world!

leowest•3mo ago

there are some strings that are numbers by using a option u can easily use the actual type for it but I dont know all your data to be able to say u can use the actual type for those strings but like the Modifiers for example which are all in ""

bᥲkᥙg᥆•3mo ago

will this also work

leowest•3mo ago

not sure if DateTime would work here I think u need DateTimeOffset but u can try you're also not use the PlaceOfService public List<PlaceOfService> PlaceOfServices { get; set; } its part of the ServiceRecord so its not inside it wont be deserialized

bᥲkᥙg᥆•3mo ago

inside public class PlaceOfService { public int Id { get; set; } public int DocumentId { get; set; } public string LocCode { get; set; } public string PlaceOfServiceName { get; set; } } this should i remove placeof service

leowest•3mo ago

if u use

var options = new JsonSerializerOptions
{
    NumberHandling = JsonNumberHandling.AllowReadingFromString | JsonNumberHandling.WriteAsString 
};

var options = new JsonSerializerOptions
{
    NumberHandling = JsonNumberHandling.AllowReadingFromString | JsonNumberHandling.WriteAsString 
};

u can do what I said above as in

public int? Modifier { get; set; }

public int? Modifier { get; set; }

yes u should replace it

bᥲkᥙg᥆•3mo ago

with this public List<PlaceOfService> PlaceOfServices { get; set; }

leowest•3mo ago

with

public List<PlaceOfService> PlaceOfServices {get;set;}

public List<PlaceOfService> PlaceOfServices {get;set;}

bᥲkᥙg᥆•3mo ago

ok let me try and check if its work. https://pastecode.io/s/ezt3vi66 i tried this but its not showing the string values in excel only data number and bool

bᥲkᥙg᥆•3mo ago

anyone alive?

leowest•3mo ago

and what is your code now?

bᥲkᥙg᥆•3mo ago

https://pastecode.io/s/3gpcgzsx data.json

leowest•3mo ago

I mean yoru code is still the same u haven't looked at all at the wiki https://github.com/ClosedXML/ClosedXML/wiki/Data-Types https://github.com/ClosedXML/ClosedXML/wiki/Styles-NumberFormat I dont expect things to work just because u have a model for your json that is just fixing 1 problem u have to properly use the library if u want the output to be what u expect

bᥲkᥙg᥆•3mo ago

in this code i was trying to use datetime in format or starttime endtime using datetimeoffset but i am seeing ##### in those places

leowest•3mo ago

I dont see that anywhere in the code

bᥲkᥙg᥆•3mo ago

i using string datatype instead let me show you.

leowest•3mo ago

pls do also u dont need to do this

            var placeOfServiceList = value as List<placeserviceL>;
            if (placeOfServiceList != null)
                    {

                        string placeOfServiceListString = JsonSerializer.Serialize(placeOfServiceList);
                        // Console.WriteLine(placeOfServiceListString);
                        row["PlaceOfServiceList"] = placeOfServiceListString;
                    }

            var placeOfServiceList = value as List<placeserviceL>;
            if (placeOfServiceList != null)
                    {

                        string placeOfServiceListString = JsonSerializer.Serialize(placeOfServiceList);
                        // Console.WriteLine(placeOfServiceListString);
                        row["PlaceOfServiceList"] = placeOfServiceListString;
                    }

bᥲkᥙg᥆•3mo ago

leowest•3mo ago

u would just use data.placeserviceL ok but where is the code? because the code u gave me above ur not doing anything with datetime

MODiX•3mo ago

leowest

https://github.com/ClosedXML/ClosedXML/wiki/Data-Types

Quoted by

<@1102729783969861782> from #Excel modify (click here)

React with ❌ to remove this embed.

bᥲkᥙg᥆•3mo ago

code is same i just changes it to string to DateTimeOffset or datetime

bᥲkᥙg᥆•3mo ago

leowest•3mo ago

I mean you're not using closexml at all... so yeah, I dont expect it to work read the links I send u above

bᥲkᥙg᥆•3mo ago

leowest•3mo ago

it shows examples on how to do things shows how to format currency shows how to format datetime

bᥲkᥙg᥆•3mo ago

so i have to explicitly mention the datatype in closedxml when adding row column?

leowest•3mo ago

yes if u want it to behave as that type if u dont it also shows how u handle it

bᥲkᥙg᥆•3mo ago

looks like i have to change alot

leowest•3mo ago

yep 🙂 now u see my point

bᥲkᥙg᥆•3mo ago

since i was creating a table earlier but in code it they r using cell

var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Data Types");

var co = 2;
var ro = 1;

ws.Cell(++ro, co).Value = "Plain Text:";
ws.Cell(ro, co + 1).Value = "Hello World.";

ws.Cell(++ro, co).Value = "Plain Date:";
ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2);

var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Data Types");

var co = 2;
var ro = 1;

ws.Cell(++ro, co).Value = "Plain Text:";
ws.Cell(ro, co + 1).Value = "Hello World.";

ws.Cell(++ro, co).Value = "Plain Date:";
ws.Cell(ro, co + 1).Value = new DateTime(2010, 9, 2);

leowest•3mo ago

well u would be working in a loop so there are a few ways u could do it the first one would be using Cell with column/row indexes and the other one would be using a datatable with types and adding rows

bᥲkᥙg᥆•3mo ago

lets see

leowest•3mo ago

// header
DataTable table = new DataTable();
table.Columns.Add("DocumentId", typeof(int));
table.Columns.Add("Site Name", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
// rows
foreach (var item in data)
{
    table.Rows.Add(item.DocumentId, item.SiteName, item.StartTime);
}

// header
DataTable table = new DataTable();
table.Columns.Add("DocumentId", typeof(int));
table.Columns.Add("Site Name", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
// rows
foreach (var item in data)
{
    table.Rows.Add(item.DocumentId, item.SiteName, item.StartTime);
}

bᥲkᥙg᥆•3mo ago

ngl i am new to c# and its a lot different from other languages

leowest•3mo ago

with cells u would do something like

leowest•3mo ago

perhaps u might want to take a trip to https://learn.microsoft.com/en-us/shows/csharp-fundamentals-for-absolute-beginners/

C# Fundamentals for Absolute Beginners

Learn C# programming from an expert in the industry. Get the tools, see how to write code, debug features, explore customizations, and more. For newer videos head over to dot.net/videos

leowest•3mo ago

these are short videos on the basics of c#

bᥲkᥙg᥆•3mo ago

thanks

leowest•3mo ago

but yes depending on the language it would be different yes

bᥲkᥙg᥆•3mo ago

foreach (var property in typeof(Document).GetProperties())
        {
            dt.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
        }

foreach (var property in typeof(Document).GetProperties())
        {
            dt.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
        }

fir header isnt it same?

leowest•3mo ago

the header is the top row

leowest•3mo ago

its often called a header because it just tells us what that column represents so u only need to print it once hence its outside the loop

bᥲkᥙg᥆•3mo ago

yeah i mean i am also giving types in this

leowest•3mo ago

the types in the header should propagate to the rows at least that is what the wiki says

bᥲkᥙg᥆•3mo ago

since i have lots of headers that why

leowest•3mo ago

u have 1 header with many columns as I understood but yes u dont wnat to do that

bᥲkᥙg᥆•3mo ago

DataTable table = new DataTable();
table.Columns.Add("DocumentId", typeof(int));
table.Columns.Add("Site Name", typeof(string));
table.Columns.Add("Date", typeof(DateTime));

DataTable table = new DataTable();
table.Columns.Add("DocumentId", typeof(int));
table.Columns.Add("Site Name", typeof(string));
table.Columns.Add("Date", typeof(DateTime));

leowest•3mo ago

because not all of your header represents the right type u want the excel to have like u have DateTime as string

bᥲkᥙg᥆•3mo ago

instead of doing this i am using loop

leowest•3mo ago

yes you're using a loop, but your data is not properly represented

bᥲkᥙg᥆•3mo ago

and also giving type how isnt it same thing

leowest•3mo ago

public string? UtcDateCreated { get; set; } string != DateTime that is one example but even if u do the datatable and resulting excel fails to show it properly then u would need to use the cell type directly to format it but in your case your not even creating the right types with your loop

bᥲkᥙg᥆•3mo ago

ish so i have to check every time if its a datetime then use datetime

leowest•3mo ago

because some of your data is not properly represented by its type

bᥲkᥙg᥆•3mo ago

looks like i needed more time to fix it

leowest•3mo ago

its just a matter of checking the wiki and trying to see what works for u and your data

bᥲkᥙg᥆•3mo ago

thanks

leowest•3mo ago

like for example

bᥲkᥙg᥆•3mo ago

in cell how do i define headers

leowest•3mo ago

if u want to display date time as text

bᥲkᥙg᥆•3mo ago

as add.cloumn

leowest•3mo ago

it shows u that u have to append ' in front of the string u would just use row and columns variable as indexes and the first row, would be your header.

bᥲkᥙg᥆•3mo ago

the cell? what i found is i dont really need to change code just have to convert it .tostring

leowest•3mo ago

i.e.: something like this

var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("My Sheet");

var total = data.Count;
var columnSize = 10;
var row = 0;

for (var row = 0; row < total; row++)
{
    var item = data[row];
    var column = 0;
    ws.Cell(row, column++).Value = item.DocumentId;
    ws.Cell(row, column++).Value = $"'{item.StartTime}";
    // here I dont ++ because we want to apply the datatype to current item
    ws.Cell(row, column).DataType = XLDataType.DateTime;
    ws.Cell(row, column++).Value = item.SiteName;
}

var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("My Sheet");

var total = data.Count;
var columnSize = 10;
var row = 0;

for (var row = 0; row < total; row++)
{
    var item = data[row];
    var column = 0;
    ws.Cell(row, column++).Value = item.DocumentId;
    ws.Cell(row, column++).Value = $"'{item.StartTime}";
    // here I dont ++ because we want to apply the datatype to current item
    ws.Cell(row, column).DataType = XLDataType.DateTime;
    ws.Cell(row, column++).Value = item.SiteName;
}

bᥲkᥙg᥆•3mo ago

new DateTime(2010, 9, 2, 13, 45, 22).ToString();

leowest•3mo ago

this is all in their wiki

leowest•3mo ago

https://github.com/ClosedXML/ClosedXML/wiki

GitHub

Home

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXM...

leowest•3mo ago

also the above code is hypothetical and may not work I dont remember but I think the rows and columns with excel starts at 1 instead of 0 so u might need to adjust things

bᥲkᥙg᥆•3mo ago

how to set the headers name ws.Cell(row, column).DataType = XLDataType.DateTime; Property or indexer 'IXLCell.DataType' cannot be assigned to -- it is read only

leowest•3mo ago

well that is on their example u would have to check why that happens u would either skip the first row and do it outside the loop or use an if to do it on the first row

bᥲkᥙg᥆•3mo ago

leowest•3mo ago

well I just gave it a try and u dont even need the DataType anymore

leowest•3mo ago

both $"'{item.DateAsString}"; and just using item.DateTimeProperty works

leowest•3mo ago

bᥲkᥙg᥆•3mo ago

how can you share code the problem is loop and data type since i am using a json file

leowest•3mo ago

no, otherwise u wont learn and will just ask people for code instead. u dont need to set DataType just add the data normally, Value = ... u do however need the proper type in your json classes because it uses that internally to define what type it is

bᥲkᥙg᥆•3mo ago

can we do that in table not in cell

leowest•3mo ago

u can use the example I provided u earlier but u need to provide the proper type in the header

bᥲkᥙg᥆•3mo ago

well that working for me aslo but i have to click on it in my old code

leowest•3mo ago

well yeah because it needs to expand no and I already explained above why

bᥲkᥙg᥆•3mo ago

fuck i though it was nah when i click all of them are showing

leowest•3mo ago

ws.Columns(2, 6).AdjustToContents(); should fix the ### it should expand the cell accordingly im not sure what 2 and 6 are if its a range or a the row and column u would have to check

bᥲkᥙg᥆•3mo ago

should i use type datetime or offset?

leowest•3mo ago

DateTime apparently closedxml does not support DateTimeOffset

bᥲkᥙg᥆•3mo ago

well some values are null its throwing error can i also need null values in one of data and i am converting them

leowest•3mo ago

u have to add checks for null values

bᥲkᥙg᥆•3mo ago

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

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

leowest•3mo ago

i.e.:

Value = item.ThisPropertyIsNull ?? "";

Value = item.ThisPropertyIsNull ?? "";

... guess I can't help u after all sorry. ur back to the same code your posted at the begin

bᥲkᥙg᥆•3mo ago

because i dont think i need to change anything since i am specifying types in headers its just i thought ##### was means datetime is not recognised in excel except that everything in excel showing perfectly

Gaming

Programming

Excel modify