39

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.

mathematical.coffee
  • 55,977
  • 11
  • 154
  • 194
gaurav kumar
  • 859
  • 2
  • 10
  • 24
  • What do you mean by "when the same is printed using write.xlsx function"? Are you doing `print(write.xlsx(...))`? Or is the error just when you call `write.xlsx(...)`? Can you provide a reproducible example? – mathematical.coffee Dec 23 '15 at 03:20
  • Output is printed using: write.xlsx(q1, file = paste0(File Address","_6",".xlsx"),sheetName="Sheet1",col.names=TRUE, row.names=FALSE, append=TRUE) – gaurav kumar Dec 23 '15 at 03:22
  • Could you provide a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example)? Also, your "dput of the data.frame" does not look like something that `dput(my.dataframe)` would produce, can you look at `class(my.dataframe)` and confirm that the (only) class is "data.frame"? – mathematical.coffee Dec 23 '15 at 03:23
  • class(q1) [1] "tbl_df" "tbl" "data.frame" – gaurav kumar Dec 23 '15 at 03:29

3 Answers3

89

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!
Community
  • 1
  • 1
mathematical.coffee
  • 55,977
  • 11
  • 154
  • 194
10

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

Michael Bellhouse
  • 1,547
  • 3
  • 14
  • 26
  • 1
    This worked! Ending with ungroup() allowed me to keep my dplyr chain of analysis in tact and still export with write.xlsx() – Kodiakflds Mar 15 '18 at 20:46
0

It seems that there is a bug with the Date/Time format of the first column (Timestamp). If you convert the first column to character, it should work. So, you may change your first column to 

q1[,1] <- as.character(q1[,1])

and try again...

Dimitris Patikas
  • 9
  • 2