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:
How to append or prepend a string to a variable

 

First page Previous page 1 2 Next page Last page  View All


Tejas
User

Nov 18, 2014, 11:06 PM

Post #1 of 46 (13096 views)
How to append or prepend a string to a variable Can't Post

The problem has nt so easy as i have written in the subject

The task is to create a workbook for 14 differennt countries in one go


Code
 
use strict;
use warnings;

use Excel::Writer::XLSX;
use Spreadsheet::XLSX;


my $US_workbook = Excel::Writer::XLSX->new( "US_GL_SL.xlsx");
my $US_GL_Sheet = $US_workbook->add_worksheet("GL_Sheet");
my $US_SL_Sheet = $US_workbook->add_worksheet( "SL_Sheet" );

my $GB_workbook = Excel::Writer::XLSX->new( "GB_GL_SL.xlsx");
my $GB_GL_Sheet = $GB_workbook->add_worksheet("GL_Sheet");
my $GB_SL_Sheet = $GB_workbook->add_worksheet( "SL_Sheet" );

my $DE_workbook = Excel::Writer::XLSX->new( "DE_GL_SL.xlsx");
my $DE_GL_Sheet = $DE_workbook->add_worksheet("GL_Sheet");
my $DE_SL_Sheet = $DE_workbook->add_worksheet( "SL_Sheet" );

my $FR_workbook = Excel::Writer::XLSX->new( "FR_GL_SL.xlsx");
my $FR_GL_Sheet = $FR_workbook->add_worksheet("GL_Sheet");
my $FR_SL_Sheet = $FR_workbook->add_worksheet( "SL_Sheet" );

my $ES_workbook = Excel::Writer::XLSX->new( "ES_GL_SL.xlsx");
my $ES_GL_Sheet = $ES_workbook->add_worksheet("GL_Sheet");
my $ES_SL_Sheet = $ES_workbook->add_worksheet( "SL_Sheet")


Now iam trying to set the columns for all the worksheet in one go appending _workbook with @Orgs value linearly



Code
my @Orgs = ("US","GB","DE","ES","IT","FR","GB","MX","BR","AU"); 

or(my $col = 0 ;$col < scalar @Orgs ; $col++ ){
my $workbook = $Orgs[$col];
$workbook .= "_workbook";
print "$workbook \n";
foreach my $worksheet ($workbook->sheets())
{
$worksheet->set_column( 'A:Q', 20 ); # Column E width set to 20
}
}


Though it is able to convert the variable name to worksheet name and prints = > US_workbook

Error

Quote

Can't call method "sheets" without a package or object reference at ./GL_SL_Final_Test.pl line 48.


What is missing ? Is something wrong in code or in understanding.
Is it possible in any other way to do it one go


And the same isssue with the code below


Code
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>"; 
open $sl_fh, '<', $sl_file or die "could not open $sl_fh <$!>";
while (my $my_line = <$gl_fh>) {
my $Sheet = (split /,/, $my_line)[0];
$Sheet .= "_GL_Sheet";
my @gl_line = (split /\|/, $my_line);
for(my $col=0 ;$col < scalar @gl_line ; $col++ ){
$Sheet->write_string ($gl_row, $col, $gl_line[$col],$frmt);
}
$gl_row++;
}



(This post was edited by Tejas on Nov 18, 2014, 11:15 PM)


Zhris
Enthusiast

Nov 19, 2014, 12:31 AM

Post #2 of 46 (13092 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Hi,

The key issue with both your examples is that you are assigning a variable with a string, but then later using it like an object by attempting to call a method on it, resulting in "Can't call method "sheets" without a package or object reference" error:


Code
my $workbook = $Orgs[$col]; # assigns workbook with string i.e. US. 
...
foreach my $worksheet ($workbook->sheets()) # attempts to call method sheets on workbook string.


The below is completely untested, but is what I think you should be doing instead:


Code
my @orgs = ('US','GB','DE','ES','IT','FR','GB','MX','BR','AU'); # note duplicate gb.  

for my $org ( @orgs )
{
my $workbook = Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx");
$workbook->add_worksheet( 'GL_Sheet' );
$workbook->add_worksheet( 'SL_Sheet' );

print "${org}_workbook";

for my $worksheet ( $workbook->sheets( ) )
{
$worksheet->set_column( 'A:Q', 20 ); # Column E width set to 20
}
}


You had alot of duplicate workbook instantiations at the top, 1 for each org, this can be reduced by handling in the org for loop instead. the variable $workbook is then handly an org specific excel writer object.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 12:39 AM)


Tejas
User

Nov 19, 2014, 12:40 AM

Post #3 of 46 (13083 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

Hi Zhris

But i am also trying to read the a file , And depending on the input org, iam trying to write the excel sheet,
How can this be acheived


Code
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";  
open $sl_fh, '<', $sl_file or die "could not open $sl_fh <$!>";
while (my $my_line = <$gl_fh>) {
my $Sheet = (split /,/, $my_line)[0];
$Sheet .= "_GL_Sheet";
my @gl_line = (split /,/, $my_line);
for(my $col=0 ;$col < scalar @gl_line ; $col++ ){
$Sheet->write_string ($gl_row, $col, $gl_line[$col],$frmt);
}
$gl_row++;
}


So Through bove code u ve posted, iam able to create 10 excel sheets.
But here i have a file with data in it
And depending on the Org(US,DE,...) value in that file, i have to write data to the correspondig worksheet.

IF ORG IS US , THEN I HAVE TO OPEN USE WORKBOOK AND WRITE DATA TO US WORKSHEET(Here all the names of work sheet are same (gl_shhe and sl_sheet))
How can this be acheived

I would like to have each woksheetwith its org or it ll be tough to find the excat worsheet where i can fill the data depending on th einput i get


(This post was edited by Tejas on Nov 19, 2014, 1:09 AM)


Zhris
Enthusiast

Nov 19, 2014, 1:26 AM

Post #4 of 46 (13073 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Hi,

The below is again untested, I am also inexperienced using Excel::Writer::XLSX therefore my code is likely more convuluted than it could be (I was looking through the documentation to try and find methods to get/set single worksheets and get their max row number, with no luck). It creates a single workbook, then worksheets are added as and when an org is seen. Worksheets and row numbers are held in the worksheets state hash.


Code
my %worksheets; 

my $workbook = Excel::Writer::XLSX->new( 'workbook.xlsx' );

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

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

my ( $org, @data ) = split /,|\|/, $line;

my ( $worksheet, $row );

if ( defined $worksheets{$org} )
{
$worksheet = $worksheets{$org}{worksheet};
$row = ++$worksheets{$org}{row};
}
else
{
$worksheet = $worksheets{$org}{worksheet} = $workbook->add_worksheet( "${org}_GL_Sheet" );
$row = $worksheets{$org}{row} = 0;
}

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

close $gl_fh;

__DATA__
US,one|two|three
GB,four|five|six


I'm still not certain this is what you desire, but appears to be more fitting based on your most recent description.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 2:26 AM)


Tejas
User

Nov 19, 2014, 1:28 AM

Post #5 of 46 (13069 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

The requirement is such that i need a seperate workbook per org
not worksheets

I have to create 10 workbooks , with 2 worksheets each

Depeing on the input from a file , i decide which file has to be printed with these values

Thanks
Tejas


Zhris
Enthusiast

Nov 19, 2014, 1:35 AM

Post #6 of 46 (13066 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Could you provide your input data, I made a guess based on your code, then explain how we decide which of the two worksheets to write to.

"AND WRITE DATA TO US WORKSHEET" threw me, I assumed you needed a worksheet for each org.

Chris


(This post was edited by Zhris on Nov 19, 2014, 1:38 AM)


Tejas
User

Nov 19, 2014, 1:51 AM

Post #7 of 46 (13062 views)
Post deleted by Tejas [In reply to]

 


Zhris
Enthusiast

Nov 19, 2014, 2:20 AM

Post #8 of 46 (13058 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Hi,

I am by no means 100% clear. Careful of your namespacing, i.e. workbooks, books, worksheets, sheets, tabs etc, its very confusing when you say worksheet but you mean workbook etc. The most useful thing you could do would be to attach example input and the resultant xlsx output.

Here is another untested attempt. It creates a workbook for each unique org found, stores this in the workbooks state hash along with the gl worksheet and row number and sl worksheet and row number. It proceeds to write the data to the gl worksheet only as per your code:


Code
my %workbooks; 

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

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

my ( $org, @data ) = split /\|/, $line;

my $workbook = $workbooks{$org}{workbook} //= Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
my $gl_sheet = $workbooks{$org}{worksheets}{gl}{worksheet} //= $workbook->add_worksheet( 'GL_Sheet' );
my $gl_row = $workbooks{$org}{worksheets}{gl}{row} += ( defined $workbooks{$org}{worksheets}{gl}{row} ) ? 1 : 0;
my $sl_sheet = $workbooks{$org}{worksheets}{sl}{worksheet} //= $workbook->add_worksheet( 'SL_Sheet' );
my $sl_row = $workbooks{$org}{worksheets}{sl}{row} += ( defined $workbooks{$org}{worksheets}{sl}{row} ) ? 1 : 0;

for my $gl_col ( 0 .. $#data )
{
$gl_sheet->write_string( $gl_row, $gl_col, $data[$gl_col], $frmt );
}
}

close $gl_fh;

__DATA__
US|one|two|three
GB|four|five|six


Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 3:01 AM)


Tejas
User

Nov 19, 2014, 3:24 AM

Post #9 of 46 (13031 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

As per xlsx terminology
A workbook is a new excel file
And Worksheet is a tab in that excel file


I need 14 Diffferent excel files , with two tabs in each file

Is it clear ?
Thanks
Tejas


Tejas
User

Nov 19, 2014, 3:36 AM

Post #10 of 46 (13030 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

Hey Zhris

As per xlsx terminology
A workbook is a new excel file
And Worksheet is a tab in that excel file


I need 14 Diffferent excel files , with two tabs in each file

Is it clear ?
Also ,

Quote
Can u please explain your code line by line.
As u have used shortest way , i dint undertand a bit

Also tell me what //= , ||= mean in perl..


And your code throws following error, i really dint understand why :(
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 30.
Global symbol "$workbook" requires explicit package name at ./Test.pl line 31.
Search pattern not terminated at ./Test.pl line 33.



Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 3:47 AM)


Zhris
Enthusiast

Nov 19, 2014, 3:57 AM

Post #11 of 46 (13025 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Hi,

With regards to terminology, I was thrown off most recently by "I print to GL_Tab of the US Excel sheet", whereas I assumed you meant "I print to GL worksheet of the US workbook" based on your other descriptions. Inevitably if I had assumed you did meant "US worksheet", the resultant script would be very different.


Quote
I need 14 Diffferent excel files , with two tabs in each file


My most recent code creates a different excel file (workbook) per org seen, if there are fourteen unique orgs then it certainly will create fourteen different excel files (workbooks). Two tabs (worksheets) are created per workbook (gl and sl).


Quote
Can u please explain your code line by line.


I will post a new version and include comments.


Quote
As u have used shortest way , i dint undertand a bit


The short way of reading / constructing the state hash is an alternative way to the way I used in my code prior to that. It is indeed confusing whats going on, I'll revert to a longer form thats easier to follow rather than explain.


Quote
Also tell me what //= , ||= mean in perl..


//= means assign the left side the right side value unless the left side is already defined.

||= means assign the left side the right side value unless the left side is already true.


Code
my $var1 = undef; # assign $var1 an undefined value. 
$var1 //= 'hello'; # $var1 becomes hello because it is/was undefined.
$var1 //= 'world'; # $var1 remains hello because it is/was defined.

my $var2 = 0; # assign $var2 an untrue value.
$var2 ||= 1; # $var1 becomes 1 because it is/was untrue.
$var2 ||= 2; # $var1 remains 1 because it is/was true.



Quote
And your code throws following error, i really dint understand why :(
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 30.
Global symbol "$workbook" requires explicit package name at ./Test.pl line 31.
Search pattern not terminated at ./Test.pl line 33.


I'm not certain why you are getting global symbol errors. Could you post your full code and I can see why. And the search pattern not terminated could be because you are using an older version of Perl that doesn't support the //, //= notation, I will remove in my new version.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 4:41 AM)


Tejas
User

Nov 19, 2014, 4:03 AM

Post #12 of 46 (13022 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

Hi

I have attached an excel sheet (A work book) and 14 such have to be created
If u can see insode , there are 2 tabs (i.e 2 worksheets , a tab can also be called a worksheet in a workbook)


I tried running your code
and below are errors


Quote
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 29.
Global symbol "$workbook" requires explicit package name at ./Test.pl line 30.
Search pattern not terminated at ./Test.pl line 32.


Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 4:04 AM)
Attachments: US_GL_SL.xlsx (8.22 KB)


Zhris
Enthusiast

Nov 19, 2014, 4:23 AM

Post #13 of 46 (13019 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

As per above:


Code
# define workbooks state hash.  
my %workbooks;

# open input filehandle.
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

# read input data.
while ( my $line = <$gl_fh> )
{
# remove newline character from end of line.
chomp $line;

# split the line up into org and column data.
my ( $org, @data ) = split /\|/, $line;

# define variables we require in order to write column data to appropriate worksheet.
my ( $workbook, $gl_sheet, $gl_row, $sl_sheet, $sl_row );

# we have seen this org before.
if ( defined $workbooks{$org} )
{
# assign each variable FROM the workbook state hash. Increment row numbers.
$workbook = $workbooks{$org}{workbook};
$gl_sheet = $workbooks{$org}{worksheets}{gl}{worksheet};
$gl_row = ++$workbooks{$org}{worksheets}{gl}{row};
$sl_sheet = $workbooks{$org}{worksheets}{sl}{worksheet};
$sl_row = ++$workbooks{$org}{worksheets}{sl}{row};
}
# we haven't seen this org before.
else
{
# assign each variable FROM the instantiated Excel::Writer::XLSX object. 0 row numbers.
$workbook = Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
$gl_sheet = $workbook->add_worksheet( 'GL_Sheet' );
$gl_row = 0;
$sl_sheet = $workbook->add_worksheet( 'SL_Sheet' );
$sl_row = 0;

# assign each variable TO the workbook state hash, in preparation for the next time we see this org.
$workbooks{$org}{workbook} = $workbook;
$workbooks{$org}{worksheets}{gl}{worksheet} = $gl_sheet;
$workbooks{$org}{worksheets}{gl}{row} = $gl_row;
$workbooks{$org}{worksheets}{sl}{worksheet} = $sl_sheet;
$workbooks{$org}{worksheets}{sl}{row} = $sl_row;
}

# write column data to appropriate worksheet.
for my $gl_col ( 0 .. $#data )
{
$gl_sheet->write_string( $gl_row, $gl_col, $data[$gl_col], $frmt );
}
}

# close input filehandle.
close $gl_fh;



(This post was edited by Zhris on Nov 19, 2014, 4:27 AM)


Zhris
Enthusiast

Nov 19, 2014, 4:27 AM

Post #14 of 46 (13017 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Please can you post the actual code you ran, I do not receive the same errors running the code I provided.

Chris


Tejas
User

Nov 19, 2014, 4:44 AM

Post #15 of 46 (13011 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

here is the code




Code
my $gl_file  = $ARGV[0]; 
my $gl_fh;


my $workbook = Excel::Writer::XLSX->new( 'workbook.xlsx' );
my $frmt = $workbook->add_format(color => 9,bg_color => 8,pattern => 1,border => 1,font => 'Calibri',size => 9,align => 'center' );
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

my %workbooks;

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

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

my ( $org, @data ) = split /;/, $line;

my $workbook = $workbooks{$org}{workbook} //= Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
my $gl_sheet = $workbooks{$org}{worksheets}{gl}{worksheet} //= $workbook->add_worksheet( 'GL_Sheet' );
my $gl_row = $workbooks{$org}{worksheets}{gl}{row} += ( defined $workbooks{$org}{worksheets}{gl}{row} ) ? 1 : 0;
my $sl_sheet = $workbooks{$org}{worksheets}{sl}{worksheet} //= $workbook->add_worksheet( 'SL_Sheet' );
my $sl_row = $workbooks{$org}{worksheets}{sl}{row} += ( defined $workbooks{$org}{worksheets}{sl}{row} ) ? 1 : 0;

for my $gl_col ( 0 .. $#data )
{
$gl_sheet->write_string( $gl_row, $gl_col, $data[$gl_col], $frmt );
}
}

close $gl_fh;


The error

Quote
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 36.
Search pattern not terminated at ./Test.pl line 39.



Tejas
User

Nov 19, 2014, 4:48 AM

Post #16 of 46 (13009 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

Sorry but i have two input files
SL and GL

So i will have to read two file

my $gl_file = $ARGV[0];
my $sl_file = $ARGV[1];

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";
open $sl_fh, '<', $sl_file or die "could not open $sl_fh <$!>";

while ( my $line = <$gl_fh> ) {
//Write data to GL Tab of the excel

}


and

while ( my $line = <$sl_fh> )
{ // Write data to sl taab of excel
}

Did u get this ?


Thanks


Zhris
Enthusiast

Nov 19, 2014, 4:48 AM

Post #17 of 46 (13009 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

That isn't your actual script i.e. you haven't used Excel::Writer::XLSX and line 36 doesn't exist etc.


Zhris
Enthusiast

Nov 19, 2014, 4:49 AM

Post #18 of 46 (13007 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Yes I understand, lets get it working with just 1 input file for now.


Tejas
User

Nov 19, 2014, 4:51 AM

Post #19 of 46 (13005 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

Hey its working with one input file zhris..
The latest code u ve posted ..works wonders

Now all i have to do is to read the second input file again
and write it to corresponding tab

I hope u r inline, as i undestand its merely confusing .

I think i have to repeat the same code for the other input file too .is that correct ?

You really do have a superb understanding though ur nt into XLSX.

Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 4:52 AM)


Zhris
Enthusiast

Nov 19, 2014, 4:57 AM

Post #20 of 46 (13000 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

Ah, it was perhaps the "Search pattern not terminated at ./Test.pl line 32" error that inadvertedly caused the other errors! Is the formatting working correctly since you applied it to a workbook that we do not use? I am just off out for half an hour, I will get back to you shortly with a hopefully finalised version.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 4:58 AM)


Tejas
User

Nov 19, 2014, 5:02 AM

Post #21 of 46 (12997 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

:( sorry i dint get that really

But ur second code works really great
I just have to read a second file and write it to other tab

GL Tab with GL data is done
Have to fill the sl tab


Thanks
tejas


(This post was edited by Tejas on Nov 19, 2014, 5:36 AM)


Tejas
User

Nov 19, 2014, 6:09 AM

Post #22 of 46 (12987 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post


Code
while ( my $line = <$gl_fh> ) {  
//Write data to GL Tab of the excel

}


and

while ( my $line = <$sl_fh> )
{ // Write data to sl taab of excel
//Here we have to point to file we have created in above while loop
//Aslo the value will not be direcly in the file
if ($org eq '04' || $org eq '2Y' || $org eq '2R' || $org eq '3S' || $org eq '79' || $org eq '3T' || $org eq '3M' || $org eq '2H'){
$org = "US " ; }

//And then we have to write the data to Excel sheet created in the above while loop to a SL_Tab

}


I think its too confusing

But all in all, i have to read two input file and write data accordingly to thet correspoding Excel sheets tab.

And condition being we dont get the org id as we got in first file


Zhris
Enthusiast

Nov 19, 2014, 6:18 AM

Post #23 of 46 (12987 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

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)


Tejas
User

Nov 19, 2014, 6:22 AM

Post #24 of 46 (12984 views)
Re: [Zhris] How to append or prepend a string to a variable [In reply to] Can't Post

Zhris,

The two input files are delimited with diffrent demlimiters
dur to some constraints

Second file is delimited with ; as the values might contain | in it

ex: my|tranasaction|success can be a value

And reading a second file isnt staright forward , it doesnt hasve ORG code in it
we have to match it to the exisiting sheet like this, which is failing

Code
 my ( $org, @data ) = split /\|/, $line; 
if ($org eq '04' || $org eq '2Y' || $org eq '2R' || $org eq '3S' || $org eq '79' || $org eq '3T' || $org eq '3M' || $org eq '2H'){
$org = "1" ; }

Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 6:26 AM)


Zhris
Enthusiast

Nov 19, 2014, 6:35 AM

Post #25 of 46 (12977 views)
Re: [Tejas] How to append or prepend a string to a variable [In reply to] Can't Post

I have updated the code above to allow for a different deliminator per file. For simplicity, the file information is now hardcoded in the script rather than fetched from args.


Quote
And reading a second file isnt staright forward , it doesnt hasve ORG code in it
we have to match it to the exisiting sheet like this, which is failing


Looks like you have this covered by chucking a set of conditions in beneath the split, although a lookup hash, perhaps "regexp => org" pairs would be nicer.

Chris


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

First page Previous page 1 2 Next page Last page  View All
 
 


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

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