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
If I understand your question correctly, what you are looking for is JSON properties.
would serialize into
Link:
https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json/customize-properties?pivots=dotnet-7-0
Just add columns name as JsonProperty
or add a JsonConstructor if you want to serialize it again using new property names