excel 2010 Type Conflict NumberFormat

Question

I have a small program which creates Excel files from a database table, with Excel 2013 it works all fine, but i Need it now for Excel 2010 and now I get the following exception when i will add the "Format" to the NumberFormatLocal (range.NumberFormatLocal = format;) The same exception will come when I use the range.NumberFormat = format;

Exception:

Error message: System.Runtime.InteropServices.COMException (0x80020005): Type Conflict. (Exception of HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) At System.RuntimeType.ForwardCallToInvokeMember (String memberName, BindingFlags flags, ObjectTarget, Int32 [] aWrapperTypes, MessageData & msgData)

function:

if (chkWithValues.Checked && results.Item3.Any())
            {
                var rows = results.Item3.Count;
                var cols = results.Item3.Max(x => x.Count);
                object[,] values = new object[rows, cols];
                object[,] format = new object[rows, cols];

                //All returned items are inserted into the Excel file
                //Item2 contains the database types, Item3 the Values
                // pgMain shows the progress for the selected tables
                for (int j = 0; j < results.Item3.Count(); j++)
                {
                    int tmpNbr = 1;
                    SetMessage($"{selectedTableItem.TableName} {j} von {results.Item3.Count}", LogHelper.NotificationType.Information);
                    foreach (string value in results.Item3[j])
                    {
                        values[j, tmpNbr - 1] = Converter.Convert(results.Item2[tmpNbr - 1], value).ToString().Replace("'", "");
                        format[j, tmpNbr - 1] = ExcelColumnTypes.ConvertToExcelTypes(results.Item2[tmpNbr - 1]);
                        tmpNbr++;
                    }
                    pgMain.Maximum = results.Item3.Count();
                    pgMain.PerformStep();
                }
                Excel.Range range = xlWorksheet.Range["A3", GetExcelColumnName(cols) + (rows + 2)];
                SetMessage($"{results.Item3.Count * results.Item1.Count} Zellen werden formatiert....", LogHelper.NotificationType.Information);

                range.NumberFormatLocal = format;
                range.Value = values;
            }

My Excel Types:

public const string INT = "0";

    public const string TEXT = "@";
    public const string GENERAL = "General";
    public const string STANDARD = "Standard";

    public const string Date1 = "m/d/yyyy";
    public const string DATE2 = "TT.MM.JJJJ";
    public const string DATE3 = "T.M.JJ h:mm;@";
    public const string DATETIME = "d/m/yy h:mm;@";

    public const string DOUBLECO1 = "#.##0,00";
    public const string DOUBLECO2 = "0,00";

    public const string DOUBLEPO1 = "#0,##0.00";
    public const string DOUBLEPO2 = "0.00";

    public const string CUSTOM = "#,##000";

    public const string CURRENCYEU1 = "#,##0,00 _€";
    public const string CURRENCYEU2 = "#,##0 _€";
    public const string CURRENCYEU3 = "#,##0,00 €";

    public const string CURRENCYDO1 = "#,##0.00 _$";
    public const string CURRENCYDO2 = "#,##0 _$";
    public const string CURRENCYDO3 = "#,##0.00 $";

    public const string PERCENTAGE1 = "0.00%";
    public const string PERCENTAGE2 = "0.0%";
    public const string PERCENTAGE3 = "0%";

Update:

I've tried to use already the public const string TEXT = "@"; as only format but the same error comes

Update 2 :

The error only occurs if the table has to many entrys. When I use for example a table with 1000 entrys its no problem and all works fine, if i use a table with 200.000 entrys the error occurs

Update 3:

[

I've tried to use only the standard format for testing, the following error occurs:

Error message: System.OutOfMemoryException: Insufficient memory available to continue the program.

Answer 1

Could it be that you're passing something to Excel that it is interpreting as an integer that's too large for an integer data type in Excel 2010? Maybe the row number for example? Test it by loading 32,760 rows then 32,770 as integers only go up to 32,767 in XL 2010. Worth a try :o)