Split cells using Perl

February 20, 2012 at 07:02:38
Specs: Windows XP
Perl snippet:
#!/usr/bin/perl -w
use warnings;
use strict;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
# open excel application
my $Excel = Win32::OLE->new('Excel.Application');

$Excel->{'Visible'} = 1;

#get a new work book
my $book = $Excel-> Workbooks -> Add;

#write to a particular cell
my $Sheet=$book->Worksheets(1);

#Globals
our $row = 1;
our $col = 2;

my ($tag, %list);
while (<DATA>){
next unless /./;
if (/^(\w+)/){
$tag = $1;
next;
}
if (/^-- (\w+)/){
push @{$list{$tag}}, $1;
}
}
for my $type (@{$list{main}}){
if ($list{$type}){
for (@{$list{$type}}){
print "main $type $_\n";
$Sheet-> Cells($row,$col+1)->{'Value'} = "main $type $_\n";
$row=$row+1;
}
}
else {
print "main $type\n";
$Sheet-> Cells($row,$col+1)->{'Value'} = "main $type\n";
$row=$row+1;
}
}

__END__

computing
-- check
-- check1

net
-- check2
-- check3

main
-- computing
-- net

The above snippet will write the following in an excel sheet in one column:

Output: Cell A1
main computing check
main computing check1
main net check2
main net check3

But i need the output in the following way:

Output: Cell A1 B1 C1
main computing check
main computing check1
main net check2
main net check3

That is i want every function in independent cell not in single cell..Help out guys


See More: Split cells using Perl

Report •


#1
February 20, 2012 at 08:30:17
Instead of the OLE modules, I use Spreadsheet::WriteExcel

Here's my version which works correctly with your sample data, but if the format of your actual data differs, then this will of course need a slight adjustment.

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $workbook  = Spreadsheet::WriteExcel->new('test.xls');
my $worksheet = $workbook->add_worksheet();

$/ = '';
my %list;
while ( my $block = <DATA> ) {
    my ($tag, @list) = split /\n/, $block;
    s/^-- // for @list;
    $list{$tag} = \@list;
}

my $row = 0;
foreach my $tag ( @{ $list{'main'} } ) {
    if ( exists $list{$tag} ) {
        $worksheet->write($row, 0, $tag);
        $worksheet->write_row($row++, 1, $list{$tag});
    }
}
$workbook->close();

__END__

computing
-- check
-- check1

net
-- check2
-- check3

main
-- computing
-- net


Report •

#2
February 20, 2012 at 08:36:11
Oops, I forgot that you wanted 'main' in col A1 for each row.

Here's the corrected adjustment.

        $worksheet->write_row($row, 0, ['main', $tag]);
        $worksheet->write_row($row++, 2, $list{$tag});


Report •

Related Solutions


Ask Question