[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

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

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.