How to read import excel file with EPPLUS?

user_19331304 0
Viewed 102
    private readonly ILogger<HomeController> _logger;

    public HomeController(ILogger<HomeController> logger, ApplicationDbContext context)
    {
        _logger = logger;
        _context = context;
    }

    public IActionResult Index()
    {
        return View();
    }

    ApplicationDbContext _context;
    
    public List<Contact>Import (string fileName)
    {

        var FilePath = $"{Directory.GetCurrentDirectory()}{@""}" + "\\" + fileName;
        FileInfo fileInfo = new FileInfo(FilePath);
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (ExcelPackage package = new ExcelPackage(fileInfo))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
          
            int rowCount = worksheet.Dimension.End.Row;
            for (int row = 2; row <= rowCount; row++)
            {
                Contact con = new Contact();
             
                    if (row == 1) con.Libelle = worksheet.Cells[row, row].Value.ToString();
                    else if (row == 2) con.Id = (int)worksheet.Cells[row, row].Value;

               
                _context.Add(con);
                _context.SaveChanges(); 
            }
        }
        return _context.Contacts.ToList();
    }

I'm trying to read excel file with EPPLUS but i have one excepton, it is saying that System.NullReferenceException : 'Object reference not set to an instance of an object.' worksheet a été null.

epplus excel exception c#
1 Answers

I will suggest You adapt 'defensive programming' paradigm to Your approach in these cases. Specifically meaning do not write code that acts on assuming something else goes well without checking. When you do so I believe the problem will show.

Of cause community cannot help you if the problem is with the excel sheet, unless you can supply it - but I do see some helpfull suggestions as to coding style which i belive can help You solve it Yourself.

Curious to learn what You found if You give it a go :)

From:

public List<Contact>Import (string fileName)
{

    var FilePath = $"{Directory.GetCurrentDirectory()}{@""}" + "\\" + fileName;
    FileInfo fileInfo = new FileInfo(FilePath);
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    using (ExcelPackage package = new ExcelPackage(fileInfo))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
      
        int rowCount = worksheet.Dimension.End.Row;
        for (int row = 2; row <= rowCount; row++)
        {
            Contact con = new Contact();
         
                if (row == 1) con.Libelle = worksheet.Cells[row, row].Value.ToString();
                else if (row == 2) con.Id = (int)worksheet.Cells[row, row].Value;

           
            _context.Add(con);
            _context.SaveChanges(); 
        }
    }
    return _context.Contacts.ToList();
}

TO:

public List<Contact> Import(string fileName)
{
    //Try not to use uppercase for local variables, to be able to tell them from member properties
    var FilePath = $"{Directory.GetCurrentDirectory()}{@""}" + "\\" + fileName;

    FileInfo fileInfo = new FileInfo(FilePath);
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    using (ExcelPackage package = new ExcelPackage(fileInfo))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();

        //Here ok, because a workbook has at least one sheet
        int rowCount = worksheet.Dimension.End.Row;
        for (int row = 2; row <= rowCount; row++)
        {
            try
            {
                Contact con = new Contact();

                var cell = worksheet.Cells[row, row];

                if (row == 1)
                {
                    //Do You know how your package handles in case there is nothing in the cell?
                    con.Libelle = cell.Value?.ToString();
                }
                else if (row == 2)
                {
                    if (int.TryParse(cell.Value, out int parsedResult))
                    {
                        con.Id = parsedResult;
                    }
                    else throw new Exception("Invalid data"); //Your direct cast in source code justifies it not being possible to be an exceptional case 'impossible' or you wouldn't write code presuming it ;)
                }
                _context.Add(con);
                _context.SaveChanges();
            }
            catch (Exception ex)
            {
                //Let Your debugger help you pinpoint the problem
                System.Diagnostics.Debug.Write($"problem with row {row} :{ex.ToString()}");
            }
        }
    }
    //Are You sure Contacts are initialized, know the implementation of  the _context
    return _context.Contacts?.ToList() ?? null;
}
user_29156150
Related