Excel Automation by C++. Fast random Color update

[antonj]

Hi!

What I need is fast worksheet update, by C++ program. The problem is that I want to update background of cells.
There is examples in the net, for value setting as array. In my case, solution is:

Array^ my2DArray = Array::CreateInstance( String::typeid , 2 , Rows );
int cID = 0;

while ( ValueAvailable ( ) ) {
	my2DArray -> SetValue ( newString( ) ,my2DArray -> GetUpperBound( 0 ) , cID );
	safe_cast <Range^>(MyWs -> Cells[ 1, cID ] )->Interior->ColorIndex = cID % 56 ;
	cID++;
}
Range^ RangeOfCells = MyWs->Range[getRange(),Type::Missing];
RangeOfCells->Value2 = my2DArray;

Row 6 in code above, makes update very slow, for big amounts of data. So I can replace

safe_cast<Range^>(MyWs->Cells[1, cID])->Interior->ColorIndex = cID%56;

by array with colors, but i can't set this array to RangeOfCells...
This code gives me all range in one color only :( .

RangeOfCells->Interior->ColorIndex = 41;

So is there a way to speed up color definition for worksheet?

P.S. following code definitely is not enough.

exApp->ScreenUpdating = false;
exApp->Calculation = XlCalculation::xlCalculationManual;
....
exApp->ScreenUpdating = true;
exApp->Calculation = XlCalculation::xlCalculationAutomatic;

[freddie1]

What I need is fast worksheet update...

Not likely to happen.

First, Excel is a pig. Big, slow bloatware.

Second, COM access is through IDispatch - the very slowest COM communication possible.

Good luck!

[antonj]

Well I have found close solution:
1) create array of 56 strings, since there is only 56 ColorIndex values.
2) fill 56 strings with areas i.e. "A1:B2,C3:D4"
3) in cycle do

Range^ RangeOfCells = MyWs->Range[myAreaArray[i],Type::Missing];
RangeOfCells->Interior->ColorIndex = i;

This gives disadvantages
1) I can't use random colors (0xBBGGRR, for RangeOfCells->Interior->Color),
2) I have to manage areas manually.
3) 56 ColorIndex set's processing about 1..2 seconds so I can't slow it any more :(
4) Size for "myAreaArray[i]" is limited to 256 characters!

[Computergeek01]

Can I just be TFG for a second and suggest that VBScript is going to be a much faster\easier option for you. I'm glad that you found something that works but I agree with freddie1 that the entire MS Office API needs to be torn down and thrown out for a fresh start.

[antonj]

Well, I can't... I'm using Excel as visualization for MySQL database data. I can't use my own interface, because all users in company is familiar to Excel and want to continue use it. It would take a lot of time to create similar interface as in Excel. I don't know about OpenOffice or LibreOffice. Suppose both has similar problems.