CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner: Re: [Tejas] How to append or prepend a string to a variable : Edit Log



Zhris
Enthusiast

Nov 19, 2014, 6:18 AM


Views: 12757
Re: [Tejas] How to append or prepend a string to a variable

Hi,

Again I feel that as I am unfamiliar with the module and I couldn't find everything I needed via the documentation, the code is probably more convuluted than it could be i.e. I couldn't find a method to get the format from the workbook, I couldn't find a method to read a single identifiable worksheet from the workbook and I couldn't find a method to get the last row number of the worksheet, therefore this information is stored and fetched from the workbooks state hash.

The below reads the gl and sl files from args and uses their data to build the workbooks. I have once again not tested the code below but I'm sure you'll let me know if there are any issues. This is a standalone script, run it as it is, do not try to fit it in somewhere in your existing code:

$perl Test.pl

Code
use strict; 
use warnings;
use Excel::Writer::XLSX;

my %workbooks;

my %input =
(
GL =>
{
path => $ARGV[0],
delim => '|',
org_map => undef,
},
SL =>
{
path => $ARGV[1],
delim => ';',
org_map =>
{
'3H' => 'US',
'04' => 'US',
'2R' => 'US',
'6H' => 'GB',
}
}
);

while ( my ( $worksheet_id, $worksheet_info ) = each ( %input ) )
{
my $path = $worksheet_info->{path};
my $delim = $worksheet_info->{delim};
my $org_map = $worksheet_info->{org_map};

open my $handle, '<', $path or die "could not open '$path': $!";

while ( my $line = <$handle> )
{
chomp $line;

my ( $org, @data ) = split /\Q$delim\E/, $line;

$org = $org_map->{$org} if ( defined $org_map and exists $org_map->{$org} );

my ( $workbook, $format );
if ( defined $workbooks{$org} )
{
$workbook = $workbooks{$org}{workbook};
$format = $workbooks{$org}{format};
}
else
{
$workbook = Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
$format = $workbook->add_format( color => 9,
bg_color => 8,
pattern => 1,
border => 1,
font => 'Calibri',
size => 9,
align => 'center' );

$workbooks{$org}{workbook} = $workbook;
$workbooks{$org}{format} = $format;
}

my ( $worksheet, $row );
if ( defined $workbooks{$org}{worksheets}{$worksheet_id} )
{
$worksheet = $workbooks{$org}{worksheets}{$worksheet_id}{worksheet};
$row = ++$workbooks{$org}{worksheets}{$worksheet_id}{row};
}
else
{
$worksheet = $workbook->add_worksheet( "${worksheet_id}_Sheet" );
$row = 0;

$workbooks{$org}{worksheets}{$worksheet_id}{worksheet} = $worksheet;
$workbooks{$org}{worksheets}{$worksheet_id}{row} = $row;
}

for my $col ( 0 .. $#data )
{
$worksheet->write_string( $row, $col, $data[$col], $format );
}
}

close $handle;
}


Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 7:36 AM)


Edit Log:
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 6:18 AM
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 6:24 AM
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 6:31 AM
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 6:36 AM
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 6:40 AM
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 6:51 AM
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 7:31 AM
Post edited by Zhris (Enthusiast) on Nov 19, 2014, 7:36 AM


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives