CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Need help with inserting images to excel from db

 



terrykhatri531
User

Jun 24, 2014, 5:41 AM

Post #1 of 9 (2009 views)
Need help with inserting images to excel from db Can't Post

Hi guys.

Need your help once again on inserting images this time in excel from the database :



Here is my script :


Code
 
#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Spreadsheet::WriteExcel;

my $Summary;

my $dbh = DBI->connect("DBI:Pg:dbname=northwind;host=localhost", "postgres", "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', pattern => 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 $width;
$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.

May be Chris can help me :)


FishMonger
Veteran / Moderator

Jun 24, 2014, 7:04 AM

Post #2 of 9 (1945 views)
Re: [terrykhatri531] Need help with inserting images to excel from db [In reply to] Can't Post

The insert_image() method expects a filename, not the raw image data.

Generate a temp file of the image and pass that to the method.

However, once the temp file is deleted, the image in the spreadsheet may also get deleted. That's something you'll need to test.


terrykhatri531
User

Jun 24, 2014, 7:14 AM

Post #3 of 9 (1938 views)
Re: [FishMonger] Need help with inserting images to excel from db [In reply to] Can't Post

Hi, Can someone please give me the code to generate a temp file of the image and pass that to the method. Thanks for your help RgdsTerry


FishMonger
Veteran / Moderator

Jun 24, 2014, 7:38 AM

Post #4 of 9 (1920 views)
Re: [terrykhatri531] Need help with inserting images to excel from db [In reply to] Can't Post

Take a look at the File::Temp module.
http://search.cpan.org/~dagolden/File-Temp-0.2304/lib/File/Temp.pm


terrykhatri531
User

Jun 25, 2014, 7:07 AM

Post #5 of 9 (1707 views)
Re: [FishMonger] Need help with inserting images to excel from db [In reply to] Can't Post

Hi,

With help from someone I added the following routines to write images to a temp file :


Code
 
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++;
}




But its giving me the following error:




Code
 
Could not open ./images/1.jpg : No such file or directory at emp.pl line 62.




Please help, Thanks


FishMonger
Veteran / Moderator

Jun 25, 2014, 7:52 AM

Post #6 of 9 (1663 views)
Re: [terrykhatri531] Need help with inserting images to excel from db [In reply to] Can't Post

Why are you indenting the code like that? That is not how poj formatted it when (s)he gave it to you?

Is there any part of the script written by you, or are you expecting others here or on perlmonks or similar sites to do provide you with a fully tested and working solution?

I usually put in a lot of time helping others with their code, but they need to show that they are putting in at least as much time and effort. I am not seeing that in your case.

You need to create the images directory or put the file into an already existing directory where you have write access.


terrykhatri531
User

Jun 25, 2014, 8:02 AM

Post #7 of 9 (1650 views)
Re: [FishMonger] Need help with inserting images to excel from db [In reply to] Can't Post

Hi,

I am a perl newbie my friend, the scripts are put together by me, writing complex routines will take sometime, anyway since you are following that thread I did create a folder images but its still giving me the same error.

rgds

Terry


FishMonger
Veteran / Moderator

Jun 25, 2014, 8:19 AM

Post #8 of 9 (1635 views)
Re: [terrykhatri531] Need help with inserting images to excel from db [In reply to] Can't Post

Specify the full absolute path instead of a relative path.


terrykhatri531
User

Jul 6, 2014, 2:03 AM

Post #9 of 9 (297 views)
Re: [FishMonger] Need help with inserting images to excel from db [In reply to] Can't Post

Hi,

The code provided by Poj works except for the deleting part which I commented out.

Tks

Terry

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives