Using openxlsx, I’d like to insert three separate plots on the same worksheet row. I know this can be done by manually, by specifying the startCol argument for each insertPlot() call. But this strategy runs into trouble when the column widths on the worksheet are variable because startCol doesn’t take widths into account.
Example 1, with adequate spacing between plots :
library(openxlsx)
df1 <- data.frame(a = "abcdefghijklmnop",b = "abcdefghijklmnop", c = "abcdefghijklmnop",
d = "abcdefghijklmnop",e = "abcdefghijklmnop",f = "abcdefghijklmnop",
g = "abcdefghijklmnop",h = "abcdefghijklmnop")
wb <- createWorkbook()
addWorksheet(wb, "sheet1")
writeData(wb, "sheet1", df1)
plot(c(1,2,3), c(10,11,12), main = "Plot1")
insertPlot(wb, "sheet1", startRow = 5, startCol = 1, width = 4, height = 3)
plot(c(100,20,300), c(15,11,55), main = "Plot2")
insertPlot(wb, "sheet1", startRow = 5, startCol = 5, width = 4, height = 3)
plot(c(100000,33333,99), c(42,420,4200), main = "Plot3")
insertPlot(wb, "sheet1", startRow = 5, startCol = 9, width = 4, height = 3)
setColWidths(wb, "sheet1", cols = 1:10, widths = "auto")
saveWorkbook(wb, "plots.xlsx", overwrite = TRUE)
Example 2, with overlapping plots :
library(openxlsx)
df2 <- data.frame(a = "abcdefghijklmnop", b = "abcdefgh", c = "abcdefghi", d = "aop",
e = "abcdenop", f = "abcdefghijklmnp", g = "abcdefghijkl", h = "abcdefg")
wb <- createWorkbook()
addWorksheet(wb, "sheet1")
writeData(wb, "sheet1", df2)
plot(c(1,2,3), c(10,11,12), main = "Plot1")
insertPlot(wb, "sheet1", startRow = 5, startCol = 1, width = 4, height = 3)
plot(c(100,20,300), c(15,11,55), main = "Plot2")
insertPlot(wb, "sheet1", startRow = 5, startCol = 5, width = 4, height = 3)
plot(c(100000,33333,99), c(42,420,4200), main = "Plot3")
insertPlot(wb, "sheet1", startRow = 5, startCol = 9, width = 4, height = 3)
setColWidths(wb, "sheet1", cols = 1:10, widths = "auto")
saveWorkbook(wb, "plots.xlsx", overwrite = TRUE)
Is there "dynamic" coding solution that allows these plots to be inserted with more sensible horizontal inter-plot spacing? (Maybe by estimating the start column by what "auto" width does, and also taking into account the image widths?)
My reports have dozens of worksheets, so doing this manually would be tedious. The plots must to be separate (not combined into one image). The plots will always have the same height and width, but column widths will vary each time this worksheet is generated.
question from:
https://stackoverflow.com/questions/66050534/control-horizontal-spacing-for-multiple-plots-on-same-xlsx-worksheet-row 
