How to Add Text to Merged Cell using XLSX Package – R

The merging is an operation of combining two or more cells in an excel sheet. So to insert data into those merged cells using xlsx package we have a function named addMergingRegion() to merge cells around the specific regions and addDataFrame() to add data frame/text in that merged area.

Steps to add text to merged cell using xlsx package in R

Step 1: First, we need to install and load the xlsx package.

R

# install required packages
install.packages("xlsx")

# Load the installed Package
library(xlsx)

Step 2: Next we need to create a Workbook and later we’ll update the data of that created workbook. To do so we have functions in the R Programming Language.

createWorkbook(): To create an empty workbook

Syntax: createWorkbook(creator, title)

Where,

• creator – Creator of Workbook(Defaults to login username)
• title – Title of the workbook is specified here.

createSheet(): Used to create a worksheet with sheet names in the Workbook

Syntax: createSheet(object, name)

Where,

• object – The workbook which need to be used
• name – The name of the Sheet is Specified here

R

# Creation of empty Workbook
work_book=xlsx::createWorkbook()

# Creation of sheet in the workbook(
sheet =  xlsx::createSheet(work_book,"Sheet1")

Step 3: After the Creation of the sheet, we need to merge cells and add text in that merged area. So in our example, we are merging cells of the 5th row from the 3rd column to the 6th column and will add text there using 

addMergedRegion(): Used to merge the cells under Specified Region.

Syntax: addMergedRegion(sheet,startRow,endRow,startColumn,endColumn)

Where,

• sheet – worksheet is specified here
• startRow -used to specify the starting row of merged cell
• endRow – used to specify the ending row of merged cell
• startColumn – used to specify the starting column of merged cell
• endColumn – used to specify the ending column of merged cell

addDataFrame(): Used to add a data frame in the specified sheet.

Syntax: addDataFrame(x,sheet,col.names,row.names)

Where,

• x – dataframe to be added to sheet
• sheet – the sheet where the data need to be specified
• col.names – TRUE if the column names of x are to be written along with x to the file else FALSE
• row.names – TRUE if the row names of x are to be written along with x to the file else FALSE

In the addDataFrame() function we need to pass matrix as data because by default the add dataframe() function is going to append text in the first cell so to append at a merged position we need to specify that data in that matrix and insert that data into excel file.

R

# Merging cells of the sheet of 5th
# and 6th row and 3rd to 7th column
xlsx::addMergedRegion(sheet = sheet,
                      startRow = 5,
                      endRow = 6,
                      startColumn = 3,
                      endColumn = 6)

# Creating a blank matrix and initializing
# it with the desired data.
m = matrix(nrow = 7, ncol = 10)
m[5: 6, 3: 6] <-"Geeks For Geeks is highly Informative"

Now let’s add the above dataframe to the worksheet we created earlier and then we can save that excel file and then open it with MS Excel to see the output.

R

# Addition of dataframe to the sheet
# Finally save the workbook
xlsx::addDataFrame(m, sheet=sheet,
                   col.names=F,
                   row.names=F)
xlsx::saveWorkbook(work_book, "Sample.xlsx")

Output: