Using mergeCells() in openxlsx package R creates error in output

user_7881408 0
Viewed 2044

I'm trying to output a data frame in R into excel but I keep getting an error when I use mergeCells() when trying to open the resulting xlsx file. While the cells do merge, my data is "lost." I can unmerge the cells and the data is there but I want to format it so the output (ex. column 1 of my df) spans over multiple columns.

I've tried merging the cells before and after writing the data to the worksheet. I've also tried using writeDataTable() and writeData(), both did not work. I've tried starting the df on different columns (as seen below). For example, start writing the df to column 2, and merge columns 1:2. The other one I merged columns 1:2 first then wrote data starting on column 1.

df <- data.frame(
Category = c("A", "B", "C"),
Type = c("x", "y", "z"),
Number = c("1", "2", "3"), stringsAsFactors = FALSE)

book <- createWorkbook()
sheet <- "Sheet1"
writeData(book, sheet, df, startCol = 2, startRow = 1, colNames = TRUE)
mergeCells(book, sheet, cols = 1:2, rows = 1)
mergeCells(book, sheet, cols = 1:2, rows = 2)
mergeCells(book, sheet, cols = 1:2, rows = 3)
saveWorkbook(book)

OR

mergeCells(book, sheet, cols = 1:2, rows = 1)
mergeCells(book, sheet, cols = 1:2, rows = 2)
mergeCells(book, sheet, cols = 1:2, rows = 3)    
writeDataTable(book, sheet, df, startCol = 1, startRow = 1, colNames = TRUE)
saveWorkbook(book)

When opening the file after saving, the error is "We found a problem with some content, etc. Excel was able to open the file by removing or repairing unreadable content."

Any help is appreciated!

openxlsx dataframe r
1 Answers

If I understand your question correctly, you want to merge columns 1 and 2 to produce a merged column with the first column of df. If this is correct, then your issue is that the left most column to which you are merging (column 1) is blank. openxlsx::mergeCells() will then keep the content of the left-most column merged. To merge columns 1 and 2 and have the contents of df[1] in it, you need to write the content of df[1] on the left-most column like so:

library(openxlsx)

df <- data.frame(
  Category = c("A", "B", "C"),
  Type = c("x", "y", "z"),
  Number = c("1", "2", "3"), stringsAsFactors = FALSE)

wb <- createWorkbook() # creates workbook
addWorksheet(wb, "Sheet1") # adds sheet
writeData(wb, 1, df[1], startCol = 1, startRow = 1, colNames = TRUE) # writing content on the left-most column to be merged
writeData(wb, 1, df[2:3], startCol = 3, startRow = 1, colNames = TRUE) # write the rest of the content on the columns that wont be merged
for(i in seq_len(nrow(df) + 1)){ # loop over rows for merging
  mergeCells(wb, 1, cols = 1:2, rows = i)
}
rm(i)
saveWorkbook(wb, "test.xlsx", overwrite = T) # save workbook
user_51995140
Related