Apply format to many excel cells at once
<p>I would like to format all added values in my excel file and i have a "small" and "fast" solution like this:</p>

<p><code>Item2</code> is a <code>List&lt;string&gt;</code>, <code>Item3</code> is a <code>List&lt;List&lt;string&gt;&gt;</code></p>

<pre><code>if (chkWithValues.Checked &amp;&amp; results.Item3.Any())
{
    var rows = results.Item3.Count;
    var cols = results.Item3.Max(x =&gt; 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 &lt; results.Item3.Count(); j++)
    {
        int tmpNbr = 1;

        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)];
    range.Value = values;
    range.NumberFormat = format;
}
</code></pre>

<p>To add the numberformat efficiently with a single assignment, I've found a solution with a 2d array, which contains all number formats that should be set.</p>

<p>The problem is that I get the following error message <code>"Unable to set the NumberFormat property of the Range class"</code> when I have more than (i think) <code>50.000</code> cells to format.</p>

<p>Does someone know a solution that is fast and can handle a large amount of cells without error?</p>

<p>update:
ExcelColumnTypes.ConvertToExcelTypes</p>

<pre><code>public static string ConvertToExcelTypes(string databaseType)
        {
            if (DatabaseColumnTypes.DOUBLE.Contains(databaseType))
                return DOUBLEPO1;
            if (DatabaseColumnTypes.DATE.Contains(databaseType))
                return DATE2;
            if (DatabaseColumnTypes.INTEGER.Contains(databaseType))
                return INT;
            return TEXT;
        }
</code></pre>

<p>The DatabaseColumnTypes are List with const or direct const.
Sample:</p>

<pre><code>public const string VARBINARY = "varbinary";

 public static List&lt;string&gt; STRING = new List&lt;string&gt;()
        {
            CHAR,
            VARCHAR,
            TEXT,
            NTEXT,
            NCHAR,
            NVARCHAR,
            BINARY,
            VARBINARY
        };
</code></pre>