R: how to use writexl when data frame contains strings and numbers

Question

I'm new to R, and I'm trying to save data to an xlsx file. I'm using writexl (xlsx was causing trouble).

It seems that having strings and integers in my data frame causes problems when I try to use write_xlsx.

I've recreated the issue here:

library(writexl)

matrix <- matrix(1,2,2)
block <- cbind(list("ones","more ones"),matrix)
df <- data.frame(block)

data = list("sheet1"=df)

write_xlsx(data, path = "data.xlsx", col_names = FALSE, format_headers = FALSE)

The file data.xlsx correctly contains "sheet1", but it is blank. I would like

ones 1 1

more ones 1 1

Any way to get this output using write_xlsx?

It's more likely the problem is that you're creating a malformed data frame. Were you aware that each of the columns in `df` is actually a list, not a vector? In fact, even your matrix is a matrix of lists. Try just `df <- data.frame(a = c("ones","more ones"),b = c(1,1),c = c(1,1))`. Lots can go wrong trying to create data frames with `matrix` and `cbind` or `rbind` rather than just using `data.frame`. — joran, Jun 25 '19 at 21:30
Ok, good to know. Is there an acceptable way to incorporate an existing matrix and a list into a data frame, or do I have to build it up list by list? (I also have a list for the column names too, so it would be slightly inconvenient to break everything apart to build it back up.) — mkn, Jun 25 '19 at 22:02
Well, it would be safer to avoid lists unless the data types are actually different. If you have column names, those are all characters, and there's really no reason to use `list()` rather than just `c()`. But if you're going to build onto data frames using `cbind` or `rbind` you need to always check you're getting what you think and never just assume that R will magically coerce everything to what you naively assume. — joran, Jun 25 '19 at 22:07

score 1 · Accepted Answer · answered Jun 25 '19 at 21:21

1

I usually use openxlsx package. Try and adapt the following code:

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
writeData(wb, "Sheet1", df, colNames = FALSE)
saveWorkbook(wb, "test.xlsx", overwrite = TRUE)

answered Jun 25 '19 at 21:21

eastclintw00d

2,250
1
9
18

score 1 · Answer 2 · answered Jun 25 '19 at 21:37

I'd raise an issue on the github repo of the package: https://github.com/ropensci/writexl/issues.

Doing this:

df <- data.frame(
  X1 = c("ones", "more ones"),
  X2 = c(1, 1),
  X3 = c(1, 1)
)

write_xlsx(df, path = "data.xlsx", col_names = FALSE, format_headers = FALSE)

works fine. I'd say it's because df in your code has list-columns:

> str(df)
'data.frame':   2 obs. of  3 variables:
 $ X1:List of 2
  ..$ : chr "ones"
  ..$ : chr "more ones"
 $ X2:List of 2
  ..$ : num 1
  ..$ : num 1
 $ X3:List of 2
  ..$ : num 1
  ..$ : num 1

Not sure if the package is meant to have this functionality or not.

R: how to use writexl when data frame contains strings and numbers

2 Answers2