Page 1 of 1

[Solved] Method for detecting merged cells

PostPosted: Tue Nov 03, 2009 12:53 am
by zabolyx
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?

Re: Method for detecting merged cells

PostPosted: Tue Nov 03, 2009 6:23 am
by FJCC
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)

Re: Method for detecting merged cells

PostPosted: Tue Nov 03, 2009 3:44 pm
by zabolyx
Thank you it worked like a charm

I was trying

Code: Select all   Expand viewCollapse view
If oSheet.getCellRangeByName("A" & iRowCounter).Merge = True Then


which wasn't doing me any good.

Re: [Solved] Method for detecting merged cells

PostPosted: Thu Dec 27, 2018 7:12 pm
by opensezame
crashes OO and makes this a good reason not to use it

Re: [Solved] Method for detecting merged cells

PostPosted: Thu Dec 27, 2018 9:47 pm
by Lupp
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.

Re: [Solved] Method for detecting merged cells

PostPosted: Thu Dec 27, 2018 11:49 pm
by Villeroy

Re: [Solved] Method for detecting merged cells

PostPosted: Fri Dec 28, 2018 2:52 pm
by Villeroy
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   Expand viewCollapse view
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   Expand viewCollapse view
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

Re: [Solved] Method for detecting merged cells

PostPosted: Wed Jan 06, 2021 11:44 pm
by Lupp
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   Expand viewCollapse view
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.)