| View previous topic :: View next topic |
| Author |
Message |
bitouze
General User
Joined: 31 May 2005
Posts: 6
|
 Posted: Wed Jun 01, 2005 8:21 am Post subject: Hidden cells in merged range |
 |
|
Hi,
as I can see in pitonyak's (excellent) book:
| Quote: | | After merging the range B2:D7, cell B2 appears in the area formerly used by the entire range. What is not shown in Figure 4 is that the cells that aren’t visible still exist and are accessible; they are simply not displayed. Use the getIsMerged() method to determine if all of the cells in a range are merged. |
and, as he said in the thread http://www.oooforum.org/forum/viewtopic.phtml?t=15702,
| Quote: | | I do not know off hand how to quickly find cells that are merged. |
and that's a pitty! 
Indeed, I actually have to know if some cells are in a merged area or not. Has anybody an idea?
Thanks.
_________________
Denis |
|
| Back to top |
|
 |
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
| Posted: Thu Jun 02, 2005 12:55 am Post subject: |
|
|
there seems to be no easy solution, indeed. Browsing through the CellFormatRanges and identifying adjacent areas helps ...
See the following code, which is not thoroughly tested, however 
| Code: | REM ***** BASIC *****
option explicit
Sub main
dim oCell as Object
oCell = ThisComponent.sheets.getByname("Sheet3").getCellRangeByName("B3")
msgbox getIsCellInMergedArea(oCell)
end sub
function getIsCellInMergedArea(oCell)
dim oCellFormatRanges as Object, oCellFormatRange as Object
dim j as long, k as long, lStartCol as long, lEndCol as long, lStartRow as long, lEndRow as long
getIsCellInMergedArea = false
oCellFormatRanges = oCell.SpreadSheet.CellFormatRanges
for k=0 to oCellFormatRanges.Count-1
oCellFormatRange = oCellFormatRanges.getByIndex(k)
if oCellFormatRange.IsMerged then
lStartCol = oCellFormatRange.RangeAddress.StartColumn
lStartRow = oCellFormatRange.RangeAddress.StartRow
for j=0 to oCellFormatRanges.Count-1
with oCellFormatRanges.getByIndex(j).RangeAddress
if (.StartColumn = lStartCol) and (.EndColumn = lStartCol) and (.StartRow = lStartRow+1) then
lEndRow = .EndRow
endif
if (.StartRow = lStartRow) and (.EndRow = lStartRow) and (.StartColumn = lStartCol+1) then
lEndCol = .EndColumn
endif
end with
next j
with oCell.RangeAddress
if (.StartColumn >= lStartCol) and (.EndColumn <= lEndCol) and _
(.StartRow >= lStartRow) and (.EndRow <= lEndRow) then
getIsCellInMergedArea = true
exit function
end if
end with
' msgbox (" " + lStartCol + ":" + lEndCol + " " + lStartRow + ":" + lEndRow + " " + bDum)
endif
next k
end function |
|
|
| Back to top |
|
|
bitouze
General User
Joined: 31 May 2005
Posts: 6
|
| Posted: Thu Jun 02, 2005 9:15 am Post subject: Almost working! |
|
|
Hi,
everything's all right except for instance in the following case: A1 and A2 are merged then B1 is said to be in the merged area. In fact, it seems that every *single* cell, adjacent by right from *some* *vertical* *unicolumn* range, is said to be in the merged area: I don't know why, but for instance, if J1 and J2 are merged then K1 is said to *not* be in the merged area!
In any case, thanks a lot for the code! It helps me to understand OO's API which is not very clear for me. By the way, do you know where I can find a synthetical view of, for instance, what kind of object CellFormatRanges is and what are his properties and methods?
Greetings
_________________
Denis |
|
| Back to top |
|
|
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
| Posted: Thu Jun 02, 2005 9:40 am Post subject: |
|
|
| Quote: | By the way, do you know where I can find a synthetical view of, for instance, what kind of object CellFormatRanges is and what are his properties and methods?
|
I always work with Xray, and have the SDK installed for quick documentation access. I think this is the easiest way ...
I will check the rest later ... |
|
| Back to top |
|
|
bitouze
General User
Joined: 31 May 2005
Posts: 6
|
| Posted: Thu Jun 02, 2005 10:02 am Post subject: |
|
|
| Quote: | | I always work with Xray, and have the SDK installed for quick documentation access. I think this is the easiest way ... |
Hum, excuse me but what is Xray? And what do you mean by "SDK"? OpenOffice or something else? Because OpenOffice and its documentation are installed on my computer but when I try to find something about, for instance, CellFormatRanges in this documentation, there is no result 
_________________
Denis |
|
| Back to top |
|
|
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
|
| Back to top |
|
|
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
| Posted: Thu Jun 02, 2005 12:12 pm Post subject: |
|
|
| Quote: | | everything's all right except for instance in the following case: A1 and A2 are merged then B1 is said to be in the merged area. In fact, it seems that every *single* cell, adjacent by right from *some* *vertical* *unicolumn* range, is said to be in the merged area: I don't know why, but for instance, if J1 and J2 are merged then K1 is said to *not* be in the merged area! |
You are absolutely right. The code does not work for one row / one col merged areas. After thinking second time, I am convinced that it will also fail for other cases, e.g. when one of the merged cells is differently formatted than the others (merging only hides the formats, and does not destroy it).
The SheetCellCursor should do the job. Please have a try ... | Code: |
Sub main
oCell= ThisComponent.sheets.getByName("Sheet3").getCellRangeByName("E3")
msgbox getIsCellRangeInMergedArea(oCell)
end sub
function getIsCellRangeInMergedArea(oCell as Object)
dim oCRA_New as new com.sun.star.table.CellRangeAddress
dim oCRA_Old as new com.sun.star.table.CellRangeAddress
dim oCur as Object
getIsCellRangeInMergedArea = false
oCur = oCell.SpreadSheet.createCursorByRange(oCell)
oCRA_Old = oCur.getRangeAddress
oCur.collapseToMergedArea
oCRA_New = oCur.getRangeAddress
with oCRA_Old
if (oCRA_New.StartColumn<>.StartColumn) or (oCRA_New.StartRow<>.StartRow) or _
(oCRA_New.EndColumn <>.EndColumn) or (oCRA_New.EndRow<>.EndRow) then
getIsCellRangeInMergedArea = true
endif
end with
end function
|
|
|
| Back to top |
|
|
bitouze
General User
Joined: 31 May 2005
Posts: 6
|
| Posted: Fri Jun 03, 2005 2:18 am Post subject: |
|
|
I'm sorry but it seems that the code of getIsCellRangeInMergedArea doesn't do the expected job: for instance, if (just) B1 and C1 are merged then range A2:B2 is said to be merged by getIsCellRangeInMergedArea...
For me, who used to write Visual Basic code for Excel, it is very strange that with OObasic, there is no simple way to test if a cell range is in a merged area.
Anyway, thanks a lot!
_________________
Denis |
|
| Back to top |
|
|
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
| Posted: Fri Jun 03, 2005 8:37 am Post subject: |
|
|
| Quote: | | I'm sorry but it seems that the code of getIsCellRangeInMergedArea doesn't do the expected job: for instance, if (just) B1 and C1 are merged then range A2:B2 is said to be merged by getIsCellRangeInMergedArea... |
cant reproduce that. On my computer it shows the correct result ... but never mind. Your statement | Quote: | | it is very strange that with OObasic, there is no simple way to test if a cell range is in a merged area. |
is very true, indeed ! |
|
| Back to top |
|
|
bitouze
General User
Joined: 31 May 2005
Posts: 6
|
| Posted: Wed Jun 08, 2005 3:08 am Post subject: "Dimensions" of the range covered by a merged cell |
|
|
As said by Andrew Pitonyak, | Quote: | | After merging the range B2:D7, cell B2 appears in the area formerly used by the entire range. What is not shown in Figure 4 is that the cells that aren’t visible still exist and are accessible; they are simply not displayed. |
So, maybe it is possible to know if a cell is in a merged area by knowing if it is in the range covered by merged cell (B2 in Andrew's example). So my question is: is it possible to know the "dimensions" (B2:D7 in the example) of the range covered by a merged cell?
_________________
Denis |
|
| Back to top |
|
|
|
