Magrittr forward pipe fails to forward values into openXL::addWorksheet - "Error ...: First argument must be a Workbook"

Question

magrittr appears to be failing to pipe 'workbook' class objects into the addWorkbook function from the package openxlsx.

(Never mind why I need to use excel...eugh yuk)

For example, to write the InsectSprays dataset to an excel file in 'base' syntax:

library("openxlsx")
insect.wb <- createWorkbook()
addWorksheet(wb = insect.wb,
             sheetName = "Insect Spray")
writeData(wb = insect.wb,
          sheet = "Insect Spray",
          x= InsectSprays)

openXL(insect.wb)

Opens a temp excel file with the data.

So magrittr should

pipe a value forward into an expression or function call; something along the lines of x %>% f, rather than f(x)

But e.g.

library("openxlsx")
library("magrittr")
insect.wb <- createWorkbook()
insect.wb %>%
  addWorksheet(sheetName = "Insect Spray") %>%
  writeData(sheet = "Insect Spray",
            x= InsectSprays)

Returns

Error in writeData(., sheet = "Insect Spray", x = InsectSprays) :
First argument must be a Workbook.

But insect.wb is a workbook object:

 > insect.wb <- createWorkbook()
 > class(insect.wb)

[1] "Workbook"
attr(,"package")
[1] "openxlsx"

Which suggests that the issue is that the object isn't getting piped in...

Am I just buggering up the syntax, or is there a more interesting explanation for why this is failing?

For interest, there is no issue with forward pipe and writeData –- if we shift the use of forward-pipes until after addWorksheet, it also works fine:

insect.wb <- createWorkbook() 
addWorksheet(wb = insect.wb,
             sheetName = "Insect Spray")

insect.wb %>%
  writeData(sheet = "Insect Spray",
            x= InsectSprays)

openXL(insect.wb)

I think `addWorksheet` doesn't return a workbook, and that's the output of `addWorksheet` that you're passing to `writeData` — moodymudskipper, Jul 03 '17 at 07:28
It seems to be the case, addWorksheet probably returns TRUE or something like that (doc doesn't say it), it modifies the object directly. Can you try this ? `insect.wb %T>% addWorksheet(sheetName = "Insect Spray") %>% writeData(sheet = "Insect Spray", x= InsectSprays)` (notice the `%T>%` operator). If it works will depend if a copy of insect.wb is made from the start by magrittr or not. — moodymudskipper, Jul 03 '17 at 07:38
@Moody_Mudskipper got it - that works well. I had a poke in the docs but also couldn't tell. If you care to add that as an answer I'll accept — Scransom, Jul 03 '17 at 23:40
Hmm yeah `xxx <- addWorksheet(wb = insect.wb, sheetName = "qqq")` returns an integer which seems to be the number of sheets in the workbook. Weird. — Scransom, Jul 03 '17 at 23:45

score 4 · Accepted Answer · edited Jul 05 '17 at 07:08

In your command chain, writeData takes as a first input the output of insect.wb %>% addWorksheet(sheetName = "Insect Spray")

addWorksheet modifies directly the object, and doesn't return the object, so whatever you're passing to writeData is NOT a worksheet (from your comment, it's probably the number of sheets after the add).

You may use the operator %T>% and write

insect.wb %T>%
  addWorksheet(sheetName = "Insect Spray") %>%
  writeData(sheet = "Insect Spray", x= InsectSprays)

%T>%starts a new chain in the sense that the ouput of the rhs is not returned, but the lhs is returned, so then writeData takes it where %T>% left, except that insect.wb has now been modified by addWorksheet.

Magrittr forward pipe fails to forward values into openXL::addWorksheet - "Error ...: First argument must be a Workbook"

1 Answers1

Linked