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

This looks like a bug in the implementation related to processing the style of your cell. Try `process.Information = sheet.GetValue(line, i++)` instead. — Sergey Kalinichenko, Jul 06 '16 at 10:50
What would be the problem with reading the Value property? Do you actually need the `.Text` property? I have googled for some examples and I see that `Value` is used a lot instead of `Text`. Can't really find documentation for the package you are using, but isn't `Text` some special property? — Sander Aernouts, Jul 06 '16 at 10:52
Actually I don't know if there's a problem using `.Value` instead `.Text`. This is old code and can't remember why choose this way (but it must have been a reason). This code always worked and the customer is reporting this error for the very first time. I guess I'll change it to `.Value` instead, but just wanna understand the problem... — NunoRibeiro, Jul 06 '16 at 11:04
While experimenting I think that my choice was based on the null/empty cells. Using `.Text` I don´t need to validate the cell content (if it's null the `.Text` will return null) while using `.Value`, because it returns an `object` I have to do `.Value.ToString()` which will throw an exception if the value is null — NunoRibeiro, Jul 06 '16 at 11:10

Sergey Kalinichenko · Accepted Answer · 2016-07-06T14:35:42.950

2

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.

edited Jul 06 '16 at 14:35

answered Jul 06 '16 at 11:29

Sergey Kalinichenko

714,442
84
1,110
1,523

Yes, I replace the `.Text` by `GetValue` (as you suggested) but now I'm facing yet again the exception while using `sheet.Column(22).AutoFit()`... any thoughts? – NunoRibeiro Jul 06 '16 at 11:33
@NunoRibeiro There's got to be something wrong with the style of one value in column 22, because whatever is stored there for the style seems to uncover multiple bugs in the implementation. – Sergey Kalinichenko Jul 06 '16 at 11:38
@NunoRibeiro If you can freely modify this spreadsheet, consider copying its content without the style into a new one, and see if you can open/AutoFit the columns in the resulting spreadsheet. – Sergey Kalinichenko Jul 06 '16 at 11:40
I copied the contents (without style) to another spreadsheet and the error disappeared. But this is not a solution since the client creates it's own spreadcheats... Must understand why it happens and what I can do to prevent it... – NunoRibeiro Jul 06 '16 at 12:43
@NunoRibeiro This, unfortunately, requires access to the spreadsheet itself: it looks like there is a style in the cell that has a non-numeric value where the library expected a numeric string. – Sergey Kalinichenko Jul 06 '16 at 13:02
You're absolutely correct. That column is set to "Accounting" and should be General. Please add your last comment to your answer so that I can accept it. Thank you very much – NunoRibeiro Jul 06 '16 at 14:28

C# ArgumentOutOfRangeException while reading ExcelWorksheet

1 Answers1