[Solved] Aligning images in cells

[NeoMe]

Greetings all,

I am having trouble finding an answer to this question: how can I align images in Calc cells? Specifically, I want them vertically and horizontally centered in the cell, and be anchored to the cell they are in.

The problem I'm having is, when I drag the .png file into the desired cell (or use Insert -> Picture -> From File...) the image is located in the cell where I drag it. I just want to drag and drop it into the cell and have it aligned correctly (the images are small icons, and the cells themselves are roughly 1 inch wide by .75 inches high, though it could vary). Does anyone have a suggestion on how I can do this?

[Zizi64]

how can I align images in Calc cells? Specifically, I want them vertically and horizontally centered in the cell, and be anchored to the cell they are in.

I think, it is not possible.
The picture is anchored only to the cell...
The picture not becomes content of cell. The cells are not graphical containers.
In other words: The picture is located not inside the cell, but OVER the cell, on the page (on the worksheet). You can align it manually, but it is a "virtual alignment" because when you change the size of cell, the picture not aligned automatically anymore.

[Charlie Young]

Try this on a backup. It is supposed to resize cells to fit graphics objects anchored to them. It works on the ActiveSheet. I've only tested it on one image so far

Code: Select all   Expand viewCollapse view

Sub fitGraphics()

   Dim Doc As Object
   Dim Sheet As Object
   Dim g as Object
   Dim c As Integer
   Dim s As new com.sun.star.awt.Size
   Dim p As new com.sun.star.awt.Point      
   
   Doc = ThisComponent
      
   Sheet = Doc.CurrentController.ActiveSheet
      
   c = Sheet.DrawPage.count
      
   Do While c >= 1
      g = Sheet.DrawPage(c - 1)
      if InStr(g.ShapeType,"GraphicObjectShape") > 0 then
         s = g.getSize()
         
         if Sheet.Rows(g.anchor.CellAddress.Row).Height < s.Height then
            Sheet.Rows(g.anchor.CellAddress.Row).Height = s.Height
         endif
         if Sheet.Columns(g.anchor.CellAddress.Column).Width < s.Width then
            Sheet.Columns(g.anchor.CellAddress.Column).Width = s.Width
         endif
         
         g.setSize(s)
         g.setPosition(g.anchor.Position)
      endif
      
      c = c - 1
   Loop
   
End Sub



[NeoMe]

Hi Charlie,

Try this on a backup. It is supposed to resize cells to fit graphics objects anchored to them. It works on the ActiveSheet. I've only tested it on one image so far

I'm still fairly new to NeoOffice, so I'm not sure how to use that method. Could you walk me through the procedure for using it, please?

[Hagar Delest]

See [Tutorial] How to install a code snippet. And run the macro.

Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[Charlie Young]

I'm a bit glad for the delay in testing - the code above isn't quite complete in fulfilling the task - it only handles the case where the graphic is larger than the cell.

The code is a bit more complicated now, but tested a little better. I threw a couple of shapes I had laying around into a spreadsheet and attached it here, with a button to run the code, which is installed (please see Hagar's link anyway).

Code: Select all   Expand viewCollapse view

Sub fitGraphics()

   Dim oDoc As Object
   Dim oSheet As Object
   Dim aCell As Object
   Dim g as Object
   Dim c As Integer
   Dim s As new com.sun.star.awt.Size
   Dim gp As new com.sun.star.awt.Point     
   Dim ap As new com.sun.star.awt.Point     
   Dim p As new com.sun.star.awt.Point     
   Dim xAdjust As Long
   Dim yAdjust As Long
   Dim rowHeight As Long
   Dim colWidth As Long
   
   oDoc = ThisComponent
   
   oSheet = oDoc.CurrentController.ActiveSheet
   
   c = oSheet.DrawPage.count
   
   Do While c >= 1
         g = oSheet.DrawPage(c - 1)
         if InStr(g.ShapeType,"GraphicObjectShape") > 0 then
            s = g.getSize()
            gp = g.getPosition()
            aCell = g.anchor
            rowHeight = oSheet.Rows(aCell.CellAddress.Row).Height
            colWidth = oSheet.Columns(g.anchor.CellAddress.Column).Width
            
            if rowHeight < s.Height then
               oSheet.Rows(aCell.CellAddress.Row).Height = s.Height
               yAdjust = 0
         else
            yAdjust = (rowHeight - s.Height) \ 2
         endif
         if colWidth < s.Width then
               oSheet.Columns(aCell.CellAddress.Column).Width = s.Width
               xAdjust = 0
         else
            xAdjust = (colWidth - s.Width) \ 2
         endif      
            ap = aCell.Position()
            gp = g.getPosition()
            if (rowHeight < s.Height) or colWidth < s.Width then
               g.setSize(s)
               g.setPosition(ap)
            else
               p.X = ap.X + xAdjust
               p.Y = ap.Y + yAdjust
               g.setPosition(p)
            endif
         endif
         c = c - 1
   Loop
   
End Sub



[NeoMe]

Charlie Young,

My apologies for the delay. I've tried your script on my document and it works brilliantly! Thank you very much for taking the time to solve my problem.