wirte output in excel file

[realoneomer]

Hello,

I need help to write an output of perl script in a excel file on daily basis. I want to maintain a sheet of excel file like history data that file would contain the history output of all last runs.

Although i have used perl formatting to write data in a text files but i also want to write the same output data in an excel file.

here is the sample data that i want to write in an excel file. Moreover i want to manitain two sheets in an excel file (i.e., sheet1 & sheet2) sheet1 will contain all the job History data and it would be looks like

Jobs History (This is a heading and the back grouop i want to put set in orange)

Date Time Job Group Total Jobs# (columns name backgroup will be orange
Current system date Current system time A 4
Current system date Current system time B 5
Current system date Current system time C 89
Current system date Current system time D 86
Current system date Current system time E 52

and sheet2 will looks like

Jobs Movement Sheet2 Headin backgroup should be orange

Movement Date Movement Time Job ID Moved from Moved to (Columns name background should be orange)
Current system date Current system time 12 A E
Current system date Current system time 15 B G
Current system date Current system time 17 C H
Current system date Current system time 18 D I
Current system date Current system time 19 F J

Pls help me out to done this

Thanks in advance

[shibblez]

http://search.cpan.org/search?query=WriteExcel&mode=all

I've successfully used Spreadsheet::WriteExcel in the past.

[shibblez]

Try

http://search.cpan.org/search?query=WriteExcel&mode=all

I've successfully user Spreadsheet::WriteExcel in the past.

[realoneomer]

Hello guys,

Thanks for your support but i want to write a code for linux which means this script would be running from Linux machine and it will copy the excel sheet from Linux to Windows. Pls help me out

Thanks

[shibblez]

I've used Spreadsheet::WriteExcel on HP-UX and Linux without any problems, generating a valid Excel spreadsheet.

What options do you have for the transfer to windows?

Does the windows box export a share that can be mounted using SAMBA? Is it running as a NFS server? Has it got a SSH server running?

[realoneomer]

Hi Shibblez,

Thanks for your response...
i have used Spreadsheet::WriteExcel but it is giving me an error that WriteExcel.pm does not exist @INC more over i cannot install any additional software or patch on Linux Machine...Well i first i want to generate an excel file on linux after that i will transfer file using ftp or SFTP from Linux to Windows.

What do you think FTP will work out or not..?

Thanks

[realoneomer]

Hi Shibblez,

I have installed Spreadsheet::WriteExcel Module through PPM and also an excel file has been generated through perl but every time when i run perl program its generate a new excel sheet with current data i want to append data in an excel file. Can you please help me out

[d5e5]

Hi Shibblez,

Thanks for your response...
i have used Spreadsheet::WriteExcel but it is giving me an error that WriteExcel.pm does not exist @INC more over i cannot install any additional software or patch on Linux Machine...Well i first i want to generate an excel file on linux after that i will transfer file using ftp or SFTP from Linux to Windows.

What do you think FTP will work out or not..?

Thanks

If you can copy your script to the Linux server it seems to me you can install Spreadsheet::WriteExcel to that server. As a test, I just used CPAN Minus to install Spreadsheet::WriteExcel on my Ubuntu 9.10 system as a non-root user and I didn't have to use sudo.

david@david-laptop:~$ cpanm install Spreadsheet::WriteExcel
install is up to date. (0.01)
--> Working on Spreadsheet::WriteExcel
Fetching http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/Spreadsheet-WriteExcel-2.37.tar.gz ... OK
Configuring Spreadsheet-WriteExcel-2.37 ... OK
==> Found dependencies: OLE::Storage_Lite, Parse::RecDescent
--> Working on OLE::Storage_Lite
Fetching http://search.cpan.org/CPAN/authors/id/J/JM/JMCNAMARA/OLE-Storage_Lite-0.19.tar.gz ... OK
Configuring OLE-Storage_Lite-0.19 ... OK
Building and testing OLE-Storage_Lite-0.19 ... OK
Successfully installed OLE-Storage_Lite-0.19
--> Working on Parse::RecDescent
Fetching http://search.cpan.org/CPAN/authors/id/D/DC/DCONWAY/Parse-RecDescent-1.965001.tar.gz ... OK
Configuring Parse-RecDescent-1.965001 ... OK
Building and testing Parse-RecDescent-1.965001 ... OK
Successfully installed Parse-RecDescent-1.965001
Building and testing Spreadsheet-WriteExcel-2.37 ... OK
Successfully installed Spreadsheet-WriteExcel-2.37

Also have a look at How do I keep my own module/library directory?

[realoneomer]

Hi d5e5,

Thanks for your response..

i have installed SS::WS on my laptop just for tesing purpose it is working fine so far but now the problem is i want to append data in an excel file every time when i execute my code it deletes old data and write a new data in excel file.
Can you please help me out with this..?

Thanks

[d5e5]

I haven't used Spreadsheet::WriteExcel, just installed it to test that it can be installed on Linux without root permissions. The documentation that comes with it says

This module cannot be used to write to an existing Excel file (See
"MODIFYING AND REWRITING EXCEL FILES").

[realoneomer]

Hi d5e5,

its mean i have waited my time with Spreadsheet::WriteExcel Module because it can only work with new data and will delete old data then how i can append data in excel file i have been tired now pls help me out

Thanks

[realoneomer]

Hi d5e5,

I have searched out and find that a perl Module Spreadsheet::ParseExcel allow us to append data in an excel file although i have tried so many times but i am unable to get any usefull results.
Do you have any idea that how i can do append data using Spreadsheet::ParseExcel module...?

Thanks

[d5e5]

Hi d5e5,

I have searched out and find that a perl Module Spreadsheet::ParseExcel allow us to append data in an excel file although i have tried so many times but i am unable to get any usefull results.
Do you have any idea that how i can do append data using Spreadsheet::ParseExcel module...?

Thanks

Sorry, I really don't know. I don't even have Excel software on my computer.

Are you sure that you have to append data to your spreadsheet? Why not save your data in a database or even a text file which your Perl program can read and process to create a new spreadsheet with all the historical and new data? I am not an expert with Excel but it seems to me that having Perl save and process data in database or text files and then creating a new spreadsheet for users to view would be simpler than having Perl save, read and modify an existing Excel worksheet.

Maybe I don't understand your requirements but it seems that the Excel spreadsheet can be useful for viewing or presenting your output, but not for saving or processing it.

[realoneomer]

Hi d5e5,

Thanks for your response..i will try to make it possible and will let you know but for the time being i have one more question related to writing data in text file..

I am writing a data in text file through perl and i am using perl formatting to write the data now when i run the code the columns header also write every time in a text file i only want to disply the column header only one time and after that when ever i execute my perl script only columns values print into a text file. that file will act as a history data
Can you pls help me out with this...?

Thanks

[d5e5]

I've haven't used format to write from Perl yet (I am only an amateur in Perl.) I'll look it up in perldoc when I get a chance, but meanwhile please ask that question as a new thread. Maybe somebody else who knows the answer will notice the question if they see it as a title for a new thread.

[d5e5]

Hi d5e5,

Thanks for your response..i will try to make it possible and will let you know but for the time being i have one more question related to writing data in text file..

I am writing a data in text file through perl and i am using perl formatting to write the data now when i run the code the columns header also write every time in a text file i only want to disply the column header only one time and after that when ever i execute my perl script only columns values print into a text file. that file will act as a history data
Can you pls help me out with this...?

Thanks

Test if the file you want to write to exists, if it does not exist then you want to write a header, otherwise you do not want a header. To prevent the write command from writing a header, declare an empty format that will write nothing as a header, select the filehandle to which you want to write and set the value of $^ to the name of the empty format.

#!/usr/bin/perl
use strict;
use warnings;
use 5.010;

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();
$year = $year + 1900;
$mon = $mon + 1;
my $yymmdd = "$year-$mon-$mday";
my $hhmnss = "$hour:$min:$sec";
my $grp;
my $jc;

my $header_wanted = 0; # 0 means false, 1 means true
my ($date_hdr, $time_hdr, $jg_hdr, $tj_header) = ('Date', 'Time', 'Job Group', 'Total Jobs#');

format STDOUT_TOP =
@<<<<             @<<<<           @<<<<<<<<<         @<<<<<<<<<<<
$date_hdr,        $time_hdr,      $jg_hdr,           $tj_header
.

format EMPTY_TOP =
.

format STDOUT =
@<<<<<<<<<<      @<<<<<<<<          @>               @#######
$yymmdd,         $hhmnss,           $grp,            $jc
.

unless ($header_wanted){
    $^ = 'EMPTY_TOP';
}

foreach $grp(qw(A B C D E F G H)){
    $jc++;
    write;
}

See http://perldoc.perl.org/functions/write.html