A fast way to fill cells in Excel

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

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

RBS

"Tas" <[email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> Post the code and we can see if it could be done faster.
>
> RBS
>
> "Tas" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

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

"Tas" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> Post the code and we can see if it could be done faster.
>>
>> RBS
>>
>> "Tas" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>

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.
"RB Smissaert" wrote:

> 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
>
> "Tas" <[email protected]> wrote in message
> news:[email protected]...
> > 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;
> > }
> >
> > "RB Smissaert" wrote:
> >
> >> Post the code and we can see if it could be done faster.
> >>
> >> RBS
> >>
> >> "Tas" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
>
>

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

"Tas" <[email protected]> wrote in message
news:[email protected]...
> 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.
> "RB Smissaert" wrote:
>
>> 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
>>
>> "Tas" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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;
>> > }
>> >
>> > "RB Smissaert" wrote:
>> >
>> >> Post the code and we can see if it could be done faster.
>> >>
>> >> RBS
>> >>
>> >> "Tas" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >>
>> >>
>>
>>

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.
--
HTH...

Jim Thomlinson


"Tas" wrote:

> 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

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.
"Jim Thomlinson" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Tas" wrote:
>
> > 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