Number formats for OpenDocument ODS lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- wp · 2014-06-16 14:23 I am currently trying to implement writing of ods number formats for fpspreadsheet and got stuck with the percentage format which is not shown in the opening application although I'm pretty sure that the structure of the xml string is correct. Code: [Select]     %   Fixed and scientific formats are working fine, but I fear this is just by chance because I do not understand the basics behind the scene: Every number format is assigned to a name like N10 or N850. There seem to be some built-in formats which have a low number value, but sometimes they are duplicated with higher numbers. What are the rules for these numbers? Is there a range reserved for"built-in formats"? Which numbers can I safely use? What if I reassign a format that is expected by the application at some value to another value?In which section are the number formats to be expected? There are two places with number formats, styles.xml and content.xml. Peeking into openoffice files, I see that sometimes the same format is in styles.xml, but in another fie it is in content.xml. Excel, on the other hand, seems to put most of it into styles.xml. Quite confusing... What are the rules?I hope that somebody out there has experience with these non-Lazarus questions. ---------------------------------------------------------------------- Mike.Cornflake · 2014-06-16 15:13 I'm guessing you're working backwards from existing OpenOffice documents.  I did the same when I was working on the ODT and DOCX formats.  For ODT files at least, all those names are autogenerated by LibreOffice, and they link back to styles defined in, ooh, probably in Styles.xml - but that is a guess based on a year old memory for a different format. These links weren't much help http://docs.oasis- open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05-part1.html#element- number_percentage-style http://docs.oasis- open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05-part1.html#attribute- style_name_element-number_percentage-style The latter link contained the following tidbit "For automatic styles, the name may be generated by OpenDocument producers."  Which goes back to my first suspicion, it's a LibreOffice generated name. UPDATE: The good news about that is, you should be able to use whatever name you like there.  Just so long as it is unique. ---------------------------------------------------------------------- Mike.Cornflake · 2014-06-16 16:03 Sorry, I flipped that last answer out too quickly.  Didn't read your post correctly. While developing the ODT writer, I found the following resource very helpful (it's the same document for odt and ods) http://books.evc-cit.info/OD_Essentials.pdf According to that document, you need to put your definition in content.xml The example given in that book is Code: [Select]     % Which is pretty much exactly what you have. QuoteIn which section are the number formats to be expected? There are two places with number formats, styles.xml and content.xml. Peeking into openoffice files, I see that sometimes the same format is in styles.xml, but in another fie it is in content.xml. Excel, on the other hand, seems to put most of it into styles.xml. Quite confusing... What are the rules?You've just asked the very question that leads me to dislike the OASIS format.   Guess where headers and footers are stored in odt files.  Styles.xml.  wtf?  In Word, they're stored in a series of HeaderXX.xml and FooterXX.xml.  Much more intuitive.  the ODT writer was developed around an assumption that headers and footers either went in the main content.xml, or in their own xml files.  They're currently outstanding for ODT as a massive code refactoring is required :-( The document I've posted has two useful chapters for you Spreadsheet Information in styles.xml Spreadsheet Information in content.xml Quotevery number format is assigned to a name like N10 or N850. There seem to be some built-in formats which have a low number value, but sometimes they are duplicated with higher numbers. What are the rules for these numbers? Is there a range reserved for"built-in formats"? Which numbers can I safely use?If I'm understanding everything correctly (and I think I am), you can use whatever unique name you like in there.  There are no predefined names.  Just LibreOffice (or OpenOffice) code is producing very similar names in similar circumstances. Hopefully these will answer your question.  And sorry for the quick rushed answer before... ---------------------------------------------------------------------- wp · 2014-06-16 19:14 Found it: the "value-type" of percent values is not "float" but "percentage"! After fixing that, the percentages formats are written correctly. Glad to hear that you confirm my gut feeling that the ods file format appear rather chaotic, and thanks for giving me the feeling that my rudimentary understanding seems to be correct. Now that my minimum test file is written correctly I am stumbling into another issue: One of the cells has a larger font. The rows are written with the attribute 'style:use-optimal-row-height="true"', but I do not calculate the correct row heights and I am just using the default font height for that purpose. The file loads fine in OpenOffice with the row of that particular cell being higher than the others. But in LibreOffice, the row heights are strange: the row header column shows equal row heights, but the mouse cursor changes to the resizing shape at an intermediate position. It looks that I should put the correct row height into the file. But then why should I use the "use-optimal-row-height" at all? Any idea what I am missing here? I am attaching that file. ---------------------------------------------------------------------- Mike.Cornflake · 2014-06-16 22:09 Quote One of the cells has a larger font. You're talking about Cell B1 yes? Quote      2   Alone of all the styles, ce1 is defined as such... Quote            And only Cell B1 is defined at having syle "ce1" Absolutely none of the other styles have style:text-properties style defined.   ce1 is alone in hhaving fo:font-size="16.0pt" defined, this is why it's larger than the rest. OK that's not true.  ce0 has a style:text-properties defined that set font=bold.  ce0 is for cell A1.  I missed that originally, but now I've seen it in the xml, I can confirm it's there in the spreadsheet. But that's it :-)  Only ce0 and ce1 have the additional formatting defined. I'd look at your writer code.  It's doing something unexpected :-) ---------------------------------------------------------------------- wp · 2014-06-17 09:19 Thank for your assistance. Are you saying that every cell should have a style? But when I save a new file from LibreOffice with a few dummy numbers having no special format, the file does not contain styles for these cells, I guess that the "default-cell-style-name" of the "table- column" element is assumed as a replacement. In addition I also removed all cells except for the big B1 in my test file code. But the file shows still the same issue in LibreOffice. ---------------------------------------------------------------------- Mike.Cornflake · 2014-06-17 09:33 I'm just making a fool of myself.  Again I didn't read your reply correctly :-(  Probably best if you ignore that last reply. QuoteOne of the cells has a larger font. The rows are written with the attribute 'style:use-optimal-row-height="true"', but I do not calculate the correct row heights and I am just using the default font height for that purpose.I've confirmed that your test file shows the issue in LibreOffice. When I resize Row 1, and hit save in LibreOffice, there are a few changes in the resulting XML to yours.  And yes, it's an oddity.  The change is in the style for row 1.  Directly after style:use-optimal- row-height="true", your value of style:row-height has been changed to "0.2736in".  So your question about what on earth style:use-optimal- row-height="true" is used for is a valid one.  I can't answer that. Your value for style:row-height was "4.23mm", which sounds wrong anyway, but is what LibreOffice was rendering. Looks like LibreOffice obeys style:row-height if it is present, where MS Excel will ignore style:row-height if style:use-optimal-row- height="true" is present. I made a small change by hand in your ods file.  I manually deleted your entry for style:row-height was "4.23mm", leaving behind the style:use-optimal-row-height="true".  This time when I opened the ods file, the first row was now shown at default Row Height, which was still not sufficient to fully display your large text. Basically, I think LibreOffice just plumb ignores style:use-optimal- row-height="true".  I'd leave it in though as it looks like MS Excel will use it. Your value of 4.23mm is too small, it needed to be around 6.95mm.  It looks like you're going to have to calculate the row height after all. And again, sorry for the dumb replies.  Hopefully this one is on the ball... I'll look at your next issue later... Small update QuoteAre you saying that every cell should have a style?I don't know for the spreadsheet, but I can confirm that for the odt file, every single cell in a table requires a Style.  Every single one :-(   It's currently causing bloat in the resulting file - I need to normalise the collection of styles.  I would strongly suspect that the schema for tables and spreadsheets would share common design, in which case, yes I think you're going to need to apply a style for each cell.