Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A fast way to fill cells in Excel
Post the code and we can see if it could be done faster.
RBS "Tas" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A fast way to fill cells in Excel
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_ro ws()-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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A fast way to fill cells in Excel
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" wrote in message ... 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_ro ws()-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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A fast way to fill cells in Excel
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" wrote in message ... 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_ro ws()-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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A fast way to fill cells in Excel
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" wrote in message ... 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" wrote in message ... 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_ro ws()-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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A fast way to fill cells in Excel
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
A fast way to fill cells in Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to fast format cells? | Excel Discussion (Misc queries) | |||
cannot make the excel fill in/delete cells fast | Excel Programming | |||
create a fill in template to tab to fill in cells | Excel Discussion (Misc queries) | |||
Fast way to search many cells by column for text strings | Excel Discussion (Misc queries) | |||
Another avoid the loop trick request - on fast fill (Maybe an Alan trick) | Excel Programming |