[Solved] Setting a print ranges causes images to disappear

[zmotiwala]

OKay I have a xls file which has images/charts embedded into it.

If I explicity set a print range as

Code: Select all

printAreas = (XPrintAreas) UnoRuntime.queryInterface(XPrintAreas.class, xSpreadsheet);
                printAreas.setPrintAreas(new CellRangeAddress[] {});

                
                cursor = xSpreadsheet.createCursor();
                cursor2 = (XUsedAreaCursor) UnoRuntime.queryInterface(XUsedAreaCursor.class, cursor);
                cursor2.gotoStartOfUsedArea(false);
                cursor2.gotoEndOfUsedArea(true);
                printAreas.setPrintAreas(new CellRangeAddress[] {});

                lAddressable = (XCellRangeAddressable) UnoRuntime.queryInterface(XCellRangeAddressable.class, cursor2);
                printAreas.setPrintAreas(new CellRangeAddress[] { lAddressable.getRangeAddress() });

The charts and embedded images are gone. I need to set the print areas because another xls file does not convert to pdf correctly without it. But other xls with embedded images are not converting correctly.
This is a major issue any way I can set a print area also have the charts/image rendered.
Not I have set the page style property to render all of these as true, yet no luck.

[Villeroy]

I take this as a follow-up of our earlier topic http://forum.openoffice.org/en/forum/vi ... 20&t=56942

I noticed that the used range does not include any pictures beyond the used range of cell contents. Ctrl+Shift+End expands the current selection until the last used cell including any objects. I recorded Ctrl+Shift+End in Basic. Then I get the address of the current selection which can be used with setPrintAreas((addr,)).
The ".uno:GoToEndOfData" dispatch can be translated to any other language (just get the frame and create a dispatcher object).

Code: Select all

REM  *****  BASIC  *****

Sub Main
REM load my Calc library where to find function getUsedRange
globalscope.basiclibraries.loadlibrary("Calc")

view = thiscomponent.getCurrentController()
sh = thiscomponent.sheets.getbyindex(0)
urg = Calc.scalc.getUsedRange(sh)
view.select(urg)
rec_expandSelection
addr = view.currentselection.getRangeAddress()
End Sub


sub rec_expandSelection()
REM this is a recorded Ctrl+Shift+End to expand the selection
REM beyond the used cells including any draw page objects 
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Sel"
args1(0).Value = true

dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args1())


end sub

[zmotiwala]

Hello Villeroy,
The object this.component is that your spreadsheet document object?
Also what exactly is this view object , I haven't yet found any equivalent JAVA object to do a select on. Also I am assuming at the end that addr value is set in the print range.

I know how to get the sheet and get the used range in java. But this view object is totally throwing me ff, the controller in java for a spread sheet object does not have a select capability.

Also when you execute the dispatch, the document object is that the spreadsheet document?

[Villeroy]

The following Java code has been recorded by http://extensions.libreoffice.org/exten ... ction-tool
It demonstrates how to get the current controller (aka view) from a model (aka document, Object oInitialTarget) and the frame from the controller and then back to the frame's model (aka document).

Code: Select all

import com.sun.star.frame.XController;
import com.sun.star.frame.XFrame;
import com.sun.star.frame.XModel;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;

public static void snippet(XComponentContext xComponentContext, Object oInitialTarget)
{
	XModel xModel = UnoRuntime.queryInterface(
		XModel.class, oInitialTarget);
	XController xController = xModel.getCurrentController();
	
	XFrame xFrame = xController.getFrame();
	
	XController xController2 = xFrame.getController();
	
	XModel xModel2 = xController2.getModel();
	
}

[zmotiwala]

Hello Villeroy,
I am still tyring to sort of this print margin issue, but discovered that if a xls spread sheet does NOT have a print margin explicitly defined i.e. I clear all print margins in the document it works great.

Weird but it does. So for now my workaround is make sure all my offending xls file DO NOT have a print margin defined.

Now I have run into another issue I have a xls file which has a chart that has a forumal the resulting pdf seems to different value in the formula column.

Attached are the original xls file and the resulting pdf, I would expect Series B to be shown and not column B in that chart.

[Villeroy]

Are you aware of scenarios?
http://forum.openoffice.org/en/forum/do ... hp?id=3004 (one chart, many data sets)

[zmotiwala]

No, this is the first time I have seen this.

It was some sample file I have used in the past.

Sorry my excel expertize is minimal , so is this rendering correctly is my question?

[Villeroy]

Sorry my excel expertize is minimal , so is this rendering correctly is my question?

I don't understand what you are after. I use to avoid macros like the pest. Whenever someone wastes a lot of time with macros without knowing much about the hacked application i get the feeling that things go wrong.

[zmotiwala]

So it turns out that setting print areas correctly in Office worked for them, they are all happy and things are getting rendered just fine.

Thank you!