Need help with inserting images to excel from db

[terrykhatri]

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

[AppleFritter]

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.

[terrykhatri]

Thanks !! an expert Monk named Poj helped me solve it.

[poj]

It looks like you can't insert the image without writing it to a file first - see here
http://stackoverflow.com/questions/23117540/use-perl-gd-to-create-and-insert-image-directly-into-excel-workbook.
poj

[terrykhatri]

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

[poj]

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

[wjw]

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...

[terrykhatri]

Thanks !!

[terrykhatri]

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

[poj]

'/home/postgres/scripts/images/'$ref->{'empid'}.hex'
                               ^ remove

poj

[terrykhatri]

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

[terrykhatri]

Here is the command line :

northwind=# copy (SELECT encode("Photo", 'hex') from "Employees" where
+ "EmployeeID"=1) TO '/home/postgres/scripts/images/1.hex';