November 9, 2020

Fixing Excel read/write issues (System.IO.FileNotFoundException) 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.

In Such case below error will be shown

 Message: 

System.IO.FileNotFoundException: Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. The system cannot find the file specified.

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)
{

	System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

	try
	{

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

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

		}
		catch (Exception)
		{
			throw;
		}

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

		});
		DataTableCollection table = result.Tables;
		
		//gets first sheet
		DataTable curTable = table[0];
		stream.Close();

		curTable.Columns.Add("NewColumn");

		DataRow rw = null;

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

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

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

}
logoblog

Thanks for reading Fixing Excel read/write issues (System.IO.FileNotFoundException) in .NET Core using IExcelDataReader and XLWorkbook

Previous
« Prev Post

No comments:

Post a Comment

Bookmark this website for more workarounds and issue fixes.

Verify Zip file contents without extracting using C# + Selenium

While doing automation testing, we may get a scenario where we need to download a zip file and to validate the contents inside it. One way t...