Hi Dave,
did you write your formula directly to the cell attribute, or did you write it to <cell attribute name>.formula?
See here:
http://docs.safe.com/fme/html/FME_ReadersWriters/Default.htm#xlsx/Feature_Representation.htm
David
Thanks David - I'm not familiar with that option? I have just been writing into a standard recieving attribute name. I actually can't get any formula to resolve correctly, for example if I create this in a string concatenator =MEAN($C$2:C2)) and write it out that's exactly what I see in the spreadsheet, rather than the results of the formula.
This recent blog posting states that you can write formulas, but I can't spot how...
http://blog.safe.com/2013/10/tale-magical-transformation-excel-spreadsheet/
I suspect it's obvious but I've been staring for too long!
I'd say that it's deceptively easy since it isn't terribly well documented...
If you have an attribute called "myURL" you simply create an attribute called "myURL.formula" and write your formula there.
Example:
Two things to notice:
- the HYPERLINK will not automatically be formatted as such, although it will behave as it should. You can override the font and color, though.
- For the SUM to work, make sure that the columns "val_a" and "val_b" are defined as "number" on the writer.
Hope this helps.
David
Resulting Excel-file could look something like this:
When I switch on the formulas, we can see that the formulas have indeed been written as such:
It even translated the formulas for me :-)
David
David - A thousand thank you's! It took me a few moments to realise that to turn the attribute blue and underline it you actually have to define the attribute twice:
1) URL - no need to write anything here, just access the 'Edit' button for the User Attribute and set the blue and underline style here.
2) URL.formula - Send the concatenated formula here.
Job done. I agree, it was a fiddle and the documentation is lacking but we got there in the end, thanks so much!
I'd say that it's deceptively easy since it isn't terribly well documented...
If you have an attribute called "myURL" you simply create an attribute called "myURL.formula" and write your formula there.
Example:
Two things to notice:
- the HYPERLINK will not automatically be formatted as such, although it will behave as it should. You can override the font and color, though.
- For the SUM to work, make sure that the columns "val_a" and "val_b" are defined as "number" on the writer.
Hope this helps.
David
@david_r I think I tried this but can't get it to work. I'm constructing a URL in one attribute, then made another one like you suggested and called it URL.FORMULA, and that's where I put this:
=HYPERLINK("@Value(URL)",12345)
.
When I write it to the XLSX, I still have to click in the cell, hit F2 + Enter before it is actually made into a hyperlink. Any suggestions?
Hi Dave,
did you write your formula directly to the cell attribute, or did you write it to <cell attribute name>.formula?
See here:
http://docs.safe.com/fme/html/FME_ReadersWriters/Default.htm#xlsx/Feature_Representation.htm
David
The link you posted in 2013 is now broken, but here's the updated link:
https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/xlsx/xlsx.htm.
@david_r I think I tried this but can't get it to work. I'm constructing a URL in one attribute, then made another one like you suggested and called it URL.FORMULA, and that's where I put this:
=HYPERLINK("@Value(URL)",12345)
.
When I write it to the XLSX, I still have to click in the cell, hit F2 + Enter before it is actually made into a hyperlink. Any suggestions?
Hi @dmatranga, It was a workaround in old versions of FME which didn't support Excel hyperlink officially. Currently (maybe 2015+?), you can set the display value (12345) and its hyperlink URL (@Value(URL)) to a destination cell separately, with adding the suffix .hyperlink to the attribute name for the URL, like this.
Hi @dmatranga, It was a workaround in old versions of FME which didn't support Excel hyperlink officially. Currently (maybe 2015+?), you can set the display value (12345) and its hyperlink URL (@Value(URL)) to a destination cell separately, with adding the suffix .hyperlink to the attribute name for the URL, like this.
Thanks Takashi. So when you actually write an attribute to the XLSX, it would be Field.hyperlink, correct?
And what value does the attribute URL contain? Is it just a string with a URL? Or does it contain the Excel formula for HYPERLINK?
Thanks Takashi. So when you actually write an attribute to the XLSX, it would be Field.hyperlink, correct?
And what value does the attribute URL contain? Is it just a string with a URL? Or does it contain the Excel formula for HYPERLINK?
No, you should just write "Field".
Assumes that the attribute URL stores a URL string. e.g.
http://www.safe.com
@takashi I finally got it to work! Your screenshot finally helped me figure this out, thanks again.
One last note for the benefit of other people experiencing issues: the word 'hyperlink' must be lowercase.
Apparently that was the issue that I was having the entire time. I'm usually OCD about my attribute names being in ALL CAPS, but for formulas they have to be in lowercase. :)
@takashi I finally got it to work! Your screenshot finally helped me figure this out, thanks again.
Digging up an old thread here, but I'm having trouble making the hyperlinks active. I have to double-click inside the cell, then click outside it to make it active, or click in the cell and press F2 and Enter. They are not active (blue, underlined) when I open the spreadsheet.
Digging up an old thread here, but I'm having trouble making the hyperlinks active. I have to double-click inside the cell, then click outside it to make it active, or click in the cell and press F2 and Enter. They are not active (blue, underlined) when I open the spreadsheet.
Hi @ctownsend! I'm sorry you ran into this issue. What version of FME are you using?
I found a problem report (internal reference FMEENGINE-60326) for this issue which seems to have appeared in FME 2018. The suggested workaround is to explicitly add the hyperlink attribute (i.e. Field.hyperlink) to the output schema (Writer Feature Type Parameters > User Attributes):
Give this a shot and let us know if it works for you.
We'll try posting any updates to the problem report here on this thread.
@takashi I finally got it to work! Your screenshot finally helped me figure this out, thanks again.
Hi @nampreetatsafe, I have that in my settings. I'm using 2019.0. In any case, after a bit more digging, this seems to be a security setting we have for Excel that prevents active hyperlinks and macros being embedded in spreadsheets that we cannot turn off. So I have a small macro that I have stored separately that I can run which makes the hyperlinks active and persists after saving the spreadsheet.
One last note for the benefit of other people experiencing issues: the word 'hyperlink' must be lowercase.
Apparently that was the issue that I was having the entire time. I'm usually OCD about my attribute names being in ALL CAPS, but for formulas they have to be in lowercase. :)
AND add this attribute Field.hyperlink as a standard attribute to your Excel Writer too ;)
No specific synax for both attribute is required :
Field : lien
Field.hyperlink : https://knowledge.safe.com/questions/3266/create-hyperlinks-in-excel.html?childToView=35259
