[Solved] Method for detecting merged cells

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

[Solved] Method for detecting merged cells

Post 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?
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/
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Method for detecting merged cells

Post 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)
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.
zabolyx
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Method for detecting merged cells

Post by zabolyx »

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.
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/
opensezame
Posts: 26
Joined: Tue Aug 16, 2016 8:51 pm

Re: [Solved] Method for detecting merged cells

Post by opensezame »

crashes OO and makes this a good reason not to use it
OPenOffice v4.1.2 on win10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Method for detecting merged cells

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Method for detecting merged cells

Post by Villeroy »

Attachments
selectMerged.ods
(11.49 KiB) Downloaded 431 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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Method for detecting merged cells

Post 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

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
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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Method for detecting merged cells

Post 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

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