R write xlsx files converting blank cells in a numeric output

Question

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?

What package are you using that has the `write.xlsx` function? — MrFlick, Jul 02 '18 at 20:18

score 0 · Accepted Answer · answered Jul 03 '18 at 09:13

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?

Thanks a lot!! I don't know why but I tried all day showNA=TRUE — Francesco, Jul 04 '18 at 09:59

R write xlsx files converting blank cells in a numeric output

1 Answers1