Axolot Data

Axolot Data
https://www.axolot.se/phpBB3/

Problem reading excel files with images

https://www.axolot.se/phpBB3/viewtopic.php?f=16&t=2393

Page 1 of 2

Problem reading excel files with images

Posted: Fri Jul 17, 2015 1:54 pm
by 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

Re: Problem reading excel files with images

Posted: Thu Jul 23, 2015 11:14 am
by 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;

Re: Problem reading excel files with images

Posted: Mon Jul 27, 2015 10:02 am
by 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

Re: Problem reading excel files with images

Posted: Tue Jul 28, 2015 8:18 am
by 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)?

Re: Problem reading excel files with images

Posted: Fri Aug 07, 2015 11:18 am
by 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.

Re: Problem reading excel files with images

Posted: Wed Aug 12, 2015 10:03 am
by larsa
Hello

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

Re: Problem reading excel files with images

Posted: Thu Aug 20, 2015 12:49 pm
by 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

Re: Problem reading excel files with images

Posted: Thu Aug 20, 2015 1:01 pm
by d3nton
Here is a screenshot of the result (if I open the text.xlsx file in excel):
http://workupload.com/file/J3HKJR9d

Re: Problem reading excel files with images

Posted: Thu Sep 03, 2015 1:15 pm
by d3nton
Hi!
Can you reproduce the problem with the code and the image file above?

Re: Problem reading excel files with images

Posted: Mon Sep 14, 2015 9:35 am
by 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.

Re: Problem reading excel files with images

Posted: Mon Sep 21, 2015 10:17 am
by 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

Re: Problem reading excel files with images

Posted: Wed Sep 23, 2015 7:15 am
by 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.

Re: Problem reading excel files with images

Posted: Mon Sep 28, 2015 6:48 am
by 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?

Re: Problem reading excel files with images

Posted: Mon Sep 28, 2015 9:37 am
by larsa
Hello

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

Re: Problem reading excel files with images

Posted: Fri Oct 02, 2015 8:40 am
by 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
All times are UTC
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited