[Solved] Getting all formatted cells from sheet in Java

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
Lookris
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

[Solved] Getting all formatted cells from sheet in Java

Post by Lookris »

Is there a way to make some kind of collection (be at array, Xenumeration, or whatnot) of cells that have some formatting applied to them (non-default font size, boldness, color, borders, etc.) in vanilla Java UNO? I know there's probably solution involving MRI, but I would prefer to go without it, if possible.
I've tried the XEnumeration approach, like with "STRING", "FORMULA" and "VALUE" cells, but this:

Code: Select all

short formattedCellsFlags = CellFlags.FORMATTED | CellFlags.HARDATTR | CellFlags.EDITATTR | CellFlags.STYLES;
XEnumeration formattedCells = rangesQuery.queryContentCells(formattedCellsFlags).getCells().createEnumeration();
doesn't seem to give me anything, enumeration in always empty. Any other way?
Last edited by Lookris on Fri Jun 08, 2018 2:34 pm, edited 1 time in total.
LibreOffice 6.0 on Linux Mint 18.3 Sylvia
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting all formatted cells from sheet in Java

Post by Villeroy »

You know what CellFlags.FORMATTED means? Formatted is just another word for text content with hyperlink fields and/or line breaks and/or formatting attributes applied to parts of the text.

I believe that the following attributes are meaningless (not implemented, obsolete).
HARDATTR
STYLES
OBJECTS
EDITATTR

at least they were when I wrote this extenstion: viewtopic.php?f=47&t=37989&p=430458&hil ... ls#p430458 12 years ago
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
Lookris
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Getting all formatted cells from sheet in Java

Post by Lookris »

Well, the javadoc defines CellFlags.FORMATTED as " selects cells with formatting within the cells or cells with more than one paragraph within the cells." which seemed to me to include boldness and italicization of text inside the sell. Well, who would have thought.
And obsoleteness of HARDATTR, STYLES and EDITATTR doesn't change much, since, even if they are working, they don't give me what I want.

So for now there's no way to do it through CellFlags, if I understand the situation correctly. Any other option?
LibreOffice 6.0 on Linux Mint 18.3 Sylvia
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting all formatted cells from sheet in Java

Post by Villeroy »

Quick demo by macro. The API is the same.
Attachments
CellFlags_Demo.ods
Report and select ranges by CellFlags
(21.06 KiB) Downloaded 245 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
Lookris
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Getting all formatted cells from sheet in Java

Post by Lookris »

Yup, marco seems to demonstrate exactly the same problem: CellFlags doesn't collect changes to bgColor, boldness of the cell as a whole and other similar changes.
LibreOffice 6.0 on Linux Mint 18.3 Sylvia
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting all formatted cells from sheet in Java

Post by Villeroy »

https://api.libreoffice.org/docs/idl/re ... anges.html
returns a collection of continuous ranges with same formatting.

https://api.libreoffice.org/docs/idl/re ... anges.html
returns a collection of cell ranges collections. Each cell ranges collection represents a group with equal formatting attributes.

UniqueCellFormatRanges are grouped CellFormatRanges

A typical bullshit Excel sheet has dozends of UniqueCellFormatRanges cluttered across hundreds or thousands of CellFormatRanges
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
Lookris
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Getting all formatted cells from sheet in Java

Post by Lookris »

Wow, suddenly it seems like more trouble than it's worth. So, if I understand it correctly, I need to get UniqueCellFormatRanges of a sheet, get IndexAccess of it, get an array of CellFormatRanges' get Access of each one of them, get the format of a cell in collection, determine whether it's default and I need to ingore it, and then make new collection of all non-default formatted collections. WHOA. Anyway, this seems like a solution, however clunky, so, [SOLVED], I guess
LibreOffice 6.0 on Linux Mint 18.3 Sylvia
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting all formatted cells from sheet in Java [SOLVED]

Post by Villeroy »

Normally you don't need that.
Ctrl+A selects all cells.
Ctrl+M removes hard formatting attributes, which is what I want in most cases but I don't know what you are after.

My SpecialCells extension lists all (unique) format ranges with their respective style name plus hard attributes for a selected range or the entire sheet.
Since every item has the same set of attributes, you have to scan the properties for the ones that are not default.

The relevant Python routine is:

Code: Select all

    def getUniqueFormatAttributes(self,oCell):
        """workaround for failing queryContentCells(HARDATTR).
        Return an array of property names where style's property-values != style-properties.
        Adapted to localized SpecialCells"""
        oStylePropertyInfo = self.Document.StyleFamilies.getByName("CellStyles").getByName(oCell.CellStyle).getPropertySetInfo()
        aProperties = oCell.getPropertySetInfo().getProperties()
        prpNames = []
        if oCell.getIsMerged():
            prpNames.append(self.Merging) 
            # <Merging> refers to visible top-left cells of a merged range
        elif isSubMerged(oCell):
            prpNames.append(self.Merged)
            ## <Merged> refers to hidden submerged cells
        for prp in aProperties:
            sName = prp.Name
            iState = oCell.getPropertyState(sName)
            if (iState == DIRECT_VALUE):
                if oStylePropertyInfo.hasPropertyByName(sName):
                    prpNames.append(sName)
                if sName == 'Validation':
                    prpNames.append(self.Validation)
                elif sName == 'ConditionalFormat':
                    prpNames.append(self.Conditional)
        prpNames.sort()
        return tuple(prpNames)
It shows <Merged>, <Merging>, <Conditional formatting> and "<Validation>" together with the "normal" cell format attribute names for properties where the property state is DIRECT_VALUE (hard formatting). The program lists only the names of these properties without additional info about the concrete values.
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
Post Reply