Problem reading excel files with images

[d3nton]

Hi!

I am using the latest version 5.20.51: Reading an existing excel file that contains an image does not work.
Repro is simple:
Just create an excel file with an image and try to read the file:
lExcelFile := TXLSReadWriteII5.Create(nil);
lExcelFile.Filename := 'C:\test\test.xlsx';
lExcelFile.Read;

best regards
d3nton

[d3nton]

There seems to be several issues regarding images in excel files:
1. XLS.IsEmpty function returns true for both XLS and XLSX:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  XLS: TXLSReadWriteII5;
begin
  XLS := TXLSReadWriteII5.Create(nil);
  XLs.Filename := 'D:\test.xlsx';

  XLS.Insert(0);
  XLS.Items[0].Name := 'test1';
  XLS[0].Drawing.InsertImage('D:\test1.jpg', 0,0, 0, 0, 1);
  XLS[0].IsEmpty; <- returns true, should be false
end;

Also the indexes seem to be wrong for XLS files in TXLSReadWriteII5.AddImage97(AAnchor: TCT_TwoCellAnchor; const ASheetIndex: integer; const AColOffs,ARowOffs: double): boolean; as soon as you
insert two sheets with one image on each sheet at the same position e.g.0:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  XLS: TXLSReadWriteII5;
begin
  XLS := TXLSReadWriteII5.Create(nil);
  XLs.Filename := 'D:\test.xls';

  XLS.Insert(0);
  XLS.Items[0].Name := 'test1';
  XLS[0].Drawing.InsertImage('D:\test1.jpg', 0,0, 0, 0, 1);
  XLS.Insert(0);
  XLS.Items[0].Name := 'test2';
  XLS[0].Drawing.InsertImage('D:\test2.jpg', 0,0, 0, 0, 1);
  XLS.Write;
  FreeAndNil(xls);

2. XLs Files are corrupted:

Code: Select all

var
  XLS: TXLSReadWriteII5;
begin
  XLS := TXLSReadWriteII5.Create(nil);
  XLs.Filename := 'D:\test.xls';

  XLS.Insert(0);
  XLS.Items[0].Name := 'test1';
  XLS[0].Drawing.InsertImage('D:\test1.jpg', 0,0, 0, 0, 1);
  XLS.Write;
  FreeAndNil(xls);
end;

[larsa]

Hello

1. IsEmpty only checks if there are any cells. Other objects such as images are not checked. This is how it's intended to work.

2. You must use the Version property if you want to write XLS files. Setting the filename will not affect the file type that is written.

3. Images on the wrong sheet in XLS files is fixed in update 5.20.52

[d3nton]

1. Okay, thanks for the clarification.
2. Yes, I am sorry, I forgot that line in the code example. Stupid mistake
3. Thanks, works now!

Nevertheless there seems to be some unexpected results in some cases or I am doing something wrong:
1. When I write an image with scale factor 1, the image is always stretched:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  XLS: TXLSReadWriteII5;
  i: Integer;
begin
  XLS := TXLSReadWriteII5.Create(nil);
  XLS.Version := xvExcel2007;
  XLS.Filename := 'D:\test.xlsx';
  XLS.Insert(0);
  XLS[0].Name := 'test1';
  XLS[0].Drawing.InsertImage('D:\test1.jpg', 0,0, 0, 0, 1);
  XLS.Write;
  FreeAndNil(xls);
end;

2. Whats the difference between
XLS[0].Drawing.InsertImage('D:\test1.jpg', 0,0, 0, 0, 1);
and
XLS.items[0].Drawing.InsertImage('D:\test1.jpg', 0,0, 0, 0, 1)?

[d3nton]

In addition to my last comment regarding the stretched images:
If I manually correct the scaling via excel (via right click on the image -> Size and Properies > reset ) and compare the changes in the xml structure of the file I can see differences in xl/drawing1.xml:
The excel file which is produced with XLS ReadWrite has other values in tags <xdr:col> and <xdr:colOff> than the file which I have corrected with excel.
E.g. in my test
xlsReadWrite:
<xdr:col>3</xdr:col>
<xdr:colOff>361950</xdr:colOff>

Excel:
<xdr:col>2</xdr:col>
<xdr:colOff>666750</xdr:colOff>

Hope this helps.

[larsa]

Hello

I can't reproduce this. Can you send me the image?

[d3nton]

Yes, of course.
I can reproduce it with every image.
The easiest way to see the problem is to create a jpg with a colored square in Ms Paint and add this to an excel file using the code above.
-> The square is stretched to an rectangle (open the created text.xlsx and right click on the image in the excel file -> Size and Properties.. and take a look at the Height and Size).
Nevertheless here is my test1.jpg (zipped):
http://workupload.com/file/Xa56S4FX

[d3nton]

Here is a screenshot of the result (if I open the text.xlsx file in excel):
http://workupload.com/file/J3HKJR9d

[d3nton]

Hi!
Can you reproduce the problem with the code and the image file above?

[larsa]

Hello

No, I can't reproduce this. Your download don't works but if I create an image as you describes, it works correct.

[d3nton]

I have created a delphi project which demonstrates the issue.
You can download it from the link below:
http://fileshare.jam-software.de/share. ... id=0a3lDva

The zip file contains the source code as well as the used image file and the created excel file.
I have also added a screenshot of the excel file opened in Excel on my PC (I use Excel 2013 with default settings).

Best regards d3nton

[larsa]

Hello

The image still looks fine here. The only thing I can think of is that you may have changed the default column width. If this is true, images will be wrong and as the default column width is stored in excel, the component can't know what it is.

[d3nton]

Thats strange. I did not change the default column width.I am not sure what the default value is but if I open a new excel sheet the column width is 10,71 (80 Pixels). The default font set in the Excel options is Courier New, Size 8. I am not sure how to solve the problem. Do I have to change a setting in Excel or do I have to change the default column width in the XLS ReadWrite component?

[larsa]

Hello

There you got the problem. The default column width when not changed is 64 pixels.

[d3nton]

I made a clean install (delete old settings) and the default width is still 80 pixels (10,71), default font Calibri 11 (in my last post I wrote Courier New, which was wrong, sorry), We also have lots of VMs (Vista, Win7, Win 8 ) in our company with Excel 2007 / 2010 or 2013 installed and the default width is always 80 pixels. Could it be that the width changed in newer Excel version or that the width depends on the operating system or the language.
I found some articles which also mention that the default pixel width is 80 pixels:

http://100computertipsin100days.blogspo ... width.html

http://www.jeepyurongfu.com/490944461-w ... xcel-cell/

https://books.google.de/books?id=RC9Lhr ... ls&f=false

Maybe it depends on the excel version or the windows or language version?
Is there a way I can query the current default column width? Or even better, is there a way I can ensure that an image added to a sheet is not streched?

best regards d3nton