Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Need help with inserting images to excel from db

by terrykhatri (Acolyte)
on Jun 24, 2014 at 16:22 UTC ( #1091070=perlquestion: print w/ replies, xml ) Need Help??
terrykhatri has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, Sorry but I am really a newbie !!! Need your help on inserting images in excel from the database Here is my script :
use strict; use warnings; use DBI; use Spreadsheet::WriteExcel; my $Summary; my $dbh = DBI->connect("DBI:Pg:dbname=northwind;host=localhost", "post +gres", "postgres", {'RaiseError' => 1}); my $workbook = Spreadsheet::WriteExcel->new("Employees.xls"); # Some common formats my $center = $workbook->add_format(align => 'center'); my $bold = $workbook->add_format(bold => 1); my $number = $workbook->add_format(num_format => '#,##'); my $heading = $workbook->add_format(bold => 1, fg_color => 'silver', p +attern => 1, border => 1, size => 11); my $worksheet = $workbook->add_worksheet("Summary"); $worksheet->add_write_handler(qr[\w], \&store_string_widths); my $col=0; my $i=0; foreach $i ("EmployeeID", "FirstName","LastName","Photo") { $worksheet->write(0, $col++, $i, $heading); } my $sth = $dbh->prepare(' SELECT "EmployeeID", "FirstName", "LastName", "Photo" FROM "Employees" ORDER BY "EmployeeID" '); $dbh->do("SET search_path to northwind") or die; $sth->execute(); my $row=1; while ($a = $sth->fetchrow_hashref()) { $worksheet->write($row,0, $a->{EmployeeID}, $number); $worksheet->write($row,1, $a->{FirstName}, $bold); $worksheet->write($row,2, $a->{LastName}, $bold); $worksheet->insert_image($row,3, $a->{Photo}); autofit_columns($worksheet); $row++; } ###################################################################### +######### ### ### Functions used for Autofit. ### ###################################################################### +########### ## ###################################################################### +########### ### ### Adjust the column widths to fit the longest string in the column. ### sub autofit_columns { my $worksheet = shift; my $col = 0; for my $width (@{$worksheet->{__col_widths}}) { $worksheet->set_column($col, $col, $width) if $wid +th; $col++; } } sub store_string_widths { my $worksheet = shift; my $col = $_[1]; my $token = $_[2]; return if not defined $token; # Ignore undefs. return if $token eq ''; # Ignore blank cells. return if ref $token eq 'ARRAY'; # Ignore array refs. return if $token =~ /^=/; # Ignore formula return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+) +)?$/; return if $token =~ m{^[fh]tt?ps?://}; return if $token =~ m{^mailto:}; return if $token =~ m{^(?:in|ex)ternal:}; my $old_width = $worksheet->{__col_widths}->[$col]; my $string_width = string_width($token); if (not defined $old_width or $string_width > $old_width) { $worksheet->{__col_widths}->[$col] = $string_width; } return undef; } sub string_width { return 0.9 * length $_[0]; } $workbook->close(); $dbh->disconnect(); exit;
If I use write instead of insert_image I get the raw binary garbage in Photo column of the excel sheet, and if I use insert_image I see the raw binary garbage on the terminal window where I execute the script and nothing in the Photo column of the excel sheet. There's apparently something I am not doing right, your help to get it right will be very much appreciated. Many thanks Terry

Comment on Need help with inserting images to excel from db
Download Code
Re: Need help with inserting images to excel from db
by wjw (Curate) on Jun 24, 2014 at 18:06 UTC

    Have you looked at this example? Here

    I did not examine your code, but that example should help you spot where the problem might lie...

    Hope that helps...

    ...the majority is always wrong, and always the last to know about it...

    Insanity: Doing the same thing over and over again and expecting different results...

    A solution is nothing more than a clearly stated problem...otherwise, the problem is not a problem, it is a facct

      Thanks !!
Re: Need help with inserting images to excel from db
by poj (Priest) on Jun 24, 2014 at 18:14 UTC
      Hi I know I will have to write it to a temp file/dir first and then pass it on the insert_image() method, since I am quite new in perl I don't know how to do that, if someone can write the necessary code for me and add it to my script that will be great. Thanks anyways for your help/suggestions. Rgds Terry
        while ($a = $sth->fetchrow_hashref()) { $worksheet->write($row,0, $a->{EmployeeID}, $number); $worksheet->write($row,1, $a->{FirstName}, $bold); $worksheet->write($row,2, $a->{LastName}, $bold); # Change directory and filename to suit. my $imgfile = './images/'.$a->{'EmployeeID'}.'.jpg'; # or png,bmp open OUT,'>',$imgfile or die "Could not open $imgfile : $!"; binmode OUT; print OUT $a->{'Photo'}; close OUT or die "Could not close $imgfile : $!"; $worksheet->insert_image($row,3, $imgfile); # delete file unlink($imgfile) or warn "Could not unlink $imgfile: $!";; autofit_columns($worksheet); $row++; }
        poj
Re: Need help with inserting images to excel from db
by AppleFritter (Curate) on Jun 24, 2014 at 18:15 UTC

    Spreadsheet::WriteExcel actually expects a filename to be passed to ->insert_image(), not raw image data. The following, fairly minimal example works for me:

    #!/usr/bin/perl use strict; use warnings; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("Employees.xls"); my $worksheet = $workbook->add_worksheet("Summary"); $worksheet->insert_image('A1', 'Lenna.png'); $workbook->close();

    Since you're pulling the image from a database, you may have to write it to a temporary file. That, or bug the module's author to add a function that takes image data and inserts it directly into the spreadsheet.

      Thanks !! an expert Monk named Poj helped me solve it.
Re: Need help with inserting images to excel from db
by terrykhatri (Acolyte) on Jun 29, 2014 at 10:17 UTC
    Hi Monks, What Poj suggested did not work, I tried everything including adding absolute path for the images dir, now I want to try a different approach i.e. to get the images first to a folder and pass that to the insert_image() method, for which I have to use COPY command using DBI which again giving errors this command works fine when executed on the command line but DBD::Pg is giving syntax error, here is my script :
    #!/usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect("DBI:Pg:dbname=northwind;host=localhost", "post +gres", "postgres", {'RaiseError' => 1}); my $empid = $dbh->prepare('SELECT DISTINCT("EmployeeID") as empid FROM + "Employees" ORDER BY 1'); $dbh->do("SET search_path to northwind") or die; $empid->execute(); while(my $ref = $empid->fetchrow_hashref()) { $dbh->do("SET search_path to nortwind") or die; $dbh->do("COPY (SELECT encode(\"Photo\", 'hex') FROM \"Employees\" WHERE \"EmployeeID\"='$ref->{'empid'}') TO '/home/postgres/scripts/images/'$ref->{'empid'}.hex'") o +r die; system("/usr/bin/xxd -p -r /home/postgres/scripts/images/'$ref->{'empi +d'}.jpg'"); } $dbh->disconnect(); exit;
    Here is the error :
    DBD::Pg::db do failed: ERROR: syntax error at or near "1." LINE 8: '/home/postgres/scripts/images/'1.hex' ^ at photo.pl li +ne 17. DBD::Pg::db do failed: ERROR: syntax error at or near "1." LINE 8: '/home/postgres/scripts/images/'1.hex' ^ at photo.pl li +ne 17.
    Please do help. Thanks Terry
      '/home/postgres/scripts/images/'$ref->{'empid'}.hex' ^ remove
      poj
        Hi Poj, I removed it and it works, many thanks !! it created all the images in images folder then I ran your script and it removed all the images and threw an error :
        Couldn't import /home/postgres/scripts/images/1.jpg: No such file or d +irectory at emp2.pl line 131.
        Which is the last line of the script ! Rgds Terry
Re: Need help with inserting images to excel from db
by terrykhatri (Acolyte) on Jun 29, 2014 at 10:31 UTC
    Here is the command line :
    northwind=# copy (SELECT encode("Photo", 'hex') from "Employees" where + "EmployeeID"=1) TO '/home/postgres/scripts/images/1.hex';

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2014-12-18 03:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (41 votes), past polls