A fast way to fill cells in Excel

[Tas]

Hello there,

I have some questions regarding this Excel Automation (I'm not an expert on
this so please "excuse" if the questions are too trivial)...

I try to build a small application that will generate a very large number of
columns and rows into Excel sheets (lets say about 200 sheets each one with
50 000 rows and 100 columns). There is a way to store "Cell" objects in
arrays so I could lets say "create" them into this array and than add the
array to a specific range (similar to the "Text", double, short storage into
an array...)? Why I'm asking this is because my Cells must have a specific
format (they are somehow structured as "BLOCKS" and this block will repeat
itself for many times) and I need a fast way to "copy" this "block"... I
already did a sample where I'm generatig arround 10 000 "blocks" on a sheet,
each block containing 3 rows and 15 colums, having each cell with a different
color and a random border. The time until it was finished was arround 31
seconds... Can I do this faster? Any ideas are more than wellcome.

Thanks,
Tas

 

[RB Smissaert]

Post the code and we can see if it could be done faster.

RBS

 

[Tas]

Excel_generator is the main class that will initialize the COM conecxion.
I have a "sheet" member which at the calling of the following method will be
available and pointing to the current active sheet.

void Excel_generator::add_some_data
( int staring_row,
int no_blocks,
int block_row_length,
int block_col_length
)
{
for (int i=0;i<no_blocks;i++)
{
Excel_block blk
(block_row_length,
block_col_length,
Excel_border::THICK,
Excel_coordinate((staring_row+i-1)*block_row_length*2,1));

for (int j=0;j<block_row_length;j++)
{
for (int k=0;k<block_col_length;k++)
{
blk.add_content(j, k, L" Something");
}
}

blk.write_to_sheet(sheet);
}
}

My Excel_block class has as variables(among others) one COleSafeArray
content_array which will store the "data" and a static Range object generated
only once in the actual sheet and than copied by the write_to_sheet method:
void Excel_block::write_to_sheet(CWorksheet &sheet)
{
Excel_coordinate end_coordinate =
Excel_coordinate(start_coordinate.get_row()+get_rows()-1,
start_coordinate.get_col()+get_columns()-1);

Excel_coordinate start_coordinate2 =
Excel_coordinate(start_coordinate.get_row()+2*(get_rows()-1)-1,
start_coordinate.get_col());

char *range1=new char[10];
start_coordinate.get_excel_coordinate(range1);
char *range2=new char[10];
end_coordinate.get_excel_coordinate(range2);

CRange range = sheet.get_Range(COleVariant(range1),
COleVariant(range2));

if (block_format)//this is my static range object which is already generated
{
VARIANT copyTo;
V_VT(&copyTo) = VT_DISPATCH;
V_DISPATCH(&copyTo) = range;
block_format.Copy(copyTo); // Here I copy the block to my range!!
}

range.put_Value2(COleVariant(content_array));//and then put my values
content_array.Detach();

delete range1;
delete range2;
}

 

[RB Smissaert]

Is this C++? I don't know enough (nearly nil) of that to help.
Maybe you could post in the microsoft.public.excel.sdk group, although it
seems a very quiet group.
Or maybe you could explain what the purpose of the whole exercise is and
there might be a better way
to do it.

RBS

Excel_generator is the main class that will initialize the COM conecxion.
I have a "sheet" member which at the calling of the following method will
be
available and pointing to the current active sheet.

void Excel_generator::add_some_data
( int staring_row,
int no_blocks,
int block_row_length,
int block_col_length
)
{
for (int i=0;i<no_blocks;i++)
{
Excel_block blk
(block_row_length,
block_col_length,
Excel_border::THICK,
Excel_coordinate((staring_row+i-1)*block_row_length*2,1));

for (int j=0;j<block_row_length;j++)
{
for (int k=0;k<block_col_length;k++)
{
blk.add_content(j, k, L" Something");
}
}

blk.write_to_sheet(sheet);
}
}

My Excel_block class has as variables(among others) one COleSafeArray
content_array which will store the "data" and a static Range object
generated
only once in the actual sheet and than copied by the write_to_sheet
method:
void Excel_block::write_to_sheet(CWorksheet &sheet)
{
Excel_coordinate end_coordinate =
Excel_coordinate(start_coordinate.get_row()+get_rows()-1,
start_coordinate.get_col()+get_columns()-1);

Excel_coordinate start_coordinate2 =
Excel_coordinate(start_coordinate.get_row()+2*(get_rows()-1)-1,
start_coordinate.get_col());

char *range1=new char[10];
start_coordinate.get_excel_coordinate(range1);
char *range2=new char[10];
end_coordinate.get_excel_coordinate(range2);

CRange range = sheet.get_Range(COleVariant(range1),
COleVariant(range2));

if (block_format)//this is my static range object which is already
generated
{
VARIANT copyTo;
V_VT(&copyTo) = VT_DISPATCH;
V_DISPATCH(&copyTo) = range;
block_format.Copy(copyTo); // Here I copy the block to my range!!
}

range.put_Value2(COleVariant(content_array));//and then put my values
content_array.Detach();

delete range1;
delete range2;
}

Post the code and we can see if it could be done faster.

RBS

 

[Tas]

Yes it is a C++ "approach"... (I cannot use VB in this project ...) and I
couldn't find more information about this. I could't find also a more
"active" forum for c++ for this that is why I posted it here... The purpose
is to find a fast way for formating cells with color and Border without each
time getting the Interior and the Border of the cell (as I saw this is time
consuming). Imagine that you have a "page" of an normal "report" (A4) with
rows and columns (lets say 50 rows and 10 columns). Some cells from this page
must pe formated with a special color to notify the user about the
"importance" of the content on that cell. And imagine that you can "paste"
the formated "block" in an excel sheed (not one per sheet but let say 10000
per sheet, one after another). How can you do this faster? As I searched over
the doc and the forums I have come up with this solution which is less time
consumig: To have only once define this "Formated" block and then using the
Copy method I'm modifying the format of all my other "ranges" (which allways
will have the same size (but different positioning in the sheet)) and the
fill the range with the data from an Array (wich is faster than filling each
cell). I hope you understand what I want to do...

Regards,
T.

Is this C++? I don't know enough (nearly nil) of that to help.
Maybe you could post in the microsoft.public.excel.sdk group, although it
seems a very quiet group.
Or maybe you could explain what the purpose of the whole exercise is and
there might be a better way
to do it.

RBS

Excel_generator is the main class that will initialize the COM conecxion.
I have a "sheet" member which at the calling of the following method will
be
available and pointing to the current active sheet.

void Excel_generator::add_some_data
( int staring_row,
int no_blocks,
int block_row_length,
int block_col_length
)
{
for (int i=0;i<no_blocks;i++)
{
Excel_block blk
(block_row_length,
block_col_length,
Excel_border::THICK,
Excel_coordinate((staring_row+i-1)*block_row_length*2,1));

for (int j=0;j<block_row_length;j++)
{
for (int k=0;k<block_col_length;k++)
{
blk.add_content(j, k, L" Something");
}
}

blk.write_to_sheet(sheet);
}
}

My Excel_block class has as variables(among others) one COleSafeArray
content_array which will store the "data" and a static Range object
generated
only once in the actual sheet and than copied by the write_to_sheet
method:
void Excel_block::write_to_sheet(CWorksheet &sheet)
{
Excel_coordinate end_coordinate =
Excel_coordinate(start_coordinate.get_row()+get_rows()-1,
start_coordinate.get_col()+get_columns()-1);

Excel_coordinate start_coordinate2 =
Excel_coordinate(start_coordinate.get_row()+2*(get_rows()-1)-1,
start_coordinate.get_col());

char *range1=new char[10];
start_coordinate.get_excel_coordinate(range1);
char *range2=new char[10];
end_coordinate.get_excel_coordinate(range2);

CRange range = sheet.get_Range(COleVariant(range1),
COleVariant(range2));

if (block_format)//this is my static range object which is already
generated
{
VARIANT copyTo;
V_VT(©To) = VT_DISPATCH;
V_DISPATCH(©To) = range;
block_format.Copy(copyTo); // Here I copy the block to my range!!
}

range.put_Value2(COleVariant(content_array));//and then put my values
content_array.Detach();

delete range1;
delete range2;
}

Post the code and we can see if it could be done faster.

RBS

Hello there,

I have some questions regarding this Excel Automation (I'm not an
expert
on
this so please "excuse" if the questions are too trivial)...

I try to build a small application that will generate a very large
number
of
columns and rows into Excel sheets (lets say about 200 sheets each one
with
50 000 rows and 100 columns). There is a way to store "Cell" objects in
arrays so I could lets say "create" them into this array and than add
the
array to a specific range (similar to the "Text", double, short storage
into
an array...)? Why I'm asking this is because my Cells must have a
specific
format (they are somehow structured as "BLOCKS" and this block will
repeat
itself for many times) and I need a fast way to "copy" this "block"...
I
already did a sample where I'm generatig arround 10 000 "blocks" on a
sheet,
each block containing 3 rows and 15 colums, having each cell with a
different
color and a random border. The time until it was finished was arround
31
seconds... Can I do this faster? Any ideas are more than wellcome.

Thanks,
Tas

 

[RB Smissaert]

Have you tried the usual things to speed up code working on sheet ranges
such as turning off automatic calculation:

Application.Calculation = xlManual

'run all the sheet code here

ActiveSheet.Calculate

Application.Calculation = xlAutomatic

Would it make a difference just to copy values and do the formatting at the
end when all ranges are filled, so the formatting
can be done on larger ranges?

Not sure if you are doing this already, but could you manipulate Excel more
directly with the Excel API?
This is one for the SDK forum.

Can't think of anything else now, but I am not much into sheet work and I am
sure somebody will come up with some better ideas.


RBS

Yes it is a C++ "approach"... (I cannot use VB in this project ...) and I
couldn't find more information about this. I could't find also a more
"active" forum for c++ for this that is why I posted it here... The
purpose
is to find a fast way for formating cells with color and Border without
each
time getting the Interior and the Border of the cell (as I saw this is
time
consuming). Imagine that you have a "page" of an normal "report" (A4) with
rows and columns (lets say 50 rows and 10 columns). Some cells from this
page
must pe formated with a special color to notify the user about the
"importance" of the content on that cell. And imagine that you can "paste"
the formated "block" in an excel sheed (not one per sheet but let say
10000
per sheet, one after another). How can you do this faster? As I searched
over
the doc and the forums I have come up with this solution which is less
time
consumig: To have only once define this "Formated" block and then using
the
Copy method I'm modifying the format of all my other "ranges" (which
allways
will have the same size (but different positioning in the sheet)) and the
fill the range with the data from an Array (wich is faster than filling
each
cell). I hope you understand what I want to do...

Regards,
T.

Is this C++? I don't know enough (nearly nil) of that to help.
Maybe you could post in the microsoft.public.excel.sdk group, although it
seems a very quiet group.
Or maybe you could explain what the purpose of the whole exercise is and
there might be a better way
to do it.

RBS

Excel_generator is the main class that will initialize the COM
conecxion.
I have a "sheet" member which at the calling of the following method
will
be
available and pointing to the current active sheet.

void Excel_generator::add_some_data
( int staring_row,
int no_blocks,
int block_row_length,
int block_col_length
)
{
for (int i=0;i<no_blocks;i++)
{
Excel_block blk
(block_row_length,
block_col_length,
Excel_border::THICK,
Excel_coordinate((staring_row+i-1)*block_row_length*2,1));

for (int j=0;j<block_row_length;j++)
{
for (int k=0;k<block_col_length;k++)
{
blk.add_content(j, k, L" Something");
}
}

blk.write_to_sheet(sheet);
}
}

My Excel_block class has as variables(among others) one COleSafeArray
content_array which will store the "data" and a static Range object
generated
only once in the actual sheet and than copied by the write_to_sheet
method:
void Excel_block::write_to_sheet(CWorksheet &sheet)
{
Excel_coordinate end_coordinate =
Excel_coordinate(start_coordinate.get_row()+get_rows()-1,
start_coordinate.get_col()+get_columns()-1);

Excel_coordinate start_coordinate2 =
Excel_coordinate(start_coordinate.get_row()+2*(get_rows()-1)-1,
start_coordinate.get_col());

char *range1=new char[10];
start_coordinate.get_excel_coordinate(range1);
char *range2=new char[10];
end_coordinate.get_excel_coordinate(range2);

CRange range = sheet.get_Range(COleVariant(range1),
COleVariant(range2));

if (block_format)//this is my static range object which is already
generated
{
VARIANT copyTo;
V_VT(©To) = VT_DISPATCH;
V_DISPATCH(©To) = range;
block_format.Copy(copyTo); // Here I copy the block to my range!!
}

range.put_Value2(COleVariant(content_array));//and then put my values
content_array.Detach();

delete range1;
delete range2;
}

:

Post the code and we can see if it could be done faster.

RBS

Hello there,

I have some questions regarding this Excel Automation (I'm not an
expert
on
this so please "excuse" if the questions are too trivial)...

I try to build a small application that will generate a very large
number
of
columns and rows into Excel sheets (lets say about 200 sheets each
one
with
50 000 rows and 100 columns). There is a way to store "Cell" objects
in
arrays so I could lets say "create" them into this array and than
add
the
array to a specific range (similar to the "Text", double, short
storage
into
an array...)? Why I'm asking this is because my Cells must have a
specific
format (they are somehow structured as "BLOCKS" and this block will
repeat
itself for many times) and I need a fast way to "copy" this
"block"...
I
already did a sample where I'm generatig arround 10 000 "blocks" on
a
sheet,
each block containing 3 rows and 15 colums, having each cell with a
different
color and a random border. The time until it was finished was
arround
31
seconds... Can I do this faster? Any ideas are more than wellcome.

Thanks,
Tas

 

[Jim Thomlinson]

Not to rain on your parade but 200 sheets * 50,000 rows x 100 columns means
that you will be working with 1 billion cells. Excel is not really
comfortable with that much data. Take a look at this page for some memory
limits...

http://www.decisionmodels.com/memlimits.htm

Based on the amount of data you are working with you shoul really look at a
database. It handles that volume of data much more effectively.

 

[Tas]

Yes... well unfortunatlly the client wants this data into excel sheets
generated (because he also wants the cell format...). My problem is not
really the amount of data, and if I will work lets say with 100 times less
cell also I will have the same problem... In my other postings I explained
what I need to do... Anyway thanks for the advice (It seems that until now I
have one not so easy solution... to generate myself the xls files (to
building an Excel Generator...)).

Regards,
T.