Picture resize with cells

[fjshi]

I am creating and saving .xlsx file using the xlsx package. Is there anyway to disable "Move and size with cells" for picture properties?

file <- system.file("tests", "log_plot.jpeg", package = "xlsx")

wb <- createWorkbook()  
sheet <- createSheet(wb, "Sheet1")

addPicture(file, sheet)

saveWorkbook(wb,"test.xlsx")

If you open the saved test.xlsx file and resize column width of columns with the plot, the inserted picture will resize with the column width change. After some research, I found that the added picture has a properties call "Move and size with cells" is enabled. Is there anyway to disable it or set as "Don't move or size with cells"

[colearendt]

Thanks for the report! Yes, this is possible, but is not exposed by xlsx today. It is a function of the underlying Java library, though.

org.apache.poi.ss.usermodel.ClientAnchor has ints / enums (old version has ints, I think that is what we are on - new version has enums) that specify, as described here:

public interface ClientAnchor
A client anchor is attached to an excel worksheet. It anchors against absolute coordinates, a top-left cell and fixed height and width, or a top-left and bottom-right cell, depending on the ClientAnchor.AnchorType:
ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE == absolute top-left coordinates and width/height, no cell references
ClientAnchor.AnchorType.MOVE_DONT_RESIZE == fixed top-left cell reference, absolute width/height
ClientAnchor.AnchorType.MOVE_AND_RESIZE == fixed top-left and bottom-right cell references, dynamic width/height
Note this class only reports the current values for possibly calculated positions and sizes. If the sheet row/column sizes or positions shift, this needs updating via external calculations.

https://poi.apache.org/apidocs/4.0/

It would not be too hard to add a parameter to the addPicture() function that makes this change. I'm not sure if/when I will have time to do it, but I would welcome a PR! If you can add tests to ensure before/after functionality is covered, that would be great!

It would be an addition of something like the following to the function (tracing through the details):

anchor_type <- "DONT_MOVE_AND_RESIZE"
# translate to the correct integer, then
.jcall(anchor, "V", "setAnchorType", as.integer(anchor_type))

You can see some of the patterns used for enums in the past with:

?xlsx::POI_constants

[fjshi]

That's what I imagine that if a parameter can be added to enable or disable "Move and size with cells", it will solve the issue. I would be happy to test any beta or new version to add this enhancement.