I am using Office Interop libraries in a C# WinForms application to write around 25000 rows and 110 columns data to Excel 2016. The max value length for any element is 420 characters.
I am using a 2 dimensional object array to read values from a DataTable, and I am passing this array to Range.set_Value method.
The application is working fine for other DataTables, but for a particular data, I am getting error:
C# Exception from HRESULT: 0x800A03EC
while using Range.set_Value. I am unable to figure out which of the 25000 rows and 110 columns is causing the error.
I can of course start breaking the data into small chunks until I identify which exact row and column is causing the issue, but is there a better way to debug the issue?
The data may have all kinds of special characters (the data is generated by a third party app).
EDIT 1:
I read that you can't add a cell with more than 8192 chars, if it is Excel 2007. But my data contains max 425 characters in any cell, and my Excel version is 2016.
I was able to find the issue with the data after some trial and error.
A few elements in the 2-dimensional array were starting with "=" character, which is treated as the start of a formula in Excel. Since the element value was not a valid formula, so Excel was throwing Exception.
I prepended those text values with a ' symbol, and everything worked fine.
Hope this helps somebody.