Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Two issues with XLSX reporting

by lihao (Monk)
on Sep 27, 2012 at 17:20 UTC ( #996053=perlquestion: print w/ replies, xml ) Need Help??
lihao has asked for the wisdom of the Perl Monks concerning the following question:

(1) I am using Excel::Writer::XLSX, one of the issues is that the function write_url() does not work well when there is any '&' on the URL. this looks OK if I change '&' to '&', I am not sure if this is a correct solution, the resulting XLSX file yields some waring information after being opened.

(2) need to send out daily report (i.e. report-yyyymmdd.xlsx) in an email attachment by using MIME::Lite. I tried setting Type as 'AUTO', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'. none of these are working and the attached file can not be opened properly. The file is OK if I scp to my local server and open it directly (not in email attachment).

I was using Spreadsheet::WriteExcel and Excel 97-2003, no above issues at all. but I do need some new functionality in Excel 2010. Any suggestions

many thanks in advance

lihao

Comment on Two issues with XLSX reporting
Re: Two issues with XLSX reporting
by MidLifeXis (Prior) on Sep 27, 2012 at 17:30 UTC

    Can you generate a short perl script that illustrates the issue? Include the version of E::W::XLSX that you are using, and please wrap your example in <code>...</code> tags.

    --MidLifeXis

      issues are: without adjusting '&', I will get a warning notification and the url won't work in the Excel. the email attachment won't work at all for my configurations with MIME::Lite. I am using perl 5.8.8 (servers are RHEL 5.8 or 6.2). Excel::Writer::XLSX version 0.51, MIME::Lite version 3.027. A sample is shown below, many thanks.

      #!/usr/bin/perl use strict; use warnings; use Excel::Writer::XLSX; use MIME::Lite; my $report_file = 'hyperlink.xlsx'; # Create a new workbook and add a worksheet my $workbook = Excel::Writer::XLSX->new($report_file); my $worksheet = $workbook->add_worksheet('Hyperlinks'); # Add the standard url link format. my $url_format = $workbook->add_format( color => 'blue', underline => 1, ); $worksheet->write( 'A1', 'Some Content'); $worksheet->write( 'A3', 'http://www.perl.com/?a=1&b=2', $url_format, +'Perl home with query string' ); $workbook->close(); my $msg = MIME::Lite->new( From => 'noreply@example.com', To => 'you@example.com', Subject => "Testing Report - 2012-09-27", Type => 'multipart/mixed' ); $msg->attach( Type => 'text/plain', Data => 'This is a testing email', ); $msg->attach( #Type => 'AUTO', #Type => 'application/vnd.ms-excel', Type => 'application/vnd.openxmlformats-officedocument.spre +adsheetml.sheet', Path => $report_file, Disposition => 'attachment', ); $msg->send(); __END__
Re: Two issues with XLSX reporting
by jmcnamara (Monsignor) on Sep 27, 2012 at 20:26 UTC

    The first issue is a bug. I'll post a fix for it shortly.

    I don't know what the second issue is. Does it happen when you use a file generated by Excel rather than by Excel::Writer::XLSX?

    Update: The first issue is fixed on the master branch in the Github repo and will make its way into version 0.52 in a few weeks.

    --
    John.

      Thank you John, I've got another issue fixed. the content_type should be 'application/zip' which resolved my issue.

        Lihao, here's what i used and it doesn't work: my $mail = MIME::Lite->new(
        From =>'abc@host.com',
        To =>'abc@host.com',
        #Cc =>'def@host.com',
        Subject =>'Hello!',
        Data =>"How's it goin', eh?",
        Type => 'multipart/mixed',
        content-type.charset => 'UTF-8',
        content-type => 'application/zip'
        );


        $mail->attach(
        Type => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        #Encoding => 'base64',
        Path => 'report.xlsx',
        #Filename => 'report.xlsx'
        Disposition => 'attachment'
        );

        please advise!
      I have one more question, can we make Sparklines (similar to Charts) with Excel::Writer::XLSX. that would be really cool if it does..:)

        Good to know about the application/zip type.

        Sparklines are on the TODO list but I don't have a timeframe for them, at the moment.

        --
        John.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://996053]
Approved by davies
Front-paged by MidLifeXis
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (4)
As of 2014-09-23 03:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (210 votes), past polls