Error in printing data.frame in excel using XLSX package in R

Question

The dataframe is visible with out any error. But when the same is printed using write.xlsx fucnction of the package XLSX, it gives the error.

Error in .jcall(cell, "V", "setCellValue", value) : 
  method setCellValue with signature ([D)V not found.

The dput of the data.frame looks like:

Timestamp         qs          pqs        logqs         es         p_imp      dep    r_dep       agg_rtn
               (time)      (dbl)        (dbl)        (dbl)      (dbl)         (dbl)    (dbl)    (dbl)         (dbl)
1 2015-05-04 09:29:59 0.05788732 0.0007478696 0.0007478545 0.09633803 -0.0446830986 3533.518 274079.9 -0.0006432937
2 2015-05-04 10:00:00 0.04948394 0.0006362707 0.0006362707 0.07586009  0.0088016055 2416.431 187953.1  0.0000000000
3 2015-05-04 10:30:00 0.05554795 0.0007142532 0.0007142532 0.06417808 -0.0002739726 3245.574 252422.0  0.0000000000
4 2015-05-04 10:59:59 0.04863014 0.0006194244 0.0006194244 0.08434442  0.0024951076 3563.401 279503.9  0.0000000000
5 2015-05-04 11:30:00 0.05761986 0.0007319037 0.0007319037 0.07851027  0.0154965753 2010.943 158429.1 -0.0006339144
6 2015-05-04 12:00:00 0.04957627 0.0006285051 0.0006285051 0.07025424  0.0070762712 1819.908 143546.0  0.0000000000
Variables not shown: vol_30_sum (dbl), vol_30_mean (dbl), p_return_sqr (dbl), p_return_mean (dbl), Lim_or_out (dbl),
  closing_price (dbl), closing_vol (dbl)

Kindly help in resolving this error.

Solution 1

Still no reproducible example, but from your class(q1) it appears that q1 is a tbl_df (the sort of dataframe that the dplyr package produces) whereas write.xlsx expects a data.frame.

Try giving write.xlsx a plain data.frame as it expects. e.g.

write.xlsx(as.data.frame(q1), ...)

Here's a reproducible example (i.e. you could copy-paste it into your R session to reproduce the bug + fix).

library(dplyr)
iris2 <- tbl_df(iris)
class(iris2) # like yours
# [1] "tbl_df"     "tbl"        "data.frame" 

# Now let's try to write to XLSX using command as mentioned in your comments
library(xlsx)
write.xlsx(iris2, file='test.xlsx', sheetName="Sheet1", col.names=TRUE, row.names=FALSE, append=TRUE)
# Error in .jcall(cell, "V", "setCellValue", value) : 
#   method setCellValue with signature ([D)V not found
# In addition: Warning message:
# In if (is.na(value)) { :
#  the condition has length > 1 and only the first element will be used
# ^--- we can reproduce your error. This is the point of a reproducible example, so we can see if our fixes work for you.

Now let's try fix it by making sure that write.xlsx gets a data.frame, not a tbl_df!

write.xlsx(as.data.frame(iris2), file='test.xlsx', sheetName="Sheet1", col.names=TRUE, row.names=FALSE, append=TRUE)
# huzzah!

Solution 2

I find this happens when grouping variables with dplyr. If you end a chain with %>% ungroup () it appears to resolve