R openxlsx package. writing NA as blanks?

Question

I'm using the openxlsx package to read and write Excel files.

I've noticed that when I export the table to Excel with write.xlsx(MyData, file="MyFile.xlsx") NAs appear as #NUM! when the file is opened on Excel.

Is there any option to export it just as blanks? I've being googling and tried options such as showNA or keepNA but they don't seem to have any effect.

Aleurone

If no parameter is found in that package, you could use another excel writing package or convert the NA values to blanks with something like df[] <- lapply(df, function(x) {x[is.na(x)] <- "";x}) – Bascule

I can't understand the downvote. I thought some people would know how to do it, it could be impportant and useful for other users. I've asked on openxlsx github and if it doesn't exist that option I will send a ferature request. – Aleurone

Answer 1

I can't understand the downvote. I thought some people would know how to do it using just an option, without the need of manually tansforming every NA.

As I think it could be important and useful for other users I've also asked on openxlsx's github development page and they replied:

 https://github.com/awalker89/openxlsx/issues/108#issuecomment-125142950

In case anybody is interested, if you update to the dev version with

 devtools::install_github("awalker89/openxlsx")

you will get support for this feature.

the default behaviour now is converting NAs to blanks And if we want to have #N/A we must use the option keepNA = TRUE the option keepNA = TRUE keeps the #N/A

require('openxlsx')
df <- head(iris)
df[2,3] <- NA
df[2,5] <- NA
df[3,5] <- NaN
openXL(write.xlsx(df, file = tempdir()))

 require('openxlsx')
 df <- head(iris)
 df[2,3] <- NA
 df[2,5] <- NA
 df[3,5] <- NaN
 openXL(write.xlsx(df, file = tempdir(), keepNA = TRUE))

Aleurone

Answer 2

Good question! One I've been looking at as well. I believe that the default behavior IS to output the NA values as blanks. However, as you show in your example code you have both NA and NaN values. The presence of one or more NaN values in a column will result in all NA and NaN values in that column showing up in Excel as #NUM!.

To fix this, replace all instances of NaN in your file with NA prior to exporting the file:

df[is.na(df)] = NA

Note the use of is.na rather than is.nan when x is a data.frame.

See: How to replace NaN value with zero in a huge data frame?

Experience

Answer 3

Creating the workbook before saving it works for me:

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, 1, keepNA = T, df)
saveWorkbook(wb, file = "keepNA.xlsx")

Hyetal