Display NA values in excel when using WriteXLS in R

The term NA refers to “No Value is Available”. The NA values in an Excel file are the cells that are left empty( there is no data). 

In R Programming, the data in the excel files are manipulated using the xlsx package. The Null (NA) values present in an excel file are displayed by initializing the attribute showNA to TRUE in the write.xlsx() function. By doing this we were storing the Null (NA) value as itself by default it’s stored as Blank space and to check whether NA values are stored and we are displaying the stored data in the excel file using read.xlsx() function.

WriteXLS Module

The xlsx() package is also responsible to manipulate the data in an excel file but the inclusion of the WriteXLS package is done to overcome the drawbacks present in it. Using the writeXLS package we can write one or more data frames into an Excel File which is not possible using the xlsx() package but can be done using xlsx2().

Syntax : write.xlsx(df,path,sheetName,col.names,showNA,password)

Where,

• df – data frame to be inserted into Excel file
• path – path to store excel file
• sheetName – The sheet name where the data frame is going to store is specified here
• col.ames – If TRUE the column names of the data frame are inserted as Header in the Excel file and vice versa
• showNA – If TRUE the NA values are stored as NULL only otherwise they are going to store as blank values
• password – Used to protect files by enabling passwords if required.

Steps to be followed

The steps we need to follow to display NA values in excel when using WriteXLS.

1. First we need to install the xlsx package and load it.
2. We need to initialize the dataframe.
3. Later using write.xlsx() function we can inert the data frame into an excel file the attributes of this function are discussed here…
   • df – represents the name of the data frame in our code
   • sheetName – used to specify the sheet Name when this data frame is go to be inserted(Sheet1 in our code)
   • col.names – to make sure the header of data frame are inserted as headings in excel file (TRUE)
   • showNA – This attribute is set to TRUE to make sure that all the NULL (NA) values in our data frame are inserted as NULL values instead of inserting them as blank spaces in the excel document.
4. Finally we are displaying the data present in an excel file by reading it using xlsx.read() function in xlsx package.

R

# install the xlsx package
install.packages("xlsx")

# load the package into working environment
library(xlsx)

# Creation of Sample Data Frame (Student Details)
df<-data.frame(
  stu_id=c(1,2,NA,4,5,6,NA,8,9,10),
  stud_name=c('Abhi','Bhuvan','Cherry','Don',
              'Eshwar','Firoz',NA,'Kiran',NA,'Zaheer'),
  age=c(12,13,NA,15,16,NA,NA,12,14,12)
)

Once the dataset is created, now we can use it to display NA values.

R

# Writing the data frame into an excel file including NULL(NA) values
xlsx::write.xlsx(df,"C:\\Users\\Downloads\\Sample_excel.xlsx",
                 sheetName="Sheet1",
                 col.names = TRUE,row.names=TRUE,showNA=TRUE)

# Reading data to cross check whether Null Values are stored
# and displayed to us when we read that File
data<-xlsx::read.xlsx("C:\\Users\\Downloads\\Sample_excel.xlsx",
                      sheetName="Sheet1")

# Printing the data stored in Excel File
print(data)

Output: