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); } }
No comments:
Post a Comment