I need to color Excel cells in a fast manner. I found similar method to write to Excel cells which for me is really fast, so I tried applying the same method when coloring the 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 GetLightColor2 function:
private double GetLightColor2(string hex)
{
return ColorTranslator.ToOle(ColorTranslator.FromHtml(hex));
}
When I ran the code, an error was thrown at
xlRange.Interior.Color = colorData;
With the following error:
System.Runtime.InteropServices.COMException (0x80020005): Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) at Microsoft.Office.Interop.Excel.Interior.set_Color(Object value)
I could not find any other workaround unless coloring the cell by looping through each cell which is really slow. Or is it that I'm doing it the wrong way.
Thank you for your kind attention guys.