Centering an image on merged cells on openxlsx2 package in R

[singhalbhavik]

I have been trying to center an image on merged cells using the openxlsx2 package in R. The image's default is the top-left corner of the merged cell. In the wb$add_image, if I specify the merged cells instead of the first cell, it won't keep the aspect ratio of the image which is necessary as well. I have tried playing around with center and vcenter options and offset values as well. AI is of no use as well. Is there a way I can do this?

This is my code:

This is how it looks right now, I want to center in the middle:

This is what I want:

[JanMarvin]

Hi @singhalbhavik ,

getting the middle of a cell is rather tricky. The Cell has a column width and a row height and as long as one does not want to fit something precisely into this dimension, a bit of toying around is required. (I haven't figured out a formula for this, but obviously it should be possible).

library(openxlsx2)

img <- system.file("extdata", "einstein.jpg", package = "openxlsx2")


wb <- wb_workbook()$
  add_worksheet()$
  set_col_widths(cols = "A", width = 40)$
  set_row_heights(row = 1, height = 200)

# toy around with these. probably between:
# min: "-27273042329600"
# max: "27273042316900"

row_offset <- 999999
col_offset <- 999999

wb$add_image(dims = "A1", file = img, width = 1, height = 1, units = "cm",
             row_offset = row_offset, col_offset = col_offset)

if (interactive()) wb$open()

The alternative would be (for squared images and maybe a square cell)

library(openxlsx2)

img <- system.file("extdata", "einstein.jpg", package = "openxlsx2")


wb <- wb_workbook()$
  add_worksheet()$
  set_col_widths(cols = "A", width = 40)$
  set_row_heights(row = 1, height = 200)

# toy around with these. probably between:
# min: "-27273042329600"
# max: "27273042316900"

row_offset <- c(999999, -999999)
col_offset <- c(999999, -999999)

wb$add_image(dims = "A1:A1", file = img, 
             row_offset = row_offset, col_offset = col_offset)

if (interactive()) wb$open()

[singhalbhavik]

Hi, thanks for the response! So I see you can change the dimensions of rows and columns. However, that means changing the dimension of a row will change everytime for that row. I haave components in the project that are side by side and would mess up the formatting.

Based on the image above, me changing the height of row 11 for example would mess up the formatting of the component on the left that says "Finish 56%"

[JanMarvin]

:) That's the downside of using spreadsheets. Everything is a little fluid. I assume you could try a few values and settle with something that looks centered, but isn't necessarily. Whenever somebody with a different screen resolution or a different spreadsheet version opens it, it will always look a little different. For example: if I remember correctly, cell widths in Excel for Windows and Mac are different and since there is no real "image is centered" function in the OOXML standard, there is no real way to center something fluently on both OSes.

You could center it with your spreadsheet software, load the file and inspecht wb_load()$drawings |> as_xml(). There you can see the values that your images use.