[Solved] Method for detecting merged cells

Keyboard macros or custom scripts

[Solved] Method for detecting merged cells

Postby zabolyx » 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?
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/
zabolyx
 
Posts: 216
Joined: Fri Aug 07, 2009 7:28 pm

Re: Method for detecting merged cells

Postby FJCC » 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)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7354
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Method for detecting merged cells

Postby zabolyx » Tue Nov 03, 2009 3:44 pm

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

Re: [Solved] Method for detecting merged cells

Postby opensezame » Thu Dec 27, 2018 7:12 pm

crashes OO and makes this a good reason not to use it
OPenOffice v4.1.2 on win10
opensezame
 
Posts: 15
Joined: Tue Aug 16, 2016 8:51 pm

Re: [Solved] Method for detecting merged cells

Postby Lupp » 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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Method for detecting merged cells

Postby Villeroy » Thu Dec 27, 2018 11:49 pm

Attachments
selectMerged.ods
(11.49 KiB) Downloaded 74 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27296
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Method for detecting merged cells

Postby Villeroy » 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   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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27296
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests