In LibreOffice Calc I have a link to external data working ok, its linked to an html file (file is local and on disk and created by me via another program) and it contains a column of currency values like 112.34 and 9.00 etc. They import ok but I need to have (for example) 9.00 and 85.10 show up in the spreadsheet as 9.00 and 85.10 and not just plain 9 and 85.1
Format cells doesn’t work, the format is erased whenever the link updates the sheet. Is there someway I can insert something into the html document to specify the format is 2 decimal places so my currency numbers show correctly in the spreadsheet? Or is there an alternative to the html I could use? I have full control over the source doc and can change it and its type at will so i’m in a good position there, but I need Calc to recognize that all the numbers it reads in via this “link to external data” have 2 decimal places
Or any other alternate solution?
(Editing again with respect to the workarounds described by the OQ, emphasising a more general workaround (IMO) I suggested earlier. Put at the beginning of my answer now:)
Having imported the numbers correctly, but not in the wanted format you can reference them in another place of your sheet or on another sheet. There you may define your ‘Numbers’ formats as you want. They will surely not be afflicted by an update of the linked range then. I would prefer a helper sheet for the linked range(s) to implement this solution. (Also see my new comment to this answer commenting on the way the OQ tried to reference the imported data.)
The key point concerning your issue should be the “…created by me via another program…”.
Sorry. I do not know much about html but I did an experiment: I created a html-document containing a table with the Writer-Web feature of LibO (V5.2.2). The table cells were set to a number format with 2 fixed decimal places. I entered two integer values and got the expected display showing 2 decimal places in my actual locale (German-de) with the comma as the decimal separator.
After inserting a ‘Link to external data’ (referencing the mentioned html table) into a new sheet under Calc I got the correct format again. This remained true when updating.
Now I changed my locale to ‘English-UK’ and updated the table again. The display was adapted to the new locale (point as the decimal separator) despite the fact that the html-file still contained the comma.
Firefox also displayed the table in “0,00” format. I just couldn’t easily change my locale for testing with Firefox.
Conclusion: The way Writer-Web is saving number-format-information is correctly interpreted by a standard browser and also by links to external data in Calc spreadsheets, even in different locales. If you get the software you did generate your html with to save format information in the same way, things will work. If the “number” is just text there it won’t.
Finally I edited my html with the plain-text editor of Win and created a contradiction:
<td width="25%" style="border-top: 1.00pt double #808080; border-bottom: none; border-left: 1.00pt double #808080; border-right: none; padding-top: 0.1cm; padding-bottom: 0cm; padding-left: 0.1cm; padding-right: 0cm" sdval="9,33" sdnum="1031;0;0,00">
<p>9,00</p>
</td>
As you see, the value is changed to 9,33 while the paragraph content is still “9,00”.
Surprisingly Firefox and Calc both(1) ignored the paragraph content and displayed the value in the correct format, Calc at least also adapting it to the point-is-decimal-separator-format if the locale was changed accordingly. This remained unchanged if I deleted the paragraph content completely (empty).
HTML obviously supports the needed means. It’s to the html-software to save the needed information. Calc (or browsers) cannot recreate it if it is missing in the file.
(I do not know if the means used by LibO Writer-Web probably are deprecated by HTML V5. The relevant question for you might, however, be only if they are still supported.)
(1) Sorry again. I hadn’t tested thoroughly enough. In fact Firefox displayed the paragraph content.
(Editing with respect to the comment by the OQ below:)
Having imported the numbers correctly, but not in the wanted format you can reference them in another place of your sheet or on another sheet. There you may define your ‘Numbers’ formats as you want. They will surely not be afflicted by an update of the linked range then. I would prefer a helper sheet for the linked range(s) to implement this solution.
(Edit2 regarding additional comments by the OQ:)
I am not convinced that using named ranges is as much helpful as often suggested. However, if you define a named range the expression actually assigned to the name should be a complete range address including the sheetname. If you first select a range and then call >‘Insert’>‘Named Expressions’ the absoulte address (including the sheetname) is already prepared as the default expression. You only have to accept the ‘Scope’>‘Document (Global)’ then to be able to access the range from anywhere in the document by its sheer name without worrying about the sheet the range is contained in. To poll the complete range to a different position you may enter {=MyRangeName} into the TL cell as a matrix formula then.
(You probably should read about some basics in the Calc guide from this location e.g. On this level the version will not matter much. However some changes to the user interface were made since V 4.1.)
What you are asking for are syntactical elements used for the format information by Writer-Web. As I already stated I do not know much about html and cannot tell you if these constructs are Libre-/Open-Office only or more generally used.
It seems you are looking for a solution completely implementable on the Calc side.
I will edit my answer above with this respect.
There is an interesting option for html import in Menu/Tools/Options/LibreOffice/Load+Save/HTML compatibility like use “English (USA)” locale for numbers, or ignore font settings.
Ok, so I imported the data on a different sheet called LinkedData in the same workbook using “Link to External data” Then I defined a named range for it called watchData. My idea is to have the data in my watch_list sheet come from the named range i the LinkedData sheet so i can format it properly. The problem is I cant find a way to do that. In the watch_list sheet for example, in a cell i put =LinkedData.watchData but it doesnt work. I assume thats not the correct way to do that
(continued from above due to the length limitations imposed by libreoffice.org)
How do i get the watch_list sheet to update its cells from the watchData named range on the linkedData sheet every xx seconds?
A named range, say watchData, is defined including the sheetname. and referenced as watchData and nothing else (‘Global’ scope by default). A named range cannot be combined with a sheet name since the source only exists in its original sheet.
Ok, I found a partial answer by experimentation.
If the html file includes numbers like 129.90 and you want them treated as currency the html needs to have the value written like this: $129.90 Then you will get proper currency formatting with red text for negative values.
Also, the html file needs a DOCTYPE statement at the start of the file. In my case inserting the doctype tag before the html tag itself helped get things working
Another trick, is when you are importing percentage fields, add a trailing % sign to the value, calc will then import it as a percent field format (but without the coloring - I haven’t figure out how to do that yet). you CAN specify coloring using the html font tag though, just not with formulas.
So, if you create an html page with a table to import as “link to external data” just open the html in a browser and the table will look the same when its imported into your spreadsheet
e.g:
<!DOCTYPE html>
<html>
<body>
<table>
<tr>
<td><font face="Liberation Sans" size="2"><b>AET</b></font></td>
<td><font face="Liberation Sans" size="2">75</font></td>
<td><font face="Liberation Sans" size="2">$74.11</font></td>
<td><font face="Liberation Sans" size="2">05/16/2014</font></td>
<td><font face="Liberation Sans" size="2">$124.10</font></td>
<td><font face="Liberation Sans" size="2">=((E1-C1)*B1)</font></td>
<td><font face="Liberation Sans" size="2">67.45%</font></td>
</tr>
<tr>
<td bgcolor="#D7DADC"><font face="Liberation Sans" size="2"><b>AET</b></font></td>
<td bgcolor="#D7DADC"><font face="Liberation Sans" size="2">99</font></td>
<td bgcolor="#D7DADC"><font face="Liberation Sans" size="2">$127.40</font></td>
<td bgcolor="#D7DADC"><font face="Liberation Sans" size="2">05/16/2014</font></td>
<td bgcolor="#D7DADC"><font face="Liberation Sans" size="2">$124.10</font></td>
<td bgcolor="#D7DADC"><font face="Liberation Sans" size="2">=((E2-C2)*B2)</font></td>
<td bgcolor="#D7DADC"><font face="Liberation Sans" size="2" color="red">-2.59%</font></td>
</tr>
</table>
</body>
</html>
my next step is to stop using inline formatting and create a css class instead, that’s probably a better way to do this
You can also add formulas to your html file to be imported, but the cell references are relative so cell C1 for example is 3 columns to the right of your import location(and on that same row) . e.g if your link to external data is at cell E12 then a cell reference in the html file of B1 will be at F12 and C4 in the html would end up in cell G15 of your spreadsheet.
One more point, if you put formulas in your html they don’t seem to allow you to put a $ or % sing after them to indicate the formatting.