Including Images - FPSpreadsheet

[wp]

What is strange?

[alexs75]

in demo from my previous message. (i'm update uploaded zip file)
i'm create image from lazarus TBimap, and i'ts show normal in all programs. But in ods file it's very big.

[wp]

I still don't understand... Here, all 3 images are shows to be equally-sized in the modified demo.

Are you sure that there is no magnification factor in your code? In the posted code, there is none, though. Please post a screenshot showing the incorrectly sized image, and post also the ods file.

What's wrong is that the images do not line up with the top/left cell corners. This is because LibreCalc does not expose its DefaultColWidth and DefaultRowHeight (at least I cannot find them...). Therefore, the absolute column and row positions of ods and fpspreadsheeet quickly run out of sync. This is normally no problem, but here it is a huge one because images, so far, are positioned relative to the sheet, not to the cell (see comment above).

[EDIT]
You can sync the ods column widths with fpspreadsheet calling Worksheet.WriteColWidth for each column up to the image. Same with row height:

Code: Pascal  [Select][+][-]

1. var
2.   r,c: Integer;
3. begin
4.   for r:=0 to 3 do Worksheet.WriteRowHeight(r, Worksheet.DefaultRowHeight);
5.   for c:=0 to 8 do Worksheet.WriteColWidth(c, Worksheet.DefaultColWidth);
6.   Worksheet.WriteImge(3, 8, stream);
7. end;

[alexs75]

I still don't understand... Here, all 3 images are shows to be equally-sized in the modified demo.

i'm rewrite demo

[wp]

Could you please post the original of this barcode image? I must look at it with the hex editor. I think its pixel density is not detected correctly.

[alexs75]

This image create in Lazarus.

Code: Pascal  [Select][+][-]

1.   FBmp:=TBitmap.Create;
2.   ...
3.   FBmp.SaveToFile('/home/alexs/aaa.bmp');
4.  

[wp]

You wanted this picture to have a pixel density of 100 dpi? But the units of pixel density in the bmp file format is "pixels per meter". Please use the value 3937 (= 100*100/2.54) when generating the bitmap and report back.

[alexs75]

thx.
I'm fix DPI problem - http://forum.lazarus.freepascal.org/index.php/topic,29697.0.html

[wp]

Please report to bugtracker and refer to this and the linked thread; software relying on the pixel resolution is fooled by the incorrect value.

[alexs75]

I'm write to Lazarus mantis:
http://bugs.freepascal.org/view.php?id=29852


I have one more question
On save image with scaling to xlsx and ods - the result is different - different size image.

[wp]

What a mess! I knew why I did not want to touch this initially...

Both applications position images differently (that's at least why I found out):

• OpenOffice uses sheet-absolute coordinates for the upper left corner of the image; the lower right corner has the image width and height added, i.e. floats.
• Excel uses coordinates relative to the cell containing the upper left corner of the image. In addition they also want the coordinates of the opposite corner relative to that cell containing it.
• fpspreadsheet has the upper left corner relative to the cell, but the lower right corner floating according to the image size.

Three ways of calculation - three different results...

Regarding your test project, OO displays the image as written by fps unless you change the width of a column (or height of a row) at the left of the (above) image (in fps the image moves with the cell while in OO is does not)

The Excel size, however, differs because it requires that fps calculates the lower right image corner correctly. This in turn depends on the column widths and row heights. Here again, there are discrepancies: I introduced column widths and row heights when I was working with the old xls format which specifies column widths as "character count" and row heights as "line count" - plus some ill-defined margins. This definition was introduced into fpspreadsheet. Later, Excel switched to more conventional units, such as centimeters or points. Because of the poor documentation, both methods do not match.

I am pretty sure that this argument is correct because when I adapt column widths and row heights such that the image does not extend into a neighbor cell, the image is displayed correctly also in Excel.

So, I think what will be next is a unit system for column widths and row heights. Unless we know for sure that column widths and row heights are the same in OO and Excel we cannot expect images to be equal.

[alexs75]

Good. I still will no longer ask stupid questions 
I am pleased with the results that I have obtained.
Thank you very much for your help.

[wp]

Sorry - the "mess" was not related to your questions, but to my code... Your questions are not "stupid" at all, I thank you for pointing me to these bugs.

[wp]

After some more thinking about the row height issue: It's not only the units, I also don't know how the Office applications calculate row heights in general: How do they depend on font size, type face, subscript/superscript, underlining, multiple lines, rotated text? Therefore, I think it is a waste of time to attempt to fix the image positioning issue. Instead, I added a paragraph to the wiki describing what to do if exact image locations are important (http://wiki.lazarus.freepascal.org/FPSpreadsheet#Images).

[wp]

Nevertheless I implemented size units for the workbook now. This means that row heights and column widths can be set and read using various units (millimeters, centimeters, inches, points); internally these sizes are stored in millimeters. The old line and character count units are still available.

I also modified the calculation of the image anchors, it was a floating point calculation before, now it uses approximate screen pixels. Surprisingly, now the Excel images are no longer distorted!

I'd expect some issues again on large screens having a high pixel-per-inch density. I would appreciate if somebody could test whether images extending across several rows and inserted into an Excel file by fps are distored on a high-ppi monitor or not. If they are the value "ScreenPixelsPerInch" defined in the fpsutils units and defaulting to 96 ppi should be adapted to the real value of the monitor used. Alexey's sample project above is a good test.