This is a migrated thread and some comments may be shown as answers.

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

4 Answers 157 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Jean-Christophe
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

Sort by
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. Learn More.
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. Learn More.
Tags
Spreadsheet
Asked by
Jean-Christophe
Top achievements
Rank 1
Answers by
Nikolay Demirev
Telerik team
Jean-Christophe
Top achievements
Rank 1
Share this question
or