Page 1 of 1
[Solved] Getting all formatted cells from sheet in Java
Posted: Thu Jun 07, 2018 5:50 pm
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?
Re: Getting all formatted cells from sheet in Java
Posted: Thu Jun 07, 2018 8:59 pm
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
Re: Getting all formatted cells from sheet in Java
Posted: Fri Jun 08, 2018 10:28 am
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?
Re: Getting all formatted cells from sheet in Java
Posted: Fri Jun 08, 2018 11:24 am
by Villeroy
Quick demo by macro. The API is the same.
Re: Getting all formatted cells from sheet in Java
Posted: Fri Jun 08, 2018 12:24 pm
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.
Re: Getting all formatted cells from sheet in Java
Posted: Fri Jun 08, 2018 2:24 pm
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
Re: Getting all formatted cells from sheet in Java
Posted: Fri Jun 08, 2018 2:33 pm
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
Re: Getting all formatted cells from sheet in Java [SOLVED]
Posted: Fri Jun 08, 2018 4:05 pm
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.