CODEWITHSUNDEEP
ropenxlsx
Ali
Ali

Reputation: 1080

How can I write a data frame to excel without cells overlapping

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

Upvotes: 0

Views: 228

Answers (1)

Allan Cameron
Allan Cameron

Reputation: 174586

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)

Upvotes: 3

Related Questions