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 :
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 Terryuse 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;
|
---|
Back to
Seekers of Perl Wisdom