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

	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 in .NET Core using IExcelDataReader and XLWorkbook

Previous
« Prev Post

No comments:

Post a Comment

Fixing javascript error: Cannot read properties of null (reading 'querySelector') issue when using CSS identifier in selenium

 JavaScript is another way to interact with web elements when normal selenium methods fail to act. But one issue with javascript is, it does...