CODEWITHSUNDEEP
r
Lee
Lee

Reputation: 234

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

library(dtplyr)
library(xlsx)
library(lubridate)

'data.frame':   612 obs. of  7 variables:
 $ Company           : Factor w/ 10 levels "Harbor","HCG",..: 6 10 10 3 6 8 6 8 6 6 ...
 $ Title             : chr  NA NA NA NA ...
 $ Send.Offer.Letter :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 612 obs. of  1 variable:
  ..$ Send Offer Letter: Date, format: NA NA NA NA ...
  ..- attr(*, "spec")=List of 2
  .. ..$ cols   :List of 1
  .. .. ..$ Send Offer Letter: list()
  .. .. .. ..- attr(*, "class")= chr  "collector_character" "collector"
  .. ..$ default: list()
  .. .. ..- attr(*, "class")= chr  "collector_guess" "collector"
  .. ..- attr(*, "class")= chr "col_spec"
 $ Accepted.Position : chr  NA NA NA NA ...
 $ Application.Date  : chr  NA NA NA NA ...
 $ Hire.Date..Start. :Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 612 obs. of  1 variable:
  ..$ Hire Date (Start): POSIXct, format: "2008-05-20" NA NA "2008-05-13" ...
 $ Rehire..Yes.or.No.: Factor w/ 23 levels "??","36500","continuing intern",..: NA NA NA NA NA NA NA NA NA NA ...

I have an extremely messy dataset (it was entered entirely freehand on excel spreadsheets) regarding new hires. Variables associated with dates are, of course, making things difficult. There was no consistency in entry format, sometimes random character strings were a part of a date (think 5/17, day tbd) etc. I finally got the dates consistently formatted into POSIXct format, but it led to the odd situation you see above where it appears there are nested variables in my columns. I have already coerced two date variables into as.character ($Accepted.Position and $Application.Date), as I have seen examples of POSIXct date formatting causing issues with write.xlsx.

When I attempt to write to xlsx, I get the following:

write.xlsx(forstack, file = "forstackover.xlsx", col.names = TRUE)
Error in .jcall(cell, "V", "setCellValue", value) : 
  method setCellValue with signature ([D)V not found
In addition: There were 50 or more warnings (use warnings() to see the first 50)

My dput is too long to post here, so here is the pastebin for it: Dput forstack

Attempting to coerce $Hire.Date..Start with as.character produces the odd result which I have partially pasted here: as.character result

I am not sure what action to take here. I found a similar discussion here: stack question similar to this one

but this user was trying to call a specific portion of a column for ggplot2 graphing. Any help is appreciated.

Upvotes: 20

Views: 17155

Answers (4)

edog429
edog429

Reputation: 346

For me the issue was because the data.frame was grouped so I added ungroup(forstack) prior to write.xlsx and that fixed the issue.

Upvotes: 2

ishonest
ishonest

Reputation: 483

It's a trivial issue. But this trick works.

write.xlsx(as.data.frame(forstack), file = "forstackover.xlsx", col.names = TRUE)

This happens because when col.names or row.names are called out, the input file must be a data.frame and not a tibble.

Upvotes: 1

NCC1701
NCC1701

Reputation: 149

I agree with @greg dubrow's solution. I have a simpler suggestion as code.

write.xlsx(as.data.frame(forstack), file = "forstackover.xlsx", col.names = TRUE)

You can be more free with file.choose()

write.xlsx(as.data.frame(forstack), file = file.choose(), col.names = TRUE)

By the way, in my code, similar to @Lee's, it gave an error for row.names = FALSE. The error is now resolved. If we expand it a little bit more:

write.xlsx(as.data.frame(forstack), file = file.choose(), col.names = TRUE, row.names=FALSE)

Upvotes: 2

greg dubrow
greg dubrow

Reputation: 633

I had this issue when trying to write a tibble tbl_df to xlsx using the xlsx package.

The error threw when I added the row.names = FALSE option, but no error without row.names call.

I converted the tbl_df to data.frame and it worked.

Upvotes: 36

Related Questions