CODEWITHSUNDEEP
rexcelna
John Ruco
John Ruco

Reputation: 1

R replace NA to empty field(nothing) when write in Excel

I want that NA replace empty field. Can you write function. Can in R store empty fields?

library(dplyr)
library(xlsx)
require(readxl)
Sys.setlocale(locale = 'Russian')
fname <- file.choose()
basetable<- read_xlsx(fname)
result_table<-basetable %>% 
  group_by(`Product`,`Mark`,`Dens.`,`Format`,`Cons`,`Date`,`Company`)%>% 
  summarise(`Sum` = sum(`Weight`),`Count`=sum(`Расход`==0)) 
xlsx::write.xlsx(as.data.frame(result_table), 'file.xlsx', sheetName="Sheet 5", append=TRUE)

Upvotes: 0

Views: 1245

Answers (2)

Len Greski
Len Greski

Reputation: 10865

An option that does not require the rJava package is the writexl package, which by default does not write NA to an output Excel file.

df <- data.frame(x = c(1,2,3,NA,5),y = c("a","b","c","d","e"))
library(writexl)
write_xlsx(df,"./data/writexl_output.xlsx")

...and the results when we open the file in Excel:

enter image description here

Upvotes: 0

missuse
missuse

Reputation: 19726

The function xlsx::write.xlsx has the argument showNA which is at default set to TRUE. This argument controls if NA values are left as is (when TRUE) or if they are left as empty cells (when FALSE).

xlsx::write.xlsx(as.data.frame(result_table),
                 'file.xlsx',
                 sheetName = "Sheet 5",
                 append = TRUE,
                 showNA = FALSE)

Upvotes: 2

Related Questions