Excel Interop Diffuse Cell Using Range

Question

I need to quickly colorize Excel cells. I found a similar method for writing to Excel cells, which is very fast for me, so I tried to apply the same method when staining cells. Consider the following code:

xlRange = xlWorksheet.Range["A6", "AS" + dtSchedule.Rows.Count];

double[,] colorData = new double[dtSchedule.Rows.Count, dtSchedule.Columns.Count];
for (var row = 0; row < dtSchedule.Rows.Count; row++)
{
    for (var column = 0; column < dtSchedule.Columns.Count; column++)
    { 
        if (column <= 3)
        { 
            colorData[row, column] = GetLightColor2("#ffffff"); 
            continue;
        }


        if (dtSchedule.Rows[row][column].ToString() != "#000000" && !string.IsNullOrEmpty(dtSchedule.Rows[row][column].ToString()))
        {
            string[] schedule = dtSchedule.Rows[row][column].ToString().Split('/');
            string color = schedule[0].Trim();

            colorData[row, column] = GetLightColor2(color); 
            continue;
        }

        colorData[row, column] = GetLightColor2("#000000"); 
    }
}

xlRange.Interior.Color = colorData;

This is the function GetLightColor2:

private double GetLightColor2(string hex)
{
    return ColorTranslator.ToOle(ColorTranslator.FromHtml(hex));
} 

When I ran the code, the error was reset in

xlRange.Interior.Color = colorData;

With the following error:

System.Runtime.InteropServices.COMException (0x80020005): Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) in System.RuntimeType.ForwardCallToInvokeMember (String memberName, BindingFlags flags, Object target, Int32 [] aWrapperTypes, MessageData & msgData) in Microsoft.Office.Interopor.oreroper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper.Interoper. )

, , , . , .

. .

Answer 1

excel addin, Akhil R J. , interop, - . - , :

1) , , . .

2) Application.ScreenUpdating = false, excel. , .

3) - Application.Union, . 50 . , , . . , 5-10 .

4) , . ( , OpenXML). interop, Windows. , - OpenXMl-like. , , interop. , , .