November 9, 2020

Fixing Excel read/write issues in .NET Core using IExcelDataReader and XLWorkbook

  November 9, 2020

We know Microsoft.Office.Interop.Excel does not support .Net Core as of now.

So we need to have an alternative to read/write data from excel till then.

IExcelDataReader data reader is an alternative to read data from an excel sheet to a data table/data table collection.

But it has its own limitations, one of such can be seen while we try to write the modified data table back to the excel sheet.

In this case, also we have an alternative now called XLWorkbook. The working of this extension is similar to Microsoft.Office.Interop.Excel.

Here we can pass the modified data table as an argument and save the data to our required excel sheet in the path that we specify.

One such method is given below. Here we open an excel using IExcelDataReader, modify that and save it using XLWorkbook.

Users can split the code and implement it accordingly.

Some more .Net core-specific code changes can also be seen here.

public void AddExcelData(string FilePath)



		FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.ReadWrite);

		//ExcelDataReader Nuget needed
		IExcelDataReader excelReader = null;
			if (FilePath.EndsWith(".xls"))
				excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
			if (FilePath.EndsWith(".xlsx"))
				excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

		catch (Exception)

		var result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
			ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
				UseHeaderRow = true

		DataTableCollection table = result.Tables;
		//gets first sheet
		DataTable curTable = table[0];


		DataRow rw = null;

		//appends new row to the last used row
		rw = curTable.NewRow();
		rw["NewColumn"] = "Row data";

		//XLWorkbook Nuget needed
		XLWorkbook wb = new XLWorkbook();
		wb.Worksheets.Add(curTable, "Sheet Name");
		//overwrites the file

	catch (Exception e)
		Console.WriteLine("Unable to generate excel : " + e.Message);


Thanks for reading Fixing Excel read/write issues in .NET Core using IExcelDataReader and XLWorkbook

« Prev Post

No comments:

Post a Comment

How to send email from current Outlook instance using Java + VBScript

If we are using .Net, we can easily access outlook and send emails using its own libraries. But what if we are using Java? Here we can seek ...