# Get column and row size in order to center shape in a spreadsheet Top achievements Rank 1
Jean-Christophe asked on 09 Jul 2019, 09:45 AM

Hello

I'm trying to add a picture to a spreadsheet. There is not problement to add the picture, but I'm trying to center it into a merge cell and I can't get the size.

I did the code bellow :

`Private` `Sub` `addLogo(``ByRef` `worksheet ``As` `Worksheet, ``ByVal` `columnCount ``As` `Integer``, ``ByVal` `rowCellIndex ``As` `Integer``, ``ByVal` `columnCellIndex ``As` `Integer``)`

`            ``Dim` `image ``As` `New` `FloatingImage(worksheet, ``New` `CellIndex(columnCellIndex, rowCellIndex), 0, 0)`

`            ``Using stream ``As` `Stream = ``Assembly``.GetExecutingAssembly().GetManifestResourceStream(``String``.Concat(``Assembly``.GetExecutingAssembly().GetName().Name, ``".MyLogo.png"``))`
`                ``image.ImageSource = ``New` `ImageSource(stream, ``"png"``)`
`                ``image.LockAspectRatio = ``True`
`                ``Dim` `ratio ``As` `Double` `= worksheet.Rows(rowCellIndex).GetHeight().Value.Value / image.Height`
`                ``image.Height = image.Height * ratio`
`                ``image.Width = image.Width * ratio`
`                ``image.OffsetX = worksheet.Columns(columnCellIndex, columnCellIndex + columnCount - 1).GetWidth().Value.Value - image.Width`
`            ``End` `Using`

`            ``worksheet.Shapes.Add(image)`

`        ``End` `Sub`

the worksheet.Rows(rowCellIndex).Getheight().Value.Value return 20 each time, unless the row is highter

and the worksheet.Columns(columnCellIndex, columnCellIndex + columnCount - 1).GetWidth().Value.Value return 65 each time, for one or more column.

So I don't know the right command to get the height and width of my merge cell, could you help me ?

Thanks

J-Christophe

## 4 Answers, 1 is accepted

0
Accepted Nikolay Demirev
Telerik team answered on 11 Jul 2019, 08:08 AM
Hi Jean-Christophe,

You could use the static method CalculateCellLayoutBox of the static class LayoutHelper. The method requires a worksheet instance and a cell index. The result is a CellLayoutBox instance, which contains information about the exact location of the cell in the document and it respects the merged cells. In order to get the layout box of the merged cells, you have to pass the cell index of the top left cell of the merged range.

Regards,
Nikolay Demirev
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers.
0 Jean-Christophe
Top achievements Rank 1
answered on 12 Jul 2019, 09:10 AM

Hi Nikolay

Thanks a lot, it works very well to get the cell size. But I have an other problem, it looks like the OffsetX of a floatingImage for shape could not be larger than the first column of the merge cells (I attache 2 print screen in order to show it), it looks like the picture has to be anchored at least at the right end of the first left top cell and the width is automatically increased.

here my code :

`Private` `Sub` `addLogo(``ByRef` `worksheet ``As` `Worksheet, ``ByVal` `rowCellIndex ``As` `Integer``, ``ByVal` `columnCellIndex ``As` `Integer``)`

`            ``Dim` `image ``As` `New` `FloatingImage(worksheet, ``New` `CellIndex(rowCellIndex, columnCellIndex), 1, 1)`
`            ``Dim` `cellLayout ``As` `CellLayoutBox = ``Me``.getCellSize(worksheet, rowCellIndex, columnCellIndex)`
`            ``Using stream ``As` `Stream = ``Assembly``.GetExecutingAssembly().GetManifestResourceStream(``String``.Concat(``Assembly``.GetExecutingAssembly().GetName().Name, ``".Rouge_H400 px_Mmax.png"``))`
`                ``image.ImageSource = ``New` `Telerik.Windows.Documents.Media.ImageSource(stream, ``"png"``)`
`                ``image.LockAspectRatio = ``True`
`                ``Dim` `ratio ``As` `Double` `= (cellLayout.Height - 2) / image.Height`
`                ``image.Height = image.Height * ratio`
`                ``image.Width = image.Width * ratio`
`                ``image.OffsetX = cellLayout.Width - image.Width`
`                ``image.OffsetY = (cellLayout.Height - image.Height) / 2`
`            ``End` `Using`

`            ``worksheet.Shapes.Add(image)`

`        ``End` `Sub`

And it's called like behind :

`worksheet.Cells(rowIndex, xOffset, rowIndex + 1, xOffset + maxWidth).Merge()`
`Me``.addLogo(worksheet, rowIndex, xOffset)`

Is there anything I didn't understand about shape and offsetX in telerik ?

thanks

JC

0 Jean-Christophe
Top achievements Rank 1
answered on 12 Jul 2019, 11:33 AM

Hi Nikolay

It's ok I finally find my mistake, I have to re-order my commands and use SetHeight function, so if someone needs something like this, here this is my code :

`Private` `Sub` `addLogo(``ByRef` `worksheet ``As` `Worksheet, ``ByVal` `rowCellIndex ``As` `Integer``, ``ByVal` `columnCellIndex ``As` `Integer``)`

`            ``Dim` `image ``As` `New` `FloatingImage(worksheet, ``New` `CellIndex(rowCellIndex, columnCellIndex), 1, 1)`
`            ``Dim` `cellLayout ``As` `CellLayoutBox = ``Me``.getCellSize(worksheet, rowCellIndex, columnCellIndex)`
`            ``Using stream ``As` `Stream = ``Assembly``.GetExecutingAssembly().GetManifestResourceStream(``String``.Concat(``Assembly``.GetExecutingAssembly().GetName().Name, ``".MyLogo.png"``))`
`                ``image.ImageSource = ``New` `Telerik.Windows.Documents.Media.ImageSource(stream, ``"png"``)`
`                ``image.LockAspectRatio = ``True`
`                ``Dim` `ratio ``As` `Double` `= (cellLayout.Height - 2) / image.Height`
`                ``Dim` `height ``As` `Double` `= image.Height * ratio`
`                ``Dim` `width ``As` `Double` `= image.Width * ratio`
`                ``image.OffsetX = cellLayout.Width - width`
`                ``image.OffsetY = (cellLayout.Height - height) / 2`
`                ``image.SetHeight(``True``, height, ``True``)`
`            ``End` `Using`

`            ``worksheet.Shapes.Add(image)`

`        ``End` `Sub`

Jean-Christophe

0 Nikolay Demirev
Telerik team answered on 16 Jul 2019, 12:59 PM
Hello Jean-Christophe,

I am glad to see that you have found a solution and is working fine for you.

Regards,
Nikolay Demirev
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers.
Tags Jean-Christophe
Top achievements Rank 1 Rank 1
{"ft_lang_label":"__label__en","ft_lang_prob":0.56734926,"math_prob":0.6330954,"size":1381,"snap":"2022-05-2022-21","text_gpt3_token_len":359,"char_repetition_ratio":0.16630356,"word_repetition_ratio":0.022857143,"special_character_ratio":0.217958,"punctuation_ratio":0.21825397,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9571103,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32],"im_url_duplicate_count":[null,null,null,null,null,7,null,null,null,null,null,null,null,null,null,null,null,7,null,null,null,null,null,null,null,7,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-05-25T10:37:29Z\",\"WARC-Record-ID\":\"<urn:uuid:71a07c86-110d-4b4d-a938-79c330df157d>\",\"Content-Length\":\"151531\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:42745b64-3e84-4cc3-8468-1c62b34cf6d3>\",\"WARC-Concurrent-To\":\"<urn:uuid:a83e5cfb-dcd5-4ce0-882c-ac76a5e750f1>\",\"WARC-IP-Address\":\"50.56.19.112\",\"WARC-Target-URI\":\"https://www.telerik.com/forums/get-column-and-row-size-in-order-to-center-shape-in-a-spreadsheet\",\"WARC-Payload-Digest\":\"sha1:OA6S4D4IKZW3ASKNY5TVQICPUW3ZEOCI\",\"WARC-Block-Digest\":\"sha1:YWW3ZO6IEFW45GFHHTY57UN6ONHATNS4\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-21/CC-MAIN-2022-21_segments_1652662584398.89_warc_CC-MAIN-20220525085552-20220525115552-00554.warc.gz\"}"}