skip to Main Content

So far I can read a excel spreadsheet and convert it to JSON but I’m struggling to manipulate what data i want from the spreadsheet and how I want it displayed.

this is how I read the sheet and add it to a list

foreach (Row row in rows) 
            {
                Dictionary<string, object> rowValues = new Dictionary<string, object>();
                int columnNumer = 1;

                List<InterfaceRecord> interfaceRecords = new List<InterfaceRecord>();   


                foreach (Cell cell in row.Elements<Cell>())
                {
                  
                    string columnName = GetColumnName(columnNumer); 
                    string cellValue = GetCellValue(cell, spreadsheetDocument);


                    rowValues[columnName] = cellValue;

                    InterfaceRecord record = new InterfaceRecord();
                    decimal value = -1;
                    decimal.TryParse(GetCellValue(cell, spreadsheetDocument), out value);
                    record.Value = value;
                    record.SampleNumber = "";

                    interfaceRecords.Add(record);

                    columnNumer++;
                }
                data.Add(rowValues);
            }

Here is how the JSON is converted

            //convert to JSON
            string json = JsonConvert.SerializeObject(data, Formatting.Indented);
            string jsonPath = properties.Path;
            string randomFileName = DateTime.Today.Ticks.ToString();
            File.WriteAllText($"{jsonPath}\RandomFilen.json", json);

Maps a numeric column index to its corresponding letter-based column name used in Excel.

public static string GetColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = string.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

Here is the interface

class InterfaceRecord
    {
        public string? SampleNumber { get; set; }
        public decimal Value { get; set; }
        public DateTime CreatedAt { get; set; }
        public long ClientID { get; set; }
        public string? MachineName { get; set; }
    }

Here is a sample of how the JSON file originally looked

"A": "Sample Name",//this is the Sample Number
    "B": "Description",
    "C": "Saved or unsaved State",
    "D": "Spectrum quality check summary",
    "E": "SiO2"//This is the value

This is how I want it displayed

"Sample Number": "123-ABC",
    "Value": 0.1234,
    "CreatedAt",
    "Machine Name": "Analyzer A",//Not in spreadsheet, just how I want to categorize
    "ClientID":  123

2

Answers


  1. If I understand your question correctly, what you are looking for is JSON properties.

    public class ClassyClass{
      public int A { get; set; }
      public int B { get; set; }
      [JsonPropertyName("X")]
      public int C { get; set; }
    }
    

    would serialize into

    {
      "a": 123,
      "b": 1234,
      "X": 12345
    }
    

    Link:

    https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/customize-properties?pivots=dotnet-7-0

    Login or Signup to reply.
  2. Just add columns name as JsonProperty

    var origJson=   @"{""A"": ""Sample Name"",
        ""B"": ""Description"",
        ""C"": ""Saved or unsaved State"",
        ""D"": ""Spectrum quality check summary"",
        ""E"": ""SiO2""}";
        
      InterfaceRecord interRecord = JsonConvert.DeserializeObject<InterfaceRecord>(origJson);
      
      interRecord.MachineName="Analyzer A";
      interRecord.CreatedAt=DateTime.Now;
    
    class InterfaceRecord
    {
        [JsonProperty("A")]
        public string? SampleNumber { get; set; }
        [JsonProperty("E")]
        public string Value { get; set; }
        
        public DateTime CreatedAt { get; set; }
        public long ClientID { get; set; }
        public string? MachineName { get; set; }
    }
    

    or add a JsonConstructor if you want to serialize it again using new property names

    public class InterfaceRecord
    {
        public string? SampleNumber { get; set; }
        public string Value { get; set; }
        
        // and so on
        
        [JsonConstructor]
        public InterfaceRecord(string? A, string E)
        {
            SampleNumber=A;
            Value=E;
             // and so on
        }
    }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search