0

I have a large array full of ole color values that I need to apply to cells in an excel worksheet.

They could be applied with a simple for loop. pseudo code:

 for (int k = 0; k < oleColorArray.Count; k++){
   worksheet.Cells[1, k].Interior.Color = oleColorArray[k];
 }

However this approach is slow, even after disabling ScreenUpdating. I've got around 1500 entries and it can take up to a minute.

Since the values in the array are all different I can't color the whole range.

I've seen that cell values can be applied directly from a 2D array using 

range.set_Value(); or range.Value()

Microsoft.Office.Interop.Excel really slow

Is there any similar way to apply an array of ole colors, if not is there a faster approach I am missing?

Peter Wolf
  • 3,700
  • 1
  • 15
  • 30
tfcmad
  • 418
  • 1
  • 6
  • 20
  • 1
    Does the values in `oleColorArray` repeat? It is faster to apply the style to a range of multiple cells. See [Fastest way to format multiple rows in an excel in C#](https://stackoverflow.com/questions/57058935/fastest-way-to-format-multiple-rows-in-an-excel-in-c-sharp/57074884). – Peter Wolf Jan 08 '20 at 14:12
  • Hello Peter, the colors do repeat but there are probably around 40 different variants. There seems to be a limit for the amount of different cells you can apply a color to if they are not touching. e.g. E1,E4,F6.... using xlWorkSheet.Range[range]. – tfcmad Jan 08 '20 at 15:45
  • Yes there is a limit - 255 characters for discrete range string. See the link in my previous comment. If you follow my answer and split the discrete range into chunks that do not exceed 255 characters, you reduce the number of calls to interop and thus you gain speed boost 10-15 times as described in the answer. – Peter Wolf Jan 08 '20 at 17:47

1 Answers1

1

Have you tried using the conditional formatting options function instead of using VBA coding? Any reason why you are unable to use conditional formatting and are forced to use VBA?

Conditional Formatting can be set up so its logic is built off of one cell or a range and can apply to the ranges you are looking to color code.

Pulsater
  • 44
  • 1
  • 9
  • Hello Pulsater I'm using C# from a Windows form, there's some other file management I have to do. Conditional formatting is probably the best idea I've seen yet, although I didn't really want to enter text into the cells, I guess I can just match the text color to the cell color. – tfcmad Jan 08 '20 at 15:51
  • Shouldn't nesscarily have to be tied back to text as conditional formatting can be done with multiple different methods. What I would do if the method used to get specific logic or something that might confuse a user in the future would be to tie the logic back to a column and hide it. – Pulsater Jan 08 '20 at 16:22