Migration from 4 to 5: picture alignment and PixelWidth

[maz65]

Hello Lars, I have two additional questions I faced during migration from 4 to 5 version:

1) picture aligned with columns:

in version 4 I wrote something like this:

Code: Select all

  
           with XLS[0].DrawingObjects.Pictures.Add do
                 begin
                 PictureID := XLS[0].DrawingObjects.Pictures.Count;
                 PictureName := mp.Filename;
                 Col1 := 0;
                 Row1 := 0;
                 Col2 := 2;
                 Row2 := 5;
                 end;

the result was the picture was perfectly border aligned from row,col 0,0 leftmost to 2,5 rightmost

Now with:

Code: Select all

          Image := XLS[0].Drawing.InsertImage(strFileName, 0, 0, 0, 0, 1);
            Image.Col1 := 0;
            Image.Row1 := 0;
            Image.Col2 := 2;
            Image.Row2 := 5;

The image is aligned on the left values, bottom row 5 value also is ok, but not on the right of column 2 value (= col2 alignment is wrong)

2) Columns width Excel 2007

Code: Select all

XLS.clear;
XLS[0].Columns[0].PixelWidth :=70;
XLS.write;

When I open the file with Excel 2007, the column[0] width is not 70 but 63
Delphi version is 2007 and xlsreadwrite 5 version is the latest 5.20.15

Let me know, thanks
-Massimo

[maz65]

Here is the code for the simulation:

Code: Select all

var
    Image : TXLSDrawingImage;
begin
   XLS.Clear;
   XLS.FileName := 'c:\test.xlsx';
   XLS[0].Columns[1].PixelWidth := 100;
   Image := XLS[0].Drawing.InsertImage('C:\mypicture.jpg',0,1,0,0,1);
   Image.Col1 := 0;
   Image.Row1 := 0;
   Image.Col2 := 3;
   Image.Row2 := 5;
   XLS.Write;
end;

If I put a comment on the row

Code: Select all

XLS[0].Columns[1].PixelWidth := 100;

and save again, the picture is correctly aligned. So it seems the new vaue of the column is not refreshed while inserting the image.

And note the column width is 98 and not 100 if I open it with Excel 2007.

Let me know, thanks
-Massimo

[larsa]

Hello

1 If you want to align a right/bottom of picture to the cells, you have to set Col2Offs and Row2Offs to zero. Example:

Code: Select all

  Image := XLS[0].Drawing.InsertImage('Picture.jpg',1,1,0,0,1);
  Image.Col2 := 5;
  Image.Col2Offs := 0;
  Image.Row2 := 8;
  Image.Row2Offs := 0;

2. Don't use PixelWidth. Using pixels as measurement is becoming more and more useless because of monitors with different DPI.

[maz65]

Hello Lars,

about point 1 : everything is fine now, thanks !

about point 2: the question is not related to the monitor resolution, but if I set a column width = 100 using pixelwidth, when I open it with Excel 2007 / 2010 the column width is 98. Is there any correction I need to add to width calculation ? my suspect is I need to add both (left and right) frame thickness to obtain exact column width.
Which is the property of a cell / column , left / right frame thickness ?

Thanks & Regards.
-Massimo

[larsa]

Hello

Don't set columns widths by pixels as the width will be different on monitors with different dpi. Internally excel stores the width in units of characters based on a standard font.
If you receive 98 instead of 100 it's probably a roundoff error as the component converts pixels to character width.