0

So I've been trying to write a console application (.Net Core) which reads data from an Excel file but I'm kinda stuck.

I've been trying to get this code sample to work: https://www.csharp-console-examples.com/general/reading-excel-file-in-c-console-application/

I needed to use (Range)cell explicitly because Visual Studio threw errors when the sample code tried to access the cell directly.

When trying to load the worksheet and the used range m_ObjectToDataMap = null for both worksheet and range. Accessing the rows with excelRange.Rows fails due to System.NullReferenceException: 'Object reference not set to an instance of an object.'

static void Main(string[] args)
{
    //Create COM Objects.
    Application excelApp = new Application();

    if (excelApp == null)
    {
        Console.WriteLine("Excel is not installed!!");
        return;
    }

    Workbook excelBook = excelApp.Workbooks.Open(@"D:\test.xlsx");
    _Worksheet excelSheet = (_Worksheet)excelBook.Sheets[1];
    Range excelRange = excelSheet.UsedRange;

    foreach (Range row in excelRange.Rows)  // Here is the NullReference Error
    {
        // create new line
        Console.Write("\r\n");
        foreach (Range col in excelRange.Columns)
        {
            Range cell = (Range)excelRange.Cells[row, col];
            // write the console
            if (excelRange.Cells[row, col] != null && cell.Value2 != null)
                Console.Write(cell.Value2.ToString() + "\t");
        }
    }
    //after reading, relaase the excel project
    excelApp.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
    Console.ReadLine();
}

I tried a lot of different code, looping through all sheets or calling the sheet by its name but it always ends with worksheet and range being loaded incorrectly and the NullReferenceException.

Any help would be appreciated.

ItsMeTheBee
  • 173
  • 15
  • 1
    Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Filburt Sep 30 '19 at 11:28
  • you probably have merged cells... you also need to free worksheets, ranges and workbooks – BugFinder Sep 30 '19 at 11:30
  • For testing purposes i just wrote "test" into the first 6 cells (3 in row 1 and 3 in row 2) so the exel file should be fine – ItsMeTheBee Sep 30 '19 at 11:35
  • I know what a NullReference is but i cant find how i should load the worksheet (or the range) correctly – ItsMeTheBee Sep 30 '19 at 11:38
  • Why are you making a new "range" of the a single cell every loop, just read the cell – BugFinder Sep 30 '19 at 11:38
  • well i never came that far because excel.Range.Rows and excel.Range.Columns throws the NullReferenceException. The sample code was without the explicit (Range) conversion but visual studio threw errors so i had to do it explicitly like this – ItsMeTheBee Sep 30 '19 at 11:42
  • dont use rows.count, use excelRange.GetLength(0).. and then pick up the value with excelRange.cells[i,j].Value2 – BugFinder Sep 30 '19 at 11:47
  • CS1061 'Range' does not contain a definition for 'GetLength' and no accessible extension method 'GetLength' accepting a first argument of type 'Range' could be found (are you missing a using directive or an assembly reference?) CS1061 'object' does not contain a definition for 'Value2' and no accessible extension method 'Value2' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?) --> this is why i used Range cell instead of direct access – ItsMeTheBee Sep 30 '19 at 11:56

1 Answers1

0

I think the code reference you were using was a console app in .NET Framework. I used the code from here that you referenced and put it in a .NET Framework console application and it works perfectly. I put the same code in a .NET Core console application and it does not work. So you might need to change to .NET Framework or you can use another excel library. I use NPOI which you can follow an example here or do some more googling to find other examples. There is also probably more libraries out there like EPPlus that work in .NET Core for parsing excel but I have not used or looked into any others.

Thomas Lieser
  • 41
  • 3