Recent

Author Topic: Including Images - FPSpreadsheet  (Read 18063 times)

Edson

  • Hero Member
  • *****
  • Posts: 1021
Including Images - FPSpreadsheet
« on: February 29, 2016, 02:05:06 am »
I have read that FPSpreadsheet doesn't support images. But I need to include images for header and footer. I would like to know if it is possible to do this with FPSpreadsheet, and if not, what alternatives exist.

I was thinking on modifying directly, the content of the "*.xlsx" file, including the image files and setting the XML files, to do this. But I would like to same routines that FPSpreadsheet uses, to create the "*.xlsx" file.

I'm using fpspreadsheet-1.6.0.


Thanks
Lazarus 1.6 - FPC 3.0.0 - x86_64-win64 on  Windows 7

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #1 on: February 29, 2016, 12:48:39 pm »
I can look into this issue once I'll be finished with my current TAChart work. When I did the headers/footers I saw that handling of images in headers/footers has quite a lot of aspects to consider.

Of course, you can try it yourself. You'll be finding the basic zipping procedures in TsSpreadOOXMLWriter.WriteToStream and TsSpreadOOXMLReader.ReadFromStream (of xlsxooxm.pas).
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #2 on: March 05, 2016, 12:44:54 am »
Basic functionality for adding images to worksheets has been added in one of the recent revisions. At the moment, there is only a writer for xlsx (no reading support). Just call
Code: Pascal  [Select]
  1.   worksheet.WriteImage(ARow, ACol, AFileName);
to insert the image in the specified file at the upper/left edges of the specified cell. Use optional parameters to offset the image from that edge (in millimeters), or to scale the image.

No fully tested, yet.

Just a preview on images in headers and footers: Use this code to add an image to the header (or footer), specify the left/center/right section by the parameter:
Code: Pascal  [Select]
  1.   worksheet.PageLayout.AddHeaderImage(AHeaderIndex, ASection, AFileName);
  2.   worksheet.PageLayout.AddFooterImage(AFooterIndex, ASection, AFileName);
  3.  
  4. // AHeaderIndex = 0..2 for firstpage (0), odd pages (1), even pages(2)
  5. // AFooterIndex = dto.
  6. // type TsHeaderFooterSection = (hfsLeft, hfsCenter, hfsRight);

But this is just the fpspreadsheet part. There is no writer at the moment because it's quite a pain to deal with the xlsx here,  I wonder how professional programmers can create such a mess... But I see how it works now. Be patient.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Edson

  • Hero Member
  • *****
  • Posts: 1021
Re: Including Images - FPSpreadsheet
« Reply #3 on: March 05, 2016, 05:02:45 pm »
Good. Everyday, fpspreadsheet is getting better and better... :D
Lazarus 1.6 - FPC 3.0.0 - x86_64-win64 on  Windows 7

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #4 on: March 08, 2016, 02:40:11 pm »
Please test. I added a couple of unit tests, and it seem to work now. But not: Only xlsx supported, and only writing (ods will probably follow, but I'm not sure about biff8 which will be a pain...)
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Edson

  • Hero Member
  • *****
  • Posts: 1021
Re: Including Images - FPSpreadsheet
« Reply #5 on: March 08, 2016, 08:17:57 pm »
Where do I download the library with these new changes?
Lazarus 1.6 - FPC 3.0.0 - x86_64-win64 on  Windows 7

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #6 on: March 08, 2016, 08:46:50 pm »
https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/

Download the snapshot, unfortunately the download contains all ccr projects. Extract only the components/fpspreadsheet folder.

Or (better) use svn. You find the address in the link above.
« Last Edit: October 04, 2018, 01:09:14 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

alexs75

  • Full Member
  • ***
  • Posts: 104
Re: Including Images - FPSpreadsheet
« Reply #7 on: March 09, 2016, 08:28:34 am »
Hi. Good work!

How to implement the export from the stream without an intervening write to the file?

I'm try to use export images in LazReport export (lrspreadsheetexport.lpk).

P.S.
Unit test not compiled on linux (Lazarus 1.7 r51867M FPC 3.1.1 x86_64-linux-gtk 2).



wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #8 on: March 09, 2016, 10:58:25 am »
Not sure if I understand what you mean: you want to use the image feature in LazReport? Please contact the author of LazReport, or submit a LazReport feature request to bugtracker.

The unit test is now skipped for non-Windows systems.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

alexs75

  • Full Member
  • ***
  • Posts: 104
Re: Including Images - FPSpreadsheet
« Reply #9 on: March 09, 2016, 11:15:11 am »
Need a function
Code: Pascal  [Select]
  1. function WriteImage(ARow, ACol: Cardinal; AImageStream: TStream; AOffsetX: Integer = 0; AOffsetY: Integer = 0; AScaleX: Double = 1.0; AScaleY: Double = 1.0): Integer;
similar
Code: Pascal  [Select]
  1. function WriteImage (ARRow, AWol: Cardinal; FileName: String; AOffsetX: Integer = 0; AOffsetY: Integer = 0; AScaleX: Double = 1.0; AScaleY: Double = 1.0): Integer;
for write image from Stream (for example: from the database memo field)


I am the author of LazReport to Spreasheet export component and I'm want to modify it to correctly export images from report to excell / libre office.

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #10 on: March 09, 2016, 11:26:27 am »
This makes things a bit more complicated because I cannot use the file extension any more for format detection. The format is needed to determine the image width and height from the file header for correct positioning of the image within the cell matrix - Excel is very restrictive on that. I guess I'll have to iterate through all supported formats and pick the first one for which the header matches.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #11 on: March 15, 2016, 10:06:10 pm »
Now with stream variants:
Code: Pascal  [Select]
  1. TsWorksheet:
  2.     function WriteImage(ARow, ACol: Cardinal; AFileName: String;
  3.       AOffsetX: Double = 0.0; AOffsetY: Double = 0.0;
  4.       AScaleX: Double = 1.0; AScaleY: Double = 1.0): Integer; overload;
  5.  
  6.     function WriteImage(ARow, ACol: Cardinal; AStream: TStream;      // <---
  7.       AOffsetX: Double = 0.0; AOffsetY: Double = 0.0;
  8.       AScaleX: Double = 1.0; AScaleY: Double = 1.0): Integer; overload;
  9.  
  10. TsPageLayout ( a member of TsWorksheet ):
  11.     function AddHeaderImage(AHeaderIndex: Integer; ASection: TsHeaderFooterSectionIndex;
  12.       const AFilename: String): Integer; overload;
  13.     function AddHeaderImage(AHeaderIndex: Integer; ASection: TsHeaderFooterSectionIndex;
  14.       AStream: TStream): Integer; overload;    // <---
  15.     procedure AddHeaderImage(AHeaderIndex: Integer; ASection: TsHeaderFooterSectionIndex;
  16.       AImageIndex: Integer); overload;
  17.  
  18.     function AddFooterImage(AFooterIndex: Integer; ASection: TsHeaderFooterSectionIndex;
  19.       const AFilename: String): Integer; overload;
  20.     function AddFooterImage(AFooterIndex: integer; ASection: TsHeaderFooterSectionIndex;
  21.       AStream: TStream): Integer; overload;    // <----
  22.     procedure AddFooterImage(AFooterIndex: Integer;
  23.       ASection: TsHeaderFooterSectionIndex; AImageIndex: Integer); overload;

Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

alexs75

  • Full Member
  • ***
  • Posts: 104
Re: Including Images - FPSpreadsheet
« Reply #12 on: March 16, 2016, 11:48:28 am »
Thank you for this function!

I'm find 2 errors:
- in position image after change row height. See demo - error on Page 2.
- strange size of exporting any image
« Last Edit: March 16, 2016, 01:36:22 pm by alexs75 »

wp

  • Hero Member
  • *****
  • Posts: 5842
Re: Including Images - FPSpreadsheet
« Reply #13 on: March 16, 2016, 02:14:50 pm »
Fixed in r4560.

Note that the current ods implementation anchors the images with respect to the page, not to the cell: if you change the width of a colum to the left of the image the image does not move. This is different from Excel behavior.

Does anybody know how an image can be anchored to a cell in ods? I am aware that there is an anchoring context menu in LibreOffice, and if I check the "keep aspect ratio" box (or similar) in the positioning properties then the image does what I want - but I don't see how the "keep aspect ratio" is written to the file.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

alexs75

  • Full Member
  • ***
  • Posts: 104
Re: Including Images - FPSpreadsheet
« Reply #14 on: March 16, 2016, 02:51:50 pm »
Thank!

Position work normal.
what about strange size of  image?