R write xlsx files converting blank cells in a numeric output

I have a matrix like this

> df <- head( iris )
> df[2,3] <- NA
> df[2,5] <- NA
> df[3,5] <- NA

with some NA. So, if I put convert NA like this

df[is.na( df )] <- "#N/D"

df becomes a character matrix and when I write.xlsx my file excel contains a error message because doesn't recognize the numeric format. If I put logical NA in this way df[is.na( df )] <- NA and I write.xlsx I get a matrix output with blank cells. How can I obtain a matrix with "#N/D" and with the numeric format?

Solution

I have an impression that setting keepNA = TRUE will solve your problem. According to the documentation of the openxlsx::write.xlsx():

keepNA If TRUE, NA values are converted to #N/A in Excel else NA cells will be empty. Defaults to FALSE

An example is here:

# generate test data with NA values
test_df <- iris
test_df[c(1, 3, 7), ] <- NA
# write the file
write.xlsx(test_df, file = "test.xlsx", 
    keepNA = TRUE)

Does it what you want to achieve?