ods files with external link lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- mtournay · 2015-03-12 08:18 Hi there Thanks for fpspreadsheet, as far as i can tell, it's working great ! I'm using laz 1.2.6 / fp 1.4.2 / win7x64 I have to open ods files with links to other files as sheet, with sheetname like '''file:///K:/3- Suivi%20des%20BOP/2015/Suivi%202015/BOP-224/Dotations-2015-BOP- 224.ods''#Dotations Arpège'. Actually, func TsWorkbook.AddWorksheet (line 6939 of fpspreadsheet.pas) raise an rsInvalidWorksheetName exception ! for my current dev, I test if name begins with file:// and change name to 'coucou'. I can make changes and send patch for different solutions (not 'coucou' of course ) a property for not follows links (most simple to add)if follow links is true, open the other file, get the sheet, .... I'm gonna work on first point this week. But I'm not sure of the way to deal with 2nd point. What do you think ? ---------------------------------------------------------------------- wp · 2015-03-12 10:29 The recent trunk version of fpspreadsheet supports hyperlinks of the kind that you mention. TsWorkbook does not "follow" the hyperlink, it just provides methods to handle this information in cells. The TsWorksheetGrid fires an event OnClickHyperlink in which you can do what you want. Note tha in contrast to Excel and Open/LibreOffice the hyperlink becomes active if the mouse is held down on the cell (not just the hyperlink text) for some tenths of a second; this behavior may change if I find an easy and better solution. See fpsctrls/demo_ctrls as an example of the event (which just shows a message box here). [EDIT] To be more specific: you want to add a worksheet with the sheet name ' ''file:///K:/3-Suivi%20des%20BOP/2015/Suivi%202015/BOP-224/Dotations- 2015-BOP-224.ods''#Dotations Arpège'? This is not a valid sheet name because of the / and : and the length limitation (see http://stackoverflow.com/questions/451452/valid-characters-for-excel- sheet-names). Just open LibreOffice or Excel and rename an existing sheet accordingly, it will not accept it. Of course, I could relax the error check but then you would not be able to store the workbook in a valid xls, xlsx and ods file (and csv ignores sheetnames anyway)... ---------------------------------------------------------------------- mtournay · 2015-03-12 14:22 thank you wp for your answer. I do not generate the ods file, I only have to read informations within some sheets. In my case, no writing is needed I try the trunk asap btw, I think the real name of this sheet is just 'Dotations Arpège' ---------------------------------------------------------------------- wp · 2015-03-12 16:06 Yes, the hyperlink syntax for files is "file:///path_to_file#bookmark". The "bookmark" in case of an ods file is a worksheet. The 'Dotations Arpège' should work. ---------------------------------------------------------------------- mtournay · 2015-03-13 16:15 Hi As I expected, the newer version didn't work either. For now, the modifications I made are added a property 'FollowLink' to TsWorkbook, initialized to falseadded test on name in TsWorkbook.AddWorksheet:  if name contains 'file://', returns nil if 'FollowLink' is false, returns correct name (after #) if FollowLink is trueadded test for AddWorksheet return nil in different readers I can create a patch from svn, where can I send it ? ---------------------------------------------------------------------- wp · 2015-03-13 16:25 QuoteI can create a patch from svn, where can I send it ?Either here, or to the bug tracker. Let's see what you have there. But I am very strongly against the worksheet to "follow" any links. But maybe your "follow" is not the same "follow" that I mean? To me "follow a link" is "open a link and display the linked file" - this would force a lot of dependencies on other software into fpspreadsheet, and it would make fpspreadsheet susceptible to security issues in the external files. There is certainly a better solution than modifying TsWorkbook. Please post an example showing exactly what you want to do. ---------------------------------------------------------------------- mtournay · 2015-03-13 16:47 QuoteTo me "follow a link" is "open a link and display the linked file" same for me Quotethis would force a lot of dependencies on other software into fpspreadsheet exactly... that's why for now i didnt even try to begin to think about this part ... I don't know the best solution, maybe a callback with the filename and let the dev choose what to do ? for the moment, it's time to go home, I come back tuesday ! ---------------------------------------------------------------------- wp · 2015-03-13 17:00 Is your operating procedure like this: - Open a spreadsheet file which contains hyperlinks to other files. - These other files are spreadsheet files as well, and contain a sheet which is indicated by the part of the hyperlinks following the #. - Open the linked file and display the sheet indicated by the hyperlink in an additional sheet of the currently loaded spreadsheet. - In total, more and more sheets will be added to the originally loaded file. ---------------------------------------------------------------------- mtournay · 2015-03-13 20:53 QuoteIs your operating procedure like this:Could be. The informations needed in my case are in original workbook, and external sheets doesn't have to be displayed. In case of displaying the sheets, you discribe, imho, the good way to deal with hyperlink. And managing links, maybe, has to be done directly in the reader, not in AddWorksheet : create an internal workbookseek to the linked worksheetget the sheet and add it to the current workbook currently @home, I don't have access to my source code & ods files, can't help more since tuesday ---------------------------------------------------------------------- wp · 2015-03-15 11:07 I uploaded a "hyperlinkdemo" project to fpspreadsheet trunk in which a workbook with links to other spreadsheet files is created. This file is read by the demo, and the linked sheets are copied to another workbook. http, mailto and internal links are ignored. This is probably what you want to do. ---------------------------------------------------------------------- mtournay · 2015-03-17 09:57 Hi there ! New behavior seems to be good, but I think I didn't expose the problem in the right way. my mistake. The hyperlink is in the sheet name , not in a cell ! ---------------------------------------------------------------------- wp · 2015-03-17 10:04 QuoteThe hyperlink is in the sheet name, not in a cell ! Then you don't have a valid sheet name for the well-known office applications - they don't accept a '/' and a ':' in the sheet name. Anyway, you won't need the hyperlink features of fpspreadsheet, in this case. Just parse the sheet name to extract the file name and sheet name of the linked file. Then - like in my example - load the linked file into a temporary workbook and copy the requested sheet into the destination workbook. You have all the building blocks ---------------------------------------------------------------------- mtournay · 2015-03-17 10:29 ok I take a look on what you did for hyperlinks and try to manage them in sheet names