There are times that you may have to read excel file, either for the purpose of importing records into your application or for any other use case. This post will walk you through how to read excel file into a generic collection in C#. The library we will be using is free and so you don’t have to worry about license.

The Libraries

As at the time of this post, the libraries used and the version are:

<PackageReference Include="DocumentFormat.OpenXml" Version="2.17.1" />
<PackageReference Include="ExcelDataReader" Version="3.6.0" />
<PackageReference Include="ExcelDataReader.DataSet" Version="3.6.0" />

Read Excel File from File Path

public static IEnumerable<T> GetRecordFromExcelFile<T>(string filePath)
{
	using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
	{
		using (var reader = ExcelReaderFactory.CreateReader(stream))
		{
			var conf = new ExcelDataSetConfiguration
			{
				ConfigureDataTable = _ => new ExcelDataTableConfiguration
				{
					UseHeaderRow = true
				}
			};

			var dataSet = reader.AsDataSet(conf);
			var dataTable = dataSet.Tables[0];
			var data = ConvertDataTable<T>(dataTable);
			return data;
		}
	}
}

In the code above, the file content is read into a stream which is then used to create the ExcelReaderFactory. The zero in var dataTable = dataSet.Tables[0]; represents the index of excel sheet. ConvertDataTable is a custom method which can be seen below.

Read Excel File from Byte Array (byte[])

You may have a use case where you may not want to read directly from the file, in this case the file content can be read into byte array and you can pass that to your method.

public static async Task<IEnumerable<T>> GetRecordFromExcelFileAsync<T>(byte[] fileContent)
{
	await using var fs = new MemoryStream(fileContent);
	if (fs.Length <1) return new List<T>();
	using (var reader = ExcelReaderFactory.CreateReader(fs))
	{
		var conf = new ExcelDataSetConfiguration
		{
			ConfigureDataTable = _ => new ExcelDataTableConfiguration
			{
				UseHeaderRow = true
			}
		};
		var dataSet = reader.AsDataSet(conf);
		var dataTable = dataSet.Tables[0];
		return ConvertDataTable<T>(dataTable);
	}
}

ConvertDataTable<T>

 private static IEnumerable<T> ConvertDataTable<T>(DataTable dt) =>
            from DataRow row in dt.Rows 
            let item = GetItem<T>(row) 
            select item;

GetItem<T>

private static T GetItem<T>(DataRow dr)
{
	Type objectType = typeof(T);
	T record = Activator.CreateInstance<T>();

	foreach (DataColumn column in dr.Table.Columns)
	{
		var cell = objectType.GetProperties().Where(p => p.Name == column.ColumnName).FirstOrDefault();
		if (cell == null) continue;

		cell.SetValue(record, Convert.ToString(dr[column.ColumnName]), null);
	}
	return record;
}

It is important to note that since we are using reflection to map the property name to the excel column, the names must be the same.

In order to fix this error “No data is available for encoding 1252” for usage in other project type apart from a console application, register the encoding provider:

        Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

I hope you find this helpful. Do share your thoughts in the comment section below. If you have any further request for your use, please drop your request in comment below or use the contact form to reach out to me for faster response. Happy coding.

.NetC#ExcelIEnumerableList