How to Read Excel Cell Values using Open XML in C# .Net Core
C# .NET Core provides a robust platform for building applications that interact with Excel files making it easy to read Excel sheet’s cell value of any column type. Open XML is an open standard file format for office applications, maintained by the International Organization for Standardization (ISO). It is the default file format for Microsoft Office documents, including Excel (.xlsx) files. Open XML provides a structured way to represent document content, making it possible to interact with the various elements within an Office document programmatically. In this article, we will understand the process of reading Excel cell values using Open XML in a C# .NET Core application.
Step-by-step Guide to Read Excel Spreadsheet Cell Values from OpenXML in C#
For demonstration purposes, we’re going to use a Web API to upload an Excel file. So before we begin, let’s ensure that you have a C# .NET Core project set up. Then, we’ll use the OpenXML package to read Excel sheet & its values in each cell and store them in a DataTable.
Install OpenXml SDK Nuget Package
Install an OpenXML Nuget Package into the Web API project, using either:
Or
PM> Install-Package DocumentFormat.OpenXml -Version 3.0.0
After you install the package, you can include these assembly directives to use OpenXML, in the ‘Controller.cs’ class.
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;Create API to get an Excel file
Create an HTTP POST method in the controller class to receive the uploaded file from the API.
[HttpPost("UploadExcelFile")]
public IActionResult UploadExcelSheet(IFormFile uploadRegistration)
{
string fileName = uploadRegistration.FileName;
DataTable dt = ConvertExcelToDataTable(uploadRegistration);
// write your logic,
// example insert DataTable result into Database
return Ok();
}The API takes IFormFile as a parameter, representing a file sent with the HttpRequest.
Read Data from Excel Sheet using OpenXML – C#
private DataTable ConvertExcelToDataTable(IFormFile uploadRegistration)
{
//Create a new DataTable.
DataTable dt = new DataTable();
dt.Columns.Add("FIRST_NAME", typeof(string));
dt.Columns.Add("LAST_NAME", typeof(string));
dt.Columns.Add("PHONE_NUMBER", typeof(int));
dt.Columns.Add("EMAIL", typeof(string));
dt.Columns.Add("ADDRESS", typeof(string));
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(uploadRegistration.OpenReadStream(), false))
{
//Read the first Sheet from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
//Get the Worksheet instance.
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
//Fetch all the rows present in the Worksheet.
IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
//If first row is header and you want to use same excel header name in Database column.
if (row.RowIndex.Value == 1)
{
//foreach (Cell cell in row.Descendants<Cell>())
//{
// dt.Columns.Add(GetCellValue(doc, cell));
//}
}
else
{
string firstName = GetCellValue(doc, (Cell)row.ElementAt(0));
string lastName = GetCellValue(doc, (Cell)row.ElementAt(1));
DataRow dataRow = dt.NewRow();
// Validate if empty
if (!string.IsNullOrEmpty(firstName))
{
dataRow["FIRST_NAME"] = firstName.Trim();
}
if (!string.IsNullOrEmpty(lastName))
{
dataRow["LAST_NAME"] = lastName.Trim();
}
dataRow["PHONE_NUMBER"] = GetCellValue(doc, (Cell)row.ElementAt(2)).Trim();
dataRow["EMAIL"] = GetCellValue(doc, (Cell)row.ElementAt(3)).Trim();
dataRow["ADDRESS"] = GetCellValue(doc, (Cell)row.ElementAt(4)).Trim();
dt.Rows.Add(dataRow);
}
}
}
return dt;
}
private string GetCellValue(SpreadsheetDocument doc, Cell cell)
{
if (cell.CellValue != null)
{
string value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
// in older version e.g. 2.0, you can use GetItem instead of ElementAt
return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.ElementAt(int.Parse(value)).InnerText;
}
else
{
return value;
}
}
return string.Empty;
}- We open the Excel file (RegistrationFormTest.xlsx in this case) using the SpreadsheetDocument.Open method.
- We obtain the WorkbookPart and identify the first sheet in the workbook.
- For each row in the sheet, we iterate through the cells, using the GetCellValue method to retrieve the cell value.
- The GetCellValue method takes care of differentiating between shared string values and other types of cell values. It reads Excel Sheet cell value and stores the result in the DataTable.
also read: How to Export DataSet To Excel in C# .Net Core – OpenXml
Testing – Upload & Read Excel file
Let’s consider an example. I have a basic Excel file with the column and row information. Now, let’s attempt to upload and read the file using API, as shown in the screenshots below.
Screenshots:
In this example, we demonstrate how to read Excel spreadsheet cell values and add results to the DataTable. However, you also have the option to save all records to a database. This can be done either through bulk insert or by saving each row individually, passing the values of each row via the property object.