RE: Excel Sheet Gen Code gives error
Thanks Pritish/jing!
From: Pritish Pattanaik [mailto:pattanaikprit...@gmail.com]
Sent: Monday, July 15, 2013 4:47 PM
To: Tom, Jentil Kuriakose; beginners@perl.org
Subject: Re: Excel Sheet Gen Code gives error
Hello Jentil,
Have you installed SpreadSheet::WriteExcel module ? ,
if no => then install it from cpan shell or you can download it from
(http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/Spreadsheet-WriteExcel-2.39.tar.gz
link)
follow the instruction:
wget link
tar -xzvf file.gz
cd newlycreateddir
perl Makefile.PL PREFIX="/path/to/your/testing/area"
make
make test
if yes => then search WriteExcel.pm file from your machine
find / -name "WriteExcel.pm"
/find/path/WriteExcel.pm
add this line in your main code
use lib "/find/path/"
[https://mail.google.com/mail/u/0/images/cleardot.gif]
On Mon, Jul 15, 2013 at 4:44 PM, Pritish Pattanaik
mailto:pattanaikprit...@gmail.com>> wrote:
Hello Jentil,
Have you installed SpreadSheet::WriteExcel module ? ,
if no => then install it from cpan shell or you can download it from
(http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/Spreadsheet-WriteExcel-2.39.tar.gz
link)
follow the instruction:
wget link
tar -xzvf file.gz
perl ./DIR/Makefile.PL prefix="/path/to/your/testing/area"
make
make test
if yes => then search WriteExcel.pm file from your machine
find / -name "WriteExcel.pm"
/find/path/WriteExcel.pm
add this line in your main code
use lib "/find/path/"
On Mon, Jul 15, 2013 at 4:31 PM, Tom, Jentil Kuriakose
mailto:c_j...@qti.qualcomm.com>> wrote:
Hi,
I have written simple EXCEL sheet gen PEARL code:
#!/usr/bin/perl
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("simple.xls");
my $SUMMARY = $workbook->add_worksheet("SUMMARY");
$SUMMARY->write(0, 0, "START");
$SUMMARY->write(0, 2, "A");
But it gives following error when I execute through:
>perl excel_sheet.pl<http://excel_sheet.pl>
Can't locate Spreadsheet/WriteExcel.pm in @INC (@INC contains:
/usr/perl5/5.8.4/lib/sun4-solaris-64int /usr/perl5/5.8.4/lib
/usr/perl5/site_perl/5.8.4/sun4-solaris-64int /usr/perl5/site_perl/5.8.4
/usr/perl5/site_perl /usr/perl5/vendor_perl/5.8.4/sun4-solaris-64int
/usr/perl5/vendor_perl/5.8.4 /usr/perl5/vendor_perl .) at
excel_sheet.pl<http://excel_sheet.pl> line 4.
BEGIN failed--compilation aborted at excel_sheet.pl<http://excel_sheet.pl> line
4.
Kindly help.
Thanks,
Jentil.
--
To unsubscribe, e-mail:
beginners-unsubscr...@perl.org<mailto:beginners-unsubscr...@perl.org>
For additional commands, e-mail:
beginners-h...@perl.org<mailto:beginners-h...@perl.org>
http://learn.perl.org/
Re: Excel Sheet Gen Code gives error
Hello Jentil,
Have you installed SpreadSheet::WriteExcel module ? ,
if no => then install it from cpan shell or you can download it from (
http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/Spreadsheet-WriteExcel-2.39.tar.gzlink)
follow the instruction:
wget link
tar -xzvf file.gz
cd newlycreateddir
perl Makefile.PL PREFIX="/path/to/your/testing/area"
make
make test
if yes => then search WriteExcel.pm file from your machine
find / -name "WriteExcel.pm"
/find/path/WriteExcel.pm
add this line in your main code
use lib "/find/path/"
On Mon, Jul 15, 2013 at 4:44 PM, Pritish Pattanaik <
pattanaikprit...@gmail.com> wrote:
> Hello Jentil,
>
> Have you installed SpreadSheet::WriteExcel module ? ,
>
> if no => then install it from cpan shell or you can download it from (
> http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/Spreadsheet-WriteExcel-2.39.tar.gzlink)
>
>
> follow the instruction:
>
> wget link
> tar -xzvf file.gz
> perl ./DIR/Makefile.PL prefix="/path/to/your/testing/area"
> make
> make test
>
> if yes => then search WriteExcel.pm file from your machine
>
> find / -name "WriteExcel.pm"
> /find/path/WriteExcel.pm
>
>
> add this line in your main code
>
> use lib "/find/path/"
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Mon, Jul 15, 2013 at 4:31 PM, Tom, Jentil Kuriakose <
> c_j...@qti.qualcomm.com> wrote:
>
>>
>> Hi,
>>
>> I have written simple EXCEL sheet gen PEARL code:
>>
>> #!/usr/bin/perl
>>
>> use strict;
>> use Spreadsheet::WriteExcel;
>>
>> my $workbook = Spreadsheet::WriteExcel->new("simple.xls");
>> my $SUMMARY = $workbook->add_worksheet("SUMMARY");
>>
>> $SUMMARY->write(0, 0, "START");
>> $SUMMARY->write(0, 2, "A");
>>
>> But it gives following error when I execute through:
>>
>> >perl excel_sheet.pl
>> Can't locate Spreadsheet/WriteExcel.pm in @INC (@INC contains:
>> /usr/perl5/5.8.4/lib/sun4-solaris-64int /usr/perl5/5.8.4/lib
>> /usr/perl5/site_perl/5.8.4/sun4-solaris-64int /usr/perl5/site_perl/5.8.4
>> /usr/perl5/site_perl /usr/perl5/vendor_perl/5.8.4/sun4-solaris-64int
>> /usr/perl5/vendor_perl/5.8.4 /usr/perl5/vendor_perl .) at excel_sheet.plline
>> 4.
>> BEGIN failed--compilation aborted at excel_sheet.pl line 4.
>>
>> Kindly help.
>>
>>
>> Thanks,
>> Jentil.
>>
>>
>> --
>> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
>> For additional commands, e-mail: beginners-h...@perl.org
>> http://learn.perl.org/
>>
>>
>>
>
Re: Excel Sheet Gen Code gives error
Hi Jentil,
You do not seem to have the module installed. If you have cpanm, you may try:
$ cpanm Spreadsheet::WriteExcel
Cheers,
Jing
On 15 Jul 2013, at 19:01, "Tom, Jentil Kuriakose"
wrote:
>
> Hi,
>
> I have written simple EXCEL sheet gen PEARL code:
>
> #!/usr/bin/perl
>
> use strict;
> use Spreadsheet::WriteExcel;
>
> my $workbook = Spreadsheet::WriteExcel->new("simple.xls");
> my $SUMMARY = $workbook->add_worksheet("SUMMARY");
>
> $SUMMARY->write(0, 0, "START");
> $SUMMARY->write(0, 2, "A");
>
> But it gives following error when I execute through:
>
>> perl excel_sheet.pl
> Can't locate Spreadsheet/WriteExcel.pm in @INC (@INC contains:
> /usr/perl5/5.8.4/lib/sun4-solaris-64int /usr/perl5/5.8.4/lib
> /usr/perl5/site_perl/5.8.4/sun4-solaris-64int /usr/perl5/site_perl/5.8.4
> /usr/perl5/site_perl /usr/perl5/vendor_perl/5.8.4/sun4-solaris-64int
> /usr/perl5/vendor_perl/5.8.4 /usr/perl5/vendor_perl .) at excel_sheet.pl line
> 4.
> BEGIN failed--compilation aborted at excel_sheet.pl line 4.
>
> Kindly help.
>
>
> Thanks,
> Jentil.
>
>
> --
> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
> For additional commands, e-mail: beginners-h...@perl.org
> http://learn.perl.org/
>
>
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Excel Sheet Gen Code gives error
Hi,
I have written simple EXCEL sheet gen PEARL code:
#!/usr/bin/perl
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("simple.xls");
my $SUMMARY = $workbook->add_worksheet("SUMMARY");
$SUMMARY->write(0, 0, "START");
$SUMMARY->write(0, 2, "A");
But it gives following error when I execute through:
>perl excel_sheet.pl
Can't locate Spreadsheet/WriteExcel.pm in @INC (@INC contains:
/usr/perl5/5.8.4/lib/sun4-solaris-64int /usr/perl5/5.8.4/lib
/usr/perl5/site_perl/5.8.4/sun4-solaris-64int /usr/perl5/site_perl/5.8.4
/usr/perl5/site_perl /usr/perl5/vendor_perl/5.8.4/sun4-solaris-64int
/usr/perl5/vendor_perl/5.8.4 /usr/perl5/vendor_perl .) at excel_sheet.pl line 4.
BEGIN failed--compilation aborted at excel_sheet.pl line 4.
Kindly help.
Thanks,
Jentil.
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Variables in $Excel->Workbooks->Open
> Dear All,
>
> I am having an XLSX file in server and my OS in Win7. The first open
> statement is working fine in which we have give the actual file name.
>
> 2nd open function is not working where we have given the file name as
> variable.
>
> Please help in resolving this error.
>
>
> use Win32::OLE;
> use Win32::OLE qw(in with);
> use Win32::OLE::Variant;
> use Win32::OLE::Const 'Microsoft Excel';
> $emfile="02896787";
> $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
>Win32::OLE->new('Excel.Application');
> $Excel->{'Visible'} = 0;#0 is hidden, 1 is visible
> $Excel->{DisplayAlerts}=0;#0 is hide alerts
> my $mBook = $Excel->Workbooks->Open
> ('pchns2003z\SPECIALIZED_SERVICES_I\AnI\Embase\Pull
> files_IRNPD\inventory\02896787.xlsx'); # open Excel file
>
> my $meBook = $Excel->Workbooks->Open
> ("pchns2003z\\SPECIALIZED_SERVICES_I\\AnI\\Embase\\Pull
> files_IRNPD\\inventory\\$emfile.xlsx"); # open Excel file
There is no problem with the variable interpolation or backslashes.
The two strings are the same:
my $emfile="02896787";
my $mBook =
'pchns2003z\SPECIALIZED_SERVICES_I\AnI\Embase\Pullfiles_IRNPD\inve
ntory\02896787.xlsx';
my $meBook =
"pchns2003z\\SPECIALIZED_SERVICES_I\\AnI\\Embase\\Pullfiles_IRNPD\
\inventory\\$emfile.xlsx";
print "$mBook\n$meBook\n";
print ($mBook eq $meBook ? "the same" : "different");
The problem lies elsewhere. If you ask Excel to open the same file in
two instances at the same time it will complain that it's already
open.
Try to open a different file :-)
Jenda
= je...@krynicky.cz === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Variables in $Excel->Workbooks->Open
On May 7, 2013, at 1:48 AM, Ganesh Babu N wrote: > I tried your method but it showing the same error. Which method? I made two suggestions. It is always helpful to post the exact program that you tried and the exact error message you are getting. As I explained, I do not have a Windows system on which I can try to reproduce your problem, but others on the list will. You can make it very easy to help you by providing the program so that someone with Windows can try it out on their system and maybe find a solution. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Variables in $Excel->Workbooks->Open
I tried your method but it showing the same error.
Ganesh
On Mon, May 6, 2013 at 8:58 PM, Jim Gibson wrote:
>
> On May 6, 2013, at 6:42 AM, Ganesh Babu N wrote:
>
> >
> > Dear All,
> >
> > I am having an XLSX file in server and my OS in Win7. The first open
> statement is working fine in which we have give the actual file name.
> >
> > 2nd open function is not working where we have given the file name as
> variable.
> >
> > Please help in resolving this error.
> >
> >
> > use Win32::OLE;
> > use Win32::OLE qw(in with);
> > use Win32::OLE::Variant;
> > use Win32::OLE::Const 'Microsoft Excel';
> > $emfile="02896787";
> > $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
> >Win32::OLE->new('Excel.Application');
> > $Excel->{'Visible'} = 0;#0 is hidden, 1 is visible
> > $Excel->{DisplayAlerts}=0;#0 is hide alerts
> > my $mBook = $Excel->Workbooks->Open
> > ('pchns2003z\SPECIALIZED_SERVICES_I\AnI\Embase\Pull
> files_IRNPD\inventory\02896787.xlsx'); # open Excel file
> >
> > my $meBook = $Excel->Workbooks->Open
> > ("pchns2003z\\SPECIALIZED_SERVICES_I\\AnI\\Embase\\Pull
> files_IRNPD\\inventory\\$emfile.xlsx"); # open Excel file
> >
> > $Sheet2 = $mBook->Worksheets(1);
> > $Sheet3 = $meBook->Worksheets(1);
>
> I am not using Windows, but I suggest you try using a single forward slash
> as a path delimiter for both single and double quotes. I also note that
> your both of your strings start with ''. You might try building up the
> path in a single string variable and use that as the argument to Open,
> after printing out what the variable actually contains to verify your
> string is correct.
> --
> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
> For additional commands, e-mail: beginners-h...@perl.org
> http://learn.perl.org/
>
>
>
Re: Variables in $Excel->Workbooks->Open
I make promish when i learn to perl, i'll help the more people has possible.
I hope i don't forget!!
On Mon, May 6, 2013 at 4:28 PM, Jim Gibson wrote:
>
> On May 6, 2013, at 6:42 AM, Ganesh Babu N wrote:
>
> >
> > Dear All,
> >
> > I am having an XLSX file in server and my OS in Win7. The first open
> statement is working fine in which we have give the actual file name.
> >
> > 2nd open function is not working where we have given the file name as
> variable.
> >
> > Please help in resolving this error.
> >
> >
> > use Win32::OLE;
> > use Win32::OLE qw(in with);
> > use Win32::OLE::Variant;
> > use Win32::OLE::Const 'Microsoft Excel';
> > $emfile="02896787";
> > $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
> >Win32::OLE->new('Excel.Application');
> > $Excel->{'Visible'} = 0;#0 is hidden, 1 is visible
> > $Excel->{DisplayAlerts}=0;#0 is hide alerts
> > my $mBook = $Excel->Workbooks->Open
> > ('pchns2003z\SPECIALIZED_SERVICES_I\AnI\Embase\Pull
> files_IRNPD\inventory\02896787.xlsx'); # open Excel file
> >
> > my $meBook = $Excel->Workbooks->Open
> > ("pchns2003z\\SPECIALIZED_SERVICES_I\\AnI\\Embase\\Pull
> files_IRNPD\\inventory\\$emfile.xlsx"); # open Excel file
> >
> > $Sheet2 = $mBook->Worksheets(1);
> > $Sheet3 = $meBook->Worksheets(1);
>
> I am not using Windows, but I suggest you try using a single forward slash
> as a path delimiter for both single and double quotes. I also note that
> your both of your strings start with ''. You might try building up the
> path in a single string variable and use that as the argument to Open,
> after printing out what the variable actually contains to verify your
> string is correct.
> --
> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
> For additional commands, e-mail: beginners-h...@perl.org
> http://learn.perl.org/
>
>
>
Re: Variables in $Excel->Workbooks->Open
On May 6, 2013, at 6:42 AM, Ganesh Babu N wrote:
>
> Dear All,
>
> I am having an XLSX file in server and my OS in Win7. The first open
> statement is working fine in which we have give the actual file name.
>
> 2nd open function is not working where we have given the file name as
> variable.
>
> Please help in resolving this error.
>
>
> use Win32::OLE;
> use Win32::OLE qw(in with);
> use Win32::OLE::Variant;
> use Win32::OLE::Const 'Microsoft Excel';
> $emfile="02896787";
> $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
>Win32::OLE->new('Excel.Application');
> $Excel->{'Visible'} = 0;#0 is hidden, 1 is visible
> $Excel->{DisplayAlerts}=0;#0 is hide alerts
> my $mBook = $Excel->Workbooks->Open
> ('pchns2003z\SPECIALIZED_SERVICES_I\AnI\Embase\Pull
> files_IRNPD\inventory\02896787.xlsx'); # open Excel file
>
> my $meBook = $Excel->Workbooks->Open
> ("pchns2003z\\SPECIALIZED_SERVICES_I\\AnI\\Embase\\Pull
> files_IRNPD\\inventory\\$emfile.xlsx"); # open Excel file
>
> $Sheet2 = $mBook->Worksheets(1);
> $Sheet3 = $meBook->Worksheets(1);
I am not using Windows, but I suggest you try using a single forward slash as a
path delimiter for both single and double quotes. I also note that your both of
your strings start with ''. You might try building up the path in a single
string variable and use that as the argument to Open, after printing out what
the variable actually contains to verify your string is correct.
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Variables in $Excel->Workbooks->Open
Dear All,
I am having an XLSX file in server and my OS in Win7. The first open
statement is working fine in which we have give the actual file name.
2nd open function is not working where we have given the file name as
variable.
Please help in resolving this error.
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
$emfile="02896787";
$Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
Win32::OLE->new('Excel.Application');
$Excel->{'Visible'} = 0;#0 is hidden, 1 is visible
$Excel->{DisplayAlerts}=0;#0 is hide alerts
my $mBook = $Excel->Workbooks->Open
('pchns2003z\SPECIALIZED_SERVICES_I\AnI\Embase\Pull
files_IRNPD\inventory\02896787.xlsx'); # open Excel file
my $meBook = $Excel->Workbooks->Open
("pchns2003z\\SPECIALIZED_SERVICES_I\\AnI\\Embase\\Pull
files_IRNPD\\inventory\\$emfile.xlsx"); # open Excel file
$Sheet2 = $mBook->Worksheets(1);
$Sheet3 = $meBook->Worksheets(1);
Regards,
Ganesh
Re: Running into dependency hell with Cygwin when trying to install Excel-Writer-XLSX-0.65 and have all tests pass and not skipped
On Fri, Mar 08, 2013 at 05:25:53PM -0800, Jim Gibson wrote:
> > So what's the best Perl module that recursively installs all
> > prerequisites of a package for you, jsut supplying the
> > desired package name for installation?
>
> The CPAN module is want you want. It installs the prerequisites
> of a module. It comes with an executable ('cpan'). See 'perldoc
> CPAN'.
App::cpanminus is often a more convenient module to use. The
wrapper command is called 'cpanm' (versus 'cpan' for the CPAN
module). CPAN will typically prompt you constantly to install
each recursive dependency which gets old after about the 5th
prompt, and will show you all of the output by default, which if
successful usually doesn't matter to you. If all you want is for
the modules to be installed then App::cpanminus does a much
prettier, less interactive job of it, hiding the output by
default, but storing it in a log file if you want to inspect it
after (particularly if the modules you are installing could not
be installed). I believe there is also an App::cpanplus that you
can look into, which I assume is named that because its author
felt it does more i.e., is more featureful. I haven't used the
latter myself.
Regards,
--
Brandon McCaig
Castopulence Software
Re: Running into dependency hell with Cygwin when trying to install Excel-Writer-XLSX-0.65 and have all tests pass and not skipped
On Mar 8, 2013, at 4:49 PM, Kenneth Wolcott wrote:
> Hi;
>
> I'm Running into dependency hell with Cygwin when trying to install
> Excel-Writer-XLSX-0.65 and have all tests pass and not skipped
>
>
>
> So what's the best Perl module that recursively installs all
> prerequisites of a package for you, jsut supplying the desired package
> name for installation?
The CPAN module is want you want. It installs the prerequisites of a module. It
comes with an executable ('cpan'). See 'perldoc CPAN'.
>
> I suppose Cygwin is not the best platform for doing this.
No, it's not. Good luck.
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Running into dependency hell with Cygwin when trying to install Excel-Writer-XLSX-0.65 and have all tests pass and not skipped
Hi; I'm Running into dependency hell with Cygwin when trying to install Excel-Writer-XLSX-0.65 and have all tests pass and not skipped I wanted to experiment with Excel-Writer-XLSX-0.65 under Cygwin. During the: perl Makefile.PL + make + make test + make install install process, during make test, I saw the following skip messages: t/utility/xl_date_list.t .. skipped: Date::Calc required to run optional tests. t/utility/xl_dec_col.t ok t/utility/xl_dec_row.t ok t/utility/xl_decode_date_EU.t . skipped: Date::Calc required to run optional tests. t/utility/xl_decode_date_US.t . skipped: Date::Calc required to run optional tests. t/utility/xl_inc_col.t ok t/utility/xl_inc_row.t ok t/utility/xl_parse_date.t . skipped: Date::Manip and Date::Calc required to run optional tests. So I thought it would be nice to have these modules available as well. Now I enter dependency hell with Date:Calc when creating the makefile with perl: Warning: prerequisite Bit::Vector 7.1 not found. Warning: prerequisite Carp::Clan 6.04 not found. So, this gets very tiring. So what's the best Perl module that recursively installs all prerequisites of a package for you, jsut supplying the desired package name for installation? I suppose Cygwin is not the best platform for doing this. Thanks, Ken Wolcott -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Excel::Writer::XLSX - pie chart legend's color
Hi Rajesh,
On Fri, Sep 28, 2012 at 7:13 AM, Rajesh Saha wrote:
> Hi,
>
> I am trying to create one excel file, where chart will be included.
>
> Though, I am able to create this, but I could not customise the colors of
> the individual legends within th pie chart.
>
> Can anybody give an idea , please .
>
Please, see the module's author reply here:
http://www.perlmonks.org/?node_id=996141
> The highlights of my script is as follows :
>
> =====
> use Excel::Writer::XLSX ;
> ..
> .
>
> $chart->add_series(
> categories => '=Sheet1!$C1:$D1',
> values => '=Sheet1!$C1:$D$1',
> data_labels => { value => 1 },
> );
> $chart->set_style( 18 );
> $worksheet->insert_chart ('A4', $chart,0,0) ;
> =
>
> To be precise, there are two categories in '=Sheet1!$C1:$D1' viz. "Pass" &
> "Fail" & I want them to be green & red respectively in pie chart .
>
> Thanks and regards,
> Rajesh Saha
>
--
Tim
Re: Excel::Writer::XLSX - pie chart legend's color
Hi,
I am trying to create one excel file, where chart will be included.
Though, I am able to create this, but I could not customise the colors of
the individual legends within th pie chart.
Can anybody give an idea , please .
The highlights of my script is as follows :
=
use Excel::Writer::XLSX ;
..
.
$chart->add_series(
categories => '=Sheet1!$C1:$D1',
values => '=Sheet1!$C1:$D$1',
data_labels => { value => 1 },
);
$chart->set_style( 18 );
$worksheet->insert_chart ('A4', $chart,0,0) ;
=
To be precise, there are two categories in '=Sheet1!$C1:$D1' viz. "Pass" &
"Fail" & I want them to be green & red respectively in pie chart .
Thanks and regards,
Rajesh Saha
Excel::Writer::XLSX - pie chart legend's color
Hi,
I am trying to create one excel file, where chart will be included.
Though, I am able to create this, but I could not customise the colors of
the individual legends within th pie chart.
Can anybody give an idea , please .
The highlights of my script is as follows :
=
use Excel::Writer::XLSX ;
..
.
my $p = $chart->add_series(
categories => '=Sheet1!$C1:$D1',
values => '=Sheet1!$C5:$D$5',
data_labels => { value => 1,percentage => 1},
);
$chart->set_title(name => 'Test case Result') ;
$chart->set_style( 18 );
$worksheet->insert_chart ('A4', $chart,0,0) ;
=
To be precise, there are two categories in '=Sheet1!$C1:$D1' viz. "Pass" &
"Fail" & I want them to be green & red respectively in pie chart .
Thanks and regards,
Rajesh Saha
RE: suggestions for perl program to make excel graph out from top output
> Date: Fri, 20 Jul 2012 17:19:06 +0300 > From: shlo...@shlomifish.org > To: snem...@sonusnet.com > CC: beginners@perl.org > Subject: Re: suggestions for perl program to make excel graph out from top > output > > Hi Satya, > > On Fri, 20 Jul 2012 13:33:04 + > "Nemana, Satya" wrote: > > > Gurus, > > > > I want to write a perl program that captures top output in linux and makes > > nice graphs out of them(Microsoft excel) > > Are there ready to use modules for this which will help in writing the > > program? > > Any suggestions for inspirations? > > Search in cpan for top is resulting in a lot of results, most of which are > > irrelevant, are there smarter ways to search? > > Sure, use the GTop module which interfaces against libgtop: > > https://metacpan.org/release/GTop > > Its current metacpan.org documents are kinda messed up for some reason, so you > should view this page: > > https://metacpan.org/module/MJH/GTop-0.17/GTop.pod > > This may also prove useful: > > http://developer.gnome.org/libgtop/stable/ > > For creating Excel spreadsheets with graphs you can look at a spreadsheet > generating module, such as: > > * https://metacpan.org/release/Spreadsheet-WriteExcel > > * https://metacpan.org/release/Spreadsheet-WriteExcelXML > > Or maybe something OpenDocument related. > > Regards, > > Shlomi Fish > > > > > > Regards, > > Satya > > You can also use the Chart module from CPAN to create many different kinds of charts as a PNG file. This file can then be put into an Excel spreadsheet using the modules above. This was fairly easy to do. Mike Lively
Re: suggestions for perl program to make excel graph out from top output
Hi Satya, On Fri, 20 Jul 2012 13:33:04 + "Nemana, Satya" wrote: > Gurus, > > I want to write a perl program that captures top output in linux and makes > nice graphs out of them(Microsoft excel) > Are there ready to use modules for this which will help in writing the > program? > Any suggestions for inspirations? > Search in cpan for top is resulting in a lot of results, most of which are > irrelevant, are there smarter ways to search? Sure, use the GTop module which interfaces against libgtop: https://metacpan.org/release/GTop Its current metacpan.org documents are kinda messed up for some reason, so you should view this page: https://metacpan.org/module/MJH/GTop-0.17/GTop.pod This may also prove useful: http://developer.gnome.org/libgtop/stable/ For creating Excel spreadsheets with graphs you can look at a spreadsheet generating module, such as: * https://metacpan.org/release/Spreadsheet-WriteExcel * https://metacpan.org/release/Spreadsheet-WriteExcelXML Or maybe something OpenDocument related. Regards, Shlomi Fish > > Regards, > Satya > > > -- - Shlomi Fish http://www.shlomifish.org/ Rethinking CPAN - http://shlom.in/rethinking-cpan Writing a BitKeeper replacement is probably easier at this point than getting its license changed. — Matt Mackall (who ended up writing a BitKeeper replacement) Please reply to list if it's a mailing list post - http://shlom.in/reply . -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
suggestions for perl program to make excel graph out from top output
Gurus, I want to write a perl program that captures top output in linux and makes nice graphs out of them(Microsoft excel) Are there ready to use modules for this which will help in writing the program? Any suggestions for inspirations? Search in cpan for top is resulting in a lot of results, most of which are irrelevant, are there smarter ways to search? Regards, Satya -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
new topic same old subject: excel module
I am using this code to try to access certain columns in a spreadsheet to load
into an array for comparison
@{$sheet->{Cells}[$row]};
my @indexes = (2 .. (2+4-1));
my @dcells = @{$sheet->{Cells}[$row]}[@indexes];
open FILE, ">>feckyou.txt";
foreach (@dcells) {
print FILE ($_);
}
close FILE;
}
the print is just to see the output. The output looks like this:
Spreadsheet::ParseExcel::Cell=HASH(0x70a49a8)Spreadsheet::ParseExcel::Cell=HASH(0x70a4a08)
how do I access the actual data from the cell?
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: excel in perl
Though I'm pretty sure you can parse xlsx, I've never done it. I'd start by
finding the error method of the module and .. or die "something useful " .
$ws->error; also note there's a mailing for these modules - you might
search and ask there too.
On May 25, 2012 3:49 AM, "Ken cthrogglazx" wrote:
> im running this in activeperl in windows.
>
> my $parser = Spreadsheet::ParseExcel->new();
> my $workbook = $parser->parse('build.xlsx');
>
> if ( !defined $workbook ) {
>die $parser->error(), ".\n";
>}
>
>for my $worksheet ( $workbook->worksheets() ) {
>
>my ( $row_min, $row_max ) = $worksheet->row_range();
>my ( $col_min, $col_max ) = $worksheet->col_range();
>
>for my $row ( $row_min .. $row_max ) {
>for my $col ( $col_min .. $col_max ) {
>
>my $cell = $worksheet->get_cell( $row, $col );
>next unless $cell;
>
>print "Row, Col = ($row, $col)\n";
>print "Value = ", $cell->value(), "\n";
>print "Unformatted = ", $cell->unformatted(), "\n";
>print "\n";
>}
>
>}
>}
>
> Im getting the error "no excel data found in file" but its an excel
> spresadsheet .xlsx (so after 2007)
> and it is a spreadsheet with 3 sheets and almost 1000 rows of data. I
> need to pull all the data from two different columns for comparison. I
> was just starting out with trying to get the data from the
> spreadsheet. anyone know whats up?
>
> ken
>
>
> --
> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
> For additional commands, e-mail: beginners-h...@perl.org
> http://learn.perl.org/
>
>
>
excel in perl
im running this in activeperl in windows.
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('build.xlsx');
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
for my $worksheet ( $workbook->worksheets() ) {
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
print "Row, Col = ($row, $col)\n";
print "Value = ", $cell->value(), "\n";
print "Unformatted = ", $cell->unformatted(), "\n";
print "\n";
}
}
}
Im getting the error "no excel data found in file" but its an excel
spresadsheet .xlsx (so after 2007)
and it is a spreadsheet with 3 sheets and almost 1000 rows of data. I
need to pull all the data from two different columns for comparison. I
was just starting out with trying to get the data from the
spreadsheet. anyone know whats up?
ken
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Fwd: [Spreadsheet::WriteExcel] Spreadsheet::WriteExcel is dead. Long live Excel::Writer::XLSX.
since this module comes up somewhat frequently here, i figured i'd fwd this along. -- Forwarded message -- From: jmcnamara Date: Tue, Oct 18, 2011 at 08:38 Subject: [Spreadsheet::WriteExcel] Spreadsheet::WriteExcel is dead. Long live Excel::Writer::XLSX. To: "Spreadsheet::WriteExcel" Hi, See the following blog post about the future of Spreadsheet::WriteExcel and Excel::Writer::XLSX: http://blogs.perl.org/users/john_mcnamara/2011/10/spreadsheetwriteexcel-is-dead-long-live-excelwriterxlsx.html John. -- -- You received this message because you are subscribed to the Spreadsheet::WriteExcel Google Group. For posting and other options visit this group at: http://groups.google.com/group/spreadsheet-writeexcel?hl=en You can also post by sending an email to: http://groups.google.com/group/spreadsheet-writeexcel?hl=en To unsubscribe send an email to spreadsheet-writeexcel-unsubscr...@googlegroups.com -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: perl code to export data to excel
> "MM" == Mike McClain writes: MM> use strict; # this can be removed when all bugs are fixed that is not a good comment. you should keep strict on as it isn't about finding bugs but making sure all variables are properly declared and spelled correctly. uri -- Uri Guttman -- uri AT perlhunter DOT com --- http://www.perlhunter.com -- Perl Developer Recruiting and Placement Services - - Perl Code Review, Architecture, Development, Training, Support --- -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: perl code to export data to excel
On Thu, Aug 04, 2011 at 02:33:19PM +0530, VinoRex.E wrote:
> Hi
> i am a beginner in Perl i have a pdb file containing the X,Y,and Z
> coordinates of atoms example given below. the data extends upto 1000 atoms
> and its coordinates. I need a perl program to export these data into excel
> worksheet into distinct columns and ROws.
>
> ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63
> N
> ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02
> C
> ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43
> C
Here's one way:
#!/usr/bin/perl
# convert raw data file to *.csv which can be loaded by Excel
use strict; # this can be removed when all bugs are fixed
use warnings; # more control than -w
my ($str, @fields);
open my $fh, '<', 'data.pdb' or die "Unable to open DATA: $!";
while( my $line = <$fh> )
{ chomp $line;
@fields = split /\s+/, $line;
if( @fields == 1 )
{ $str .= "," . $line;
print $str, "\n";
}
else
{ $str = join ",", @fields;
}
}
close $fh;
Mike
--
Satisfied user of Linux since 1997.
O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: perl code to export data to excel
On Aug 4, 2011 8:02 AM, "Emeka" wrote: > > Vino, > > First learn how to use Excel with Perl. The below might be useful, please > check them out. > > http://support.microsoft.com/kb/214797 > > http://www.ibm.com/developerworks/linux/library/l-pexcel/ > That is (of course) a decent 'getting started'. I will note that as of this year, Spreadsheet::WriteExcel has been superseded (same author) : http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX-0.25/lib/Excel/Writer/XLSX.pm This module is backward comparable, so the document is relevant but I'd also suggest looking at the actual pod (esp the examples). This module has some of the best documentation I've seen. You'll also want Text::CSV_XS which has tons of examples all over the web. If you look at these and fail, post your code and let us know. These are pretty popular modules and you should get a quick response if you have trouble - I for one use thesm on almost a daily basis. > Emeka > > On Thu, Aug 4, 2011 at 10:03 AM, VinoRex.E wrote: > > > Hi > > i am a beginner in Perl i have a pdb file containing the X,Y,and Z > > coordinates of atoms example given below. the data extends upto 1000 atoms > > and its coordinates. I need a perl program to export these data into excel > > worksheet into distinct columns and ROws. > > > > ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63 > > N > > ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02 > > C > > ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43 > > C > > ATOM 4 O GLY A 2 2.833 -6.107 -1.311 1.00 33.27 > > O > > ATOM 5 N GLY A 3 2.302 -6.984 0.693 1.00 24.67 > > N > > ATOM 6 CA GLY A 3 2.176 -5.723 1.348 1.00 18.88 > > C > > ATOM 7 C GLY A 3 0.700 -5.426 1.526 1.00 16.58 > > C > > ATOM 8 O GLY A 3 -0.187 -6.142 1.010 1.00 12.47 > > O > > ATOM 9 N LEU A 4 0.494 -4.400 2.328 1.00 15.00 > > N > > ATOM 10 CA LEU A 4 -0.835 -3.926 2.630 1.00 12.93 > > C > > ATOM 11 C LEU A 4 -0.868 -2.457 2.294 1.00 11.77 > > C > > ATOM 12 O LEU A 4 0.007 -1.688 2.694 1.00 12.22 > > O > > ATOM 13 CB LEU A 4 -1.151 -4.122 4.110 1.00 13.01 > > C > > ATOM 14 CG LEU A 4 -2.536 -3.681 4.562 1.00 12.36 > > C > > ATOM 15 CD1 LEU A 4 -3.577 -4.568 3.912 1.00 13.37 > > C > > ATOM 16 CD2 LEU A 4 -2.644 -3.766 6.052 1.00 12.77 > > C > > ATOM 17 N GLN A 5 -1.864 -2.055 1.522 1.00 10.93 > > N > > ATOM 18 CA GLN A 5 -2.101 -0.667 1.209 1.00 11.87 > > C > > ATOM 19 C GLN A 5 -3.075 -0.147 2.264 1.00 12.25 > > C > > ATOM 20 O GLN A 5 -4.200 -0.648 2.394 1.00 11.71 > > O > > ATOM 21 CB GLN A 5 -2.697 -0.574 -0.199 1.00 12.69 > > C > > ATOM 22 CG GLN A 5 -2.977 0.839 -0.692 1.00 18.30 > > C > > ATOM 23 CD GLN A 5 -1.725 1.720 -0.785 1.00 23.25 > > C > > ATOM 24 OE1 GLN A 5 -1.726 2.887 -0.412 1.00 29.61 > > O > > ATOM 25 NE2 GLN A 5 -0.612 1.212 -1.270 1.00 25.58 > > N > > ATOM 26 N VAL A 6 -2.637 0.814 3.049 1.00 10.73 > > N > > ATOM 27 CA VAL A 6 -3.466 1.409 4.078 1.00 10.95 > > C > > ATOM 28 C VAL A 6 -3.829 2.773 3.519 1.00 11.49 > > C > > ATOM 29 O VAL A 6 -3.054 3.742 3.589 1.00 9.28 > > O > > ATOM 30 CB VAL A 6 -2.688 1.567 5.433 1.00 13.22 > > C > > ATOM 31 CG1 VAL A 6 -3.664 2.085 6.488 1.00 13.44 > > C > > ATOM 32 CG2 VAL A 6 -2.109 0.228 5.926 1.00 11.70 > > C > > ATOM 33 N LYS A 7 -5.047 2.846 2.984 1.00 10.74 > > N > > ATOM 34 CA LYS A 7 -5.515 4.029 2.280 1.00 11.66 > > C > > ATOM 35 C LYS A 7 -5.445 5.308 3.081 1.00 11.12 > > C > > ATOM 36 O LYS A 7 -5.045 6.377 2.623 1.00 13.12 > > O > > ATOM 37 CB LYS A 7 -6.965 3.858 1.829 1.00 15.50 > > C > > ATOM 38 CG LYS A 7 -7.335 4.947 0.832 1.00 19.57 > > C > > ATOM 39 CD LYS A 7 -8.820 5.172 0.874 1.00 30.36 > > C > > ATOM 40 CE LYS A 7 -9.206 6.372 0.017 1.00 34.59 > > C > > ATOM 41
Re: perl code to export data to excel
On Thu, Aug 4, 2011 at 6:31 PM, Emeka wrote: > Vino, > > First learn how to use Excel with Perl. The below might be useful, please > check them out. > > http://support.microsoft.com/kb/214797 > > http://www.ibm.com/developerworks/linux/library/l-pexcel/ > > Emeka > > You can also learn here : 1). http://homepage.eircom.net/~jmcnamara/perl/WriteExcel.html 2). http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel/Examples.pm 3). http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm If you know very basis of perl, then you can use the example code in the above link. Thanks & Rg Mohan L
Re: perl code to export data to excel
Vino, First learn how to use Excel with Perl. The below might be useful, please check them out. http://support.microsoft.com/kb/214797 http://www.ibm.com/developerworks/linux/library/l-pexcel/ Emeka On Thu, Aug 4, 2011 at 10:03 AM, VinoRex.E wrote: > Hi > i am a beginner in Perl i have a pdb file containing the X,Y,and Z > coordinates of atoms example given below. the data extends upto 1000 atoms > and its coordinates. I need a perl program to export these data into excel > worksheet into distinct columns and ROws. > > ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63 > N > ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02 > C > ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43 > C > ATOM 4 O GLY A 2 2.833 -6.107 -1.311 1.00 33.27 > O > ATOM 5 N GLY A 3 2.302 -6.984 0.693 1.00 24.67 > N > ATOM 6 CA GLY A 3 2.176 -5.723 1.348 1.00 18.88 > C > ATOM 7 C GLY A 3 0.700 -5.426 1.526 1.00 16.58 > C > ATOM 8 O GLY A 3 -0.187 -6.142 1.010 1.00 12.47 > O > ATOM 9 N LEU A 4 0.494 -4.400 2.328 1.00 15.00 > N > ATOM 10 CA LEU A 4 -0.835 -3.926 2.630 1.00 12.93 > C > ATOM 11 C LEU A 4 -0.868 -2.457 2.294 1.00 11.77 > C > ATOM 12 O LEU A 4 0.007 -1.688 2.694 1.00 12.22 > O > ATOM 13 CB LEU A 4 -1.151 -4.122 4.110 1.00 13.01 > C > ATOM 14 CG LEU A 4 -2.536 -3.681 4.562 1.00 12.36 > C > ATOM 15 CD1 LEU A 4 -3.577 -4.568 3.912 1.00 13.37 > C > ATOM 16 CD2 LEU A 4 -2.644 -3.766 6.052 1.00 12.77 > C > ATOM 17 N GLN A 5 -1.864 -2.055 1.522 1.00 10.93 > N > ATOM 18 CA GLN A 5 -2.101 -0.667 1.209 1.00 11.87 > C > ATOM 19 C GLN A 5 -3.075 -0.147 2.264 1.00 12.25 > C > ATOM 20 O GLN A 5 -4.200 -0.648 2.394 1.00 11.71 > O > ATOM 21 CB GLN A 5 -2.697 -0.574 -0.199 1.00 12.69 > C > ATOM 22 CG GLN A 5 -2.977 0.839 -0.692 1.00 18.30 > C > ATOM 23 CD GLN A 5 -1.725 1.720 -0.785 1.00 23.25 > C > ATOM 24 OE1 GLN A 5 -1.726 2.887 -0.412 1.00 29.61 > O > ATOM 25 NE2 GLN A 5 -0.612 1.212 -1.270 1.00 25.58 > N > ATOM 26 N VAL A 6 -2.637 0.814 3.049 1.00 10.73 > N > ATOM 27 CA VAL A 6 -3.466 1.409 4.078 1.00 10.95 > C > ATOM 28 C VAL A 6 -3.829 2.773 3.519 1.00 11.49 > C > ATOM 29 O VAL A 6 -3.054 3.742 3.589 1.00 9.28 > O > ATOM 30 CB VAL A 6 -2.688 1.567 5.433 1.00 13.22 > C > ATOM 31 CG1 VAL A 6 -3.664 2.085 6.488 1.00 13.44 > C > ATOM 32 CG2 VAL A 6 -2.109 0.228 5.926 1.00 11.70 > C > ATOM 33 N LYS A 7 -5.047 2.846 2.984 1.00 10.74 > N > ATOM 34 CA LYS A 7 -5.515 4.029 2.280 1.00 11.66 > C > ATOM 35 C LYS A 7 -5.445 5.308 3.081 1.00 11.12 > C > ATOM 36 O LYS A 7 -5.045 6.377 2.623 1.00 13.12 > O > ATOM 37 CB LYS A 7 -6.965 3.858 1.829 1.00 15.50 > C > ATOM 38 CG LYS A 7 -7.335 4.947 0.832 1.00 19.57 > C > ATOM 39 CD LYS A 7 -8.820 5.172 0.874 1.00 30.36 > C > ATOM 40 CE LYS A 7 -9.206 6.372 0.017 1.00 34.59 > C > ATOM 41 NZ LYS A 7 -10.630 6.633 0.157 1.00 39.48 > N > ATOM 42 N ASN A 8 -5.791 5.168 4.347 1.00 12.07 > N > ATOM 43 CA ASN A 8 -5.912 6.310 5.227 1.00 12.81 > C > ATOM 44 C ASN A 8 -4.602 6.946 5.628 1.00 12.09 > C > ATOM 45 O ASN A 8 -4.583 8.068 6.119 1.00 14.21 > O > ATOM 46 CB ASN A 8 -6.621 5.928 6.515 1.00 14.50 > C > ATOM 47 CG ASN A 8 -8.000 5.398 6.243 1.00 11.94 > C > ATOM 48 OD1 ASN A 8 -8.128 4.266 5.782 1.00 12.17 > O > ATOM 49 ND2 ASN A 8 -9.008 6.195 6.513 1.00 14.60 > N > ATOM 50 N PHE A 9 -3.511 6.229 5.464 1.00 10.86 > N > ATOM 51 CA PHE A 9 -2.264 6.756 5.951 1.00 12.74 > C > ATOM 52 C PHE A 9 -1.522 7.551 4.909 1.00 13.14 > C > ATOM 53 O PHE A 9 -0.921 7.001 4.018 1.00 15.38 > O > ATOM 54 CB PHE A 9 -1.417 5.568 6.457 1.00 14.18 > C > ATOM 55 CG PHE A 9 -1.761 5.002 7.835 1.00 13.51 > C > ATOM 56 CD1 PHE A 9 -2.871 5.453 8.570 1.00 12.16 > C > ATOM 57 CD2 PHE A 9 -0.921 4.025 8.380 1.00 13.88 > C > ATOM 58 CE1 PHE A 9 -3.107 4.934 9.847 1.00 13.66 > C > -- *Satajanus Nig. Ltd *
perl code to export data to excel
Hi i am a beginner in Perl i have a pdb file containing the X,Y,and Z coordinates of atoms example given below. the data extends upto 1000 atoms and its coordinates. I need a perl program to export these data into excel worksheet into distinct columns and ROws. ATOM 1 N GLY A 2 1.888 -8.251 -2.511 1.00 36.63 N ATOM 2 CA GLY A 2 2.571 -8.428 -1.248 1.00 33.02 C ATOM 3 C GLY A 2 2.586 -7.069 -0.589 1.00 30.43 C ATOM 4 O GLY A 2 2.833 -6.107 -1.311 1.00 33.27 O ATOM 5 N GLY A 3 2.302 -6.984 0.693 1.00 24.67 N ATOM 6 CA GLY A 3 2.176 -5.723 1.348 1.00 18.88 C ATOM 7 C GLY A 3 0.700 -5.426 1.526 1.00 16.58 C ATOM 8 O GLY A 3 -0.187 -6.142 1.010 1.00 12.47 O ATOM 9 N LEU A 4 0.494 -4.400 2.328 1.00 15.00 N ATOM 10 CA LEU A 4 -0.835 -3.926 2.630 1.00 12.93 C ATOM 11 C LEU A 4 -0.868 -2.457 2.294 1.00 11.77 C ATOM 12 O LEU A 4 0.007 -1.688 2.694 1.00 12.22 O ATOM 13 CB LEU A 4 -1.151 -4.122 4.110 1.00 13.01 C ATOM 14 CG LEU A 4 -2.536 -3.681 4.562 1.00 12.36 C ATOM 15 CD1 LEU A 4 -3.577 -4.568 3.912 1.00 13.37 C ATOM 16 CD2 LEU A 4 -2.644 -3.766 6.052 1.00 12.77 C ATOM 17 N GLN A 5 -1.864 -2.055 1.522 1.00 10.93 N ATOM 18 CA GLN A 5 -2.101 -0.667 1.209 1.00 11.87 C ATOM 19 C GLN A 5 -3.075 -0.147 2.264 1.00 12.25 C ATOM 20 O GLN A 5 -4.200 -0.648 2.394 1.00 11.71 O ATOM 21 CB GLN A 5 -2.697 -0.574 -0.199 1.00 12.69 C ATOM 22 CG GLN A 5 -2.977 0.839 -0.692 1.00 18.30 C ATOM 23 CD GLN A 5 -1.725 1.720 -0.785 1.00 23.25 C ATOM 24 OE1 GLN A 5 -1.726 2.887 -0.412 1.00 29.61 O ATOM 25 NE2 GLN A 5 -0.612 1.212 -1.270 1.00 25.58 N ATOM 26 N VAL A 6 -2.637 0.814 3.049 1.00 10.73 N ATOM 27 CA VAL A 6 -3.466 1.409 4.078 1.00 10.95 C ATOM 28 C VAL A 6 -3.829 2.773 3.519 1.00 11.49 C ATOM 29 O VAL A 6 -3.054 3.742 3.589 1.00 9.28 O ATOM 30 CB VAL A 6 -2.688 1.567 5.433 1.00 13.22 C ATOM 31 CG1 VAL A 6 -3.664 2.085 6.488 1.00 13.44 C ATOM 32 CG2 VAL A 6 -2.109 0.228 5.926 1.00 11.70 C ATOM 33 N LYS A 7 -5.047 2.846 2.984 1.00 10.74 N ATOM 34 CA LYS A 7 -5.515 4.029 2.280 1.00 11.66 C ATOM 35 C LYS A 7 -5.445 5.308 3.081 1.00 11.12 C ATOM 36 O LYS A 7 -5.045 6.377 2.623 1.00 13.12 O ATOM 37 CB LYS A 7 -6.965 3.858 1.829 1.00 15.50 C ATOM 38 CG LYS A 7 -7.335 4.947 0.832 1.00 19.57 C ATOM 39 CD LYS A 7 -8.820 5.172 0.874 1.00 30.36 C ATOM 40 CE LYS A 7 -9.206 6.372 0.017 1.00 34.59 C ATOM 41 NZ LYS A 7 -10.630 6.633 0.157 1.00 39.48 N ATOM 42 N ASN A 8 -5.791 5.168 4.347 1.00 12.07 N ATOM 43 CA ASN A 8 -5.912 6.310 5.227 1.00 12.81 C ATOM 44 C ASN A 8 -4.602 6.946 5.628 1.00 12.09 C ATOM 45 O ASN A 8 -4.583 8.068 6.119 1.00 14.21 O ATOM 46 CB ASN A 8 -6.621 5.928 6.515 1.00 14.50 C ATOM 47 CG ASN A 8 -8.000 5.398 6.243 1.00 11.94 C ATOM 48 OD1 ASN A 8 -8.128 4.266 5.782 1.00 12.17 O ATOM 49 ND2 ASN A 8 -9.008 6.195 6.513 1.00 14.60 N ATOM 50 N PHE A 9 -3.511 6.229 5.464 1.00 10.86 N ATOM 51 CA PHE A 9 -2.264 6.756 5.951 1.00 12.74 C ATOM 52 C PHE A 9 -1.522 7.551 4.909 1.00 13.14 C ATOM 53 O PHE A 9 -0.921 7.001 4.018 1.00 15.38 O ATOM 54 CB PHE A 9 -1.417 5.568 6.457 1.00 14.18 C ATOM 55 CG PHE A 9 -1.761 5.002 7.835 1.00 13.51 C ATOM 56 CD1 PHE A 9 -2.871 5.453 8.570 1.00 12.16 C ATOM 57 CD2 PHE A 9 -0.921 4.025 8.380 1.00 13.88 C ATOM 58 CE1 PHE A 9 -3.107 4.934 9.847 1.00 13.66 C
Re: Import Files to Excel File
On 7/26/11 Tue Jul 26, 2011 10:45 AM, "Overkill"
scribbled:
> Greetings,
>
> I have a directory full of invidual GEN000*.Result files. Each one of
> these files has some output which looks like this:
>
> PDI Number: GEN000240
> Finding Category: CAT I
> Reference: UNIX STIG: 2.5.3.1
> Description: A non-local/non-authoritative (Government) time-server is used.
> Status: Not Reviewed
>
> For example:
Is the above line in the files? What is the difference between the lines
above and the lines below?
> GEN000240: Please perform a manual review of the domain.net network time
> server/client
> using Checklist Section 3, GEN000240, to check the configuration.
>
> SRR Script Version: UNIX_51-28Jan2011
> UNIX SRR Checklist Page: 28
>
>
> I want to parse over 300 of these into a single excel or csv file. I
> wanted to put each line into a new column. How do I parse/convert
> something like this?
You can use regular expressions if the lines are as consistent as you have
shown:
if( $line =~ m{ \A ([^:]+) : (.*) }x ) {
my( $key, $val ) = ($1,$2);
# store key and value somewhere
}
>
> I was thinking to do a forloop thru the files and then parse the files
> via each field to the csv. Or is there a better way to do this? Should
> I get an IDE type of program to help with this or is it pretty easy to
> put together?
>
> This is what I was able to put together so far:
>
> #!/usr/bin/perl -w
>
> @files = ;
> foreach $file (@files) {
> print $file . "\n";
> }
That just prints the name of each file. You want to open the files and read
the lines in the files, parse each line, and do something with those lines
in which you are interested.
You should also:
1. Have 'use strict;' and 'use warnings;' at the top of your program (which
makes the '-w' unnecessary and redundant).
2. Declare all local variables with 'my' so that they are lexically scoped
and not global.
3. Consider using the Spreadsheet::WriteExcel module for creating
spreadsheets (it is really cool).
4. Consider using the File::Find module for finding all of your report
files.
5. Store the data extracted from the files in a hash or hash of hashes. Read
all of the files, parse each line, and store the data from each line of
interest in the hash. Then, when all files have been read, write the saved
data to a spreadsheet document.
Let us know if you have any problems with any of this.
Good luck!
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Import Files to Excel File
Greetings,
I have a directory full of invidual GEN000*.Result files. Each one of
these files has some output which looks like this:
PDI Number: GEN000240
Finding Category: CAT I
Reference: UNIX STIG: 2.5.3.1
Description: A non-local/non-authoritative (Government) time-server is used.
Status: Not Reviewed
For example:
GEN000240: Please perform a manual review of the domain.net network time
server/client
using Checklist Section 3, GEN000240, to check the configuration.
SRR Script Version: UNIX_51-28Jan2011
UNIX SRR Checklist Page: 28
I want to parse over 300 of these into a single excel or csv file. I
wanted to put each line into a new column. How do I parse/convert
something like this?
I was thinking to do a forloop thru the files and then parse the files
via each field to the csv. Or is there a better way to do this? Should
I get an IDE type of program to help with this or is it pretty easy to
put together?
This is what I was able to put together so far:
#!/usr/bin/perl -w
@files = ;
foreach $file (@files) {
print $file . "\n";
}
-Overkill
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Pdf to Excel
On Sat, Apr 16, 2011 at 10:33 AM, muthukumar swamy wrote: > I am trying to convert the tables in pdf to Excel. I am using CAM::Pdf > module for reading the text from Pdf. please suggest me anyone for > other way for converting PDF to Excel. > you're going to have to try real hard to make it accurate (especially if the pdf wasn't written very well). however, consider this: http://efreedom.com/Question/1-745138/Get-Text-Orientation-Text-String-PDF-Page-Using-CAM-PDF then, take the x / y and check your boundary (ie, see if it is running into anything else and see if anything else is on the same line), then store it somehow (hash, array, ref, whatever). then use Excel::Writer::XLSX (or the older Spreadsheet::WriteExcel) and create your spreadsheet file however you want it. this module is so well documented, i don't think i can say too much more about it. now, you didn't say how these pdfs were made, whether you were going to have to ocr them, and whether your information was in any other format. as you'll notice when you take that example and parse a file with it, what you have are objects (generally words or lines) and their position on a page. these positions aren't nice numbers, you might have y values of 3.43, 4.15, 5.67, etc. and the same goes for x values (your columns). i haven't read the pdf spec, but it doesn't seem that these numbers (object placements) have to be put into any order in the file (so, 5 might come before 3). as you might also notice from that example, your text can appear at any angle you want - how do you plan to deal with that? if you are a 'beginner' you aren't in kansas anymore :) i almost positive that you're not going to like doing this and that if you're not making money with it, you'll probably fail. now that i've given those words of wisdom, if you do succeed and are allowed to, i'd really enjoy seeing the end result (or the function or module that makes this work). -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Pdf to Excel
I am trying to convert the tables in pdf to Excel. I am using CAM::Pdf module for reading the text from Pdf. please suggest me anyone for other way for converting PDF to Excel. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Attachment in Excel format?
FYI, Spreadsheet::WriteExcel will be replaced with Spreadsheet::WriteExcel::XLSX soon so it might be best to try to use the later. On Feb 7, 2011 1:47 AM, "Octavian Rasnita" wrote: > From: "David Newman" >> >> >> On 2/6/11 7:57 PM, terry peng wrote: >>> >>> >>> >>> Sun, 06 Feb 2011 18:18:09 -0800 письмо от David Newman >>> : >>>> >>>> This is a multi-part message in MIME format. >>>> >>>> --_--=_1297044547631150 >>>> Content-Disposition: inline >>>> Content-Transfer-Encoding: 8bit >>>> Content-Type: text/plain >>>> >>>> Here's the GIF file you wanted >>>> --_--=_1297044547631150 >>>> Content-Disposition: attachment; filename="filename.csv" >>>> Content-Transfer-Encoding: 8bit >>>> Content-Type: text/csv; name="filename.csv" >>>> >>> >>> This header looks right. >>> Thus I think it's a problem of the email client, nothing of Perl code. >> >> Since we don't control the client, making assumptions about good or bad >> client behavior seems risky. >> >> I receive Excel attachments all the time, and they do not display >> inline. I've tried using MIME type 'application/vnd.ms-excel' with the >> csv attachment but then the attachment won't open. >> >> Is there some other means of creating an Excel file using perl? >> >> thanks > > > > Create a real Excel file using Spreadsheet::WriteExcel. > > Octavian > > > -- > To unsubscribe, e-mail: beginners-unsubscr...@perl.org > For additional commands, e-mail: beginners-h...@perl.org > http://learn.perl.org/ > >
Re: Attachment in Excel format?
On 2/6/11 10:45 PM, Octavian Rasnita wrote: >> Is there some other means of creating an Excel file using perl? >> >> thanks > > > > Create a real Excel file using Spreadsheet::WriteExcel. Thanks, this worked fine. I did make the rookie mistake of copying an example off the web without fully understanding the module's syntax. The code created a spreadsheet as expected, but the resulting file wouldn't attach to email using MIME::Lite or MIME::Lite::TT:HTML. Problem was I'd forgotten to close the workbook: $workbook->close(); I found several instances of people asking why they could see the spreadsheet on disk but couldn't email it; that's why. Of course close() is well documented in this module's perldoc page. Thanks again! dn -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Attachment in Excel format?
At 15:38 -0800 06/02/2011, David Newman wrote: Having the CSV output appear in the message body is a problem for the nontechnical audience that will receive this message. Why not just zip it? Archive::zip ? JD -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Attachment in Excel format?
From: "David Newman" On 2/6/11 7:57 PM, terry peng wrote: Sun, 06 Feb 2011 18:18:09 -0800 письмо от David Newman : This is a multi-part message in MIME format. --_--=_1297044547631150 Content-Disposition: inline Content-Transfer-Encoding: 8bit Content-Type: text/plain Here's the GIF file you wanted --_--=_1297044547631150 Content-Disposition: attachment; filename="filename.csv" Content-Transfer-Encoding: 8bit Content-Type: text/csv; name="filename.csv" This header looks right. Thus I think it's a problem of the email client, nothing of Perl code. Since we don't control the client, making assumptions about good or bad client behavior seems risky. I receive Excel attachments all the time, and they do not display inline. I've tried using MIME type 'application/vnd.ms-excel' with the csv attachment but then the attachment won't open. Is there some other means of creating an Excel file using perl? thanks Create a real Excel file using Spreadsheet::WriteExcel. Octavian -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Attachment in Excel format?
On 2/6/11 7:57 PM, terry peng wrote: > > > > Sun, 06 Feb 2011 18:18:09 -0800 письмо от David Newman > : >> >> This is a multi-part message in MIME format. >> >> --_--=_1297044547631150 >> Content-Disposition: inline >> Content-Transfer-Encoding: 8bit >> Content-Type: text/plain >> >> Here's the GIF file you wanted >> --_--=_1297044547631150 >> Content-Disposition: attachment; filename="filename.csv" >> Content-Transfer-Encoding: 8bit >> Content-Type: text/csv; name="filename.csv" >> > > This header looks right. > Thus I think it's a problem of the email client, nothing of Perl code. Since we don't control the client, making assumptions about good or bad client behavior seems risky. I receive Excel attachments all the time, and they do not display inline. I've tried using MIME type 'application/vnd.ms-excel' with the csv attachment but then the attachment won't open. Is there some other means of creating an Excel file using perl? thanks dn -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re[2]: Attachment in Excel format?
Sun, 06 Feb 2011 18:18:09 -0800 письмо от David Newman : > > This is a multi-part message in MIME format. > > --_--=_1297044547631150 > Content-Disposition: inline > Content-Transfer-Encoding: 8bit > Content-Type: text/plain > > Here's the GIF file you wanted > --_--=_1297044547631150 > Content-Disposition: attachment; filename="filename.csv" > Content-Transfer-Encoding: 8bit > Content-Type: text/csv; name="filename.csv" > This header looks right. Thus I think it's a problem of the email client, nothing of Perl code. Regards. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Attachment in Excel format?
On 2/6/11 4:19 PM, terry peng wrote: > Have you set the "Disposition" option to "attachment" rather than the default > "inline"? No, I hadn't. Thanks, I missed that option. However, even with the option set for the attachment, the contents of the CSV file still appear in email, at least for my Thunderbird client, because I had "Display Attachments Inline" checked. I've pasted the current headers below. If possible I would like to avoid making assumptions about the configuration of users' mail clients. How to force the display so that an attachment always appears as an attachment? thanks dn Mail headers with CSV file disposition set to 'attachment': This is a multi-part message in MIME format. --_--=_1297044547631150 Content-Disposition: inline Content-Transfer-Encoding: 8bit Content-Type: text/plain Here's the GIF file you wanted --_--=_1297044547631150 Content-Disposition: attachment; filename="filename.csv" Content-Transfer-Encoding: 8bit Content-Type: text/csv; name="filename.csv" > Regards. > > > Sun, 06 Feb 2011 15:38:44 -0800 письмо от David Newman > : > >> Greetings. I'm looking for a way to run a query against a SQL database, >> save the result into a file, and then attach the file to an email. >> >> The DBD::mysql and MIME::Lite modules handle the SQL query and email >> attachment respectively, but at least with the MIME 'text/csv' format >> there's a problem: In Thunderbird and Outlook, the attached file appears >> embedded in the message body. Here's an example in Thunderbird: >> >> - >> >> Here's the data you requested. >> >> >> somefile.csv >> >> id,date_created,date_updated,ip_address,firstname,lastname,title,organization,email,country >> 1,2010-05-13 07:48:18,,1.2.3.4,Homer,Simpson,Safety Dept.,Burns Nuclear >> Energy,chunkylove...@aol.com,USA >> 2,2010-05-13 09:22:05,,5.6.7.8,Hank,Hill,Propane Specialist,Strickland >> Propane,,USofFrickinA >> ... >> >> - >> >> Having the CSV output appear in the message body is a problem for the >> nontechnical audience that will receive this message. >> >> Is there a module that will put the query output in native Excel format, >> or a MIME format that will attach the CSV file so that its contents do >> not appear in the message body (i.e., it just shows up as an attached file)? >> >> thanks >> >> dn >> >> >> -- >> To unsubscribe, e-mail: beginners-unsubscr...@perl.org >> For additional commands, e-mail: beginners-h...@perl.org >> http://learn.perl.org/ > > -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Attachment in Excel format?
what's the message header you got in thunderbird? Have you set the "Disposition" option to "attachment" rather than the default "inline"? Regards. Sun, 06 Feb 2011 15:38:44 -0800 письмо от David Newman : > Greetings. I'm looking for a way to run a query against a SQL database, > save the result into a file, and then attach the file to an email. > > The DBD::mysql and MIME::Lite modules handle the SQL query and email > attachment respectively, but at least with the MIME 'text/csv' format > there's a problem: In Thunderbird and Outlook, the attached file appears > embedded in the message body. Here's an example in Thunderbird: > > - > > Here's the data you requested. > > > somefile.csv > > id,date_created,date_updated,ip_address,firstname,lastname,title,organization,email,country > 1,2010-05-13 07:48:18,,1.2.3.4,Homer,Simpson,Safety Dept.,Burns Nuclear > Energy,chunkylove...@aol.com,USA > 2,2010-05-13 09:22:05,,5.6.7.8,Hank,Hill,Propane Specialist,Strickland > Propane,,USofFrickinA > ... > > - > > Having the CSV output appear in the message body is a problem for the > nontechnical audience that will receive this message. > > Is there a module that will put the query output in native Excel format, > or a MIME format that will attach the CSV file so that its contents do > not appear in the message body (i.e., it just shows up as an attached file)? > > thanks > > dn > > > -- > To unsubscribe, e-mail: beginners-unsubscr...@perl.org > For additional commands, e-mail: beginners-h...@perl.org > http://learn.perl.org/ -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Attachment in Excel format?
Greetings. I'm looking for a way to run a query against a SQL database, save the result into a file, and then attach the file to an email. The DBD::mysql and MIME::Lite modules handle the SQL query and email attachment respectively, but at least with the MIME 'text/csv' format there's a problem: In Thunderbird and Outlook, the attached file appears embedded in the message body. Here's an example in Thunderbird: - Here's the data you requested. somefile.csv id,date_created,date_updated,ip_address,firstname,lastname,title,organization,email,country 1,2010-05-13 07:48:18,,1.2.3.4,Homer,Simpson,Safety Dept.,Burns Nuclear Energy,chunkylove...@aol.com,USA 2,2010-05-13 09:22:05,,5.6.7.8,Hank,Hill,Propane Specialist,Strickland Propane,,USofFrickinA ... - Having the CSV output appear in the message body is a problem for the nontechnical audience that will receive this message. Is there a module that will put the query output in native Excel format, or a MIME format that will attach the CSV file so that its contents do not appear in the message body (i.e., it just shows up as an attached file)? thanks dn -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: Getting LineFeed for Excel within q quoted field
>-Original Message- >From: Rob Dixon [mailto:rob.di...@gmx.com] >Sent: Thursday, January 06, 2011 21:49 >To: David Wagner >Cc: beginners@perl.org >Subject: Re: Getting LineFeed for Excel within q quoted field > >At 11:55 -0600 06/01/2011, David Wagner wrote: >> >> For something that should have been relatively easy, this has been a >> real pain in getting to work as I would have expected it to. >> ...So to get things in what I believe was a correct setup and using >> the binmode which was giving me what I thought Excel wanted, I >> replaced my \n with \r\n and did the \x0a for the soft returns. Now >> when it is opened in Excel, there are no question marks in either of >> the two columns that I am doing soft returns.. > >Hi David > >The strings "\n" and "\x0A" are identical - it is the translation done >by Perl's IO system that makes the difference. > > From this description it looks like you have things working. The >question marks in the fields were surely a bad sign? Please say if you >still need help. > >- Rob I appreciate the response. Yes, I have it going, but to me it seems like there should be an easier way of doing it. I ended up replacing each \n with \r\n and doing the \x0a. Then when I am ready to write out the csv, then I do binmode on the output file and it writes out correctly. So the bottom line becomes if using hex changes and it stands for some pre-defined value or variable within Perl, then one has to take some extra ordinary measures to make things work... Slow but sure... Thanks. Wags ;) David R. Wagner Senior Programmer Analyst FedEx Services 1.719.484.2097 Tel 1.719.484.2419 Fax 1.408.623.5963 Cell http://Fedex.com/us -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: Getting LineFeed for Excel within q quoted field
>-Original Message- >From: John Delacour [mailto:johndelac...@gmail.com] >Sent: Thursday, January 06, 2011 11:33 >To: beginners@perl.org >Subject: RE: Getting LineFeed for Excel within q quoted field > >At 11:55 -0600 06/01/2011, David Waner wrote: > >> >From: John Delacour [mailto:johndelac...@gmail.com] >> >#!/usr/bin/perl >>>use strict; >>>my $csv="temp.csv"; >>>open CSV, ">$csv"; >> >print CSV qq~1,2,"3\n4\n5"\n6,7,8~; > > >>For something that should have been relatively easy, this has been a >>real pain in getting to work as I would have expected it to. >>...So to get things in what I believe was a correct setup and using >>the binmode which was giving me what I thought Excel wanted, I >>replaced my \n with \r\n and did the \x0a for the soft returns. Now >>when it is opened in Excel, there are no question marks in either of >>the two columns that I am doing soft returns.. > >You are still making a mountain out of a molehill. The first point >is that there is no such character as a 'soft return'. The term is >used loosely in wordprocessor-speak etc. to refer to an apparent new >line that is due to word wrapping. If you think you see returns in >this paragraph then you are mistaken. Make the window wider and, >unless you're using a very inadequate mailer, you will discover it is >one line without returns that stretches to fill the width of the >window. > >You day you used "\x0A" for your imaginary 'soft returns' when all >you're doing is respecting Excel's convention of using "\n" for the >in-cell hard return in a Windows document. Why not just use "\n", >which is precisely the same thing? > Ok, I did exactly as stated and only used \n for all the processing within my script for writing out the csv that I am after. Excel does NOT do the right thing in a text wrapped column with \n. What I now get is two question marks and no wrap at all. When you say there are no 'soft returns', I accept that, but when I use \x0a and open the column to the widest elemnt I have in that column, they all line up as I expect. I may be making a mountain out of a molehill, but everything that I am seeing says, to get it correctly displayed in Excel, only the \x0a should be used in column which has double quotes around it. I even tried your simple script and with that, the question marks are part of the output. >I really don't know what 'binmode' has to do with anything either. >You're dealing in this case with plain text and you're working only >in Windows so all that's needed is to discover what rules Excel goes >by and respect them. > That is what I have been doing. Crlf is what I see in the text file for end of lines and lf is what I see for keeping my columns in a correctly setup display of data. Thanks. Wags ;) David R. Wagner Senior Programmer Analyst FedEx Services 1.719.484.2097 Tel 1.719.484.2419 Fax 1.408.623.5963 Cell http://Fedex.com/us >JD > > > >-- >To unsubscribe, e-mail: beginners-unsubscr...@perl.org >For additional commands, e-mail: beginners-h...@perl.org >http://learn.perl.org/ > -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: Getting LineFeed for Excel within q quoted field
>-Original Message-
>From: John Delacour [mailto:johndelac...@gmail.com]
>Sent: Wednesday, January 05, 2011 18:32
>To: Perl Beginners
>Subject: Re: Getting LineFeed for Excel within q quoted field
>
>At 17:56 -0600 04/01/2011, Wagner, David wrote:
>
>
>>I am generating an CSV and want a couple of fields to have soft
>>returns in them. I went into Excel and added a couple of soft
>>returns to a couple of different fields and then saved the modified
>>file back to a CSV.
>
>If you run this script you will have what you seem to want. So far
>as I know any line ending whether CR LF or CRLF is a new row signal
>and if you want new lines within a cell then you need to *enclose the
>cell contents in double quotes.*
>
>
>#!/usr/bin/perl
>use strict;
>my $csv="temp.csv";
>open CSV, ">$csv";
>print CSV qq~1,2,"3\n4\n5"\n6,7,8~;
[Wags]
[Wags] For something that should have been relatively easy, this has been a
real pain in getting to work as I would have expected it to.
I am using printf to do my printing and in this case, was using sprintf
to place into a hash and when done, then I was doing the write to the file. I
was placing within double quotes, but still ending up with the question marks
within Excel.
So I tried the binmode approach, but ended up a mixed bag there. Now
everything was LF and the two columns that had the data with soft returns, the
first one showed the question marks while the second column everything looked
good.
So to get things in what I believe was a correct setup and using the
binmode which was giving me what I thought Excel wanted, I replaced my \n with
\r\n and did the \x0a for the soft returns. Now when it is opened in Excel,
there are no question marks in either of the two columns that I am doing soft
returns..
To me, this was way too much work for something that should have been
relatively easy to accomplish. If I was doing anything OTHER than the \x0a, it
was done. But since Perl is trying to help me, it took me down a road of way
too much work to get this accomplished.
I appreciate all the inputs and suggestions. Without them, I would not
have completed what I needed to do.
Thanks so much...
Wags ;)
David R. Wagner
Senior Programmer Analyst
FedEx Services
1.719.484.2097 Tel
1.719.484.2419 Fax
1.408.623.5963 Cell
http://Fedex.com/us
>close CSV;
>`start excel.exe "$csv"`;
>
>
>On the Mac, and I guess on UNIX, then you need to use "\r" instead of
>"\n" to have the multiple lines show in the cell. As a new row
>signal the "\n" "\r" or "\r\n" will work fine on both platforms
>regardless of the different new line conventions.
>
>#!/usr/bin/perl
>use strict;
>my $csv="temp.csv";
>open CSV, ">$csv";
>print CSV qq~1,2,"3\r4\r5"\n6,7,8~;
>close CSV;
>`open -a 'Microsoft Excel' '$csv'`;
>
>JD
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Getting LineFeed for Excel within q quoted field
David Wagner wrote: From: Rob Dixon [mailto:rob.di...@gmx.com] At 11:55 -0600 06/01/2011, David Wagner wrote: For something that should have been relatively easy, this has been a real pain in getting to work as I would have expected it to. ...So to get things in what I believe was a correct setup and using the binmode which was giving me what I thought Excel wanted, I replaced my \n with \r\n and did the \x0a for the soft returns. Now when it is opened in Excel, there are no question marks in either of the two columns that I am doing soft returns.. The strings "\n" and "\x0A" are identical - it is the translation done by Perl's IO system that makes the difference. From this description it looks like you have things working. The question marks in the fields were surely a bad sign? Please say if you still need help. I appreciate the response. Yes, I have it going, but to me it seems like there should be an easier way of doing it. I ended up replacing each \n with \r\n and doing the \x0a. Then when I am ready to write out the csv, then I do binmode on the output file and it writes out correctly. So the bottom line becomes if using hex changes and it stands for some pre-defined value or variable within Perl, then one has to take some extra ordinary measures to make things work... Slow but sure... The problem is that you want the LFs at the end of your records converted to CR LF, but those in the middle of the records left as they are. How is a piece of software to know the difference?! I would use some unusual character - a tilde say, or a caret as you mentioned in one of your posts - to represent a soft newline, leaving the linefeed - "\n" or "\x0A", which are identical - to represent the end of record. Then you can just s/\n/\r\n/g; s/^/\n/g; before printing the data to a binmode handle. You could also consider playing with the $\. If your records are chomped in your program (without a terminating \n) then setting $\ = "\r\n"; will give you the right result as long as you print a single whole record at a time. A final possibility - though it is probably overkill unless there is a lot of code doing this - is to write a custom IO layer. Take a look at perldoc PerlIO::via HTH, Rob -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Getting LineFeed for Excel within q quoted field
At 11:55 -0600 06/01/2011, David Wagner wrote: For something that should have been relatively easy, this has been a real pain in getting to work as I would have expected it to. ...So to get things in what I believe was a correct setup and using the binmode which was giving me what I thought Excel wanted, I replaced my \n with \r\n and did the \x0a for the soft returns. Now when it is opened in Excel, there are no question marks in either of the two columns that I am doing soft returns.. Hi David The strings "\n" and "\x0A" are identical - it is the translation done by Perl's IO system that makes the difference. From this description it looks like you have things working. The question marks in the fields were surely a bad sign? Please say if you still need help. - Rob -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: Getting LineFeed for Excel within q quoted field
At 11:55 -0600 06/01/2011, David Waner wrote: >From: John Delacour [mailto:johndelac...@gmail.com] >#!/usr/bin/perl use strict; my $csv="temp.csv"; open CSV, ">$csv"; >print CSV qq~1,2,"3\n4\n5"\n6,7,8~; For something that should have been relatively easy, this has been a real pain in getting to work as I would have expected it to. ...So to get things in what I believe was a correct setup and using the binmode which was giving me what I thought Excel wanted, I replaced my \n with \r\n and did the \x0a for the soft returns. Now when it is opened in Excel, there are no question marks in either of the two columns that I am doing soft returns.. You are still making a mountain out of a molehill. The first point is that there is no such character as a 'soft return'. The term is used loosely in wordprocessor-speak etc. to refer to an apparent new line that is due to word wrapping. If you think you see returns in this paragraph then you are mistaken. Make the window wider and, unless you're using a very inadequate mailer, you will discover it is one line without returns that stretches to fill the width of the window. You day you used "\x0A" for your imaginary 'soft returns' when all you're doing is respecting Excel's convention of using "\n" for the in-cell hard return in a Windows document. Why not just use "\n", which is precisely the same thing? I really don't know what 'binmode' has to do with anything either. You're dealing in this case with plain text and you're working only in Windows so all that's needed is to discover what rules Excel goes by and respect them. JD -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Getting LineFeed for Excel within q quoted field
> So how do I tel>So how do I tell Perl to leave alone on 0a. Do > I have to play with $? Or ??? >Making no sense to me at this point...l Perl to leave alone on 0a. Do > I have to play with $? Or ??? > Making no sense to me at this point... use strict; use warnings; open my $fh, '>', 'file.out' or die "Could not create file: $!\n"; binmode($fh); print $fh "New\nLine"; This would insert 0a where you have used \n irrespective of Operating System. And if you want to generate 0d0a for \n specially on Unix then modify the fourth line of the above script to binmode($fh,':crlf') Cheers, Parag On Wed, Jan 5, 2011 at 3:48 PM, Wagner, David --- Senior Programmer Analyst --- CFS wrote: > Original Message- >>From: Parag Kalra [mailto:paragka...@gmail.com] >>Sent: Wednesday, January 05, 2011 16:42 >>To: Wagner, David --- Senior Programmer Analyst --- CFS >>Cc: Perl Beginners >>Subject: Re: Getting LineFeed for Excel within q quoted field >> >>Ok. >> >>May be I need to understand your scenario in better way. Kindly >>correct me if I am wrong. So this is what my understanding is: >> >>1. Your aim is to generate a CSV file >>2. You are parsing a flat text file and substituting ^ with new line >>character (0a) >>3. But when you are viewing the file in Excel the new character is not >>added > [Wags] Not quite. I look at the file using Hex application. What I see in the > file is > 0d0a where I would expect to ONLY see 0a. As another test, I changed the 0a > to 0f and > Ran my script. When I look at the file, it ONLY has the 0f. So what I am > thinking is > That Perl sees the 0a and says he is not doing it right, so we will replace > with a > 0d0a since on Windows. Simplistic, but that is what I am seeing. > > So how do I tell Perl to leave alone on 0a. Do I have to play with $? > Or ??? > Making no sense to me at this point... > > So what are the thoughts now??? > > If you have any questions and/or problems, please let me know. > Thanks. > > Wags ;) > David R. Wagner > Senior Programmer Analyst > FedEx Services > 1.719.484.2097 Tel > 1.719.484.2419 Fax > 1.408.623.5963 Cell > http://Fedex.com/us > >>4. And reason as per what you think is that Excel is expecting 0a but >>what is getting inserted is 0d0a >>5. Also I assume you are doing all these experiments on Windows box. >>The reason I am asking this is because both Linux and Windows treat >>new line character in different way. Have a look - >>http://www.perlmonks.org/index.pl?node_id=68687 >>And from the same reference it appears to me that if you want to add >>only 0a, may be you need to handle the csv file in ascii format >> >>Cheers, >>Parag >> >> >> >> >>On Wed, Jan 5, 2011 at 3:28 PM, Wagner, David --- Senior Programmer >>Analyst --- CFS wrote: >>> >>>>-Original Message- >>>>From: Parag Kalra [mailto:paragka...@gmail.com] >>>>Sent: Wednesday, January 05, 2011 12:13 >>>>To: Wagner, David --- Senior Programmer Analyst --- CFS >>>>Cc: Perl Beginners >>>>Subject: Re: Getting LineFeed for Excel within q quoted field >>>> >>>>It may have to do something how you are opening the file handler of CSV >>>>file. >>>> >>>>The data you seen in the csv file may depend on which encoding you >>>>have used while creating the file. >>>> >>>>Couple of questions: >>>> >>>>1. I believe currently you are view the file on Windoze, when you view >>>>the file on Unix, do you still see the graphics. >>> >>> [Wags] I am viewing with Scite and also a Hex editor. >>> But even when I am doing the change using s/\^/\x0a/g I am seeing in the >>file itself as 0d0a and not just the 0a. Obviously I am missing something >>very basic at this point. Excel is expecting a 0a indicating a soft return, >>and I have verified I am using the right code, but comes out incorrectly. >>> >>> What am I missing?? >>> >>> Thanks much for any insight.. >>> >>> Wags ;) >>> David R. Wagner >>> Senior Programmer Analyst >>> FedEx Services >>> 1.719.484.2097 Tel >>> 1.719.484.2419 Fax >>> 1.408.623.5963 Cell >>> http://Fedex.com/us >>> >>> >>> >>>>2. Is graphics visible on most of the editors or have you used only 1 >>>>editor? >>&g
RE: Getting LineFeed for Excel within q quoted field
>-Original Message-
>From: Parag Kalra [mailto:paragka...@gmail.com]
>Sent: Wednesday, January 05, 2011 12:13
>To: Wagner, David --- Senior Programmer Analyst --- CFS
>Cc: Perl Beginners
>Subject: Re: Getting LineFeed for Excel within q quoted field
>
>It may have to do something how you are opening the file handler of CSV
>file.
>
>The data you seen in the csv file may depend on which encoding you
>have used while creating the file.
>
>Couple of questions:
>
>1. I believe currently you are view the file on Windoze, when you view
>the file on Unix, do you still see the graphics.
[Wags] I am viewing with Scite and also a Hex editor.
But even when I am doing the change using s/\^/\x0a/g I am seeing in the file
itself as 0d0a and not just the 0a. Obviously I am missing something very basic
at this point. Excel is expecting a 0a indicating a soft return, and I have
verified I am using the right code, but comes out incorrectly.
What am I missing??
Thanks much for any insight..
Wags ;)
David R. Wagner
Senior Programmer Analyst
FedEx Services
1.719.484.2097 Tel
1.719.484.2419 Fax
1.408.623.5963 Cell
http://Fedex.com/us
>2. Is graphics visible on most of the editors or have you used only 1
>editor?
>
>Cheers,
>Parag
>
>
>
>
>On Tue, Jan 4, 2011 at 3:56 PM, Wagner, David --- Senior Programmer
>Analyst --- CFS wrote:
>> I am generating an CSV and want a couple of fields to have soft
>> returns in them. I went into Excel and added a couple of soft returns to
>> a couple of different fields and then saved the modified file back to a
>> CSV.
>> I opened in a editor and reviewed what was there. What I saw
>> was:
>> "xxx(lf) # shows as LF verses the std end of line for
>> windows of CR/LF
>> Yyyy(lf)
>> "
>>
>> I left the editor and double clicked again and brought into
>> Excel. The data had the soft returns.
>>
>> So I added the following to my processing:
>>
>> for ( @MyWorka ) {
>> if ( /\^/ ) {
>> s/\^/\x0a/g; # I have tried the \r and even
>> \n and when opened in
>> # Excel always has the
>> graphic explained below....
>> }
>> }
>>
>> Whereever there is a ^ replace with a hex A which to me is a
>> LineFeed ( incorrectly as I have read ). I run and create my csv. I
>> double click the file and it opens in Excel. It appears to be working,
>> but where each linefeed is you get a little graphic with a question mark
>> inside a circle. In a way it is doing the soft returns, but obviously
>> not correctly.
>>
>> Any thoughts on what I am doing incorrectly??
>>
>> Thanks.
>>
>> Wags ;)
>> David R. Wagner
>> Senior Programmer Analyst
>> FedEx Services
>> 1.719.484.2097 Tel
>> 1.719.484.2419 Fax
>> 1.408.623.5963 Cell
>> http://Fedex.com/us
>>
>>
>>
>> --
>> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
>> For additional commands, e-mail: beginners-h...@perl.org
>> http://learn.perl.org/
>>
>>
>>
RE: Getting LineFeed for Excel within q quoted field
Original Message-
>From: Parag Kalra [mailto:paragka...@gmail.com]
>Sent: Wednesday, January 05, 2011 16:42
>To: Wagner, David --- Senior Programmer Analyst --- CFS
>Cc: Perl Beginners
>Subject: Re: Getting LineFeed for Excel within q quoted field
>
>Ok.
>
>May be I need to understand your scenario in better way. Kindly
>correct me if I am wrong. So this is what my understanding is:
>
>1. Your aim is to generate a CSV file
>2. You are parsing a flat text file and substituting ^ with new line
>character (0a)
>3. But when you are viewing the file in Excel the new character is not
>added
[Wags] Not quite. I look at the file using Hex application. What I see in the
file is
0d0a where I would expect to ONLY see 0a. As another test, I changed the 0a to
0f and
Ran my script. When I look at the file, it ONLY has the 0f. So what I am
thinking is
That Perl sees the 0a and says he is not doing it right, so we will replace
with a
0d0a since on Windows. Simplistic, but that is what I am seeing.
So how do I tell Perl to leave alone on 0a. Do I have to play with $?
Or ???
Making no sense to me at this point...
So what are the thoughts now???
If you have any questions and/or problems, please let me know.
Thanks.
Wags ;)
David R. Wagner
Senior Programmer Analyst
FedEx Services
1.719.484.2097 Tel
1.719.484.2419 Fax
1.408.623.5963 Cell
http://Fedex.com/us
>4. And reason as per what you think is that Excel is expecting 0a but
>what is getting inserted is 0d0a
>5. Also I assume you are doing all these experiments on Windows box.
>The reason I am asking this is because both Linux and Windows treat
>new line character in different way. Have a look -
>http://www.perlmonks.org/index.pl?node_id=68687
>And from the same reference it appears to me that if you want to add
>only 0a, may be you need to handle the csv file in ascii format
>
>Cheers,
>Parag
>
>
>
>
>On Wed, Jan 5, 2011 at 3:28 PM, Wagner, David --- Senior Programmer
>Analyst --- CFS wrote:
>>
>>>-Original Message-
>>>From: Parag Kalra [mailto:paragka...@gmail.com]
>>>Sent: Wednesday, January 05, 2011 12:13
>>>To: Wagner, David --- Senior Programmer Analyst --- CFS
>>>Cc: Perl Beginners
>>>Subject: Re: Getting LineFeed for Excel within q quoted field
>>>
>>>It may have to do something how you are opening the file handler of CSV
>>>file.
>>>
>>>The data you seen in the csv file may depend on which encoding you
>>>have used while creating the file.
>>>
>>>Couple of questions:
>>>
>>>1. I believe currently you are view the file on Windoze, when you view
>>>the file on Unix, do you still see the graphics.
>>
>> [Wags] I am viewing with Scite and also a Hex editor.
>> But even when I am doing the change using s/\^/\x0a/g I am seeing in the
>file itself as 0d0a and not just the 0a. Obviously I am missing something
>very basic at this point. Excel is expecting a 0a indicating a soft return,
>and I have verified I am using the right code, but comes out incorrectly.
>>
>> What am I missing??
>>
>> Thanks much for any insight..
>>
>> Wags ;)
>> David R. Wagner
>> Senior Programmer Analyst
>> FedEx Services
>> 1.719.484.2097 Tel
>> 1.719.484.2419 Fax
>> 1.408.623.5963 Cell
>> http://Fedex.com/us
>>
>>
>>
>>>2. Is graphics visible on most of the editors or have you used only 1
>>>editor?
>>>
>>>Cheers,
>>>Parag
>>>
>>>
>>>
>>>
>>>On Tue, Jan 4, 2011 at 3:56 PM, Wagner, David --- Senior Programmer
>>>Analyst --- CFS wrote:
>>>> I am generating an CSV and want a couple of fields to have soft
>>>> returns in them. I went into Excel and added a couple of soft returns
>to
>>>> a couple of different fields and then saved the modified file back to a
>>>> CSV.
>>>> I opened in a editor and reviewed what was there. What I saw
>>>> was:
>>>> "xxx(lf) # shows as LF verses the std end of line for
>>>> windows of CR/LF
>>>> Yyyy(lf)
>>>> "
>>>>
>>>> I left the editor and double clicked again and brought into
>>>> Excel. The data had the soft returns.
>>>>
>>>> So I added the following to my processing:
>>>>
>>>> for ( @MyWorka ) {
>>>> if ( /\^/ ) {
>>>> s/\^/\x0a/g; # I have tried the \r and e
Re: Getting LineFeed for Excel within q quoted field
At 17:56 -0600 04/01/2011, Wagner, David wrote: I am generating an CSV and want a couple of fields to have soft returns in them. I went into Excel and added a couple of soft returns to a couple of different fields and then saved the modified file back to a CSV. If you run this script you will have what you seem to want. So far as I know any line ending whether CR LF or CRLF is a new row signal and if you want new lines within a cell then you need to *enclose the cell contents in double quotes.* #!/usr/bin/perl use strict; my $csv="temp.csv"; open CSV, ">$csv"; print CSV qq~1,2,"3\n4\n5"\n6,7,8~; close CSV; `start excel.exe "$csv"`; On the Mac, and I guess on UNIX, then you need to use "\r" instead of "\n" to have the multiple lines show in the cell. As a new row signal the "\n" "\r" or "\r\n" will work fine on both platforms regardless of the different new line conventions. #!/usr/bin/perl use strict; my $csv="temp.csv"; open CSV, ">$csv"; print CSV qq~1,2,"3\r4\r5"\n6,7,8~; close CSV; `open -a 'Microsoft Excel' '$csv'`; JD -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Getting LineFeed for Excel within q quoted field
Ok.
May be I need to understand your scenario in better way. Kindly
correct me if I am wrong. So this is what my understanding is:
1. Your aim is to generate a CSV file
2. You are parsing a flat text file and substituting ^ with new line
character (0a)
3. But when you are viewing the file in Excel the new character is not added
4. And reason as per what you think is that Excel is expecting 0a but
what is getting inserted is 0d0a
5. Also I assume you are doing all these experiments on Windows box.
The reason I am asking this is because both Linux and Windows treat
new line character in different way. Have a look -
http://www.perlmonks.org/index.pl?node_id=68687
And from the same reference it appears to me that if you want to add
only 0a, may be you need to handle the csv file in ascii format
Cheers,
Parag
On Wed, Jan 5, 2011 at 3:28 PM, Wagner, David --- Senior Programmer
Analyst --- CFS wrote:
>
>>-Original Message-
>>From: Parag Kalra [mailto:paragka...@gmail.com]
>>Sent: Wednesday, January 05, 2011 12:13
>>To: Wagner, David --- Senior Programmer Analyst --- CFS
>>Cc: Perl Beginners
>>Subject: Re: Getting LineFeed for Excel within q quoted field
>>
>>It may have to do something how you are opening the file handler of CSV
>>file.
>>
>>The data you seen in the csv file may depend on which encoding you
>>have used while creating the file.
>>
>>Couple of questions:
>>
>>1. I believe currently you are view the file on Windoze, when you view
>>the file on Unix, do you still see the graphics.
>
> [Wags] I am viewing with Scite and also a Hex editor.
> But even when I am doing the change using s/\^/\x0a/g I am seeing in the file
> itself as 0d0a and not just the 0a. Obviously I am missing something very
> basic at this point. Excel is expecting a 0a indicating a soft return, and I
> have verified I am using the right code, but comes out incorrectly.
>
> What am I missing??
>
> Thanks much for any insight..
>
> Wags ;)
> David R. Wagner
> Senior Programmer Analyst
> FedEx Services
> 1.719.484.2097 Tel
> 1.719.484.2419 Fax
> 1.408.623.5963 Cell
> http://Fedex.com/us
>
>
>
>>2. Is graphics visible on most of the editors or have you used only 1
>>editor?
>>
>>Cheers,
>>Parag
>>
>>
>>
>>
>>On Tue, Jan 4, 2011 at 3:56 PM, Wagner, David --- Senior Programmer
>>Analyst --- CFS wrote:
>>> I am generating an CSV and want a couple of fields to have soft
>>> returns in them. I went into Excel and added a couple of soft returns to
>>> a couple of different fields and then saved the modified file back to a
>>> CSV.
>>> I opened in a editor and reviewed what was there. What I saw
>>> was:
>>> "xxx(lf) # shows as LF verses the std end of line for
>>> windows of CR/LF
>>> Yyyy(lf)
>>> "
>>>
>>> I left the editor and double clicked again and brought into
>>> Excel. The data had the soft returns.
>>>
>>> So I added the following to my processing:
>>>
>>> for ( @MyWorka ) {
>>> if ( /\^/ ) {
>>> s/\^/\x0a/g; # I have tried the \r and even
>>> \n and when opened in
>>> # Excel always has the
>>> graphic explained below
>>> }
>>> }
>>>
>>> Whereever there is a ^ replace with a hex A which to me is a
>>> LineFeed ( incorrectly as I have read ). I run and create my csv. I
>>> double click the file and it opens in Excel. It appears to be working,
>>> but where each linefeed is you get a little graphic with a question mark
>>> inside a circle. In a way it is doing the soft returns, but obviously
>>> not correctly.
>>>
>>> Any thoughts on what I am doing incorrectly??
>>>
>>> Thanks.
>>>
>>> Wags ;)
>>> David R. Wagner
>>> Senior Programmer Analyst
>>> FedEx Services
>>> 1.719.484.2097 Tel
>>> 1.719.484.2419 Fax
>>> 1.408.623.5963 Cell
>>> http://Fedex.com/us
>>>
>>>
>>>
>>> --
>>> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
>>> For additional commands, e-mail: beginners-h...@perl.org
>>> http://learn.perl.org/
>>>
>>>
>>>
>
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Getting LineFeed for Excel within q quoted field
It may have to do something how you are opening the file handler of CSV file.
The data you seen in the csv file may depend on which encoding you
have used while creating the file.
Couple of questions:
1. I believe currently you are view the file on Windoze, when you view
the file on Unix, do you still see the graphics.
2. Is graphics visible on most of the editors or have you used only 1 editor?
Cheers,
Parag
On Tue, Jan 4, 2011 at 3:56 PM, Wagner, David --- Senior Programmer
Analyst --- CFS wrote:
> I am generating an CSV and want a couple of fields to have soft
> returns in them. I went into Excel and added a couple of soft returns to
> a couple of different fields and then saved the modified file back to a
> CSV.
> I opened in a editor and reviewed what was there. What I saw
> was:
> "xxx(lf) # shows as LF verses the std end of line for
> windows of CR/LF
> Yyyy(lf)
> "
>
> I left the editor and double clicked again and brought into
> Excel. The data had the soft returns.
>
> So I added the following to my processing:
>
> for ( @MyWorka ) {
> if ( /\^/ ) {
> s/\^/\x0a/g; # I have tried the \r and even
> \n and when opened in
> # Excel always has the
> graphic explained below
> }
> }
>
> Whereever there is a ^ replace with a hex A which to me is a
> LineFeed ( incorrectly as I have read ). I run and create my csv. I
> double click the file and it opens in Excel. It appears to be working,
> but where each linefeed is you get a little graphic with a question mark
> inside a circle. In a way it is doing the soft returns, but obviously
> not correctly.
>
> Any thoughts on what I am doing incorrectly??
>
> Thanks.
>
> Wags ;)
> David R. Wagner
> Senior Programmer Analyst
> FedEx Services
> 1.719.484.2097 Tel
> 1.719.484.2419 Fax
> 1.408.623.5963 Cell
> http://Fedex.com/us
>
>
>
> --
> To unsubscribe, e-mail: beginners-unsubscr...@perl.org
> For additional commands, e-mail: beginners-h...@perl.org
> http://learn.perl.org/
>
>
>
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Getting LineFeed for Excel within q quoted field
I am generating an CSV and want a couple of fields to have soft
returns in them. I went into Excel and added a couple of soft returns to
a couple of different fields and then saved the modified file back to a
CSV.
I opened in a editor and reviewed what was there. What I saw
was:
"xxx(lf) # shows as LF verses the std end of line for
windows of CR/LF
Yyyy(lf)
"
I left the editor and double clicked again and brought into
Excel. The data had the soft returns.
So I added the following to my processing:
for ( @MyWorka ) {
if ( /\^/ ) {
s/\^/\x0a/g;# I have tried the \r and even
\n and when opened in
# Excel always has the
graphic explained below
}
}
Whereever there is a ^ replace with a hex A which to me is a
LineFeed ( incorrectly as I have read ). I run and create my csv. I
double click the file and it opens in Excel. It appears to be working,
but where each linefeed is you get a little graphic with a question mark
inside a circle. In a way it is doing the soft returns, but obviously
not correctly.
Any thoughts on what I am doing incorrectly??
Thanks.
Wags ;)
David R. Wagner
Senior Programmer Analyst
FedEx Services
1.719.484.2097 Tel
1.719.484.2419 Fax
1.408.623.5963 Cell
http://Fedex.com/us
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Modify excel worrkbook in perl
At 7:19 AM -0700 4/26/10, Jim Gibson wrote:
At 1:37 AM -0700 4/26/10, luke devon wrote:
Hi #!/usr/bin/perl -w use strict; use
Spreadsheet::ParseExcel; my $parser =
Spreadsheet::ParseExcel->new(); my $workbook =
$parser->parse('Book1.xls'); Like this , as per examples, we could
open/read the existing excel files.but how can we execute other
functions which are exclude in the Spreadsheet::ParseExcel. Like
delete sheet , delete row, adding data ...etc. For that task ,
do we need to call Spreadsheet::WriteExcel package? Can somebody
help me on this please?
Unfortunately, you cannot do that. ParseExcel reads Excel
spreadsheets (but only the older format). WriteExcel writes files
that can be read and edited with Excel. However, the two modules are
not compatible, in that you cannot apply both to the same file.
What you will have to do if you want to add data to an existing
spreadsheet is open the spreadsheet and read the contents using
ParseExcel, create a new spreadsheet with WriteExcel, and copy all of
the data from the existing spreadsheet document to the new one. That
can be tedious.
Another approach, if you are working on Windows, is to control the
Excel application from a Perl program using one of the OLE modules
from CPAN, e.g. Win32::OLE.
--
Jim Gibson
j...@gibson.org
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Modify excel worrkbook in perl
Hi
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Book1.xls');
Like this , as per examples, we could open/read the existing excel files.but
how can we execute other functions which are exclude in the
Spreadsheet::ParseExcel. Like delete sheet , delete row, adding data
...etc.
For that task , do we need to call Spreadsheet::WriteExcel package? Can
somebody help me on this please?
Many Thanks
Luke
From: Shlomi Fish
To: beginners@perl.org
Cc: luke devon
Sent: Sunday, April 25, 2010 21:12:59
Subject: Re: Modify excel worrkbook in perl
On Sunday 25 Apr 2010 10:12:02 luke devon wrote:
> Hi
>
> I need to modify a excel file in perl and for which i installed perl in
> ubuntu.
>
> 1. Open a existing excel file
>
> 2. delete an unwanted Sheet called "summary"
>
> 3. and i want to insert some data into range of cells ( B1:B11)
>
> Could you please tell me what is the most suitable perl module that we can
> use ?
>
There are two modules for that on CPAN:
1. http://search.cpan.org/dist/Spreadsheet-WriteExcel/ .
2. http://search.cpan.org/dist/Spreadsheet-ParseExcel/ .
Note that the Excel format (.xls) is binary and has been mostly undocumented
for a long time, and even Excel itself (or at least MS Word) has had problems
with forward-compatibility and backwards-compatibility of its own formats.
Recently, the specification was released and it's horribly complicated:
http://www.joelonsoftware.com/items/2008/02/19.html
It's not mentioned in the link, but when it came out it came under a
problematic EULA which prevented some use by open source software developers.
As a result of all this, it is no guaranteed that the roundtrip to and from
Excel will work. It's possible you'll have better luck with OpenOffice.org's
support for Excel import and export, though naturally this is not 100% bullet-
proof either.
Regards,
Shlomi Fish
--
-
Shlomi Fish http://www.shlomifish.org/
Original Riddles - http://www.shlomifish.org/puzzles/
Deletionists delete Wikipedia articles that they consider lame.
Chuck Norris deletes deletionists whom he considers lame.
Please reply to list if it's a mailing list post - http://shlom.in/reply .
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Modify excel worrkbook in perl
On Sunday 25 Apr 2010 10:12:02 luke devon wrote: > Hi > > I need to modify a excel file in perl and for which i installed perl in > ubuntu. > > 1. Open a existing excel file > > 2. delete an unwanted Sheet called "summary" > > 3. and i want to insert some data into range of cells ( B1:B11) > > Could you please tell me what is the most suitable perl module that we can > use ? > There are two modules for that on CPAN: 1. http://search.cpan.org/dist/Spreadsheet-WriteExcel/ . 2. http://search.cpan.org/dist/Spreadsheet-ParseExcel/ . Note that the Excel format (.xls) is binary and has been mostly undocumented for a long time, and even Excel itself (or at least MS Word) has had problems with forward-compatibility and backwards-compatibility of its own formats. Recently, the specification was released and it's horribly complicated: http://www.joelonsoftware.com/items/2008/02/19.html It's not mentioned in the link, but when it came out it came under a problematic EULA which prevented some use by open source software developers. As a result of all this, it is no guaranteed that the roundtrip to and from Excel will work. It's possible you'll have better luck with OpenOffice.org's support for Excel import and export, though naturally this is not 100% bullet- proof either. Regards, Shlomi Fish -- - Shlomi Fish http://www.shlomifish.org/ Original Riddles - http://www.shlomifish.org/puzzles/ Deletionists delete Wikipedia articles that they consider lame. Chuck Norris deletes deletionists whom he considers lame. Please reply to list if it's a mailing list post - http://shlom.in/reply . -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Modify excel worrkbook in perl
Hi I need to modify a excel file in perl and for which i installed perl in ubuntu. 1. Open a existing excel file 2. delete an unwanted Sheet called "summary" 3. and i want to insert some data into range of cells ( B1:B11) Could you please tell me what is the most suitable perl module that we can use ? Thanks in Advance Luke
Re: Working with Excel spreadsheets
> What about open office spread sheets. Can they be read by these > excel CPAN entries? Probably not, Excel is Excel and open Office is open office However if you search CPAN for openoffice, you might find something to use, http://search.cpan.org/search?query=OpenOffice&mode=all Owen -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Working with Excel spreadsheets
What about open office spread sheets. Can they be read by these excel CPAN entries? --- On Mon, 1/25/10, ANJAN PURKAYASTHA wrote: From: ANJAN PURKAYASTHA Subject: Re: Working with Excel spreadsheets To: "Mike Blezien" Cc: rc...@pcug.org.au, "Perl List" Date: Monday, January 25, 2010, 8:59 PM Spreadsheet::ParseExcel::Simple is the simplest module to use. Very user friendly. I use it often. HTH, Anjan On Mon, Jan 25, 2010 at 7:48 PM, Mike Blezien wrote: > > - Original Message - From: "Owen" > To: "Mike Blezien" > Cc: "Perl List" > Sent: Monday, January 25, 2010 6:14 PM > Subject: Re: Working with Excel spreadsheets > > > > >> Hello, >>> >>> we need to extract data from uploaded Excel spreadsheets, XLS binary >>> files. What >>> is the best perl module to accomplish this with ?? >>> >> >> >> >> Updated yesterday, Spreadsheet::ParseExcel >> >> >> Put 'excel' into the box at http://search.cpan.org/ and you may find >> others, like Spreadsheet::DataFromExcel >> > > Thanks Owen. I was reading through that module but wanted to find something > bit simpler to use, like the DataFromExcel or Excel2Text then work with the > exported data. Think we'll do some testing and see which one will suite our > need. > > Thanks for the help > > Mike > > -- > To unsubscribe, e-mail: beginners-unsubscr...@perl.org > For additional commands, e-mail: beginners-h...@perl.org > http://learn.perl.org/ > > > -- = anjan purkayastha, phd * research associate * fas center for systems biology, * harvard university * =
Re: Working with Excel spreadsheets
Yes this seem quiet easy to use also. I must have missed this one while browsing through CPAN's site. thanks, Mike - Original Message - From: ANJAN PURKAYASTHA To: Mike Blezien Cc: rc...@pcug.org.au ; Perl List Sent: Monday, January 25, 2010 7:59 PM Subject: Re: Working with Excel spreadsheets Spreadsheet::ParseExcel::Simple is the simplest module to use. Very user friendly. I use it often. HTH, Anjan On Mon, Jan 25, 2010 at 7:48 PM, Mike Blezien wrote: - Original Message - From: "Owen" To: "Mike Blezien" Cc: "Perl List" Sent: Monday, January 25, 2010 6:14 PM Subject: Re: Working with Excel spreadsheets Hello, we need to extract data from uploaded Excel spreadsheets, XLS binary files. What is the best perl module to accomplish this with ?? Updated yesterday, Spreadsheet::ParseExcel Put 'excel' into the box at http://search.cpan.org/ and you may find others, like Spreadsheet::DataFromExcel Thanks Owen. I was reading through that module but wanted to find something bit simpler to use, like the DataFromExcel or Excel2Text then work with the exported data. Think we'll do some testing and see which one will suite our need. Thanks for the help Mike -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/ -- = anjan purkayastha, phd * research associate* fas center for systems biology, * harvard university * =
Re: Working with Excel spreadsheets
Spreadsheet::ParseExcel::Simple is the simplest module to use. Very user friendly. I use it often. HTH, Anjan On Mon, Jan 25, 2010 at 7:48 PM, Mike Blezien wrote: > > - Original Message - From: "Owen" > To: "Mike Blezien" > Cc: "Perl List" > Sent: Monday, January 25, 2010 6:14 PM > Subject: Re: Working with Excel spreadsheets > > > > >> Hello, >>> >>> we need to extract data from uploaded Excel spreadsheets, XLS binary >>> files. What >>> is the best perl module to accomplish this with ?? >>> >> >> >> >> Updated yesterday, Spreadsheet::ParseExcel >> >> >> Put 'excel' into the box at http://search.cpan.org/ and you may find >> others, like Spreadsheet::DataFromExcel >> > > Thanks Owen. I was reading through that module but wanted to find something > bit simpler to use, like the DataFromExcel or Excel2Text then work with the > exported data. Think we'll do some testing and see which one will suite our > need. > > Thanks for the help > > Mike > > -- > To unsubscribe, e-mail: beginners-unsubscr...@perl.org > For additional commands, e-mail: beginners-h...@perl.org > http://learn.perl.org/ > > > -- = anjan purkayastha, phd * research associate* fas center for systems biology, * harvard university * =
Re: Working with Excel spreadsheets
- Original Message - From: "Owen" To: "Mike Blezien" Cc: "Perl List" Sent: Monday, January 25, 2010 6:14 PM Subject: Re: Working with Excel spreadsheets Hello, we need to extract data from uploaded Excel spreadsheets, XLS binary files. What is the best perl module to accomplish this with ?? Updated yesterday, Spreadsheet::ParseExcel Put 'excel' into the box at http://search.cpan.org/ and you may find others, like Spreadsheet::DataFromExcel Thanks Owen. I was reading through that module but wanted to find something bit simpler to use, like the DataFromExcel or Excel2Text then work with the exported data. Think we'll do some testing and see which one will suite our need. Thanks for the help Mike -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Working with Excel spreadsheets
> Hello, > > we need to extract data from uploaded Excel spreadsheets, XLS binary > files. What > is the best perl module to accomplish this with ?? Updated yesterday, Spreadsheet::ParseExcel Put 'excel' into the box at http://search.cpan.org/ and you may find others, like Spreadsheet::DataFromExcel -- Owen -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Working with Excel spreadsheets
Hello, we need to extract data from uploaded Excel spreadsheets, XLS binary files. What is the best perl module to accomplish this with ?? Thanks, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: retrieving and parsing an excel spreadsheet
On Dec 22, 6:35 am, meda...@googlemail.com (Paul) wrote:
> I want to retrieve an excel spreadsheet (eventually several at
> different locations) and display the contents in a web page.
> if i copy the file locally ive got everything working as expected.I
> cant retrieve the file and do the same though.
>
> if i do this with a local file it works
>
> #!/usr/bin/perl -w
> use strict;
> use warnings;
> use Spreadsheet::ParseExcel;
> use DateTime::Format::Excel;
> use Data::Dumper;
> use Date::Manip;
> use Statistics::Descriptive::Discrete ;
> use Storable;
> use LWP::Simple;
> use CGI;
>
> my $q = new CGI;
> print $q->header;
> print $q->start_html();#
>
> $file='/tmp/data.xls';
> die "Couldn't get excel file " unless defined $file;
> my $cell;my $row;my $sheet;my $col;my $excel;
> $excel = Spreadsheet::ParseExcel::Workbook->Parse($file) or die ("cant
> open file");
> etc etc
>
> if i do this it doesnt display
>
> #!/usr/bin/perl -w
> use strict;
> use warnings;
> use Spreadsheet::ParseExcel;
> use DateTime::Format::Excel;
> use Data::Dumper;
> use Date::Manip;
> #use Date::Calc;# qw(Week_of_Year);
> use Statistics::Descriptive::Discrete ;
> use Storable;
> use LWP::Simple;
> use CGI;
>
> my $q = new CGI;
> print $q->header;
> print $q->start_html();#
>
> my $url = "http://intranet/..xls";
> my $file =get $url;
> die "Couldn't get excel file " unless defined $file;
> my $cell;my $row;my $sheet;my $col;my $excel;
> $excel = Spreadsheet::ParseExcel::Workbook->Parse($file) or die ("cant
> open file");
>
> the original file when copied is
>
> -rwxrwxrwx 1 paul paul 58880 2009-12-15 08:47 data.xls
>
> if i get it and save it , it is different
>
> my $url = "http://blah.xls";;
> my $file = get $url;
> open (F, '>/tmp/data.xls');
> print F $file;
> close (F);
>
> -rw-r--r-- 1 paul paul 59392 2009-12-21 18:18 data.xls
>
> is there a way to automatically retrieve the excel file as i wish ( so
> that changes to the excel spreadshet are automatically updated in the
> web page .
>
> Excel will open the the downloaded file as well as from the intranet
> and they look identical in excel
>
> doing a diff just says the binary files are different.
>
> what am i doing wrong ?
Just a guess but the Spreadsheet::ParseExcel doc has a version
warning.
Did you see this:
Note: Versions of Spreadsheet::ParseExcel prior to 0.50 also
documented
a Workbook parse() method as a syntactic shortcut for the above
new()
and parse() combination. This is now deprecated since it breaks
error
handling.
Also, the documented error() method may provide more helpful messages
if the file's unparseable:
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Book1.xls');
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
--
Charles DeRykus
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
retrieving and parsing an excel spreadsheet
I want to retrieve an excel spreadsheet (eventually several at
different locations) and display the contents in a web page.
if i copy the file locally ive got everything working as expected.I
cant retrieve the file and do the same though.
if i do this with a local file it works
#!/usr/bin/perl -w
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use DateTime::Format::Excel;
use Data::Dumper;
use Date::Manip;
use Statistics::Descriptive::Discrete ;
use Storable;
use LWP::Simple;
use CGI;
my $q = new CGI;
print $q->header;
print $q->start_html();#
$file='/tmp/data.xls';
die "Couldn't get excel file " unless defined $file;
my $cell;my $row;my $sheet;my $col;my $excel;
$excel = Spreadsheet::ParseExcel::Workbook->Parse($file) or die ("cant
open file");
etc etc
if i do this it doesnt display
#!/usr/bin/perl -w
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use DateTime::Format::Excel;
use Data::Dumper;
use Date::Manip;
#use Date::Calc;# qw(Week_of_Year);
use Statistics::Descriptive::Discrete ;
use Storable;
use LWP::Simple;
use CGI;
my $q = new CGI;
print $q->header;
print $q->start_html();#
my $url = "http://intranet/..xls";
my $file =get $url;
die "Couldn't get excel file " unless defined $file;
my $cell;my $row;my $sheet;my $col;my $excel;
$excel = Spreadsheet::ParseExcel::Workbook->Parse($file) or die ("cant
open file");
the original file when copied is
-rwxrwxrwx 1 paul paul 58880 2009-12-15 08:47 data.xls
if i get it and save it , it is different
my $url = "http://blah .xls";
my $file = get $url;
open (F, '>/tmp/data.xls');
print F $file;
close (F);
-rw-r--r-- 1 paul paul 59392 2009-12-21 18:18 data.xls
is there a way to automatically retrieve the excel file as i wish ( so
that changes to the excel spreadshet are automatically updated in the
web page .
Excel will open the the downloaded file as well as from the intranet
and they look identical in excel
doing a diff just says the binary files are different.
what am i doing wrong ?
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Perl script to Diff 2 Excel sheets
Hi Parag! On Saturday 12 Dec 2009 07:05:02 Parag Kalra wrote: > Hello All, > > I am planning to write a small Perl script to diff 2 excel sheets > (workbooks) using the module - Spreadsheet::ParseExcel. > > I am planning to implement following algorithm. > > What we can do is to pick up a worksheet at time (inside a loop) from each > of the 2 workbooks and compare the cell values. > > 1. I would start with storing the cell values inside 2 hashes (1 hash for > each worksheet). > > 2. So for first worksheet, its hash would have keys like A1, A2, A3 ... B1, > B2, B3 ... C1, C2, C3 ... etc and values of these keys would be contents of > the corresponding cells. > > 3. Similarly I would create another hash for corresponding worksheet of > second workbook and it would have the same data structure as the first > hash. > > 4. Then I would pick corresponding keys from each hash and compare the > contents of the related cells using 'eq' operator. > > And again repeat the steps from 1 to 4 for the other corresponding > worksheets of the 2 workbooks. > > So what I actually want to know is that - do we already have a module that > will do the above steps or shall I go ahead with my algorithm. > You might wish to look at: http://search.cpan.org/dist/Test-Differences/ and or some of its dependencies. Regards, Shlomi Fish -- - Shlomi Fish http://www.shlomifish.org/ Optimising Code for Speed - http://shlom.in/optimise Bzr is slower than Subversion in combination with Sourceforge. ( By: http://dazjorz.com/ ) -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Perl script to Diff 2 Excel sheets
Hello All, I am planning to write a small Perl script to diff 2 excel sheets (workbooks) using the module - Spreadsheet::ParseExcel. I am planning to implement following algorithm. What we can do is to pick up a worksheet at time (inside a loop) from each of the 2 workbooks and compare the cell values. 1. I would start with storing the cell values inside 2 hashes (1 hash for each worksheet). 2. So for first worksheet, its hash would have keys like A1, A2, A3 ... B1, B2, B3 ... C1, C2, C3 ... etc and values of these keys would be contents of the corresponding cells. 3. Similarly I would create another hash for corresponding worksheet of second workbook and it would have the same data structure as the first hash. 4. Then I would pick corresponding keys from each hash and compare the contents of the related cells using 'eq' operator. And again repeat the steps from 1 to 4 for the other corresponding worksheets of the 2 workbooks. So what I actually want to know is that - do we already have a module that will do the above steps or shall I go ahead with my algorithm. Cheers, Parag
Re: Need to process Excel sheet through Perl
I have one more question related to the module - 'Spreadsheet-ParseExcel' I want to extract the default header names of the excel sheet. So basically I am looking for a method to extract the column names whose default values are 'A', 'B', 'C', 'D' etc. Thus I am looking for a function which would return list of all the column names. By default it would return A, B, C so and so forth. Or it will return the column names that are actually set. I hope I am clear on this. :) Cheers, Parag 2009/12/4 Shlomi Fish > On Friday 04 Dec 2009 12:15:58 Rene Schickbauer wrote: > > Xiao Lan (小兰) wrote: > > > On Fri, Dec 4, 2009 at 3:40 PM, Parag Kalra > wrote: > > >> Hello All, > > >> > > >> I am looking for some good Perl modules to process excel sheets. > > > > > > Hi, > > > > > > If you just want a module, take a look at this one (I once used it): > > > > > > > http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.55/lib/Spreads<http://search.cpan.org/%7Ejmcnamara/Spreadsheet-ParseExcel-0.55/lib/Spreads> > > >heet/ParseExcel.pm > > > > Depending on the Excel version used to generate the files and the actual > > filetype saved (OOXML anyone?), this might or might not work. > > > > If in doubt, you could use Win32::OLE (on a windows machine) to convert > > the file to a CSV-File (comma seperated values) first, take a look here: > > > > http://www.ngbdigital.com/perl_ole_excel.html > > > > Then you could use a number of CPAN modules to process the CSV. One of > > the more exotic one is DBD::CSV, where use can use the file like a > > database table ;-) > > > > And another option that may work better than the Spreadsheet-ParseExcel one > but probably not as well as the OLE solution would be to utilise > OpenOffice.org to read the file and then save it in an easier-to-digest > format > (such as CSV or OpenDocument). It's possible OpenOffice.org may be able to > handle more diverse Excel files than the CPAN module. > > Regarding using OLE - if you're not running Windows, you can still make use > of > it to convert the XML into shape by running a network service of some sort > on > top of a different Windows machine or a virtual machine (see > http://www.virtualbox.org/ for example). > > Regards, > >Shlomi Fish > > > LG > > rene > > > > -- > - > Shlomi Fish http://www.shlomifish.org/ > Freecell Solver - http://fc-solve.berlios.de/ > > Chuck Norris read the entire English Wikipedia in 24 hours. Twice. >
Re: Need to process Excel sheet through Perl
On Friday 04 Dec 2009 12:15:58 Rene Schickbauer wrote: > Xiao Lan (小兰) wrote: > > On Fri, Dec 4, 2009 at 3:40 PM, Parag Kalra wrote: > >> Hello All, > >> > >> I am looking for some good Perl modules to process excel sheets. > > > > Hi, > > > > If you just want a module, take a look at this one (I once used it): > > > > http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.55/lib/Spreads > >heet/ParseExcel.pm > > Depending on the Excel version used to generate the files and the actual > filetype saved (OOXML anyone?), this might or might not work. > > If in doubt, you could use Win32::OLE (on a windows machine) to convert > the file to a CSV-File (comma seperated values) first, take a look here: > > http://www.ngbdigital.com/perl_ole_excel.html > > Then you could use a number of CPAN modules to process the CSV. One of > the more exotic one is DBD::CSV, where use can use the file like a > database table ;-) > And another option that may work better than the Spreadsheet-ParseExcel one but probably not as well as the OLE solution would be to utilise OpenOffice.org to read the file and then save it in an easier-to-digest format (such as CSV or OpenDocument). It's possible OpenOffice.org may be able to handle more diverse Excel files than the CPAN module. Regarding using OLE - if you're not running Windows, you can still make use of it to convert the XML into shape by running a network service of some sort on top of a different Windows machine or a virtual machine (see http://www.virtualbox.org/ for example). Regards, Shlomi Fish > LG > rene > -- - Shlomi Fish http://www.shlomifish.org/ Freecell Solver - http://fc-solve.berlios.de/ Chuck Norris read the entire English Wikipedia in 24 hours. Twice. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Need to process Excel sheet through Perl
Xiao Lan (小兰) wrote: On Fri, Dec 4, 2009 at 3:40 PM, Parag Kalra wrote: Hello All, I am looking for some good Perl modules to process excel sheets. Hi, If you just want a module, take a look at this one (I once used it): http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.55/lib/Spreadsheet/ParseExcel.pm Depending on the Excel version used to generate the files and the actual filetype saved (OOXML anyone?), this might or might not work. If in doubt, you could use Win32::OLE (on a windows machine) to convert the file to a CSV-File (comma seperated values) first, take a look here: http://www.ngbdigital.com/perl_ole_excel.html Then you could use a number of CPAN modules to process the CSV. One of the more exotic one is DBD::CSV, where use can use the file like a database table ;-) LG rene -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Need to process Excel sheet through Perl
On Fri, Dec 4, 2009 at 3:40 PM, Parag Kalra wrote: > Hello All, > > I am looking for some good Perl modules to process excel sheets. > Hi, If you just want a module, take a look at this one (I once used it): http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.55/lib/Spreadsheet/ParseExcel.pm -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Need to process Excel sheet through Perl
Hello All, I am looking for some good Perl modules to process excel sheets. I have a very basic requirement – I need a method to which I would provide the excel sheet and any column name of that excel sheet. It should return the list of all the elements present in that column which I can store may be in an array. So just wanted to know the frequently used Perl modules for Excel sheet and which one would suffice my needs in best & in simple way. --Parag
Re: How to append to existing excel sheet?
On 5/27/09 Wed May 27, 2009 2:14 AM, "Kelvin Philip" scribbled: > Hi, > > Is there any method to append a row to an existing Excel file? Can I do it > using the Spreadsheet::WriteExcel module? The Spreadsheet::WriteExcel (SS:WE) module will not add data to an existing spreadsheet. If you are on Windows, you can try one of the OLE modules (I haven't used them) to run Excel and get it to add the row. Otherwise, you can use Spreadsheet::ParseExcel to read your existing spreadsheet and SS:WE to copy the data to a new spreadsheet, adding whatever data you want. However, you may lose some data or formatting in that approach if the two modules cannot handle something in your spreadsheet or you are not careful to copy everything. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: How to append to existing excel sheet?
-Original Message-
From: Kelvin Philip [mailto:kelvinphi...@gmail.com]
Sent: Wednesday, May 27, 2009 2:45 PM
To: beginners@perl.org
Subject: How to append to existing excel sheet?
>>Hi,
>>Is there any method to append a row to an existing Excel file? Can I do it
>>using the Spreadsheet::WriteExcel module?
>>Looking forward to hear from you.
>>Regards,
>>Kelvin
Hi Kevin
Use 'write' method. Refer CPAN description of Spreadsheet::WriteExcel
Change the column no. using for loop & keep the row no. fixed.
e.g. for appending into 3rd row upto 100th column
$row = 3;
for $col (1..100)
{
$worksheet->write($row, $col, $data);
}
#$data will be specific for each cell
Regards,
Sanket Vaidya
_
This e-mail message may contain proprietary, confidential or legally privileged
information for the sole use of the person or entity to whom this message was
originally addressed. Any review, e-transmission dissemination or other use of
or taking of any action in reliance upon this information by persons or
entities other than the intended recipient is prohibited. If you have received
this e-mail in error kindly delete this e-mail from your records. If it appears
that this mail has been forwarded to you without proper authority, please
notify us immediately at netad...@patni.com and delete this mail.
_
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
RE: How to append to existing excel sheet?
-Original Message-
From: Kelvin Philip [mailto:kelvinphi...@gmail.com]
Sent: Wednesday, May 27, 2009 2:45 PM
To: beginners@perl.org
Subject: How to append to existing excel sheet?
Hi,
Is there any method to append a row to an existing Excel file? Can I do it
using the Spreadsheet::WriteExcel module?
Looking forward to hear from you.
Regards,
Kelvin
Use 'write' method. Refer CPAN description of Spreadsheet::WriteExcel
Change the column no. using for loop & keep the row no. fixed.
e.g. for appending into 3rd row upto 100th column
$row = 3;
for $col (1..100)
{
$worksheet->write($row, $col, $data);
}
#$data will be specific for each cell
Regards,
Sanket Vaidya
_
This e-mail message may contain proprietary, confidential or legally privileged
information for the sole use of the person or entity to whom this message was
originally addressed. Any review, e-transmission dissemination or other use of
or taking of any action in reliance upon this information by persons or
entities other than the intended recipient is prohibited. If you have received
this e-mail in error kindly delete this e-mail from your records. If it appears
that this mail has been forwarded to you without proper authority, please
notify us immediately at netad...@patni.com and delete this mail.
_
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
How to append to existing excel sheet?
Hi, Is there any method to append a row to an existing Excel file? Can I do it using the Spreadsheet::WriteExcel module? Looking forward to hear from you. Regards, Kelvin
Re: Write Excel and Open file from CGI
On 5/13/09 Wed May 13, 2009 11:03 AM, "Vaishak" scribbled: > Hello List, > > > > > > I have a DOS batch file which has perl script that runs at the end. The perl > script fetches information from different files and database and generates > an excel file. Is there a way I can open the excel from CGI and display on > screen? Write a CGI program that uses the Perl Spreadsheet::ParseExcel module (available from CPAN) to extract the contents of the Excel file and display them on the screen in the browser window. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: Write Excel and Open file from CGI
--Hello List, -- --I have a DOS batch file which has perl script that runs at the end. The --perl --script fetches information from different files and database and generates --an excel file. Is there a way I can open the excel from CGI and display on --screen? -- -- --Thanks -- --Vau # Print HTTP header information at top of page print 'Content-type: application/vnd.ms-excel; name="'.$filename.'"'."\n"; print "Content-Transfer-Encoding: text; \n"; print 'Content-Disposition: inline; filename="'.$filename.'"'."\n"; print 'Content-Description: "msexcel";' . "\n\n"; # Print file content print "$file_contents\n"; Output that via CGI and the browser should open an inline excel spreadsheet. Capgemini is a trading name used by the Capgemini Group of companies which includes Capgemini UK plc, a company registered in England and Wales (number 943935) whose registered office is at No. 1 Forge End, Woking, Surrey, GU21 6DB. This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Write Excel and Open file from CGI
Hello List, I have a DOS batch file which has perl script that runs at the end. The perl script fetches information from different files and database and generates an excel file. Is there a way I can open the excel from CGI and display on screen? Thanks Vau
Re: How to Extract to a flat file thru Excel an Access DB file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Wagner, David --- Senior Programmer Analyst --- CFS wrote: > I have an Excel file that I am copying from a Windows server to Linux > box(initially). I then extract each worksheet to their own csv. Within the > Excel is detail that is contained within a Access DB. I am getting the > necessary worksheets out ok, but am unsure how or if one can extract to a > csv, the detail data from Access DB. > > Any thoughts or how one would research to do this? I am on 5.8.8 > ActiveState Build 824. > > If you have any questions and/or problems, please let me know. > Thanks. > > Wags ;) > David R. Wagner > Senior Programmer Analyst > FedEx Freight > 1.719.484.2097 TEL > 1.719.484.2419 FAX > 1.408.623.5963 Cell > http://fedex.com/us > Not Perl, but MDB Tools might be of use. From the FAQ: 1.2 What does MDB Tools do MDB Tools is an open source suite of libraries and utilities to read (and soon write) MDB database files. http://mdbtools.sourceforge.net/faq.html They say it'll run on Linux. Regards, Rich Loveland -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFKCXQS4EG8v4hpG/ERAhJKAKCiUcv7YQ8ba5r81FadB37cEi0u4wCeKd7R tTsNRdT3wKlNasSI6FNvRsE= =JeS/ -END PGP SIGNATURE- -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: How to Extract to a flat file thru Excel an Access DB file
From: "Wagner, David --- Senior Programmer Analyst --- CFS" > I have an Excel file that I am copying from a Windows server to Linux > box(initially). I then extract each worksheet to their own csv. Within > the Excel is detail that is contained within a Access DB. I am getting > the necessary worksheets out ok, but am unsure how or if one can > extract to a csv, the detail data from Access DB. I believe you'll have to do the conversion before the copying, on the windows side, using Win32::OLE. Jenda = je...@krynicky.cz === http://Jenda.Krynicky.cz = When it comes to wine, women and song, wizards are allowed to get drunk and croon as much as they like. -- Terry Pratchett in Sourcery -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
How to Extract to a flat file thru Excel an Access DB file
I have an Excel file that I am copying from a Windows server to Linux box(initially). I then extract each worksheet to their own csv. Within the Excel is detail that is contained within a Access DB. I am getting the necessary worksheets out ok, but am unsure how or if one can extract to a csv, the detail data from Access DB. Any thoughts or how one would research to do this? I am on 5.8.8 ActiveState Build 824. If you have any questions and/or problems, please let me know. Thanks. Wags ;) David R. Wagner Senior Programmer Analyst FedEx Freight 1.719.484.2097 TEL 1.719.484.2419 FAX 1.408.623.5963 Cell http://fedex.com/us -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
RE: Generating Excel Charts from *nix
Hi all, I am looking to find a way to generate an excel sheet with charts on a *nix flavour (its MAC OSX Actually). I used Spreadsheet:::WriteExcel quite happily to gen a sheet and fill it with the data I want. However the chart facility has a slight drawback in that you need to build the chart and extend the series to allow for room to cover the largest number of data items you want to chart. The items I want to chart range from a series length of 1000 ranging to 7000. So I would need to allow >7000 for the reporting period that has lots of data. This means that the chart is either squashed up or could miss data items from the chart altogether. Unfortunately Spreadsheet::WriteExcel does not support named ranges - yet, and the chart series will not take a formula to redirect the reference to a range. Has anyone done anything like this before? Ta Stu [Stewart Anderson] Meant to say that I don't want to do this is Win32::OLE, I can just as easy do it from outlook when I send the mail with the sheet attached, really looking for a "neat" perl solution for the whole thing is all. Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and are used under licence. British Sky Broadcasting Limited (Registration No. 2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are direct or indirect subsidiaries of British Sky Broadcasting Group plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
Generating Excel Charts from *nix
Hi all, I am looking to find a way to generate an excel sheet with charts on a *nix flavour (its MAC OSX Actually). I used Spreadsheet:::WriteExcel quite happily to gen a sheet and fill it with the data I want. However the chart facility has a slight drawback in that you need to build the chart and extend the series to allow for room to cover the largest number of data items you want to chart. The items I want to chart range from a series length of 1000 ranging to 7000. So I would need to allow >7000 for the reporting period that has lots of data. This means that the chart is either squashed up or could miss data items from the chart altogether. Unfortunately Spreadsheet::WriteExcel does not support named ranges - yet, and the chart series will not take a formula to redirect the reference to a range. Has anyone done anything like this before? Ta Stu Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and are used under licence. British Sky Broadcasting Limited (Registration No. 2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are direct or indirect subsidiaries of British Sky Broadcasting Group plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
Re: perl to excel
DeeDee Messersmith wrote: > > Sorry guys and gals. I'm new and didn't reply to all. > > I tried this and I got a message that it could not find this repository. I > can't cut and paste the errors but when I start my PPM (windows XP) it says: > Downloading tcool packlistfailed 411 length required. I got the same > thing when someone told me to use http://trouchelle.com/ppm/package.xml > > I am doing this under the edit and repositories tabs in the PPM. Not sure > what else to try. I have my program creating a text file the users can pull > up in Excel but I want to eliminate that step for them and generate the > Excel file OK if you're using the GUI version of PPM you need a different address. According to the site you need an address of http://ppm.tcool.org/archives/ but take a look yourself and you may be able to fix a lot of problems yourself. Just point your browser at http://ppm.tcool.org/ and start reading. Rob -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
Re: perl to excel
Hello If you're using the GUI version of PPM, set the repository to this value: http://ppm.tcool.org/archives/See here for details:http://ppm.tcool.org/intro/register-- Kevin Safford -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
Re: perl to excel
Sorry guys and gals. I'm new and didn't reply to all. I tried this and I got a message that it could not find this repository. I can't cut and paste the errors but when I start my PPM (windows XP) it says: Downloading tcool packlistfailed 411 length required. I got the same thing when someone told me to use http://trouchelle.com/ppm/package.xml I am doing this under the edit and repositories tabs in the PPM. Not sure what else to try. I have my program creating a text file the users can pull up in Excel but I want to eliminate that step for them and generate the Excel file DeeDee Messersmith Specialist -Programmer/Analyst Alliance Solutions Falcon Jet - Little Rock (ILS) 903-624-1355 cell 903-464-9064 fax - Original Message - From: "Rob Dixon" <[EMAIL PROTECTED]> To: "Perl Beginners" Cc: "DeeDee Messersmith" <[EMAIL PROTECTED]> Sent: Monday, August 11, 2008 5:16 PM Subject: Re: perl to excel DeeDee Messersmith wrote: I have been trying to create an Excel file from inside a Perl program but my program can't seem to access the module. I have tried using PPM to get the correct module but when it shows 0 packages in the respository I was told to use. Therefore, when I try and acces Excel it can't find the right module. Ths is the code I was trying to use: When it couldn't find the Date module I commented that out. Can someone help? If you are trying to install the Spreadsheet::WriteExcel module then as far as I know the latest version 2.23 isn't available on a ppm repository. Version 2.22 is likely to work for you though, and is available on the excellent tcool repository. If you don't already have it set as one of your repositories then run ppm rep add tcool http://ppm.tcool.org/server/ppmserver.cgi?urn:PPMServer followed by ppm install Spreadsheet::WriteExcel and it will be avaialable on your system. If you are trying to use another module then please let us know. HTH, Rob -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
Re: perl to excel
DeeDee Messersmith wrote: > > I have been trying to create an Excel file from inside a Perl program but my > program can't seem to access the module. I have tried using PPM to get the > correct module but when it shows 0 packages in the respository I was told to > use. Therefore, when I try and acces Excel it can't find the right module. > Ths is the code I was trying to use: When it couldn't find the Date module I > commented that out. Can someone help? If you are trying to install the Spreadsheet::WriteExcel module then as far as I know the latest version 2.23 isn't available on a ppm repository. Version 2.22 is likely to work for you though, and is available on the excellent tcool repository. If you don't already have it set as one of your repositories then run ppm rep add tcool http://ppm.tcool.org/server/ppmserver.cgi?urn:PPMServer followed by ppm install Spreadsheet::WriteExcel and it will be avaialable on your system. If you are trying to use another module then please let us know. HTH, Rob -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
perl to excel
I have been trying to create an Excel file from inside a Perl program but my program can't seem to access the module. I have tried using PPM to get the correct module but when it shows 0 packages in the respository I was told to use. Therefore, when I try and acces Excel it can't find the right module. Ths is the code I was trying to use: When it couldn't find the Date module I commented that out. Can someone help? Thanks, DeeDee Messersmith Specialist -Programmer/Analyst Alliance Solutions Falcon Jet - Little Rock (ILS) 903-624-1355 cell 903-464-9064 fax
Re: reformatting excel files
minky arora wrote:
> Hi Gurus,
> I need some sugegstions as to how to go about doing the following.
>
> I have a huge excel file and a file map associated with it, which says how
> the excel sheet is to be formatted. For instance, a set of 96 cells which
> are named "A" , need to be entered below a set of 96 cells named "B" ("B"
> could be located anywhere in the file). So following the map in this way, I
> need to reconstruct the file.
>
> I do have a couple of floating ideas but am clueless as to where to being
> /best way to solve such problems.
Without seeing an example it's very hard to help you. My only suggestion at this
stage is that you should export the spreadsheet as a comma-separated text file
and process that instead of trying to operate on the native Excel file. Please
show us some example data and we will help you further.
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
reformatting excel files
Hi Gurus,
I need some sugegstions as to how to go about doing the following.
I have a huge excel file and a file map associated with it, which says how
the excel sheet is to be formatted. For instance, a set of 96 cells which
are named "A" , need to be entered below a set of 96 cells named "B" ("B"
could be located anywhere in the file). So following the map in this way, I
need to reconstruct the file.
I do have a couple of floating ideas but am clueless as to where to being
/best way to solve such problems.
Please advise,
Thanks,
M
Re: parsing a large excel file
ANJAN PURKAYASTHA wrote:
>
> i have installed Spreadshee::ParseExcel to parse some large excel data
> files.
>
> Here is the problem I'm facing. I need to parse data from columns M to P and
> rows 10 to 43000. Now I know that there is a PrintArea method that can print
> an area of a worksheet specified in (start row, start col, end row, end
> col). However I'm having difficulty in specifying these parameters correctly
> for the PrintArea method. I'm also not sure what the output is going to look
> like.
I found both the code and the documentation of this module very difficult to
understand. However I have an answer for you.
If you read the documentation carefully it says "Workbook class has these
properties:" before the list that includes PrintArea. What the author means by
properties is hash elements, so you can access the array of print areas for the
workbook by
my $areas = $book->{PrintArea};
and the print areas for the first worksheet (did anyone else know an Excel
worksheet could have multiple print areas?!) can be displayed by
print "@$_\n" foreach @{$areas->[0]};
But it has a bug whereby the hash element sometimes doesn't appear at all. I
sometimes had to ad a second sheet and give that a print area to make the
attribute appear.
However, if you remember that when the documentation refers to a 'property' it
means a hash element it will make much more sense.
HTH,
Rob
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: parsing a large excel file
thanks for your helpful responses.
Dr Ruud my aim was to parse the text area M10..P43000 and then print it
using PrintArea. Maybe I have misunderstood the function of PrintArea.
Stewart, many thanks for the suggestion. I will indeed try it out and see
what happens.
Anjan
On Tue, Jul 22, 2008 at 5:11 AM, Stewart Anderson <
[EMAIL PROTECTED]> wrote:
>
> > -Original Message-
> > From: Stewart Anderson
> > Sent: 22 July 2008 09:34
> > To: ANJAN PURKAYASTHA; beginners@perl.org
> > Cc: Stewart Anderson
> > Subject: RE: parsing a large excel file
> >
> >
> > > -Original Message-
> > > From: ANJAN PURKAYASTHA [mailto:[EMAIL PROTECTED]
> > > Sent: 22 July 2008 02:41
> > > To: beginners@perl.org
> > > Subject: parsing a large excel file
> > >
> > > to all,
> > > i have installed Spreadshee::ParseExcel to parse some large excel
> data
> > > files.
> > > Here is the problem I'm facing. I need to parse data from columns M
> to P
> > > and
> > > rows 10 to 43000. Now I know that there is a PrintArea method that
> can
> > > print
> > > an area of a worksheet specified in (start row, start col, end row,
> end
> > > col). However I'm having difficulty in specifying these parameters
> > > correctly
> > > for the PrintArea method. I'm also not sure what the output is going
> to
> > > look
> > > like.
> > > does anyone in this forum have any pointers?
> > > all advice will be appreciated.
> > > tia,
> > > anjan
> > >
> > I have only used the Simple version previously to parse and entire
> row
> > but the method Cell ( ROW, COL ) In the docs for the module you
> > showed suggests you can get at the data directly.
> >
> > Stu
> >
> >
> Maybe I spoke to soon, that method seem to return the iobject, not
> 100% sure what that gives you without trying it.
>
> But the sample script in the doc looks like a good place to start,
> just set your row min/max and col min/max and you should be close to
> getting something.
>
>
> However, the sample program in the docs work as is. It should be
> easy to adapt it for your needs. Try this as a start. The first bit is
> pretty much out of the box and the last bit just shows you can extract
> what row/column you want.
>
> The data is at the end, load it into excel.
>
>
>
> #! /usr/bin/perl
>
> use warnings;
> use strict ;
> use Data::Dumper;
> use Spreadsheet::ParseExcel;
>
>my $excel =
> Spreadsheet::ParseExcel::Workbook->Parse('c:\temp\filetest.xls');
>foreach my $sheet (@{$excel->{Worksheet}}) {
>printf("Sheet: %s\n", $sheet->{Name});
>$sheet->{MaxRow} ||= $sheet->{MinRow};
>foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
>$sheet->{MaxCol} ||= $sheet->{MinCol};
>foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
> my $cell = $sheet->{Cells}[$row][$col];
>if ($cell) {
>printf("( %s , %s ) => %s\n", $row, $col,
> $cell->{Val});
>}
>}
>}
>}
>
>print "Extract specific row/cell row 2, col b \n" ;
> foreach my $sheet (@{$excel->{Worksheet}}) {
>my $row = 2;
>my $col = 2;
> my $cell = $sheet->{Cells}[$row][$col];
> printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
>
> }
>
>
>
> Data to load in excel
> 1a,1b,1c,1d,1e,1f
> 2a,2b,3c,4d,2e,2f
> 3a,3b,3c,3d,3e,3f
> 4a,4b,4c,4d,4e,4f
>
>
> Information in this email including any attachments may be privileged,
> confidential and is intended exclusively for the addressee. The views
> expressed may not be official policy, but the personal views of the
> originator. If you have received it in error, please notify the sender by
> return e-mail and delete it from your system. You should not reproduce,
> distribute, store, retransmit, use or disclose its contents to anyone.
> Please note we reserve the right to monitor all e-mail communication through
> our internal and external networks. SKY and the SKY marks are trade marks of
> British Sky Broadcasting Group plc and are used under licence. British Sky
> Broadcasting Limited (Registration No. 2906991), Sky Interactive Limited
> (Registration No. 3554332), Sky-In-Home Service Limited (Registration No.
> 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are
> direct or indirect subsidiaries of British Sky Broadcasting Group plc
> (Registration No. 2247735). All of the companies mentioned in this paragraph
> are incorporated in England and Wales and share the same registered office
> at Grant Way, Isleworth, Middlesex TW7 5QD.
>
> --
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> http://learn.perl.org/
>
>
>
--
=
anjan purkayastha
bioinformatics analyst
whitehead institute for biomedical research
nine cambridge center
cambridge, ma 02142
purkayas [at] wi [dot] mit [dot] edu
703.740.6939
RE: parsing a large excel file
> -Original Message-
> From: Stewart Anderson
> Sent: 22 July 2008 09:34
> To: ANJAN PURKAYASTHA; beginners@perl.org
> Cc: Stewart Anderson
> Subject: RE: parsing a large excel file
>
>
> > -Original Message-
> > From: ANJAN PURKAYASTHA [mailto:[EMAIL PROTECTED]
> > Sent: 22 July 2008 02:41
> > To: beginners@perl.org
> > Subject: parsing a large excel file
> >
> > to all,
> > i have installed Spreadshee::ParseExcel to parse some large excel
data
> > files.
> > Here is the problem I'm facing. I need to parse data from columns M
to P
> > and
> > rows 10 to 43000. Now I know that there is a PrintArea method that
can
> > print
> > an area of a worksheet specified in (start row, start col, end row,
end
> > col). However I'm having difficulty in specifying these parameters
> > correctly
> > for the PrintArea method. I'm also not sure what the output is going
to
> > look
> > like.
> > does anyone in this forum have any pointers?
> > all advice will be appreciated.
> > tia,
> > anjan
> >
> I have only used the Simple version previously to parse and entire
row
> but the method Cell ( ROW, COL ) In the docs for the module you
> showed suggests you can get at the data directly.
>
> Stu
>
>
Maybe I spoke to soon, that method seem to return the iobject, not
100% sure what that gives you without trying it.
But the sample script in the doc looks like a good place to start,
just set your row min/max and col min/max and you should be close to
getting something.
However, the sample program in the docs work as is. It should be
easy to adapt it for your needs. Try this as a start. The first bit is
pretty much out of the box and the last bit just shows you can extract
what row/column you want.
The data is at the end, load it into excel.
#! /usr/bin/perl
use warnings;
use strict ;
use Data::Dumper;
use Spreadsheet::ParseExcel;
my $excel =
Spreadsheet::ParseExcel::Workbook->Parse('c:\temp\filetest.xls');
foreach my $sheet (@{$excel->{Worksheet}}) {
printf("Sheet: %s\n", $sheet->{Name});
$sheet->{MaxRow} ||= $sheet->{MinRow};
foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
$sheet->{MaxCol} ||= $sheet->{MinCol};
foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
my $cell = $sheet->{Cells}[$row][$col];
if ($cell) {
printf("( %s , %s ) => %s\n", $row, $col,
$cell->{Val});
}
}
}
}
print "Extract specific row/cell row 2, col b \n" ;
foreach my $sheet (@{$excel->{Worksheet}}) {
my $row = 2;
my $col = 2;
my $cell = $sheet->{Cells}[$row][$col];
printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
}
Data to load in excel
1a,1b,1c,1d,1e,1f
2a,2b,3c,4d,2e,2f
3a,3b,3c,3d,3e,3f
4a,4b,4c,4d,4e,4f
Information in this email including any attachments may be privileged,
confidential and is intended exclusively for the addressee. The views expressed
may not be official policy, but the personal views of the originator. If you
have received it in error, please notify the sender by return e-mail and delete
it from your system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone. Please note we reserve the right to
monitor all e-mail communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and
are used under licence. British Sky Broadcasting Limited (Registration No.
2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home
Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of British Sky
Broadcasting Group plc (Registration No. 2247735). All of the companies
mentioned in this paragraph are incorporated in England and Wales and share the
same registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
RE: parsing a large excel file
> -Original Message- > From: ANJAN PURKAYASTHA [mailto:[EMAIL PROTECTED] > Sent: 22 July 2008 02:41 > To: beginners@perl.org > Subject: parsing a large excel file > > to all, > i have installed Spreadshee::ParseExcel to parse some large excel data > files. > Here is the problem I'm facing. I need to parse data from columns M to P > and > rows 10 to 43000. Now I know that there is a PrintArea method that can > print > an area of a worksheet specified in (start row, start col, end row, end > col). However I'm having difficulty in specifying these parameters > correctly > for the PrintArea method. I'm also not sure what the output is going to > look > like. > does anyone in this forum have any pointers? > all advice will be appreciated. > tia, > anjan > I have only used the Simple version previously to parse and entire row but the method Cell ( ROW, COL ) In the docs for the module you showed suggests you can get at the data directly. Stu Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trade marks of British Sky Broadcasting Group plc and are used under licence. British Sky Broadcasting Limited (Registration No. 2906991), Sky Interactive Limited (Registration No. 3554332), Sky-In-Home Service Limited (Registration No. 2067075) and Sky Subscribers Services Limited (Registration No. 2340150) are direct or indirect subsidiaries of British Sky Broadcasting Group plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
Re: parsing a large excel file
"ANJAN PURKAYASTHA" schreef: > i have installed Spreadshee::ParseExcel to parse some large excel data > files. > Here is the problem I'm facing. I need to parse data from columns M > to P and rows 10 to 43000. Now I know that there is a PrintArea > method that can print an area of a worksheet specified in (start row, > start col, end row, end col). However I'm having difficulty in > specifying these parameters correctly for the PrintArea method. I'm > also not sure what the output is going to look like. > does anyone in this forum have any pointers? To me it is not clear what you want. First you state that you want to parse M10..P43000, then you state that you want to do something with the PrintArea. What is it that you try to do, two things, or one? Do you want to import the data from the first(!) range that is in the current PrintArea? Or is the PrintArea just something you found out about, and that you think you need but that you actually don't? -- Affijn, Ruud "Gewoon is een tijger." -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] http://learn.perl.org/
parsing a large excel file
to all, i have installed Spreadshee::ParseExcel to parse some large excel data files. Here is the problem I'm facing. I need to parse data from columns M to P and rows 10 to 43000. Now I know that there is a PrintArea method that can print an area of a worksheet specified in (start row, start col, end row, end col). However I'm having difficulty in specifying these parameters correctly for the PrintArea method. I'm also not sure what the output is going to look like. does anyone in this forum have any pointers? all advice will be appreciated. tia, anjan -- = anjan purkayastha bioinformatics analyst whitehead institute for biomedical research nine cambridge center cambridge, ma 02142 purkayas [at] wi [dot] mit [dot] edu 703.740.6939
