AddPicture Method: Identifying Scale Adjustment Direction and Proposing Enhanced Functionality

[t4traw]

I have a question regarding the AddPicture method.

I understand that in Excel, cell sizes can change depending on the screen size and other environmental factors. Therefore, I thought about adjusting the Scale value for each environment where the file is opened. However, when using the autoFit option with AddPicture, I realized I couldn't proceed without knowing in which direction the Scale is being adjusted.

When passing {autoFit: true} in GraphicOptions, and the image is scaled down to fit the cell width, for example, with a square image in a vertically longer cell, I assume the width fills the cell and the height in pixels is adjusted accordingly.

Is there a way to know whether the adjustment is being made to the width or the height?

Additionally, if there's no way to know this, should I propose a function that returns information about the scaled-down image (for example: AddPictureAndGetInfo) via a Pull Request?

[xuri]

Thanks for your issue. If you specify the AutoFit value as true when adding a picture, the image will be resized to fit the cell size but keep the original aspect ratio, not fill all over the cell if the aspect ratio is a different image size and cell size, the internal function drawingResize could calculate the height and width after resizing.

[t4traw]

@xuri Thank you for your response!

For instance, when I add an image using AddPicture, in my environment, the width gets scaled down to 18% and the height to 17%. In this case, it seems adjusting the height to a slightly larger scale value would make it fit nicely.

However, since the aspect ratio of cells and images can vary each time, attempting to adjust based on height alone might result in exceeding the cell's height.

The AddPicture function (specifically the drawResize function) first scales down the width, and if the image is still larger than the cell width, it scales down further.

    if float64(cellWidth) < width {
        asp := float64(cellWidth) / width
        width, height = float64(cellWidth), height*asp
    }
    if float64(cellHeight) < height {
        asp := float64(cellHeight) / height
        height, width = float64(cellHeight), width*asp
    }

Therefore, if we could know which side ends up being 100% when AutoFit is applied, I thought it would be possible to adjust the scale for either width or height accordingly.

I considered doing the above calculation myself, but since there isn't a public function to obtain the width and height of a cell, I decided to ask this question.

https://github.com/t4traw/AddPictureSample

[xuri]

This related issues #260, #279 and #569. We can calculate column width by referencing the following docs (ECMA-376, ISO/IEC 29500 §18.3.1.13):

Column width measured as the number of characters of the maximum digit width of the
numbers 0, 1, 2, …, 9 as rendered in the normal style's font. There are 4 pixels of margin
padding (two on each side), plus 1 pixel padding for the gridlines.

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel
padding}]/{Maximum Digit Width}*256)/256

[Example: Using the Calibri font as an example, the maximum digit width of 11 point font
size is 7 pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore, if
the cell width is 8 characters wide, the value of this attribute must be
Truncate([8*7+5]/7*256)/256 = 8.7109375. end example]

To translate the value of width in the file into the column width value at runtime
(expressed in terms of pixels), use this calculation:

=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum
Digit Width})

[Example: Using the same example as above, the calculation would be
Truncate(((256*8.7109375+Truncate(128/7))/256)*7) = 61 pixels. end example]

To translate from pixels to character width, use this calculation:
=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100

[Example: Using the example above, the calculation would be Truncate((61-
5)/7*100+0.5)/100 = 8 characters. end example]

[Note: when wide borders are applied, part of the left/right border must overlap with the
2 pixel padding on each side. Wide borders do not affect the width calculation of the
column. end note]

[Note: When the sheet is in the mode to view formulas instead of values, the pixel width
of the column is doubled. end note]

The possible values for this attribute are defined by the W3C XML Schema double
datatype.

The function convertRowHeightToPixels covert the row's height from pt to pixels (ECMA-376, ISO/IEC 29500 §18.3.1.73):

Row height measured in point size. There is no margin padding on row height

The point size is 1/72 of an inch.

[t4traw]

@xuri Thank you for your reply!

This might be slightly off-topic, but I tried to calculate cell sizes and extract margins by myself. However, I discovered that the calculation results differ between the public function GetRowHeight and the getRowHeight used internally by drawingResize.

-----GetRowHeight, GetColWidth-----
cellWidth:  306
cellHeight:  333
-----drawingResize(getRowHeight, getColWidth)-----
cellWidth:  360
cellHeight:  264

Being able to perform the same size calculations as those done during the AutoFit in AddPicture would allow for more flexible adjustment of Scale values. Thus, I desired a function to obtain the pixel size of this cell.