[Solved] Aligning images in cells

Discuss the spreadsheet application

[Solved] Aligning images in cells

Postby NeoMe » Tue Sep 28, 2010 9:03 pm

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?
Last edited by NeoMe on Mon Oct 11, 2010 10:12 pm, edited 1 time in total.
NeoOffice 3.0.2 Patch 0 on OS X.5.8
NeoMe
 
Posts: 9
Joined: Mon Sep 13, 2010 9:13 pm

Re: Aligning images in cells

Postby Zizi64 » Tue Sep 28, 2010 9:52 pm

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.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.1 on Win7x64Prof.
PortableApps, winPenPack: LO3.3.0-LO6.1.2 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7442
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Aligning images in cells

Postby Charlie Young » Wed Sep 29, 2010 2:32 am

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 :alarm:

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

Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Aligning images in cells

Postby NeoMe » Wed Sep 29, 2010 7:33 am

Hi Charlie,

Charlie Young wrote: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?
NeoOffice 3.0.2 Patch 0 on OS X.5.8
NeoMe
 
Posts: 9
Joined: Mon Sep 13, 2010 9:13 pm

Re: Aligning images in cells

Postby Hagar Delest » Wed Sep 29, 2010 8:08 am

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.
AOO 4.1.6 on Xubuntu 18.10 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28289
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Aligning images in cells

Postby Charlie Young » Wed Sep 29, 2010 10:20 am

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

Attachments
fitgraphics.ods
Fit graphics in cells
(16.97 KiB) Downloaded 900 times
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Aligning images in cells

Postby NeoMe » Mon Oct 11, 2010 10:12 pm

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.
NeoOffice 3.0.2 Patch 0 on OS X.5.8
NeoMe
 
Posts: 9
Joined: Mon Sep 13, 2010 9:13 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 33 guests