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] <- "Beginner For Beginner 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: