| View previous topic :: View next topic |
| Author |
Message |
Guest
|
 Posted: Tue May 18, 2004 2:18 am Post subject: Selecting cell based on X/Y-coordinates. A better way... |
 |
|
Hi,
sometimes I need to get a specific cell in a Calc sheet based on some X/Y coordinates. These coordinates are typically retrieved from a combobox/listbox placed on the sheet. The code would look something like this:
| Code: |
oSheet = ThisComponent.Sheets.GetByName("Sheet1")
oShape = GetControlShape(oSheet, "aComboBox")
oShapePosition = oShape.getPosition()
X = oShapePosition.X
Y = oShapePosition.Y
|
Well, now I'd like to get the cell that has these exact X/Y coordinates (this cell exists). At the moment I use this code to get the cell in question:
| Code: |
Function getRowColFromPosition(X As Long, Y As Long, sSheetName As String, iRow As Integer, iCol As Integer) As Boolean
Dim oSheet As Object
Dim oCellPosition As Object
Dim i As Integer
Dim flag As Boolean
oSheet = ThisComponent.Sheets.GetByName(sSheetName)
flag = False
for i = 0 to 31999
oCellPosition = oSheet.getCellByPosition(0, i).Position
if (oCellPosition.Y = Y) then
iRow = i
flag = True
exit for
endif
next
if (flag = False) then iRow = -1
flag = False
for i = 0 to 255
oCellPosition = oSheet.getCellByPosition(i, 0).Position
if (oCellPosition.X = X) then
iCol = i
flag = True
exit for
endif
next
if (flag = False) then iCol = -1
if ((iRow = -1) or (iCol = -1)) then
getRowColFromPosition = False
else
getRowColFromPosition = True
endif
End Function
|
As you can see, I iterate through the cells until I find the one I'm looking for. I have not found a better way to do this, but surely there must be an API that does this much more efficiently...
Henrik |
|
| Back to top |
|
 |
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8979
Location: Lexinton, Kentucky, USA
|
| Posted: Tue May 18, 2004 4:21 am Post subject: |
|
|
| Prehaps I don't understand the question but if you have the exact XY position in X and Y why not just use oCell = oSheet.getCellByPosition(X,Y) or possibly (X-1,Y-1) if you are counting from 1. |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Tue May 18, 2004 5:30 am Post subject: |
|
|
| JohnV wrote: | | if you have the exact XY position in X and Y why not just use oCell = oSheet.getCellByPosition(X,Y) |
Lets use the terms Col,Row to refer to the address of a spreadsheet cell. Let's use X,Y to refer to a pixel position of that cell. The routine above, when given an X,Y coordinate (in pixels) returns the Col,Row address of the cell. The getCellByPosition() takes the column and row address and returns the cell's model. The Guest's routine above, will return the column,row address from the X,Y pixel location, and then you can use the Col,Row as parameters to getCellByPosition().
| Guest wrote: | | sometimes I need to get a specific cell in a Calc sheet based on some X/Y coordinates. |
Here are some ideas for improvement. (If I had time right now, I'd just write a routine and post it.)
Relational Operators vs. Equality test of coordinates
Rather than check Position.X = X, it might be better not to assume that the pixel position passed in exactly matches the position of a cell. That is, the routine would use relational operators (i.e. greater than, less than, etc.) to identify the cell that contains the pixel.
Something like...
If (oCell.Position.X <= X) AND (oCell.Position.X + oCell.Size.Width >= X)
Binary Search
Another possible improvement would be rather than to brute force iterate through all of the cells in a row or column, instead to do a binary search. If the middle row, say row, 16000 is higher than the position Y, then next you inspect row 8000, then row 4000, then row 2000. Since row 2000 is lower than Y, you would then try row 3000, then 2500, then 2750, then 2875, then 2812, etc. until you zero in on the cell. For 32000 rows, you will need a maximum of about 15 tests to identify the cell, rather than 32000 tests. Similar logic for finding the column.
Col,Row for consistency
Rather than have Row,Col returned in the parameter list, have Col,Row returned instead to maintain consistency with the usage in the API.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
Henrik
OOo Enthusiast
Joined: 15 Jan 2004
Posts: 118
|
| Posted: Tue May 18, 2004 7:36 am Post subject: |
|
|
Hi.
JohnV, X/Y refer to a pixel position. Sorry for not stating that explicitly. I kind of hoped the first code segment of my post made that clear, but I can easily see that I was wrong to assume so.
DannyB I have implemented the changes you suggested (also renaming the function). For anyone interested, the code now looks like this:
| Code: |
Function getColRowFromPosition(X As Long, Y As Long, sSheetName As String, iCol As Integer, iRow As Integer) As Boolean
On Error GoTo ErrorHandler
Dim oSheet As Object
Dim oCellPosition As Object
Dim low As Long
Dim high As Long
Dim middle As Long
Dim diff As Double
oSheet = ThisComponent.Sheets.GetByName(sSheetName)
low = 0
high = 32000 - 1
diff = -1
do while (low <= high)
middle = CLng((low + high)/ 2)
oCellPosition = oSheet.getCellByPosition(0, middle).Position
if (oCellPosition.Y = Y) then
iRow = middle
exit do
elseif (oCellPosition.Y > Y) then
if ((Abs(oCellPosition.Y - Y) < diff) or (diff = -1)) then
iRow = middle
diff = Abs(oCellPosition.Y - Y)
endif
high = middle - 1
else
if ((Abs(oCellPosition.Y - Y) < diff) or (diff = -1)) then
iRow = middle
diff = Abs(oCellPosition.Y - Y)
endif
low = middle + 1
endif
loop
low = 0
high = 256 - 1
diff = -1
do while (low <= high)
middle = CLng((low + high)/ 2)
oCellPosition = oSheet.getCellByPosition(middle, 0).Position
if (oCellPosition.X = X) then
iCol = middle
exit do
elseif (oCellPosition.X > X) then
if ((Abs(oCellPosition.X - X) < diff) or (diff = -1)) then
iCol = middle
diff = Abs(oCellPosition.X - X)
endif
high = middle - 1
else
if ((Abs(oCellPosition.X - X) < diff) or (diff = -1)) then
iCol = middle
diff = Abs(oCellPosition.X - X)
endif
low = middle + 1
endif
loop
getColRowFromPosition = True
exit Function
ErrorHandler:
getColRowFromPosition = False
MsgBox error$
End Function
|
Well, I hope it works like it is intended to... What I actually hoped for was an API that does the job of this function. I have not found one, and I guess I don't care to spend more time looking for one either. 
The whole idea behind the function is to find which cell in a Calc sheet a control (combobox/button...) is closest to.
Henrik |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Tue May 18, 2004 7:31 pm Post subject: |
|
|
I thought that there was a way to find the first cell in the view. That said, this is pretty nice code!
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
