Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 06-11-2011, 04:28 PM
Frag_68 Frag_68 is offline
Senior Member
 
Join Date: May 2008
Posts: 146
Default Format Cells in Excel

I'm looking for a faster way to change the formatting of an excel document (font color, size, and cell background). Right now I iterate through every cell and adjust based on the source array. For example:

Code:
//set background colors where bgarray is array of array of tcolor;
for acol := 1 to length(bgarray) do
    for arow := 1 to length(bgarray[acol]) do
      sheet.cells[arow,acol].interior.color := bgarray[arow-1,acol-1];
However, this is VERY slow, especially compounding it with other formatting arrays. Any idea how to make it faster?

Thanks!
Reply With Quote
  #2  
Old 06-12-2011, 01:11 PM
Zaaka Zaaka is offline
Senior Member
 
Join Date: Aug 2002
Location: Teesside, UK
Posts: 217
Default Format Excel Cells

You can select a range using
Code:
Sheet.Range['A1','EM1'].font.bold:=true;
this is the method I use and it is quite fast

Regards
Reply With Quote
  #3  
Old 06-12-2011, 03:08 PM
Frag_68 Frag_68 is offline
Senior Member
 
Join Date: May 2008
Posts: 146
Default

I was playing with using range and a variant array, but to no avail. For instance...

Code:
var
  varray: Variant;
  i,j : integer;
begin
  arrData := VarArrayCreate([1, myRowCount, 1, myColCount], varVariant);
  for i := 1 to myRowCount do
    for j := 1 to myColCount do
      varray[i, j] := ColorArray[i, j];  //where ColorArray is TColor
  Sheet.Range['A1','EM1'].interior.color := varray;
Reply With Quote
  #4  
Old 06-12-2011, 03:54 PM
Zaaka Zaaka is offline
Senior Member
 
Join Date: Aug 2002
Location: Teesside, UK
Posts: 217
Default

You will need to change your code to
Code:
var
  varray: Variant;
  i,j : integer;
begin
  arrData := VarArrayCreate([1, myRowCount, 1, myColCount], varVariant);
  for i := 1 to myRowCount do
    for j := 1 to myColCount do
      varray[i, j] := ColorArray[i, j];  //where ColorArray is TColor
  Sheet.Range[i,j].interior.color := varray;
where 'i' is the top left cell and 'j' is the bottom right cell

for example:
Code:
Sheet.Range['A1','B10'].HorizontalAlignment:=3;
Sheet.Range['A1','B10'].VerticalAlignment:=2;
Sheet.Range['A1','B10'].font.bold:=true;
This centers the text in cells A1 to B10, and then sets the vertical alignment to middle and changes the text to bold
Reply With Quote
  #5  
Old 06-12-2011, 04:21 PM
Frag_68 Frag_68 is offline
Senior Member
 
Join Date: May 2008
Posts: 146
Default

The Sheet.Range portion that you pointed out is outside of the for loops and i and j would be equal to MyRowCount and MyColCount respectively. This would not work properly.

The error is a type mismatch when using my code. I'm basically trying to set one array equal to another.

Example:

Excel A1:C3 interior colors
[nil,nil,nil
nil,nil,nil
nil,nil,nil]

:=

varray
[clred,clblue,clyellow
$398024,clwhite,$FF0023
clblack,$3322FF,$123456]


Kind of a goofy way to portray it, but conceptually that's my goal.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT. The time now is 04:36 PM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.