Linq to excel not working , when upgrading .net core 2.1 to .net core 2.2

Question

> I was using .Net core 2.1 for my project. I used LINQ to excel to get

read data from excel file. When i upgrade by my project to .Net core 2.2. it is not working.

my code to read data from excel file is

string pathToExcelFile = "path to excel file."
ExcelHelper ConxObject = new ExcelHelper(pathToExcelFile);

var query = from a in ConxObject.UrlConnexion.Worksheet<ExcelProcessFollowUp>()
            select a;

var data = query.ToList();

//Helper class for excel

public class ExcelHelper
{
    public string _pathExcelFile;
    public ExcelQueryFactory _urlConnexion;

    public ExcelHelper(string path)
    {
        this._pathExcelFile = path;
        this._urlConnexion = new ExcelQueryFactory(_pathExcelFile);
    }

    public string PathExcelFile
    {
        get
        {
            return _pathExcelFile;
        }
    }

    public ExcelQueryFactory UrlConnexion
    {
        get
        {
            return _urlConnexion;
        }
    }
}

but it is not working now please give some solution to it.

score 0 · Accepted Answer · answered Dec 05 '19 at 07:03

I think linq to excel don't work in .net core 2.2. so better to use EPPlus package. Download EPPlus.Core from nuget package

i have faced the same problem and i got solution for this.

using OfficeOpenXml;

using this we read each row and column and put it to our viewmodel. as shown in below code.

Dictionary<object, string> leadExcelViewModels = new Dictionary<object, string>();

          using (ExcelPackage package = new ExcelPackage(file))
                {
                    ExcelWorksheet workSheet = package.Workbook.Worksheets[1];
                    int totalRows = workSheet.Dimension.Rows;//get total rows counts of excel file
                    int totalColumns = workSheet.Dimension.Columns;// get total columns count of excel file.

                    if (totalRows > 1)
                    {
                        for (int i = 2; i < totalRows; i++)
                        {
                            leadExcelViewModels = new Dictionary<object, string>();
                            leadModel = new YourViewModel();
                            for (int j = 1; j <= totalColumns; j++)
                            {
                                if (workSheet.Cells[i, j].Value != null)
                                    leadExcelViewModels.Add(workSheet.Cells[1, j].Value, workSheet.Cells[i, j].Value.ToString());
                                else
                                    leadExcelViewModels.Add(workSheet.Cells[1, j].Value, "0");
                            }

                            var js = JsonConvert.SerializeObject(leadExcelViewModels);
                            leadModel = JsonConvert.DeserializeObject<YourViewModel>(js);
                           // bind each leadModel to List of your YourViewModel list.


                        }
                    }
                }

score 0 · Answer 2 · answered Dec 06 '19 at 16:41

0

This is an issue with the OleDb driver not working in .Net Core 2.x versions. It should be fixed in .Net Core 3.0 (see this GitHub issue)

Can you try to upgrade to .Net Core 3.0 and see if LinqToExcel works for you.

answered Dec 06 '19 at 16:41

Paul

18,349
7
49
56

sorry I need to switch in .net core 2.2 – Dec 09 '19 at 06:35
LinqToExcel doesn't work with .net core 3.1.. I am adopting to the solution given above. It is a shame that the majestic LINQ approach will bite the dust now – jaiveeru Feb 02 '21 at 12:15
Managed to fix the issue by installing oledb from nuget – jaiveeru Feb 02 '21 at 12:35

score 0 · Answer 3 · edited Aug 11 '21 at 10:25

LinqToExcel won't work on .Net Core by default because the OLEDB packages are not ported to dotnetcore. However, to fix your issue you need to install a separate package specific to OLE DB and available on Nuget to download and install which in turn will fix your other issue on LinqToExcel because that package internally uses this OLEDB.

Package from nuget will install the following:

System.Data.OleDb.OleDbCommand
System.Data.OleDb.OleDbCommandBuilder
System.Data.OleDb.OleDbConnection
System.Data.OleDb.OleDbDataAdapter
System.Data.OleDb.OleDbDataReader
System.Data.OleDb.OleDbParameter
System.Data.OleDb.OleDbParameterCollection
System.Data.OleDb.OleDbTransaction

They are now available in the System.Data.OleDb Nuget package which targets .NET Core 3.1, .NET Framework 4.6.1, .NETStandard 2.0 and other frameworks.

See also: The GitHub issue (https://github.com/dotnet/corefx/issues/23542) which led to the development and release of this package.

Linq to excel not working , when upgrading .net core 2.1 to .net core 2.2

3 Answers3