Script to append values into excel sheet, zip and send mail

[karthik.raju]

Hi Monks,

In Linux machine, I'm using the following code to write the text into excel sheet and trying to zip the same file.

#!/usr/perl/bin
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new("Dec_Logs.xlsx"); 
$worksheet = $workbook->add_worksheet(); 
push (@CommitDetails, ["1", "2", "3", "4", "5"]);
system("$worksheet->write_col('C1', \@CommitDetails)");
$worksheet->write_col('C1', \@CommitDetails);
system("zip Declogs.zip Dec_Logs.xlsx");

when i'm executing the above code, zip file is creating only with empty excel file.

But if i execute the system("zip Declogs.zip Nov_Logs.xlsx"), (Nov_Logs.xlsx is already exist) then it is giving the correct result.

can you please guide me on the mistake that i did in the above code. or is there any code which i can send excel sheet directly through mail from Linux machine??

Thanks,
Karthik

[marto]

system("$worksheet->write_col('C1', \@CommitDetails)");

No, you don't use system for this, the line which follows is on the right track.

"when i'm executing the above code, zip file is creating only with empty excel file."

You also want to read the module documentation carefully, e.g. close:

"An explicit close() is required if the file must be closed prior to performing some external action on it such as copying it, reading its size or attaching it to an email."

For emailing including attachments I use MIME::Lite frequently without issue. Some general points:

• http://learn.perl.org
• Tutorials
• Use strict and warnings

[karthik.raju]

Hi Marto,

Thank you very much for your answer,
by adding close() the script is working fine.
and i'm able to send the mails with format .txt, .pdf and .zip but i'm not able to send the excel files, so i'm zipping the excel files and sending through mail.
Thanks,
Karthik

[marto]

I'm glad that worked, however since you don't show us how you're sending the email nobody can help. The method I suggested should not have any issues sending any type of file.

[hippo]

system("$worksheet->write_col('C1', \@CommitDetails)");

What do you expect this statement to do? I'll wager it isn't doing what you suppose.

[FreeBeerReekingMonk]

Email will only work if your userid is allowed to send email.

There are 2 commands in Linux that can send email: mailx and sendmail

you would need to test them from the commandline first. If you manage to send email, these will work.

There is also a module that does what sendmail does, and only requires that your mailserver accepts incoming mail requests from you... which is sometimes tricky: Mail::Sendmail

[FreeBeerReekingMonk]

you can do an email.sh <myfile.ext> :

ATTACHMENT=$1
FNATT=`basename "$ATTACHMENT"`
EMAIL=[e-mail]
HOSTNAME=`hostname`
uuencode "$FNATT" "$ATTACHMENT" | mailx -s "$HOSTNAME $FNATT" $EMAIL

This is a fairly complete one (I really need to finish it):

fbrm@monastery:~/CODE/PERL/$ cat perl-mon/local/bin/emailto.sh
EMAILTO=$1
SUBJECT=$2
MSG=$3
FILE=${4:-""}

if [ -z "$EMAILTO" ] || [ -z "$SUBJECT" ] || [ -z "$MSG" ];then
        echo "$0 bad params."
        exit -1;
fi

#echo "EMAILTO=$EMAILTO"
#echo "SUBJECT=$SUBJECT"
#echo "MSG=$MSG"

MAILX=""
if [ -x "/usr/bin/mailx" ];then
        MAILX="/usr/bin/mailx"
elif [ -x "/usr/bin/mail" ];then
        MAILX="/usr/bin/mail"
fi

if [ ! -z "$MAILX" ];then
        if [  -z "$FILE" ];then
                #echo "$MSG  $MAILX -s $SUBJECT $EMAILTO"
                echo "$MSG" | $MAILX -s "$SUBJECT" "$EMAILTO"
        else
                FN=`basename "$FILE"`
                $(echo "$MSG\n" ; uuencode "$FILE" "$FN") | mailx -s "
+$SUBJECT" "$EMAILTO"
        fi
elif [ -x "/usr/bin/sendmail" ];then
        HOSTNAME=`hostname`
        if [ "$FILE" == "" ];then
                (
                  echo To: "$EMAILTO"
                  echo From: perlmon@$HOSTNAME
                #  echo "Content-Type: text/html; "
                  echo Subject: "$SUBJECT"
                  echo
                  echo "$MSG\n"
                #  cat tmp.html
                ) | sendmail -t
        else
                (
                  echo To: "$EMAILTO"
                  echo From: perlmon@$HOSTNAME
                #  echo "Content-Type: text/html; "
                  echo Subject: "$SUBJECT"
                  echo
                  echo "$MSG\n"
                  cat "$FILE"
                ) | sendmail -t
        fi
fi