C# ArgumentOutOfRangeException while reading ExcelWorksheet

Question

I'm reading an OfficeOpenXml.ExcelWorksheet and getting the ArgumentOufOfRangeException on the middle of the Collection.

I'm reading like this process.Information = sheet.Cells[line, i++].Text;. On this line i = 22 while the sheet.Dimension.Column = 28.

When I'm debugging and enumerate the collection I see that the Exception is thrown on the method .Text while the .Value method is showing the correct value.

According to the exception stack trace, the exception is being thrown by the System.Text.StringBuilder.Insert() method

---- EDIT ---- After the accepted answer I realized that the problem is not only on the read. I reply the same file with an extra column (import success or insuccess) and while I'm doing the sheet formatation I get again the same error, all due to the method System.Text.StringBuilder.Insert(). I'm trying to AutoFit a column sheet.Column(22).AutoFit() This is the stack trace

at System.Text.StringBuilder.Insert(Int32 index, Char* value, Int32 valueCount)
at System.Text.StringBuilder.Insert(Int32 index, Char value)
at OfficeOpenXml.Style.XmlAccess.ExcelNumberFormatXml.ExcelFormatTranslator.ToNetFormat(String ExcelFormat, Boolean forColWidth)
at OfficeOpenXml.Style.XmlAccess.ExcelNumberFormatXml.ExcelFormatTranslator..ctor(String format, Int32 numFmtID)
at OfficeOpenXml.Style.XmlAccess.ExcelNumberFormatXml.get_FormatTranslator()
at OfficeOpenXml.ExcelRangeBase.GetFormattedText(Boolean forWidthCalc)
at OfficeOpenXml.ExcelRangeBase.get_TextForWidth()
at OfficeOpenXml.ExcelRangeBase.AutoFitColumns(Double MinimumWidth, Double MaximumWidth)
at OfficeOpenXml.ExcelRangeBase.AutoFitColumns(Double MinimumWidth)
at OfficeOpenXml.ExcelRangeBase.AutoFitColumns()
at OfficeOpenXml.ExcelColumn.AutoFit()
at SkiptraceAPI.Models.ProcessosRepository.formatExcel(ExcelPackage package, Boolean addValidation) in

Answer 1

Judging from the portion of stack trace mentioning Style.XmlAccess, it looks like you ran into a genuine bug in the implementation of OfficeOpenXml triggered by style of the cell in question.

Since using Value.ToString() works for you when the cell is not null, you can work around the bug by using the newly added null conditional syntax:

process.Information = sheet.Cells[line, i++].Value?.ToString();
//                                                ^

Another possible work-around is using GetValue<T>:

process.Information = sheet.GetValue<string>(line, i++);

Edit: It looks like there is a style in the cell in the 22-nd column that has a non-numeric value where the library expected a numeric string. The library tries to parse the string for a number, causing the exception. You can work around this by changing the format of the cell, but the actual fix is to modify the library to detect format mismatches without throwing an exception.