Get column and row size in order to center shape in a spreadsheet

[Jean-Christophe]

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

 

[Nikolay Demirev]

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

[Jean-Christophe]

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

[Jean-Christophe]

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

[Nikolay Demirev]

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