Support writing formulas

[mikldk]

Would it be possible to support writing formulas, too? Maybe for R's formula type.

[behrica]

I need often a very specific type of formula, namely a hyperlink. Users love to be able to click on links in Excel files

[jeroen]

How would you expect to use this? I suppose you don't want to write an entire column of formulas?

[behrica]

My use case is to have a full column only containing formulas, in this case the formula "HYPERLINK". https://support.office.com/en-us/article/HYPERLINK-function-333c7ce6-c5ae-4164-9c47-7de9b76f577f

So I could expect to use this like this: I create a normal R data.frame with a string column "link". In this column I put strings with the text of the formula, so '=HYPERLINK("http://example.microsoft.com/report/budget report.xlsx", "Click for report")'

Then we could add a parameter to the "write_xlsx" function, which allows to specify that certain columns are formula, so

writexl::write_xlsx(df,formulaCols=c("link"))

Not sure, how this can be fitted with the original request, to specify individual cells as formula.

My proposal might be the most general (and rather easy to implement, case). Any Excel formula is a string at the end.

What the upper code would not allow is to have "formula" and "non formula" in the same column.

To implement the very general case, which would allows to specify Excel columns having formula and non-formula might be very difficult to do, as it would need a "data.frame" with different types in the same column, which is not possible.

To have this, we need a function which can specify the data to write as a list of lists. Then every "cell" could be in a different format, and potentialy rendered to Excel differently.

[mikldk]

Yes, for whole columns. (I will use it to generate sheets used for correction exams, and there will be a Total column with the total number of points.)

Maybe this can be solved with formula_cols (e.g. "Points") and formula_cells (e.g. "B4") arguments to write_xlsx?

[jeroen]

Can you provide an example xlsx with what you want, then I can see what it looks like and try to regenerate such a file with writexl.

[jeroen]

I have added some experimental support for an xl_formula class in the dev version:

devtools::install_github("ropensci/writexl")

See example here. Can you test if this works for you?

[mikldk]

Thanks!

In LibreOffice Calc, the age column is fine, but the website column is just 0, but with the correct formula.

If I do a simple calculation formula like

library(writexl)
library(tidyverse)

df <- data.frame(
  name = c("UCLA", "Berkeley"),
  founded = c(1919, 1868)
) %>% 
  mutate(founded_formula = xl_formula(paste0('=B', row_number()+1, '+1000')))    
write_xlsx(df, path = 'test.xlsx')

Then when opening in LibreOffice Calc, the cell contents of founded_formula is also just 0, but the formula is correct. If I copy the formula to a new cell, the correct numbers appear. It does not help to Recalculate (F9).

This may be a LibreOffice Calc problem.

[behrica]

I have not tried yet with MS Excel. But for my use case, ("hyperlinks in columns", I noticed that it works for Libre Office by just writing a "csv" file and having character cell content such as "=HYPERLINK('http://google.com')". This does not produced a "visible" link in LibreOffice (not "blue" or something") but a tooltip and I can "ctrl-click" on it to open in browser.

I will trie your example with Excel.

On Wed, Sep 20, 2017 at 8:38 AM, Mikkel Meyer Andersen < notifications@github.com> wrote:

Thanks!

In LibreOffice Calc, the age column is fine, but the website column is just 0, but with the correct formula.

If I do a simple calculation formula like

library(writexl) library(tidyverse)

df <- data.frame( name = c("UCLA", "Berkeley"), founded = c(1919, 1868) ) %>% mutate(founded_formula = xl_formula(paste0('=B', row_number()+1, '+1000'))) write_xlsx(df, path = 'test.xlsx')

Then when opening in LibreOffice Calc, the cell contents of founded_formula is also just 0, but the formula is correct. If I copy the formula to a new cell, the correct numbers appear. It does not help to Recalculate (F9).

This may be a LibreOffice Calc problem.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ropensci/writexl/issues/5#issuecomment-330759021, or mute the thread https://github.com/notifications/unsubscribe-auth/AAHxgRCkrJLj6fc7tYKraAwqGS0EmaXAks5skLLUgaJpZM4PQui7 .

[behrica]

The hyperlink do work, so I can click them. They don't become "blue" and "underlined", but thats my just due to mising formats.

[nacnudus]

@mikldk That seems to be a LibreOffice Calc problem. I tested with Excel, which recalculates the spreadsheet when it loads.

[jeroen]

I have just added a special xl_hyperlink function that you can use to insert blue underlined hyperlinks. See example here. Can you test if this works for you?

[jeroen]

@jmcnamara is there something like worksheet_write_formula_num for hyperlinks so that LibreOffice can display them correctly ?

[behrica]

For me the hyperlinks work, thanks for implementing

[jmcnamara]

@jeroen

Just getting to this now.

is there something like worksheet_write_formula_num for hyperlinks so that LibreOffice can display them correctly ?

I could add a worksheet_write_formula_str() function to add a string result to the formula. The Perl/Python/Lua versions have it.

However, using a =HYPERLINK() formula with libxlsxwriter isn't the right way to implement hyperlinks. There is a specific worksheet_write_url() function that replicates the way that Excel stores hyperlinks: http://libxlsxwriter.github.io/worksheet_8h.html#a9b2ac96ee23574a432f5703eedcaf9a1

[jmcnamara]

@jeroen Can I make the suggestion once more that hyperlinks should be converted using the worksheet_write_url() function and not as a =HYPERLINK() formula.

There are a few benefits to this:

• The output file will have native urls like those produced by Excel
• If no format is supplied the libxlsxwriter library will supply a default format (blue, underlined)
• This format will be the specific style used by Excel for hyperlinks and will change color if the link has been clicked

[PavoDive]

I landed in this FR while looking for some functionality that allowed me to write a formula to a cell in a similar way to the python package XlsxWriter.

My use-case:

1. I process a large amount of data using R. For the sake of simplicity, let's say that the data has columns "unit price" and "quantity", and that "unit price" is a fixed value, while "quantity" is a variable that I pre-define in R. Both variables are then multiplied to produce a "total value" column.
2. The quantity has to be manually adjusted by a user, depending on factors that are beyond what I can include in my R script. Because of user requirement, this step has to be done in Excel. Of course I can have the user typing the formula (that's what's happening now), but the idea is to provide the complete worksheet from R.
3. I'd like the "total value" to by dynamically calculated when the user updates the quantity column, hence the need for a formula. Basically I would like to have the possibility to tell writexl that column C is of type formula, and that the contents of cell C1 is the excel formula =A1 * B1.

In the mentioned python package the method would be worksheet.write_formula('C1', '=A1 * B1')

[woodtho]

@PavoDive You can already do this with writexl.

library(tidyverse)
library(writexl)

tibble(unit = 1, price = 2, value = xl_formula("=A2 * B2")) %>% 
  write_xlsx("test.xlsx")

^{Created on 2023-12-07 with reprex v2.0.2}