none
Read Data from excel in NET CORE - using Microsoft.Office.Interop.Excel RRS feed

  • Question

  • I am working on .Net core with Angular Cli  in that application i want to import data from excel. 

    Installed Microsoft.Office.Interop.Excel  successfully using nuget packet manager, based on that i  can able to get  work sheet names,  but  unable to read content from excel worksheet got  com exception error. 

    Is there any other way to resolve the issue to read excel data?


    Thanks & advance

    Victor


    Wednesday, August 29, 2018 10:49 AM

All replies

  • In order to solve the issue, could you please provide your detail code for us ? Also, you can reference this post to test read your Excel data. 

    Code snippet:

    var filePath = @"D:/test.xlsx";
    FileInfo file = new FileInfo(filePath);
    
    using (ExcelPackage package = new ExcelPackage(file))
    {       
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int rowCount = worksheet.Dimension.Rows;
        int ColCount = worksheet.Dimension.Columns;
    
        var rawText = string.Empty;
        for (int row = 1; row <= rowCount; row++)
        {
            for (int col = 1; col <= ColCount; col++)
            {   
                // This is just for demo purposes
                rawText += worksheet.Cells[row, col].Value.ToString() + "\t";    
            }
            rawText+="\r\n";
        }
        _logger.LogInformation(rawText);
    }

    Hopefully it helps you.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Simon Wu-MSFT Monday, September 3, 2018 10:30 AM
    Thursday, August 30, 2018 3:27 AM
    Moderator
  • This thread has been around for a long time. Please remember to mark the replies as answers if they helped. Please help us close the thread.

    Thank you for understanding. If you have any question, or update, please feel free to let me know.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, September 5, 2018 7:54 AM
    Moderator
  • Hi Simon,

    Thanks for your reply, I already used Epplus but i don't want to use third party library to read data from Excel

    I want to read data from excel using Microsoft Office API .

    Here is my code snippet:  

    I added Microsoft.office.interop.Excel using Nuget package.

    Excel.Application excel = new Excel.Application();
                        excel.Visible = false;
                        Excel.Workbook xlWorkbook = excel.Workbooks.Open(@"D:\test.xlsx", CorruptLoad: true);
     int worksheetcount = xlWorkbook.Worksheets.Count;
                        Excel._Worksheet wks = (Excel.Worksheet)xlWorkbook.Sheets[1];
                        string firstworksheetname = wks.Name;
                        Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet;
                        var firstcellvalue = ((Excel.Range)workSheet.Columns[1]).AutoFit();

    In above code I able to get Worksheet name and Sheet count but unable to read content. Can you please help me to resolve this?

    Tuesday, September 11, 2018 5:20 AM
  • imho ((Excel.Range)workSheet.Columns[1]).AutoFit() returns the entire range, not one cell.

    you would need to do: firstcellvalue.Cells(1,1).Value in this case.

    Tuesday, September 11, 2018 7:45 AM
  • Hi Wouter Defour,

    Thanks for your reply, i tried  to get cell value like Cells(1,1) but getting null Error that is Object reference not set to an instance of an object.

    Here is My code snippet

    Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet;
                  string test = workSheet.Cells[1, 1].ToString();

    Can you please help me how to resolve this?

    Tuesday, September 11, 2018 10:31 AM
  • you can not use the defaultvalue as you would do in VBA.

    you are missing the Value property: string test = workSheet.Cells[1, 1].Value.ToString();

    on the other hand I don't think it should return a null reference error. 

    are you sure worksheet is assigned?

    Tuesday, September 11, 2018 11:15 AM
  • Hi wouter,

    If i use value property is is showing error value is not define,  i am working in .net core. I assigned work sheet .

    Wednesday, September 12, 2018 9:31 AM
  • maybe you could try to do this in 2 stages, first assign workSheet.Cells[1, 1] to a range variable and then use the value property from that variable. also it will give you some extra debugging possibilities.

    I know it's not state of the art like this, but it might help..

    Wednesday, September 12, 2018 9:52 AM