Image is not sharp after using insert_image()

[Sorin CONSTANTINESCU]

Hello everybody,

I want to code a Perl script that will insert images (PNG format -
they are actually some RRDTOOL graphs) into some Excel file using the
Spreadsheet::WriteExcel module.

I have the image on disc and it looks normal when opened with some
file viewer, or even with Firefox (file:///xxx). After it's inserted
in the Excel file, the image is not as clear as before, and my eyes
have a hard time reading it.

I have even tried converting it to BMP using ImageMagick's convert
command line utility, but I get the same result. Below are the details
of the files (PNG and BMP) as "identify" shows them:

=== cut here ===
847.bmp BMP 591x205 591x205+0+0 8-bit DirectClass 356kb
847.png[1] PNG 591x205 591x205+0+0 8-bit PseudoClass 10c 7.36kb
=== and here ===

If I copy+paste the same image from Firefox to the Excel file, the
difference between my inserted images and the copy+paste image is huge
(if needed, I can provide some examples (screenshots)).

Has anyone had this problem before?

Thanks!

[jmcnamara]

On Sep 1, 1:15 pm, Sorin CONSTANTINESCU <con...@gmail.com> wrote:
> Hello everybody,

> I have the image on disc and it looks normal when opened with some
> file viewer, or even with Firefox (file:///xxx). After it's inserted
> in the Excel file, the image is not as clear as before, and my eyes
> have a hard time reading it.

Hi,

Try the following pre-release version of Spreadsheet::WriteExcel, it
has a fix for this issue:

http://homepage.eircom.net/~jmcnamara/perl/prerel/Spreadsheet-WriteExcel-2.25.07.tar.gz

Let me know if it does/doesn't work.

John.
--

[Sorin CONSTANTINESCU]

Hi Jack!

Thanks very much for your quick reply. I have compiled and tested the
version you linked and the problem is indeed solved!

Have a very nice day! I certainly will :)

Thanks again!

//Sorin

[Rob Smeets]

Hi there,

I think I have the same problem: in excel the png images are shown
somewhat unclear, because the size is about 1 pixel off. When I order
Excel to restore the image to the original size, then everything is
okay.

Since I prefer to update through cpan, I was wondering when will this
version 2.25.07 be released there?


/Rob Smeets

[jmcnamara]

Hi,

The next full release of WriteExcel may not be available for another
month or two. Try the pre-release for now, at least to see if it fixes
your problem.

John.
--

[Rob Smeets]

Dear John,

I've installed the new version 2.25.07, coming from 2.25. The problem
is still there, but smaller.

The excel sheet I'm working on contains 2 unscaled png files, that are
PHP-generated charts (bar and pie) with some text in the same font as
the text in the excel file itself. Because of this text and the lines
in the charts the small differences in graph size create easily
visible differences.

In the previous WriteExcel version both where about 1 pixel off. Now
only one is wrong.

It is a 630x200 png file that ends up in Excel as 5,32 x 16,67 cm,
while the original size is stated as 5,29 x 16,67. Since the chart
contains text it is immediately visible that there is something wrong
because the font looks somewhat vague, compared to some nearby text in
the excel sheet that actually has the same font.

The other chart is 630x380 pixels, and it ends up correctly in the
excel sheet.

Maybe the details help in hunting down the cause of this problem.

They are included thusly:
$worksheet->insert_image($lastheaderrow + 3, 0, $settings
{'CHART1filename'}, 3, 3, 1, 1) ;
$worksheet->insert_image($row, 0, $settings{'CHART2filename'}, 3, 3,
1, 1) ;

The first chart almost fills up a merged range of 4 columns and 18
rows. 1px borders all around this merged range. The top-border
actually belongs to the row above, left and bottom borders are
attached to the merged range itself, right border is actually the left
border of the cells to the right.

The second chart almost fills the height of its row, and extends over
almost 4 columns, no merging. It has a top-border but that is the
bottom-border of the row above.

I've included all this stuff on borders, because when I started
reading your source code, I noticed that you have to transform the
positioning settings of WriteExcel into a different set of parameters.
A fine occasion for some off-by-one errors. So I've created a second
version of my excel script that doesn't make any borders: problem
remains.

I've merged the cells that the second chart covers: problem still
exists.

I've extended the height of the row in which the second graph is
included: problem vanishes. The chart is 200 pixels high, shifted
(3,3), and the height of the row is calculated by my script as:
int($settings{'CHART2height'} / 1.3333) + 5 ;

I've varied the height of the row:
int($settings{'CHART2height'} / 1.3333) + 1 ; => FINE
int($settings{'CHART2height'} / 1.3333) + 2 ; => FINE
int($settings{'CHART2height'} / 1.3333) + 3 ; => FINE
int($settings{'CHART2height'} / 1.3333) + 4 ; => ERROR: chart is
displayed 1 pixel too high
int($settings{'CHART2height'} / 1.3333) + 5 ; => ERROR: chart is
displayed 1 pixel too high
int($settings{'CHART2height'} / 1.3333) + 6 ; => FINE
int($settings{'CHART2height'} / 1.3333) + 7 ; => ERROR: chart is
displayed 1 pixel too high
int($settings{'CHART2height'} / 1.3333) + 8 ; => FINE
int($settings{'CHART2height'} / 1.3333) + 9 ; => FINE
int($settings{'CHART2height'} / 1.3333) + 10 ; => FINE
int($settings{'CHART2height'} / 1.3333) + 11 ; => ERROR: chart is
displayed 1 pixel too high
int($settings{'CHART2height'} / 1.3333) + 12 ; => FINE

Hmm: no repetition mod 3 or 4 (related to the 4/3 ratio between pixels
and excel-units).

Removing the border on the row above the chart does not seem to change
these results, it just enlarges the margin above the chart by 1 pixel.

I've looked at the "+6"-test in more detail by examing a screenshot
with Paint: excel says the row height is 156,00 (208 pixels), but it
contains the 200px chart, 3px margin above the chart en 6px below,
making 209 in total (the light grey cell borders are off by default in
this chart).

The "+4"-test: excel says rowheight=153,75 EU=205px. Paint says 206px.
So that is the same 1px difference as in the "+6"-test. However in one
case the graph-height is stretched by 1px and in the other it is not.

I hope this helps.


/Rob Smeets

[jmcnamara]

On Nov 2, 3:00 pm, Rob Smeets <jrsme...@gmail.com> wrote:
> Dear John,
>
> I've installed the new version 2.25.07, coming from 2.25. The problem
> is still there, but smaller.

Hi Rob,

Thanks for the detailed report.

I think that there is probably more than one issue here but the basic
problem is that due to the merged ranges or cell borders the height of
the rows is different from their default and as a result WriteExcel
doesn't calculate the correct image height.

The best thing to do is to send me the image that exhibits the problem
and a small WriteExcel program that inserts it into a merged range
(with or without a border) so that we can use it as a basis for
communication.

John.
--