Including Images - FPSpreadsheet lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Edson · 2016-02-29 01:05 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 ---------------------------------------------------------------------- wp · 2016-02-29 11:48 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). ---------------------------------------------------------------------- wp · 2016-03-04 23:44 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   worksheet.PageLayout.AddHeaderImage(AHeaderIndex, ASection, AFileName);  worksheet.PageLayout.AddFooterImage(AFooterIndex, ASection, AFileName); // AHeaderIndex = 0..2 for firstpage (0), odd pages (1), even pages(2)// AFooterIndex = dto.// 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. ---------------------------------------------------------------------- Edson · 2016-03-05 16:02 Good. Everyday, fpspreadsheet is getting better and better... ---------------------------------------------------------------------- wp · 2016-03-08 13:40 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...) ---------------------------------------------------------------------- Edson · 2016-03-08 19:17 Where do I download the library with these new changes? ---------------------------------------------------------------------- wp · 2016-03-08 19:46 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. ---------------------------------------------------------------------- alexs75 · 2016-03-09 07:28 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 · 2016-03-09 09:58 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. ---------------------------------------------------------------------- alexs75 · 2016-03-09 10:15 Need a function Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2016-03-09 10:26 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. ---------------------------------------------------------------------- wp · 2016-03-15 21:06 Now with stream variants: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TsWorksheet:    function WriteImage(ARow, ACol: Cardinal; AFileName: String;      AOffsetX: Double = 0.0; AOffsetY: Double = 0.0;      AScaleX: Double = 1.0; AScaleY: Double = 1.0): Integer; overload;     function WriteImage(ARow, ACol: Cardinal; AStream: TStream;      // <---       AOffsetX: Double = 0.0; AOffsetY: Double = 0.0;      AScaleX: Double = 1.0; AScaleY: Double = 1.0): Integer; overload; TsPageLayout ( a member of TsWorksheet ):    function AddHeaderImage(AHeaderIndex: Integer; ASection: TsHeaderFooterSectionIndex;      const AFilename: String): Integer; overload;    function AddHeaderImage(AHeaderIndex: Integer; ASection: TsHeaderFooterSectionIndex;      AStream: TStream): Integer; overload;    // <---    procedure AddHeaderImage(AHeaderIndex: Integer; ASection: TsHeaderFooterSectionIndex;      AImageIndex: Integer); overload;     function AddFooterImage(AFooterIndex: Integer; ASection: TsHeaderFooterSectionIndex;      const AFilename: String): Integer; overload;    function AddFooterImage(AFooterIndex: integer; ASection: TsHeaderFooterSectionIndex;      AStream: TStream): Integer; overload;    // <----    procedure AddFooterImage(AFooterIndex: Integer;      ASection: TsHeaderFooterSectionIndex; AImageIndex: Integer); overload; ---------------------------------------------------------------------- alexs75 · 2016-03-16 10:48 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 ---------------------------------------------------------------------- wp · 2016-03-16 13:14 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. ---------------------------------------------------------------------- alexs75 · 2016-03-16 13:51 Thank! Position work normal. what about strange size of  image? ---------------------------------------------------------------------- wp · 2016-03-16 14:11 What is strange? ---------------------------------------------------------------------- alexs75 · 2016-03-16 15:25 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 · 2016-03-16 16:25 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  r,c: Integer;begin  for r:=0 to 3 do Worksheet.WriteRowHeight(r, Worksheet.DefaultRowHeight);  for c:=0 to 8 do Worksheet.WriteColWidth(c, Worksheet.DefaultColWidth);  Worksheet.WriteImge(3, 8, stream);end; ---------------------------------------------------------------------- alexs75 · 2016-03-17 05:37 QuoteI still don't understand... Here, all 3 images are shows to be equally-sized in the modified demo. i'm rewrite demo ---------------------------------------------------------------------- wp · 2016-03-17 09:45 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 · 2016-03-17 10:21 This image create in Lazarus. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   FBmp:=TBitmap.Create;  ...  FBmp.SaveToFile('/home/alexs/aaa.bmp'); ---------------------------------------------------------------------- wp · 2016-03-17 10:50 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 · 2016-03-17 11:58 thx. I'm fix DPI problem - http://forum.lazarus.freepascal.org/index.php/topic,29697.0.html ---------------------------------------------------------------------- wp · 2016-03-17 12:08 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 · 2016-03-17 12:28 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 · 2016-03-17 13:18 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 · 2016-03-17 14:00 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 · 2016-03-17 14:11 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 · 2016-03-17 16:15 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 · 2016-03-19 21:54 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. ---------------------------------------------------------------------- Mando · 2017-02-02 11:15 Hi all. I'm desperate. I want to modify an excel xlxs file, inserting images in column 0 of all rows. That file has several cols an rows with data. Col 0 for imagez, col is used to obtain the file name of the image. I can read the file correctly, resize rows for write the images and save back the file. When i open the file with libreoffice calc , the images are distorted in height, also the height of them is growing with the row number. The height of image in row 0 its almost correct, in row 1 is a little bigger, in row 2 a little more bigger an so...the last image is enormous in height. If i read the original file to obtain filenames of images and insert images in a new tworkbook, and save it, the result is ok. All images are correctly in size, position and scale. If i write somedata more, p.e. the filename of image in col 1, and save the new file, the images are wrong sized(in heigh) again. I workarround playing whith y-scale, but its is relative to the height of the row  If scale value is greater than 0.12 the image is escaled, but if it is lower, then the image height is set to the scale value. This behaviour is the same if the result file is .ods or .xlxs Help, help....i dont understand  nothing. Regards ---------------------------------------------------------------------- wp · 2017-02-02 12:10 I suppose this issue is related to the mess I refered to above (http:/ /forum.lazarus.freepascal.org/index.php/topic,31740.msg205631.html#msg 205631), and I fear there's nothing I can do about it. But let's give it a try. Please publish the code which you use to create the Excel file along with a few pictures which are to be embedded (pack lpi, lpr, lfm, pas and required image files into a shared zip and upload it under "Attachments and other options"). Without exactly knowing what you do there's no chance to help you. ---------------------------------------------------------------------- Mando · 2017-02-02 13:49 Ok, thanks. I'm not at work right now. Later i'll post the project and images. Thanks again. ---------------------------------------------------------------------- paweld · 2017-04-11 18:11 Hi WP, i have that's same problem as Mando. Error  with a image y scale occurs when insert to one row image and text, but if a row contains only image, then image scale it's ok. Example in attachment. fpspreadsheet trunk, Lazarus trunk with fpc 3.0.2. ---------------------------------------------------------------------- wp · 2017-04-11 20:02 Thanks for the demo. Fixed in r5832. If you don't use trunk you should open fpspreadsheet.pas and replace the function TsWorksheet.CalcRowHeight by this code: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function TsWorksheet.CalcRowHeight(ARow: Cardinal): Single;// In workbook unitsvar  r: PRow;begin  r := FindRow(ARow);  if (r <> nil) and (r^.RowHeightType = rhtCustom) then    Result := GetRowHeight(ARow, FWorkbook.Units)  else  begin    Result := CalcAutoRowHeight(ARow);  if Result = 0 then      Result := GetRowHeight(ARow, FWorkbook.Units);  end;end; P.S. This is included in the new release version 1.8.2. ---------------------------------------------------------------------- paweld · 2017-04-12 07:50 Thanks very much for quick reply. Now it's ok. ---------------------------------------------------------------------- Moneo · 2018-10-04 11:05 I will revive the topic. How can I attach a hyperlink to an inserted image? ---------------------------------------------------------------------- wp · 2018-10-04 11:26 This is not possible at the moment. ---------------------------------------------------------------------- Moneo · 2018-10-04 11:55 How can i cheat with hyperlink & image? ---------------------------------------------------------------------- wp · 2018-10-04 14:42 I added a hyperlink field to the TsImage record. Call the worksheet method AddHyperlinkToImage to add a hyperlink to an existing image. The image is identified by its index in the internal image list which is returned by WriteImage. So far, only writing to ODS (LibreOffice) is supported. [EDIT] Now, XLSX works too. See this demo: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program demo_image; {$mode objfpc}{$H+} uses  fpspreadsheet, fpstypes, fpsutils, fpsopendocument, xlsxooxml; var  b: TsWorkbook;  sh: TsWorksheet;  img: Integer; {$R *.res} begin  b := TsWorkbook.Create;  try    sh := b.AddWorksheet('Test');    img := sh.WriteImage(0, 0, '..\..\images\components\TSWORKSHEETGRID_200.PNG');    sh.AddHyperlinkToImage(img, 'http://www.lazarus-ide.org/');    b.WriteToFile('test.ods', sfOpenDocument, true);    b.WriteToFile('test.xlsx', sfOOXML, true);  finally    b.Free;  end;end. ---------------------------------------------------------------------- wp · 2018-10-06 11:26 As already noted in the previous post, the XLSX and ODS writers now can handle images with hyperlinks. The ODS format, also, can read such files. This is not possible for the XLSX format ATM which cannot even read images (the guy who invented their specification must have been a sadist...).