Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Writing multiline record (2D Arrays) to excel using Win32OLE 1

Status
Not open for further replies.

dmazzini

Programmer
Jan 20, 2004
480
US
Hi

I have an array with a variable number of multi lines records. Array contains data coming from different txt files. I dont care the file contents, I just need to copy and paste it into Excel worksheets

All other script routines have been developed using Win32::Ole module.

The question is which it would be the best way to write the content of the whole file (array) into the spreadheet since files are huge and write cell by cell consumes too much.

SpreadsheetWriteExcel Module has the solution but I would like to keep the program simple just using Win32OLE Module.

SpreadsheetWriteExcel uses this

Code:
$Sheet_Stats{$tab}->write('A1', [ @INFO ]);

What would it be the command for Win32OLE. keep in mind that I dont know number of records, so use Range Excel cant be constant.

When I use it, the whole array contents go to cell A1,

Code:
$Sheet_Stats{$tab}->Cells(1,1)->{Value} = @INFO;

Comments??

dmazzini
GSM/UMTS System and Telecomm Consultant

 
BTW writing cell by cell consumes too much memory, take ages and pc dies...

Code:
my $i=1; my $c =1; 
foreach my $value (@INFO) {  
        $Sheet_Stats{$tab}->Cells($i,$c)->{Value} = $value;      $c++;   
}


dmazzini
GSM/UMTS System and Telecomm Consultant

 
Hi

After whole day working on this I found the solution:

Code:
my $CLIP = Win32::Clipboard();
$CLIP->Set($stats); # stats contains whole file contents
print "Clipboard contains: ", $CLIP->Get(), "\n";         
my $paste_range = $Sheet_Stats{$tab}->Range('A1');
$paste_range->PasteSpecial();

BTW, script works very fast...Notice that I tried to use Spreadsheet XLSX Modules since files were 2007 and it was not lucky. In 2007 the xls file format changed notoriously. Happy now!

dmazzini
GSM/UMTS System and Telecomm Consultant

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top