How do i transfer data to an excel sheet?

170 New Member

hi to all,

i'm having problems with a perl programe, i'm very new to it.
currently my script output data to a excel file, however i need to group the datas into boxes, pls help

Jan 23 '08 #1

Subscribe Reply

2103

nithinpes

410

Recognized Expert Contributor

hi to all,

i'm having problems with a perl programe, i'm very new to it.
currently my script output data to a excel file, however i need to group the datas into boxes, pls help

Go through DBD::Excel and Spreadsheet::WriteExcel modules in CPAN. They may be of help.

Jan 23 '08 #2

poolboi

170

New Member

hm...so i have to install the spreadsheet::excel module right? if not it won't work

here's my program not sure if it will help u to see the problem

Expand|Select|Wrap|Line Numbers

 
#####    START OF MAIN PROGRAM    #####

#####    LIST OF PARAMETERS    #####
 
    @parameters = qw/MO /;
 
            # Please add in the neccessary fields here!!!

                                        # Field name is case-sensitive!!!
 
#####    WELCOME MESSAGE    ####
 
    print "\nWelcome to SMSC STATISTICS!\n";                    
 
#####    REQUEST NAME OF SMSC USED    #####
 
    print "\nPlease enter the name of the SMSC used (1, 2 , 3, 4 or 5):\n";

    $smsc_name = <STDIN>;

    $smsc_name = $smsc_name * 1;
 
    if ($smsc_name == "1")

    { $max_pk_msg = 140;}
 
    if ($smsc_name == "2")

    {$max_pk_msg = 40;}
 
    if ($smsc_name == "3")

    {$max_pk_msg = 100;}
 
    if ($smsc_name == "4")

    {$max_pk_msg = 80;}
 
    if ($smsc_name == "5")

    {$max_pk_msg = 100;}
 
#####    REQUEST NAME OF FILE USED        #####
 
    print "\nPlease enter the date used to compute the statistics <yymmdd : e.g. 020222>:\n";

    $file_name = <STDIN>;

    $rev_file_name = scalar reverse $file_name;

    $file_name = scalar reverse substr($rev_file_name,1);

    $file_name1 = "DR"."$file_name".".PRT";

    open (FILE1,"$file_name1") || die "Couldn't OPEN $file_name!!!\n\n";  
 
#####    REQUEST NAME OF ADDITIONAL FIELDS    #####
 
    $a = 0;
 
    do

    {

    print "\nPlease enter the name of the additional field to be computed (Case-Sensitive!)\n";

    print "(Press 1 to exit!):\n";

    $add_field = <STDIN>;

    $rev_add_field = scalar reverse $add_field;

    $add_field = scalar reverse substr($rev_add_field,1);
 
        if ($add_field == "1")

        {

        $a = 1;

        }
 
        if ($add_field != "1")

        {

        push @parameters, "$add_field";

        }
 
    }while ($a < 1);
 
#####    OPENING OF FILE    #####
 
    undef $/;

    open (FILE1,"$file_name1") || die "Couldn't OPEN $file_name!!!\n";      

    $_ = <FILE1>;

    $original = $_;                                              

    close(FILE1) || die "Couldn't CLOSE $file_name properly!!!\n";                    
 
    push @output, "Date,Total (IN),Succ (IN),Pk (IN)/Hrs,Pk (OUT)/Hrs,Peak Hour,Total (OUT),Succ (OUT),";
 
    foreach(@parameters)

    {

    push @output, "$_ (In),$_ (Out),";

    }
 
    push @output, "Pk Msg/sec,Max Pk Msg/sec\n";
 
#####    OBTAINING DATE    #####
 
    $length_date = index($original,"From (local)");                

    $date_line = substr($original,($length_date + 14));

    $rev_date = scalar reverse $date_line;                

    $date = scalar reverse substr($rev_date,-8);
 
    $day = scalar reverse substr($rev_date,-2);
 
    $date1 = scalar reverse $rev_date;

    $date2 = substr($date1,3);

    $rev_month = scalar reverse $date2;    

    $month = scalar reverse substr($rev_month,-2);
 
    $save_file_name= "smsc"."$smsc_name"."_"."$month"."$day".".xls";
 
#####    OBTAINING SUCC IN    #####
 
    $length_succ_in = index($original,"SUBMIT successful");    

    $succ_in_line = substr($original,($length_succ_in + 19));
 
    $length_succ_in1 = index($succ_in_line,"\n");    

    print "SUCC LENGTH=$length_succ_in1\n";

    $rev_succ_in = scalar reverse $succ_in_line;                

    $succ_in = scalar reverse substr($rev_succ_in,-$length_succ_in1);
 
#####    OBTAINING TOTAL IN    #####
 
    $length_total_in = index($succ_in_line,"Total");

    $total_in_line = substr($succ_in_line,($length_total_in + 7));
 
    $length_total_in1 = index($total_in_line,"\n");    

    $rev_total_in = scalar reverse $total_in_line;                

    $total_in = scalar reverse substr($rev_total_in,-$length_total_in1);
 
#####    OBTAINING SUCC OUT (Part 1)    #####
 
    $length_succ_out_p1 = index($succ_in_line,"DELIVER successful");    

    $succ_out_line_p1 = substr($succ_in_line,($length_succ_out_p1 + 20));
 
    $length_succ_out_p11 = index($succ_out_line_p1,"\n");    

    $rev_succ_out_p1 = scalar reverse $succ_out_line_p1;                

    $succ_out_p1 = scalar reverse substr($rev_succ_out_p1,-$length_succ_out_p11);
 
#####    OBTAINING TOTAL OUT (Part 1)    #####
 
    $length_total_out_p1 = index($succ_out_line_p1,"Total");    

    $total_out_line_p1 = substr($succ_out_line_p1,($length_total_out_p1 + 7));
 
    $length_total_out_p11 = index($total_out_line,"\n");    

    $rev_total_out_p1 = scalar reverse $total_out_line_p1;                

    $total_out_p1 = scalar reverse substr($rev_total_out_p1,-$length_total_out_p11);
 
#####    OBTAINING SUCC OUT (Part 2)    #####
 
    $length_succ_out_p2 = index($total_out_line_p1,"Status rep. deliv. successful");    

    $succ_out_line_p2 = substr($total_out_line_p1,($length_succ_out_p2 + 31));
 
    $length_succ_out_p21 = index($succ_out_line_p2,"\n");    

    $rev_succ_out_p2 = scalar reverse $succ_out_line_p2;                

    $succ_out_p2 = scalar reverse substr($rev_succ_out_p2,-$length_succ_out_p21);
 
#####    OBTAINING TOTAL OUT (Part 2)    #####
 
    $length_total_out_p2 = index($succ_out_line_p2,"Total");    

    $total_out_line_p2 = substr($succ_out_line_p2,($length_total_out_p2 + 7));
 
    $length_total_out_p21 = index($total_out_line,"\n");    

    $rev_total_out_p2 = scalar reverse $total_out_line_p2;                

    $total_out_p2 = scalar reverse substr($rev_total_out_p2,-$length_total_out_p21);
 
#####    OBTAINING SUCC OUT (Part 3)    #####
 
    $length_succ_out_p3 = index($total_out_line_p2,"Status rep. deliv. successful");    

    $succ_out_line_p3 = substr($total_out_line_p2,($length_succ_out_p3 + 31));
 
    $length_succ_out_p31 = index($succ_out_line_p3,"\n");    

    $rev_succ_out_p3 = scalar reverse $succ_out_line_p3;                

    $succ_out_p3 = scalar reverse substr($rev_succ_out_p3,-$length_succ_out_p31);
 
#####    OBTAINING TOTAL OUT (Part 3)    #####
 
    $length_total_out_p3 = index($succ_out_line_p3,"Total");    

    $total_out_line_p3 = substr($succ_out_line_p3,($length_total_out_p3 + 7));
 
    $length_total_out_p31 = index($total_out_line,"\n");    

    $rev_total_out_p3 = scalar reverse $total_out_line_p3;                

    $total_out_p3 = scalar reverse substr($rev_total_out_p3,-$length_total_out_p31);
 
#####    OBTAINING SUCC OUT     #####    
 
    $succ_out = $succ_out_p1 + $succ_out_p2 + $succ_out_p3;        
 
#####    OBTAINING TOTAL OUT     #####
 
    $total_out = $total_out_p1 + $total_out_p2 + $total_out_p3;
 
#####    OBTAINING SUBMIT PARAGRAPH    #####
 
    $length_submit = index($original,"Submissions by hour of day");    

    $submit = substr($original,$length_submit);
 
    $length_submit1 = index($submit,"Successful deliveries by application and hour of day");

    $rev_submit = scalar reverse $submit;                

    $submit_para = scalar reverse substr($rev_submit,-$length_submit1);
 
#####    OBTAINING DELIVERY PARAGRAPH        #####
 
    $length_delivery = index($original,"Successful deliveries by application and hour of day");    

    $delivery = substr($original,$length_delivery);
 
    $length_delivery1 = index($delivery,"Failed delivery attempts by application and hour of day");

    $rev_delivery = scalar reverse $delivery;                

    $delivery_para = scalar reverse substr($rev_delivery,-$length_delivery1);
 
#####    OBTAINING VARIOUS IN PARAMETERS        #####
 
    $l = 0;
 
    foreach(@parameters)

    {

    $length = length($_);

    $length_parameters = index($submit_para,$_);

    $parameters_req_para = substr($submit_para,$length_parameters);

    $rev_parameters = scalar reverse $submit_para;

    $parameters_line = substr($rev_parameters,-($length_parameters + $length));
 
    $length_parameters1 = index($parameters_line,"\n");

    $rev_parameters1 = scalar reverse $parameters_line;

    $parameters_line1 = substr($rev_parameters1,-$length_parameters1);
 
    $length_parameters2 = index($parameters_line1,$_) + $length;
 
    $length_total = index($parameters_req_para,"          Total ");

    $total_line = substr($parameters_req_para,$length_total);
 
    $rev_total = scalar reverse $total_line; 

    $total_line1 = scalar reverse substr($rev_total,-$length_parameters2);
 
    $parameter_in_value[$l] = substr($total_line1,-10);
 
    $parameter_in_array{$_} = $parameter_in_value[$l];
 
    $l++;

    }
 
#####    OBTAINING VARIOUS OUT PARAMETERS    #####
 
    $l = 0;
 
    foreach(@parameters)

    {

    $length = length($_);

    $length_parameters = index($delivery_para,$_);

    $parameters_req_para = substr($delivery_para,$length_parameters);

    $rev_parameters = scalar reverse $delivery_para;

    $parameters_line = substr($rev_parameters,-($length_parameters + $length));
 
    $length_parameters1 = index($parameters_line,"\n");

    $rev_parameters1 = scalar reverse $parameters_line;

    $parameters_line1 = substr($rev_parameters1,-$length_parameters1);
 
    $length_parameters2 = index($parameters_line1,$_) + $length;
 
    $length_total = index($parameters_req_para,"          Total ");

    $total_line = substr($parameters_req_para,$length_total);
 
    $rev_total = scalar reverse $total_line; 

    $total_line1 = scalar reverse substr($rev_total,-$length_parameters2);
 
    $parameter_out_value[$l] = substr($total_line1,-10);
 
    $parameter_out_array{$_} = $parameter_out_value[$l];
 
    $l++;

    }
 
#####     TESTING
 
#####    OBTAINING PEAK IN Traffics            #####
 
    print "Obtaining Peak AO\n";

    $length_pk_in = index($submit_para,"MO        AO   MO Flex   AO Flex     Total");    

#    $length_pk_in = index($submit_para,"Submissions by hour of day");    

    $pk_in_line = substr($submit_para,$length_pk_in);
 
    @hours = qw/00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23/;
 
    $l = 0;
 
        foreach (@hours)

        {

        $length_pk_in = index($pk_in_line,"$_ ");
 
        $pk_in_line = substr($pk_in_line,($length_pk_in + 2));
 
        $length_pk_in1 = index($pk_in_line,"\n");

        $rev_pk_in = scalar reverse $pk_in_line;                

        $apps_hours_in[$l] = scalar reverse substr($rev_pk_in,-$length_pk_in1);

        $apps_hours_in[$l] = substr($apps_hours_in[$l],44);

        $in_apps_hours{$apps_hours_in[$l]} = $_;
 
        print "apps_hours_in[$l] as $apps_hours_in[$l]\n";

        $l++;

        }
 
    sub numerically { $b <=> $a; }
 
    @pk_in_apps = sort numerically @apps_hours_in;
 
    $pk_in = $pk_in_apps[0];

    $pk_hour = $in_apps_hours{$pk_in_apps[0]} * 100;

    $pk_hour_dur = $pk_hour + 100;
 
        if ($pk_hour_dur == "2400") 

        { 

        $pk_hour_dur = "0000";

        }

    print "Peak IN (MO+AO) in as $pk_in at $pk_hour.\n";

########################
 
#####    OBTAINING PEAK OUT            #####
 
    $length_pk_out = index($delivery_para,"All applications");    

    $pk_out_line = substr($delivery_para,$length_pk_out);
 
    @hours = qw/00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23/;
 
    $l = 0;
 
        foreach (@hours)

        {

        $length_pk_out = index($pk_out_line,"$_ ");

        $pk_out_line = substr($pk_out_line,($length_pk_out + 2));
 
        $length_pk_out1 = index($pk_out_line,"\n");

        $rev_pk_out = scalar reverse $pk_out_line;                

        $apps_hours_out[$l] = scalar reverse substr($rev_pk_out,-$length_pk_out1);

        $l++;

        }
 
    sub numerically { $b <=> $a; }
 
    @pk_out_apps = sort numerically @apps_hours_out;

    $pk_out = $pk_out_apps[0];
 
#####    OBTAINING PEAK MESSAGE/SEC        #####
 
    $pk_msg = $pk_in_apps[0] / 3600;
 
#####    PRINTING RESULT TO FILE            #####
 
    open (OUTPUT,">$save_file_name") || die "Couldn't OPEN $save_file_name!!!\n"; 
 
    push @output, "$date,$total_in,$succ_in,$pk_in,$pk_out,$pk_hour - $pk_hour_dur hr,$total_out,$succ_out,";
 
    foreach(@parameters)

    {

    push @output, "$parameter_in_array{$_},$parameter_out_array{$_},";

    }
 
    print OUTPUT @output;

    printf (OUTPUT "%.2f,$max_pk_msg\n",$pk_msg);
 
    close(OUTPUT) || die "Couldn't CLOSE $save_file_name properly!!!\n";                
 
    print "\nPlease remember to collect your file at $save_file_name!\n";

    print "\nThank you for using STARHUB SMSC STATISTICS!\n\n";         
 
#####    END OF PROGRAM                #####

Basically, the output is to an excel file....think i just need to modify the last part, yeah
thanks

;

Jan 24 '08 #3

poolboi

170

New Member

sorrie for the long post
but i pretty much figured out how to export to excel using win32::OLE
cos using Spreadsheet:Excel i have to install extra modules in

but now if i need to append data into my program using win32::OLE, is it possible?
pls advise guys, thanks

Jan 24 '08 #4

nithinpes

410

Recognized Expert Contributor

If your objective is to type the data into separate cells and format the output into columns and rows, keep in mind the action of following escape sequences in Excel sheet:

\t - will move to next adjacent cell(in the same row)
\n - will move to next line ( to next row)

In the this line of your code,

Expand|Select|Wrap|Line Numbers

 
push @output, "$date,$total_in,$succ_in,$pk_in,$pk_out,$pk_hour -$pk_hour_dur hr,$total_out,$succ_out,";

you are pushing the variables as a single comma separated string. If you want them to be separate elements inorder to print them in separate columns, modify it as below:

Expand|Select|Wrap|Line Numbers

 
push @output, ($date,$total_in,$succ_in,$pk_in,$pk_out,$pk_hour -$pk_hour_dur hr,$total_out,$succ_out);

To print the contents use:

Expand|Select|Wrap|Line Numbers

 
print "$_\t" foreach(@output);   

### instead of: print OUTPUT @output;

 print "\n" ; ## will move to next row

Apart from this, if you want to do advanced level of formatting like printing particular data into a specific cell, you have to use Spreadsheet::WriteExcel.

Note: If you want to pass all records in an array, you have to create a two-dimensional array. Each element in the array being a list of data to be printed in each row.

Jan 24 '08 #5

poolboi

170

New Member

ok i've actually done it in another way where particular values are assigned to specified cells using win32::OLE
here's what i've done:

Expand|Select|Wrap|Line Numbers

 
use Win32::OLE;
 
        # use existing instance if Excel is already running

        eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};

        die "Excel not installed" if $@;

        unless (defined $ex) {

            $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})

                    or die "Oops, cannot start Excel";

        }
 
        # get a new workbook

        $book = $ex->Workbooks->Add;

    $sheet = $book->Worksheets(1);
 
        # write a 2 rows by 3 columns range

        $sheet->Range("A1:J2")->{Value} = [['Date','Total (IN)','Succ (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ (OUT)','MO(IN)','MO(OUT)'],

                                           [$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];
 
    $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],

                      [$max_pk_msg,$pk_msg]];
 
    $sheet->Cells(3,10)->{Value} = [$save_file_name];

    foreach(@parameters)

    {

    $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];

    $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];

    }
 
        # print "XyzzyPerl"

        $array = $sheet->Range("A2:I1")->{Value};

        for (@$array) {

            for (@$_) {

                print defined($_) ? "$_|" : "<undef>|";

            }

            print "\n";

        }
 
        # save and exit
 
         $book->SaveAs ('C:\Documents and Settings\clong\Desktop\perl\$save_file_name.xls') ;

        undef $book;

        undef $ex;

now any idea if say i wanna save $save_file_name as i've declared earlier?
cos they just save it as $save_file_name
however i declared $save_file_name as smsc_0101 in my earlier program
advise pls?

thanks

Jan 25 '08 #6

nithinpes

410

Recognized Expert Contributor

ok i've actually done it in another way where particular values are assigned to specified cells using win32::OLE
here's what i've done:

Expand|Select|Wrap|Line Numbers

use Win32::OLE;

        # use existing instance if Excel is already running

        eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};

        die "Excel not installed" if $@;

        unless (defined $ex) {

            $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})

                    or die "Oops, cannot start Excel";

        }

        # get a new workbook

        $book = $ex->Workbooks->Add;

    $sheet = $book->Worksheets(1);

        # write a 2 rows by 3 columns range

        $sheet->Range("A1:J2")->{Value} = [['Date','Total (IN)','Succ (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ (OUT)','MO(IN)','MO(OUT)'],

                                           [$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];

    $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],

                      [$max_pk_msg,$pk_msg]];

    $sheet->Cells(3,10)->{Value} = [$save_file_name];

    foreach(@parameters)

    {

    $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];

    $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];

    }

        # print "XyzzyPerl"

        $array = $sheet->Range("A2:I1")->{Value};

        for (@$array) {

            for (@$_) {

                print defined($_) ? "$_|" : "<undef>|";

            }

            print "\n";

        }

        # save and exit

         $book->SaveAs ('C:\Documents and Settings\clong\Desktop\perl\$save_file_name.xls') ;

        undef $book;

        undef $ex;

now any idea if say i wanna save $save_file_name as i've declared earlier?
cos they just save it as $save_file_name
however i declared $save_file_name as smsc_0101 in my earlier program
advise pls?

thanks

Assign the value of $save_file_name as in your previous script.
Change this line in your code:

Expand|Select|Wrap|Line Numbers

 
   $book->SaveAs ('C:\Documents and Settings\clong\Desktop\perl\$save_file_name.xls') ;

to :

Expand|Select|Wrap|Line Numbers

 
   $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;

If you use single quotes, the string inside it will be taken as literal string. There won't be any substitutions.
Use double quotes for substiotution and escape the backslashes so that they are taken as actual backslash (to avoid misinterpretation with escape sequences).

Jan 25 '08 #7

nithinpes

410

Recognized Expert Contributor

Assign the value of $save_file_name as in your previous script.
Change this line in your code:

Expand|Select|Wrap|Line Numbers

 
   $book->SaveAs ('C:\Documents and Settings\clong\Desktop\perl\$save_file_name.xls') ;

to :

Expand|Select|Wrap|Line Numbers

 
   $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;

Jan 25 '08 #8

poolboi

170

New Member

thank you very much!

it work! right i need to know how to append my info now...any problems i will get back again..thanks once again

Jan 25 '08 #9

How do i transfer data to an excel sheet?

Similar topics