Reading an excel (xlsx) file takes too much time to complete in debug mode in WPF Net 5 environment

[marlonchosky]

I have been writing and testing a code that reads an excel file and traverses the rows of an excel sheet, from the first column to the fifth column.

Running the project in non-debug mode makes the reading fast, about 1 second for a file with a total of 10400 rows.

The problem I have is that when I run the project in debug mode, the code that does this reading takes a long, long time to finish reading the excel rows. I have not let that the code finishes its execution, but I have waited more than 1 minute and it still did not finish reading the file.

Please, if anyone can know why is that the behavior of the code, since apparently it is a bug but I am not sure.

I have posted a github repository with a project to replicate the described problem.

The repository is here

Inside is the WPF project called PruebaDeLecturaDeExcel.
The code is inside the DeserealizarArchivo() method of the MainWindow class.

Inside the project there is an excel file to test.

[marlonchosky]

So, the code executes quickly in debug mode when the line that invokes the ObtenerFecha() method is commented. But, I don't understand why is that?

[tonyqus]

How many data you have in this Excel? If it's just simple and limited data, can you test the same Excel reading code in console app instead of WPF app. As I know, there should be not much difference. Only the UI thread may be blocked.

[drmason789]

Possibly related: #564

[drmason789]

I had a look at this...in debug mode throwing exceptions is very expensive time-wise. Your code is repeatedly trying to get the datetime value of the cell, which throws an InvalidOperationException, which is then caught, then it reads the string value of the cell and parses that.

It' much quicker to simply check the cell type first and act accordingly:

   private static DateTime ObtenerFecha(ICell celda) {
            if (celda.CellType == CellType.String)
                return DateTime.ParseExact(celda.StringCellValue, "dd/MM/yyyy", CultureInfo.InvariantCulture);
            if (celda.CellType == CellType.Numeric)
                return celda.DateCellValue;
            throw new InvalidOperationException("Tipo de celda inesperado."); // from Google translate
        }

[marlonchosky]

How many data you have in this Excel? If it's just simple and limited data, can you test the same Excel reading code in console app instead of WPF app. As I know, there should be not much difference. Only the UI thread may be blocked.

@tonyqus As I mentioned in the first comment, the excel has around 10400 rows. But the problem is not the quantity, since in non-debug mode it is processed fast (close to 1 second). The problem comes when it runs in debug mode.

[marlonchosky]

I had a look at this...in debug mode throwing exceptions is very expensive time-wise. Your code is repeatedly trying to get the datetime value of the cell, which throws an InvalidOperationException, which is then caught, then it reads the string value of the cell and parses that.

@drmason789 Interesting solution. I didn't know that you could compare the cell type with CellType.Numeric to get its value with the DateCellValue property. Thank you!

Possibly related: #564

I checked the issue you referenced in the previous comment and yes, they are related.

I had a look at this...in debug mode throwing exceptions is very expensive time-wise.

However, why are exceptions time consuming in debug mode? Is there an article that mentions the impact of using exceptions in debug mode?

[drmason789]

Approximately, when the runtime encounters the exception, it pauses the application and notifies the debugger which can then decide if it wants to break on that exception or not.

https://devblogs.microsoft.com/devops/performance-improvement-when-debugging-net-code-with-visual-studio-2015/

I believe the problem you are experiencing is not a problem with NPOI, so I think this issue should be closed.

On the point of the numeric cell type to get the date, when Excel knows the cell content is a date, it stores it as a double - the number of days since Jan-01-1900 (or 1904 if you've got that option configured). It is rendered to a cell based on the cell formatting, which can be the user's local date format, meaning the same date in the same workbook can display differently in different UI culture settings.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system

[marlonchosky]

Approximately, when the runtime encounters the exception, it pauses the application and notifies the debugger which can then decide if it wants to break on that exception or not.

https://devblogs.microsoft.com/devops/performance-improvement-when-debugging-net-code-with-visual-studio-2015/

@drmason789 I will review the link article. Thanks

On the point of the numeric cell type to get the date, when Excel knows the cell content is a date, it stores it as a double - the number of days since Jan-01-1900 (or 1904 if you've got that option configured). It is rendered to a cell based on the cell formatting, which can be the user's local date format, meaning the same date in the same workbook can display differently in different UI culture settings.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system

I will also review the link article.

I believe the problem you are experiencing is not a problem with NPOI, so I think this issue should be closed.

I believe too. I will finish reading both articles and based on them I will close the issue.

[marlonchosky]

@drmason789 After reading the links and answers so far, I can conclude:

On the point of the numeric cell type to get the date, when Excel knows the cell content is a date, it stores it as a double - the number of days since Jan-01-1900 (or 1904 if you've got that option configured). It is rendered to a cell based on the cell formatting, which can be the user's local date format, meaning the same date in the same workbook can display differently in different UI culture settings.

It is a very good solution that you offered to validate the part of my code that checks if a cell is date, as opposed to doing it with exceptions like I did it.
Thanks for the solution offered 👍

I believe the problem you are experiencing is not a problem with NPOI, so I think this issue should be closed.

You are right, this issue is more related to how Visual Studio behaves when running an application with a debugger attached, and it is not related to NPOI, so I am going to close this issue.

Thanks for your time :)