Read Excel File without Microsoft.Jet.OLEDB.4.0 reference (File SaveAs) or Excel workbook

  • Open a solution, install EPPlus using manage nuget packages.

    1. if (fuExcelUpload.HasFile)  
    2. {  
    3. DataTable dt = new DataTable();  
    4. if (Path.GetExtension(fuExcelUpload.FileName) == “.xlsx” || Path.GetExtension(fuExcelUpload.FileName) == “.xls”)  
    5. {  
    6. ExcelPackage package = new ExcelPackage(fuExcelUpload.FileContent);  
    7. dt = package.ToDataTable();  
    8. }  
    9.   
    10. }  
    11.   
    12. public static DataTable ToDataTable(this ExcelPackage package)  
    13. {  
    14. ExcelWorksheet workSheet = package.Workbook.Worksheets.First();  
    15. DataTable table = new DataTable();  
    16. foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])  
    17. {  
    18. table.Columns.Add(firstRowCell.Text);  
    19. }  
    20.   
    21. for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)  
    22. {  
    23. var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];  
    24. var newRow = table.NewRow();  
    25. foreach (var cell in row)  
    26. {  
    27. newRow[cell.Start.Column – 1] = cell.Text;  
    28. }  
    29. table.Rows.Add(newRow);  
    30. }  
    31. return table;  
    32. }  

    Exception Expected:

    Could not load file or assembly ‘EPPlus, Version=4.0.5.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1’ or one of its dependencies. The system cannot find the file specified.

    Solution:

    Go to solution, right click on package –> Properties –> Advanced tab, add epplus.dll