Apache OpenOffice Community Forum

Posted: **Tue Nov 03, 2009 12:53 am**

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?

Posted: **Tue Nov 03, 2009 6:23 am**

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)

Posted: **Tue Nov 03, 2009 3:44 pm**

Thank you it worked like a charm

I was trying

Code: Select all

If oSheet.getCellRangeByName("A" & iRowCounter).Merge = True Then

which wasn't doing me any good.

Posted: **Thu Dec 27, 2018 7:12 pm**

crashes OO and makes this a good reason not to use it

Posted: **Thu Dec 27, 2018 9:47 pm**

opensezame wrote:crashes OO and makes this a good reason not to use it

(This is not cut out. It's what the poster supposes to be a sentence.)

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.

Posted: **Thu Dec 27, 2018 11:49 pm**

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

Posted: **Fri Dec 28, 2018 2:52 pm**

There is a much faster variant because it is not necessary to get the merged area of the merged top-left cell.

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

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

Posted: **Wed Jan 06, 2021 11:44 pm**

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:

zabolyx wrote:...so that they can be unmerged and remerged in the correct amount.

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.

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.)

Apache OpenOffice Community Forum

[Solved] Method for detecting merged cells

[Solved] Method for detecting merged cells

Re: Method for detecting merged cells

Re: Method for detecting merged cells

Re: [Solved] Method for detecting merged cells

Re: [Solved] Method for detecting merged cells

Re: [Solved] Method for detecting merged cells

Re: [Solved] Method for detecting merged cells

Re: [Solved] Method for detecting merged cells