Cannot set visibility of sheet, row or column via java uno api

Setting visibility of sheets, rows or columns does not work using XPropertySet::setPropertyValue(). It does not throw an exception or anything, it just does not change the property value.

I use a very similar code for PowerPoint and it works perfectly fine.

The intention is to preprocess the file and then convert it to pdf via jodconverter.

My current code:

private void processCalc(@NonNull XComponent document) throws Exception {
	foreachSheet(document, sheet -> {
		setProperty(sheet, "IsVisible", true);

		final var cursor = sheet.createCursor();

		final var usedAreaCursor = Lo.qi(XUsedAreaCursor.class, cursor);
		usedAreaCursor.gotoStartOfUsedArea(false);
		usedAreaCursor.gotoEndOfUsedArea(true);

		final var columnRowRange = Lo.qi(XColumnRowRange.class, usedAreaCursor);
		setVisible(columnRowRange.getRows());
		setVisible(columnRowRange.getColumns());
	});
}

private static void setVisible(XInterface x) throws IndexOutOfBoundsException, WrappedTargetException, PropertyVetoException, UnknownPropertyException {
	final var indexAccess = Lo.qi(XIndexAccess.class, x);
	for (int i = 0; i < indexAccess.getCount(); i++) {
		final var byIndex = indexAccess.getByIndex(i);
		final var range = Lo.qi(XCellRange.class, byIndex);
		setProperty(range, "IsVisible", true);
	}
}

public static void setProperty(XInterface object, String name, boolean value) throws PropertyVetoException, WrappedTargetException, UnknownPropertyException {
	final var propertySet = Lo.qi(XPropertySet.class, object);
	propertySet.setPropertyValue(name, value);
}

public static void foreachSheet(XComponent document, FailableConsumer<XSpreadsheet, Exception> consumer) throws Exception {
	final var spreadsheetDocument = Lo.qiOptional(XSpreadsheetDocument.class, document);
	if (spreadsheetDocument.isEmpty()) {
		throw new IllegalArgumentException("Provided document was not of type CALC!");
	}

	foreachSheet(spreadsheetDocument.get(), consumer);
}

public static void foreachSheet(XSpreadsheetDocument spreadsheetDocument, FailableConsumer<XSpreadsheet, Exception> consumer) throws Exception {
	final var sheets = spreadsheetDocument.getSheets();
	final var indexedSheets = Lo.qi(XIndexAccess.class, sheets);

	final var count = indexedSheets.getCount();
	for (var i = 0; i < count; i++) {
		final var sheet = Lo.qi(XSpreadsheet.class, indexedSheets.getByIndex(i));

		consumer.accept(sheet);
	}
}

Java code recorded by MRI:

import com.sun.star.beans.PropertyVetoException;
import com.sun.star.beans.UnknownPropertyException;
import com.sun.star.beans.XPropertySet;
import com.sun.star.container.NoSuchElementException;
import com.sun.star.container.XNameAccess;
import com.sun.star.lang.IllegalArgumentException;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.uno.AnyConverter;
import com.sun.star.uno.RuntimeException;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;

public static void snippet(XComponentContext xComponentContext, Object oInitialTarget)
{
	try
	{
		XSpreadsheetDocument xSpreadsheetDocument = UnoRuntime.queryInterface(
			XSpreadsheetDocument.class, oInitialTarget);
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
		
		XNameAccess xNameAccess = UnoRuntime.queryInterface(
			XNameAccess.class, xSpreadsheets);
		XSpreadsheet xSpreadsheet = UnoRuntime.queryInterface(
			XSpreadsheet.class, xNameAccess.getByName("Sheet2"));
		
		XPropertySet xPropSet = UnoRuntime.queryInterface(
			XPropertySet.class, xSpreadsheet);
		
		boolean bIsVisible = AnyConverter.toBoolean(xPropSet.getPropertyValue("IsVisible"));
		
		xPropSet.setPropertyValue("IsVisible", false);
		
	}
	catch (NoSuchElementException e1)
	{
		// getByName
		e1.printStackTrace();
	}
	catch (WrappedTargetException e2)
	{
		// getByName, getPropertyValue, setPropertyValue
		e2.printStackTrace();
	}
	catch (RuntimeException e3)
	{
		// getByName
		e3.printStackTrace();
	}
	catch (UnknownPropertyException e4)
	{
		// getPropertyValue, setPropertyValue
		e4.printStackTrace();
	}
	catch (IllegalArgumentException e5)
	{
		// , setPropertyValue
		e5.printStackTrace();
	}
	catch (PropertyVetoException e6)
	{
		// setPropertyValue
		e6.printStackTrace();
	}
}

This code does not work for me.

Unfortunately, not all property setters are implemented to properly throw on unknown / unhandled names. Here is an example; and sheet is also implemented in that unfortunate way.

1 Like

getPropertyValue returns the correct value for “IsVisible”. If my sheet is hidden it returns false otherwise true, as expected.

Getter is a different method. Note how it handles SC_WID_UNO_CELLVIS.

Hmm, by the way, I just saw that the getter also handles that; so not reflecting the change must be a bug.

Or is your sheet that you try to hide the only sheet in the file maybe? Or protected?

1 Like

Ah, thats true. Seems like there is something weird with “IsVisible”. The property “PageStyle” for example can be changed via setPropertyValue.

possibly because:

you have no sheet Sheet2?

I changed “Sheet2” to the sheet name of my file.

I have multiple sheets in my file, some hidden, some not. The sheets are not protected.

Could you upload a test file that causes the problem?
We systematically use isVisible for sheets.

test.xlsx (17.2 KB)

I am using Libreoffice 25.2.4.3 on Win10.

1 Like

I created a macro in Basic with the same actions as you.

Sub processCalc()
  Dim document as Object, sheet as Object, cursor as Object
  document = ThisComponent
  For each sheet in document.getSheets()
    setProperty(sheet, "IsVisible", true)
    cursor = sheet.createCursor()
    cursor.gotoStartOfUsedArea(false)
    cursor.gotoEndOfUsedArea(true)
    setVisible(cursor.getRows())
    setVisible(cursor.getColumns())    
  Next sheet  
End Sub

Sub setProperty(Byval obj as Object, Byval name as String, byval value as Boolean)
  obj.setPropertyValue(name, value)
End Sub

Sub setVisible(Byval obj as Object)
  Dim i as Long, range as Object
  For i = 0 To obj.getCount() - 1
    range = obj.getByIndex(i)
    setProperty(range, "IsVisible", true)
  Next i    
End Sub

I run processCalc - in my opinion, it works correctly.
What is the sequence of actions that leads to the problem?

1 Like

Weird.
I have a feeling that somehow boolean properties are not working. I use the following code to override the files page scaling, which works fine. I recently added the last line to set the page orientation to landscape, which does not work (Before and after setPropertyValue("IsLandscape", true) getPropertyValue("IsLandscape) returns false).

private void process(XComponent document) throws Exception {
	final var styleSupplier = Lo.qi(XStyleFamiliesSupplier.class, document);
	final var pageStyles = Lo.qi(XNameContainer.class, styleSupplier.getStyleFamilies().getByName("PageStyles"));

	foreachSheet(document, sheet -> {
		final var propertySet = Lo.qi(XPropertySet.class, sheet);
		final var style = pageStyles.getByName((String) propertySet.getPropertyValue("PageStyle"));
		final var stylePropertySet = Lo.qi(XPropertySet.class, style);
		stylePropertySet.setPropertyValue("ScaleToPagesX", pageScaleHorizontal);
		stylePropertySet.setPropertyValue("ScaleToPagesY", pageScaleVertical);
		stylePropertySet.setPropertyValue("IsLandscape", Boolean.TRUE);
	});
}

What also seems weird to me is that there is no exception at all. The setPropertyValue() call just results in a no-op. I had a similar experience when i tried to set “ScaleToPagesX” using an Integer. This property requires a value of type Short. Once i changed pageScaleHorizontal to type Short it worked.

The excellent book Java LibreOffice Programming (JLOP) has many examples, including setting and reading logical properties. Please check if they work in the current version.

Nevermind. I use the following code to show hidden slides in PowerPoint files and it works.

private void processDraw(@NonNull XComponent document) throws Exception {
	foreachDrawPage(document, page -> setProperty(page, "Visible", true));
}

public static void foreachDrawPage(XComponent document, FailableConsumer<XDrawPage, Exception> consumer) throws Exception {
	final var supplier = Lo.qiOptional(XDrawPagesSupplier.class, document);
	if (supplier.isEmpty()) {
		throw new IllegalArgumentException("Provided document was not of type DRAW or IMPRESS!");
	}

	final var indexedPages = Lo.qi(XIndexAccess.class, supplier.get().getDrawPages());

	final var pageCount = indexedPages.getCount();
	for (int i = 0; i < pageCount; i++) {
		final var page = Lo.qi(XDrawPage.class, indexedPages.getByIndex(i));
		consumer.accept(page);
	}
}

Update for anyone interested:

I was able to change the visibility of sheets via dispatch commands.
Still didnt make any progress on how to make hidden rows or columns visible.

the property is isVisible ? :thinking:
likely readonly.

from https://wiki.documentfoundation.org/Development/DispatchCommands
→ uno:Hide

Hi, thanks alot for responding!

The wording in the documentation kind of suggests to me that it can be changed. Also the property descriptor (retrieved via XPropertySetInfo::getPropertyByName) has no readonly flag set (field value of Attribute is 0).

If it actually is readonly, i am wondering what the correct way of setting the visibility of sheets and sheet dimensions is.

the Api tells …IsVisible is not readonly!

sel=doc.CurrentSelection
# toggle
sel.Columns.IsVisible = not sel.Columns.IsVisible
1 Like