I'm needing to check for merged cell in a spreadsheet so that they can be unmerged and remerged in the correct amount.
I've got the source to CalcEasyToolbar (Villeroy I think this might be directed towards you) and I'm trying to get some knowledge from the code. It is where I figured out how to merge.
I'd like to know if there is a good way to determine if a selected cell is merged and then .merge (false) that cell so that it can be remerged correctly.
Any suggestions? Thanks guys...
I've also got the first version of my spreadsheet done for others to look over and tinker with as needed... where should I upload it here or in the Code Snippets?
[Solved] Method for detecting merged cells
[Solved] Method for detecting merged cells
Last edited by zabolyx on Tue Nov 03, 2009 3:45 pm, edited 1 time in total.
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user
My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user
My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
Re: Method for detecting merged cells
Each cell has a property (really a psuedo-property) called isMerged which takes the values True or False. You can look at the value of Cell.isMerged and if it is true do Cell.merge(False)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Method for detecting merged cells
Thank you it worked like a charm
I was trying
which wasn't doing me any good.
I was trying
Code: Select all
If oSheet.getCellRangeByName("A" & iRowCounter).Merge = True Then
OOo 3.1 On Windows XP SP3 (Home)
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user
My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
Running portables of 2.4, 3.0, 3.1, and 3.2 on XP SP3 (Work)
OOo BASIC user
My contribution to the OOo Community code and more
https://sites.google.com/site/ooomacrolog/
-
- Posts: 26
- Joined: Tue Aug 16, 2016 8:51 pm
Re: [Solved] Method for detecting merged cells
crashes OO and makes this a good reason not to use it
OPenOffice v4.1.2 on win10
Re: [Solved] Method for detecting merged cells
(This is not cut out. It's what the poster supposes to be a sentence.)opensezame wrote:crashes OO and makes this a good reason not to use it
Hello "opensezame". I hope you can make a decision to either post something sensible next time - or to stay away.
If you want to critisise AOO, LibO, or free software generally, you are welcome. Create a thread under 'General Discussion' and take the time needed to point ot the relevant facts as you see them. Just "contributing" nonsense to old threads won't lead anywhere.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved] Method for detecting merged cells
http://www.openoffice.org/api/docs/comm ... plier.html
http://www.openoffice.org/api/docs/comm ... anges.html
[Tutorial] Introduction into object inspection with MRI
http://www.openoffice.org/api/docs/comm ... anges.html
[Tutorial] Introduction into object inspection with MRI
- Attachments
-
- selectMerged.ods
- (11.49 KiB) Downloaded 446 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Method for detecting merged cells
There is a much faster variant because it is not necessary to get the merged area of the merged top-left cell.
It selects 34,000 merged areas cells within a second. The ranges in the collection include the top-left cells only. Whatever you need to do with that, you may want to fetch the merged area of some cell or not.
This is how to generate some areas
Code: Select all
Sub fillMergedAreas(oRange, oRanges)
ucf = oRange.getUniqueCellFormatRanges()'container of many multiple ranges
for each ranges in ucf ' for each multiple ranges
rgtest = ranges.getByIndex(0) 'test the first one
if rgtest.getIsMerged() then
addr = ranges.getRangeAddresses()
oRanges.addRangeAddresses(addr,False)'dump them into our collection
endif
next
End Sub
This is how to generate some areas
Code: Select all
Sub Main
ThisComponent.lockControllers(True)
sh = ThisComponent.Sheets.getByIndex(0)
ra = createUnoStruct("com.sun.star.table.CellRangeAddress")
ca = createUnoStruct("com.sun.star.table.CellAddress")
x = 0
for r = 5 to 500 step 5
for c = 0 to 1023 step 3
ca.Column = c
ca.Row = r
sh.copyRange(ca, ra)
x = x +1
next c
next r
ThisComponent.lockControllers(False)
msgbox x
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Method for detecting merged cells
The topic was marked [SOLVED] - and it clearly is solved as long as we restrict it to the subject line, and agree with reading "merged cells" as "the cells covering the area of a merged range".
The original question, however, also expressed a motivation for asking:
In fact unmerging (by user code) is as simple as
Assuming the intended topic was "How to unmerge (split) merged ranges being able to undo this later?", this thread isn't over.
Firstly it would require to analyze elements of the UniqueCellFormatRanges object and their sub-element (now actually SheetCellRange objects) for their being the lead of one single merged area or of a group of such areas which are equally formatted.
Secondly the merged regions would actually be needed (as Villeroy also assumed coding his first solution).
Thirdly a way would be needed to store (or/and "mark") these regions in an unambiguos way.
Finally routines for the re-merging (all ranges or selectable?) would be needed.
(Villeroy's fist example nowhere has a group of merged areas like, say B2:D2 merged, and B3:D3 also.)
The original question, however, also expressed a motivation for asking:
where I woiuld read "amount" as a short reference to "rowspan and columnspan". Of course, the found merged ranges can be unmerged having found them, and it even is sufficient to find the "lead-cells" of each range or the "lead-range" of each couple of equally merged ranges.zabolyx wrote:...so that they can be unmerged and remerged in the correct amount.
In fact unmerging (by user code) is as simple as
Code: Select all
Sub rawUnMerge(pCase)
doc = ThisComponent
cCtrl = doc.CurrentController
cSel = doc.CurrentSelection
Select Case pCase
Case 1
For Each rg In cSel
rg.merge(False)
Next rg
Case 2
sheet = cCtrl.ActiveSheet
sheet.merge(False)
End Select
cCtrl.select(cSel)
End Sub
Assuming the intended topic was "How to unmerge (split) merged ranges being able to undo this later?", this thread isn't over.
Firstly it would require to analyze elements of the UniqueCellFormatRanges object and their sub-element (now actually SheetCellRange objects) for their being the lead of one single merged area or of a group of such areas which are equally formatted.
Secondly the merged regions would actually be needed (as Villeroy also assumed coding his first solution).
Thirdly a way would be needed to store (or/and "mark") these regions in an unambiguos way.
Finally routines for the re-merging (all ranges or selectable?) would be needed.
(Villeroy's fist example nowhere has a group of merged areas like, say B2:D2 merged, and B3:D3 also.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München