[Solved] Getting all formatted cells from sheet in Java

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Getting all formatted cells from sheet in Java

Postby Lookris » Thu Jun 07, 2018 5:50 pm

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   Expand viewCollapse view
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
Lookris
 
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Getting all formatted cells from sheet in Java

Postby Villeroy » Thu Jun 07, 2018 8:59 pm

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: https://forum.openoffice.org/en/forum/v ... 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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25835
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting all formatted cells from sheet in Java

Postby Lookris » Fri Jun 08, 2018 10:28 am

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
Lookris
 
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Getting all formatted cells from sheet in Java

Postby Villeroy » Fri Jun 08, 2018 11:24 am

Quick demo by macro. The API is the same.
Attachments
CellFlags_Demo.ods
Report and select ranges by CellFlags
(21.06 KiB) Downloaded 22 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25835
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting all formatted cells from sheet in Java

Postby Lookris » Fri Jun 08, 2018 12:24 pm

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
Lookris
 
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

Re: Getting all formatted cells from sheet in Java

Postby Villeroy » Fri Jun 08, 2018 2:24 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25835
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Getting all formatted cells from sheet in Java

Postby Lookris » Fri Jun 08, 2018 2:33 pm

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
Lookris
 
Posts: 10
Joined: Mon May 28, 2018 7:14 pm

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

Postby Villeroy » Fri Jun 08, 2018 4:05 pm

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   Expand viewCollapse view
    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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25835
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Programs

Who is online

Users browsing this forum: No registered users and 0 guests