Without absolute path, and set author [SOLVED] lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- totya · 2020-07-26 11:32 Hi master! If I run the Office 2013 built in document checker on the fps created xlsx file, the result about: Document properties and personal data: "absolute path found" My question, can I save the file without absolute path? Second, can I set "author" in the xlsx file? Thank you! ---------------------------------------------------------------------- wp · 2020-07-26 12:33 No idea what Excel means here. AFAIK a standard FPSpreadsheet-written file does not contain any paths. The author field, ATM, is ignored. Do you need it? ---------------------------------------------------------------------- totya · 2020-07-26 12:38 Quote from: wp on July 26, 2020, 02:33:52 pmNo idea what Excel means here. AFAIK a standard FPSpreadsheet-written file does not contain any paths. The author field, ATM, is ignored. Do you need it? Yes, I need it if possible. Thank you! ---------------------------------------------------------------------- totya · 2020-07-27 06:56 With rev7573 the Office 2010 built-in document checker result all fine. No "absolute path" warning anymore. Thank you! Edit.: Office2013 -> Office 2010 ---------------------------------------------------------------------- wp · 2020-07-27 16:02 Strange. I did not change anything here... ---------------------------------------------------------------------- totya · 2020-07-27 18:45 Quote from: wp on July 27, 2020, 06:02:10 pmStrange. I did not change anything here... Strange indeed, because in Office 2013 built in checker, absolute path found again... LOL. https://stackoverflow.com/questions/41132541/obtain-the-absolute-path- to-the-workbook-from-an-xls-file In fps created excel file, I not found any path in workbook.xml. In the new empty excel 2013 created workbook \xl\workbook.xml I found this "C:\Tmp\": 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 Interest: https://df-stream.com/2014/01/ms-excel-2013-last-saved-location/ https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spr eadsheet.workbook.absolutepath?view=openxml-2.8.1 Absolute path is arrived in Office 2013? LOL. Then certainly the Office 2010 document checker will never found this So I'm sorry. In fps created workbook, Excel 2013 found absolute path, but this doesn't exists really (in workbook.xml). Strange indeed... ---------------------------------------------------------------------- wp · 2020-07-28 15:53 The current fpspreadsheet revision has a new workbook field, MetaData 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 type  TsMetaData = class     ...    property CreatedBy: String read FCreatedBy write FCreatedBy;    property LastModifiedBy: String read FLastModifiedBy write FlastModifiedBy;    property DateCreated: TDateTime read FDateCreated write FDateCreated;    property DateLastModified: TDatetime read FDateLastModified write FDateLastModified;    property Title: String read FTitle write FTitle;    property Comments: TStrings read FComments write FComments;    property Keywords: TStrings read FKeywords write FKeywords;    end; You simply write to these properties before saving. see the demo in the folder examples/other/metadata of the fpspreadsheet installation. Note that modifications of the worksheets/workbooks do not automatically set the "Modified" fields. Currently, only the xlsx and ods formats support reading and writing of spreadsheet metadata. ---------------------------------------------------------------------- totya · 2020-07-28 18:26 Hi master! Thanks you for your job! Tested FPS SVN version: 7581 But something goes very wrong, if I recompile my actual project (without any modify) and run, the result excel file is unreadable by MS Excel 2013. Warning message (about translated): "... xlsx file partially wrong. Do you want to recover it?" So, If I set these fields before save the file: CreatedBy, LastModifiedBy, and Title, then my app result file is opened via Excel without any warning! CreatedBy, LastModifiedBy, and Title is okay in Excel information panel, but in "last modify" I see it: "1899.12.30. 4:00" Thank you again for your job, I can test again, if new svn version arrived. ---------------------------------------------------------------------- winni · 2020-07-28 18:45 Hi! Perhaps it helps: 1899.12.30. is "Day Zero" in TDateTime. check: 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 D: Double;...D := 0.0;showMessage (DateToStr(D));.... Winni ---------------------------------------------------------------------- wp · 2020-07-28 21:39 Try r7584. ---------------------------------------------------------------------- totya · 2020-07-29 08:09 Quote from: wp on July 28, 2020, 11:39:11 pmTry r7584. Thank you, but now I'v got memory error from heaptrc from my unchanged code (without any metadata). With 7576 no error, with 7584 I'v got... see attached file (first page). ---------------------------------------------------------------------- wp · 2020-07-29 08:27 Missing "override"... Try r7585. ---------------------------------------------------------------------- totya · 2020-07-29 08:40 Quote from: wp on July 29, 2020, 10:27:31 amMissing "override"... Try r7585. Thank you master! Without metadata, I'v no see any errors. With few metadata (CreatedBy, LastModifiedBy, and Title) I'v no see any errors too. Thank you! The more metadata fileds I will test later! ---------------------------------------------------------------------- totya · 2020-07-29 11:35 ... I tested it, and I don't see any problem! I only asked the "author" (thanks), but as I see now only few section missing from complete support, for example (translated): Subject, Category, State, Company name... if you have time, it's not bad if these properties supported by fps... Thank you! ---------------------------------------------------------------------- wp · 2020-07-29 23:00 I only added Subject because it exists in both xlsx and ods. The others are an Excel-only thing. But if you'd ever need something like "Manager" in the metadata you can now define custom fields: 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 // write  Workbook.Metadata.AddCustom('Manager', 'totya-boss'); // read  WriteLn(Workbook.Metadata.Custom.Values['Manager']);  // Custom is a stringlist, use its methods to access the fields ---------------------------------------------------------------------- totya · 2020-07-30 08:35 Quote from: wp on July 30, 2020, 01:00:14 amI only added Subject because it exists in both xlsx and ods. The others are an Excel-only thing. But if you'd ever need something like "Manager" in the metadata you can now define custom fields Thank you master! Unfortunatelly these custom fields not readable by excel, only with fps, but I think might be useful these hidden properties. I noticed one small bug, Keywords separated with "," excel: "First,Second,Third"  but the comments merged without any separator: excel: "FirstSecondThird'. excel corexml: 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 First_x000d_Second_x000d_Third ---------------------------------------------------------------------- wp · 2020-07-30 09:46 Quote from: totya on July 30, 2020, 10:35:26 amUnfortunatelly these custom fields not readable by excel, only with fps, but I think might be useful these hidden properties. In Excel 2016 they are readable, and I think that they are also in your Excel 2013 because similar items in the xml files exist already in the pre-xlsx XML format of 2003. The problem is that Microsoft has an awful user interface here. In the metadata screen of my version there is a button "Properties" with a dropdown menu. After clicking on the only item of that dropdown a dialog appears in which the custom properties can be viewed and modified. See my screenshots of the file created by the meta data demo program of fpspreadsheet (they are in German, but I hope you can locate the controls nevertheless). Note that fpspreadsheet stores custom properties only as strings while Excel (as well as LO Calc) allows to select a data type for each. But I think this would make things too much complicated for such a rarely used feature. Quote from: totya on July 30, 2020, 10:35:26 amI noticed one small bug, Keywords separated with "," excel: "First,Second,Third"  but the comments merged without any separator: excel: "FirstSecondThird'. excel corexml: 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 First_x000d_Second_x000d_ThirdThe "_x000d" is the replacement of a line feed used by Excel here. Again, when you look at the comments in the "hidden" dialog mentioned above you will see the line feeds. It is hard to understand that a company like Microsoft does not care for a better user inferface here considering the age of "Excel". Are we two the only persons seeing this? BTW, you are talking of "keywords" here but your input shows up in the "description" field. "True" keywords are entered in the Excel interface in the box "keywords" as a comma-separated list. My field "description" (named after the LO Calc field) gets what is input in the "comments" box. Another BTW: I see in the "excel_metadata.png" screenshot that there is an option to add another author ("Autor hinzufügen"). FPSpreadsheet ATM supports only a single author. Do you think it is necessary provide this option, too? ---------------------------------------------------------------------- totya · 2020-07-30 16:23 Hi master! Thank you for your detailed asnwer! As I see we speak two different things. I speak the main (most important) document properties, se pictures. As I wrote, I can't modify these (wich fps uninmplemented) with Metadata.Custom, for example: subject (tárgy). These "important" document properties available in two files, app.xml, and core.xml. I attach these files too. Between "#" the hungarian keyword. Forum don't allow upload xml file, so I upload these as zip. You speak the information/properties/special properties/custom tab. (in Office 2013). I think your speak this. The more thing I will answer later! Now I'm busy... Thank you for your job! ---------------------------------------------------------------------- wp · 2020-07-30 20:29 This page "Document properties", or similar, is not displayed in my Excel 2016. It seems to be integrated into the dialog opening with "Extended properties". What a mess! Why do they need three files to store simple meta data: core.xml, app.xml, custom.xml? Excuse me, but I am not really motivated to spend much more work in this. ---------------------------------------------------------------------- totya · 2020-07-30 20:33 Your job is much more than I excepted, thank you master! I can set author to the xlsx file, and much more... At the weekend I hope I have more time to answer to you as I promise. ---------------------------------------------------------------------- wp · 2020-07-30 22:18 I couldn't stop and added support for multiple authors. They are stored in the StringList Metadata.Authors where you can use the method Add() to add multiple authors. The property CreatedBy still exists, it lists a semi-colon separated list of all authors (except for the last modifying one which is still separate as LastModifiedBy). It is strange that Microsoft uses a semi-colon separated list here, because normally their list separator is a comma at the file level. In fact, the keywords are separated like that. On my German version of Excel, on the other hand, the semi-colon is the standard list- separator. Please open the metadata page in Excel and add a second author (using the "Add author" button). After saving extract the core.xml file from the Excel-zip and find the node . Which list separator do they use here between the two authors? Is this the same character that FPC displays in this short 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 program Project1;uses SysUtils;begin  WriteLn(DefaultFormatSettings.ListSeparator);  ReadLn;end. ---------------------------------------------------------------------- totya · 2020-07-31 10:21 Quote from: wp on July 31, 2020, 12:18:03 amI couldn't stop and added support for multiple authors. Hi master! I just sit of the front of my computer at the moment, so sorry for the delay! MS Office 2013 Hungarian core.xml file: QuoteFirst Author;Second Author;Third Author Your code result: Quote; So same as: ";" ---------------------------------------------------------------------- wp · 2020-07-31 10:30 Thank you. That's same situation as mine. Maybe somebody having an English Excel in which formula arguments must be separated by comma (instead of semi-colon) should repeat this experiment. ---------------------------------------------------------------------- totya · 2020-07-31 10:58 Quote from: wp on July 31, 2020, 12:30:47 pmThank you. That's same situation as mine. Maybe somebody having an English Excel in which formula arguments must be separated by comma (instead of semi-colon) should repeat this experiment. Hi! For example in my area and language settings (Win7 control panel) - see attached picture (list separator). If I change to English, I got "," But... if area and language settings stay English, and I start Excel, create empty worksheet, set authors, then savefile, the contain of the core.xml: QuoteFirst Author;Second Author;Third Author Same as... I think the "internal" sperator independent from the country list separator. Someone should to try with English version of Excel... ---------------------------------------------------------------------- totya · 2020-08-02 18:18 Hi master! Perhaps my friend can create multiple author xlsx file with English version of Excel on monday. I hope. Edit.: Today is bankholidays, and he forget it... he looks later... ---------------------------------------------------------------------- totya · 2020-08-05 15:36 Hi master! English Excel 2010 core.xml: Quotedeletedprivatedata;First Author;Second Author;Third Author So stay: ; Excel version (2010): Quote14.0300 ---------------------------------------------------------------------- wp · 2020-08-05 16:12 OK then let's keep it as it is. Hopefully they do not change their mind in some future version.