Read Excel as JSON using Open XML SDK
In our last article, we learned how to do basic Read/Write Excel using .NET Core. Today in this article we will learn how to perform a C# Excel file As JSON using Open XML SDK.
Today in this article, we will cover below aspects,
We shall be using the below Excel file as an example to read.
Let’s create a .NET Core project, here to keep it simple I am using a Console .NET Core application.
Let’s install the below package Open-XML-SDK,
PM> Install-Package Open-XML-SDK -Version 2.9.0
C# Excel file As JSON
Let’s read the content of the Excel (.xlsx) file using the below method.
static string ReadExcelasJSON()
{
try
{
DataTable dtTable = new DataTable();
//Lets open the existing excel file and read through its content . Open the excel using openxml sdk
using (SpreadsheetDocument doc = SpreadsheetDocument.Open("testdata2.xlsx", false))
{
//create the object for workbook part
WorkbookPart workbookPart = doc.WorkbookPart;
Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();
//using for each loop to get the sheet from the sheetcollection
foreach (Sheet thesheet in thesheetcollection.OfType<Sheet>())
{
//statement to get the worksheet object by using the sheet id
Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
SheetData thesheetdata = theWorksheet.GetFirstChild<SheetData>();
for (int rCnt = 0; rCnt < thesheetdata.ChildElements.Count(); rCnt++)
{
List<string> rowList = new List<string>();
for (int rCnt1 = 0; rCnt1
< thesheetdata.ElementAt(rCnt).ChildElements.Count(); rCnt1++)
{
Cell thecurrentcell = (Cell)thesheetdata.ElementAt(rCnt).ChildElements.ElementAt(rCnt1);
//statement to take the integer value
string currentcellvalue = string.Empty;
if (thecurrentcell.DataType != null)
{
if (thecurrentcell.DataType == CellValues.SharedString)
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
if (item.Text != null)
{
//first row will provide the column name.
if (rCnt == 0)
{
dtTable.Columns.Add(item.Text.Text);
}
else
{
rowList.Add(item.Text.Text);
}
}
else if (item.InnerText != null)
{
currentcellvalue = item.InnerText;
}
else if (item.InnerXml != null)
{
currentcellvalue = item.InnerXml;
}
}
}
}
else
{
if (rCnt != 0)//reserved for column values
{
rowList.Add(thecurrentcell.InnerText);
}
}
}
if (rCnt != 0)//reserved for column values
dtTable.Rows.Add(rowList.ToArray());
}
}
return JsonConvert.SerializeObject(dtTable);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
Generated sample JSON file output is as below,
[
{
"ID": "1002",
"Name": "ABCD",
"City": "city",
"Country": "USA"
},
{
"ID": "1003",
"Name": "PQRS",
"City": "City",
"Country": "UK"
},
{
"ID": "1004",
"Name": "ZYZZ",
"City": "city3",
"Country": "China"
},
{
"ID": "1005",
"Name": "LNMO",
"City": "city4",
"Country": "India"
}
]
Read Excel with Empty Cell values
I have left a few cell entries empty intentionally in the Excel file as below.
Let’s read the content and generate the JSON output for the same.
Generated JSON file output as below
Empty fields will show as below.
[
{
"ID": "1002",
"Name": "ABCD",
"City": "",
"Country": "USA"
},
{
"ID": "1003",
"Name": "PQRS",
"City": "City",
"Country": "UK"
},
{
"ID": "1004",
"Name": "ZYZZ",
"City": "city3",
"Country": "China"
},
{
"ID": "1005",
"Name": "LNMO",
"City": "city4",
"Country": "India"
}
]
So the above logic works perfectly fine for empty cells as well.
References: Read/Write Excel using .NET Core
Are you dealing with any complex scenarios? Please let me know and sound off your comments below!
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.
