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

[dmazzini]

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

$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,

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

Comments??

dmazzini
GSM/UMTS System and Telecomm Consultant

 

[dmazzini]

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

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

 

[dmazzini]

Hi

After whole day working on this I found the solution:

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