[Solved] Accessing outline state in Calc

Keyboard macros or custom scripts

[Solved] Accessing outline state in Calc

Postby hika » Sat Sep 15, 2018 4:26 pm

Through the com.sun.star.sheet.XSheetOutline interface I can create, remove or manipulate an outline in a calc sheet. However I have not been able to find how to access the current state nor have I, what would even be more handy, found a listener for this state.

Is there any such functionality and if so where can I find it?
Is it maybe linked to one or more properties I can evaluate or put a listener on?
Is there maybe a hiding state property?

Hika
Last edited by hika on Sat Sep 15, 2018 10:09 pm, edited 1 time in total.
openoffice 4.1.2/4.1.4 and libreoffice 5.4.4.2/5.4.5.1 both on Gentoo and on Windows
hika
 
Posts: 35
Joined: Tue Apr 17, 2018 12:53 am

Re: Accessing outline state in calc

Postby FJCC » Sat Sep 15, 2018 5:50 pm

You can get which cells are visible with queryVisibleCells() a deduce the state of the outline from that. It is not a very convenient method.
Code: Select all   Expand viewCollapse view
  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet1")
  oColumns = oObj1.getColumns()
 
  oObj2 = oColumns.getByName("A")
  oObj3 = oObj2.queryVisibleCells()
  sAbsoluteName = oObj3.AbsoluteName 'a string of cell ranges
 
  oCells = oObj3.getCells() 'oCells has enumeration access
  oRangeAddresses = oObj3.getRangeAddresses() 'oRangeAddresses is an array of RangeAddress
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: 7259
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Accessing outline state in calc

Postby hika » Sat Sep 15, 2018 6:18 pm

I was thinking in that direction as probably the only way, although I hadn't jet found that function. Thanks!
But one additional question, doesn't this primarily mark cells outside the windowview as invisible? Which could be because the outline is collapsed, the row or column is hidden or that they are to far to the right, left, top or bottom.

Hika
openoffice 4.1.2/4.1.4 and libreoffice 5.4.4.2/5.4.5.1 both on Gentoo and on Windows
hika
 
Posts: 35
Joined: Tue Apr 17, 2018 12:53 am

Re: Accessing outline state in calc

Postby hika » Sat Sep 15, 2018 6:25 pm

One other thing I have been looking for as a possibility. Next to using an outline, you can also without adding an outline, hide/show rows or columns. I haven't jet found that interface, but could it be that outlines are build on top this functionality and that this interface does have a "Hidden" or something property? Could this be somewhere in com.sun.star.table?

Hika
openoffice 4.1.2/4.1.4 and libreoffice 5.4.4.2/5.4.5.1 both on Gentoo and on Windows
hika
 
Posts: 35
Joined: Tue Apr 17, 2018 12:53 am

Re: Accessing outline state in calc

Postby FJCC » Sat Sep 15, 2018 6:38 pm

queryVisibleCells() does not pay attention to what is visible on the screen. It tells you whether a cell is hidden or not. A column has an IsVisible property from com.sun.star.table.TableColumn and rows have the same in com.sun.star.table.TableRow.
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: 7259
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Accessing outline state in calc

Postby hika » Sat Sep 15, 2018 6:50 pm

Thanks! I can build my own function around that. Storing any created outline, so I know what to check.
It however to me is a bit illogical/inconsistent naming, as visibility in my logic is associated with views/windows. But ...

Hika
openoffice 4.1.2/4.1.4 and libreoffice 5.4.4.2/5.4.5.1 both on Gentoo and on Windows
hika
 
Posts: 35
Joined: Tue Apr 17, 2018 12:53 am

Re: [Solved] Accessing outline state in calc

Postby hika » Sat Sep 15, 2018 10:17 pm

I have a class with all kind of sheet related functions including a variable containing a link to the sheet. I added a dict `self.outlines` that keeps track of the outlines so I on subsequent access can just use a name. These are the outline related modules. Thanks for the idea!
Code: Select all   Expand viewCollapse view
    def group(self, **kwargs):
        orientation = kwargs.get('orientation', None)
        name = kwargs.get('name', 'Outline-%s' % len(self.outlines))
        if name not in self.outlines.keys() or orientation not in (None, self.outlines[name]['orientation']) \
            or kwargs.get('do_create', True):
            start = kwargs.get('start', 0)
            end = kwargs.get('end', 0)
            if orientation == self.uno.ue_tblorient.COLUMNS:
                self.outlines[name] = {'created': False, 'orientation': orientation, 'start': start, 'end': end,
                    'range': self.file.get_rangeaddress(self.name, col1=start, col2=end)}

            elif orientation == self.uno.ue_tblorient.ROWS:
                self.outlines[name] = {'created': False, 'orientation': orientation, 'start': start, 'end': end,
                    'range': self.file.get_rangeaddress(self.name, row1=start, row2=end)}

            else:
                return

        if name != None:
            ra = self.outlines[name]
            if not ra['created']:
                self.unocomponent.group(ra['range'], ra['orientation'])
                ra['created'] = True

            if kwargs.get('collapse', False):
                self.unocomponent.hideDetail(ra['range'])

            else:
                self.unocomponent.showDetail(ra['range'])

    def clear_group(self, name):
        if not name in self.outlines.keys():
            return

        ra = self.outlines[name]
        if ra['created']:
            self.unocomponent.ungroup(ra['range'], ra['orientation'])
            ra['created'] = False

    def group_collapsed(self, name):
        if not name in self.outlines.keys():
            return True

        ra = self.outlines[name]
        if ra['orientation'] == self.uno.ue_tblorient.COLUMNS:
            for cell in self.range(1, ra['start'], 1, ra['end']).queryVisibleCells().getCells():
                return False

        if ra['orientation'] == self.uno.ue_tblorient.ROWS:
            for cell in self.range(ra['start'], 1, ra['end'], 1).queryVisibleCells().getCells():
                return False

        return True

    def hide_group(self, **kwargs):
        kwargs['collapse'] = True
        self.group(**kwargs)

    def show_group(self, **kwargs):
        kwargs['collapse'] = False
        self.group(**kwargs)

    def group_rows(self, **kwargs):
        kwargs['orientation'] = self.uno.ue_tblorient.ROWS
        self.group(**kwargs)

    def hide_rows(self, **kwargs):
        kwargs['orientation'] = self.uno.ue_tblorient.ROWS
        kwargs['collapse'] = True
        self.group(**kwargs)

    def show_rows(self, **kwargs):
        kwargs['orientation'] = self.uno.ue_tblorient.ROWS
        kwargs['collapse'] = False
        self.group(**kwargs)

    def group_cols(self, **kwargs):
        kwargs['orientation'] = self.uno.ue_tblorient.COLUMNS
        self.group(**kwargs)

    def hide_cols(self, **kwargs):
        kwargs['orientation'] = self.uno.ue_tblorient.COLUMNS
        kwargs['collapse'] = True
        self.group(**kwargs)

    def show_cols(self, **kwargs):
        kwargs['orientation'] = self.uno.ue_tblorient.COLUMNS
        kwargs['collapse'] = False
        self.group(**kwargs)


openoffice 4.1.2/4.1.4 and libreoffice 5.4.4.2/5.4.5.1 both on Gentoo and on Windows
hika
 
Posts: 35
Joined: Tue Apr 17, 2018 12:53 am

Re: [Solved] Accessing outline state in Calc

Postby hika » Sat Sep 22, 2018 3:08 am

This is weird. It did work like a charm,... when I had rows collapsed. But when I applied it on columns it always returns that it is collapsed. So I did some research and simplified the function a bit.
Code: Select all   Expand viewCollapse view
    def group_collapsed(self, name):
        if not name in self.outlines.keys():
            return True

        for cell in self.range(self.outlines[name]['range']).queryVisibleCells().getCells():
            return False

        return True

Where self.outlines[name]['range'] is the "com.sun.start.table.CellRangeAddress" used to create the outline. This way I do not need to check on whether it is horizontal or vertical. Removing a source of confusion and error. But is still did not work on collapsed columns, while working on collapsed rows.

Next I tried:
Code: Select all   Expand viewCollapse view
    def group_collapsed(self, name):
        if not name in self.outlines.keys():
            return True

        return not len(self.range(self.outlines[name]['range']).queryVisibleCells().getRangeAddressesAsString())

And that did work.
Somehow on a horizontal range like "_Overzicht.A1:D1" the getCells() function returns an empty enumeration.

Can anybody explain this? Is it a known bug?

Hika
openoffice 4.1.2/4.1.4 and libreoffice 5.4.4.2/5.4.5.1 both on Gentoo and on Windows
hika
 
Posts: 35
Joined: Tue Apr 17, 2018 12:53 am

Re: [Solved] Accessing outline state in Calc

Postby darren4 » Mon Sep 24, 2018 9:04 am

Finally I can build my own function around that. I was wondering if this primarily mark cells outside the windowview as invisible
User avatar
darren4
Banned
 
Posts: 1
Joined: Mon Sep 24, 2018 8:54 am

Re: [Solved] Accessing outline state in Calc

Postby hika » Mon Sep 24, 2018 6:32 pm

No , See FJCC's answer above to my same question and the documentation of the queryVisibleCells() function.
It works very well, but do not use queryVisibleCells().getCells() as it is still empty on a horizontal array.
Use queryVisibleCells().getRangeAddressesAsString() and check whether the resulting string has length 0.

Of cause on crossing outlines you must make sure to check cells outside the crossing. My function checks the top or leftmost cells.

Hika
openoffice 4.1.2/4.1.4 and libreoffice 5.4.4.2/5.4.5.1 both on Gentoo and on Windows
hika
 
Posts: 35
Joined: Tue Apr 17, 2018 12:53 am


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests