0

I have a data frame to write to excel but I'm struggling to fit the border around a cell.

If a cell of a data frame is overlapping with another cell, I want to merge the columns together and the same for it's column name.

Here is a reproducible example of what I am after:

library(openxlsx)
wb <- createWorkbook()
Sheet <- addWorksheet(wb, "Sheet 1")

Df <- data.frame(Location = c("AAAAAAAAAAAAAAAAAAAAAAAAA"))

writeDataTable(wb, Sheet, Df)

shell(shQuote(string = wb$saveWorkbook()), wait = TRUE)

enter image description here

Ali
  • 1,048
  • 8
  • 19

1 Answers1

3

Sure, you just need to merge and center the cells. The following code will do what you asked (though see my footnote)

library(openxlsx)
wb <- createWorkbook()
Sheet <- addWorksheet(wb, "Sheet 1")

Df <- data.frame(Location = c("AAAAAAAAAAAAAAAAAAAAAAAAA"))

# Write the data without a filter
writeData(wb, Sheet, Df, withFilter = FALSE)

# Create a style to allow the cells to be centred
centred <- createStyle(halign = "center")

# Merge and center each row                   <- Loop referred to in footnote
for (i in 1:(length(Df$Location) + 1) )
{
  addStyle(wb, Sheet, centred, i, 1:4)
  mergeCells(wb, Sheet, 1:4, i)
}

# Save the file
saveWorkbook(wb, path.expand("~/test_merge.xlsx"))

Footnote

Instead of merging the cells, it would be better to adjust their width according to the contents. This would allow you to write multi-column data frames. So, instead of having the loop above, you could just do

addStyle(wb, Sheet, centred, 1:(length(Df$Location) + 1), 1)
setColWidths(wb, Sheet, 1, max(nchar(as.character(Df$Location))) * 1.5)
Allan Cameron
  • 147,086
  • 7
  • 49
  • 87
  • Thank you for your answer and the footnote. The reason behind merging instead of adjusted width is due to further tables will need to be written below which different widths. To overcome this, I thought merging would be the best solution. – Ali Dec 18 '19 at 11:21