[Solved] Load currently opened OO files [C#]

[SERG]

Hello!

I found a lot of C# code examples for working with a document being opened (using the URL ) or a newly created one.
Everything works fine, but I need to take control of one already open Calc document.

Specifically, I need to get the address of the active cell.
I can not do it. I did not find a similar example using C#.

Can you help me with an example of such a connection in C#.

I believe that this procedure should not be too complicated, but for a novice programmer it turned out to be a dead end.

[JeJe]

If you get MRI

https://extensions.openoffice.org/en/project/MRI

You'll be able to explore objects and it will generate code like the following, in my case without even knowing any C#

Code: Select all

using System;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.uno;
using unoidl.com.sun.star.view;

public class Snippet {
public void snippet(XComponentContext xContext, object oInitialTarget)
{
	try
	{
		XController xController = oInitialTarget.getCurrentController();
		
		XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) xController;
		
		XSpreadsheet xSpreadsheet = xSpreadsheetView.getActiveSheet();
		
		XPropertySet xPropSet = (XPropertySet)xSpreadsheet;
		
		String sAbsoluteName = (String) xPropSet.getPropertyValue("AbsoluteName").Value;
		
		XSelectionSupplier xSelectionSupplier = (XSelectionSupplier) xController;
		
		object oObj1 = (XInterface) xSelectionSupplier.getSelection().Value;
		XCellAddressable xCellAddressable = (XCellAddressable) oObj1;
		CellAddress aCellAddress = xCellAddressable.getCellAddress();
		
	}
	catch (WrappedTargetException e)
	{
		// getPropertyValue
		Console.WriteLine(e.Message);
	}
	catch (UnknownPropertyException e)
	{
		// getPropertyValue
		Console.WriteLine(e.Message);
	}
}
}

[SERG]

Thank you very much for your answer.
MRI looks like a very useful extension to me.

I have not yet figured out how to use it to get such a piece of code.
While I'm just getting acquainted with OpenOffice

I'm experimenting with the example you provided.
I don't understand what arguments to pass to the method

Code: Select all

snippet(XComponentContext xContext, object oInitialTarget)

Can you give me some advice on this.

[JeJe]

I just know basic and haven't used it from another language - from which, with the MRI library loaded its

MRI Thiscomponent

Or whatever object you choose. Then you explore by clicking on the listboxes for properties and methods. By moving the splitter at the bottom you'll see the code produced - which language for is set by clicking in the menu.

Tutorial here:

viewtopic.php?t=49294

[RoryOF]

Wiki help at
https://github.com/hanya/MRI/wiki

[ms777]

Hi, one of the key questions here is if you use your c# program to start AO/LO, or if AO/LO is already started, when you launch your c# program. The former is relatively easy, the latter is quite difficult

P.S. Can you share some of the c# code which works for you?

[SERG]

Thanks for the helpful links to MRI.
I figured out a little how it works and I managed to generate the code provided by JeJe.

But the code is generated naturally on an already running instance of the OOo document.
How to get an open document about which my program knows nothing, I still do not understand.

As ms777 says, getting control of the OOo document open before running my C# program is not that easy.
That was the way it was planned.

Similar actions were implemented by me on MS Excel:

Code: Select all

             app = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
             Excel.Worksheetsheet = app.ActiveSheet;
             varactivRow = app.ActiveCell.Row;

I am ready to accept that my program will start earlier and I will open a document from it by reference (this is acceptable).
After that, getting the address of the active cell is much easier, as I understand it.

My program code is simple - there are some buttons to upload information from another application and save the data in a OOo file.
After that, the user manipulates the file, then, if necessary, launches my program and, according to the data from the cells, contacts the program with the data source in the table.

Now I will add a button to open the file from my application and work with it afterwards.

[Villeroy]

The CurrentSelection is one of
1. A single range. A single cell is a special case of cell range plus Formula, Value, String.
2. A multiple selection of ranges or cells.
3. A shape.
In either of the 3 cases there is always one active cell, however no direct API method to get hold of that cell which is so important when writing spreadsheet macros.
The current controller provides a string property "ViewData" from where the active cell can be derived.
StarBasic function getting the active cell from any controller (current controller by default):

Code: Select all

'-------usefull helper-function, returning focussed cell 
'by UROS > http://www.oooforum.org/forum/viewtopic.phtml?t=19348
REM 2006-08-09: fixed error when row > 8191
'		 ;sh;							;lSheet +3
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253/8191/0/0/0/0/2/246/0/0/8158;0/0/0/0/0/0/2/0/0/0/0
'100/60/0;1;tw:309;2/2/0/0/0/0/2/0/0/0/0;253+8192+0+0+0+0+2+246+0+0+8158;0/0/0/0/0/0/2/0/0/0/0
Function getActiveCell(Optional oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
If isMissing(oView) then oView = ThisComponent.getCurrentController()
	as1()  = Split(oView.ViewData, ";")
	lSheet = CLng(as1(1))
	sDum = as1(lSheet +3)
	as1() = Split(sDum, "/")
	on error goto errSlash
		lCol = CLng(as1(0))
		lRow = CLng(as1(1))
	on error goto 0
	getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
	if NOT(bErr) then
		bErr = True
		as1() = Split(sDum, "+")
		resume
	endif
End Function

When the limits of rows/columns rised beyond 65536 and 256, the ViewData string changed for the high numbers only. I simply added an error handler to handle the new format of that string (+ delimiter instead of /)

[Lupp]

Code: Select all

Load currently opened OO files

Currently opened OO files are already loaded. This is clearly about getting access.

Code: Select all

I need to take control of one already open Calc document.

You may get access, but can you consider and resolve every possible conflict between your controlling process and any different process running for that component due to a different reason anyway? I have no expertise concerning the problem.

Code: Select all

... [b]one[/b] already open Calc document ...

How should this specific Calc component be identified? There obviously may be many.

I can only see ways you first of all need access to the StarDesktop (service com.sun.star.frame.Desktop or singleton object theDesktop) for. As soon as you have, you can choose the one component based on its URL or probably based on a UserDefinedProperty of that component. Simply loop through the StarDesktop.Components object and compare for the used property till you found the desired one.

(I don't program using C++ or C# or... Thus I don't know in what specific way you get the StarDesktop in every case.)

[SERG]

Villeroy thank you very much.

It turned out to pick up the text from the currently active cell.
It works inside OOBasic.

My task now is simply to rewrite the code of the function in C# in my application (this seems accessible to me)

This is how it happened to pick up ViewData in my C# code:

Code: Select all

   XController xController = ((XModel)document).getCurrentController();
   string xView = xController.getViewData();

[Villeroy]

The macro splits the ViewData string by semicolons.
The second element (index 1) is the index of the current sheet lSheet.
Beginning at index 3 each sheet has its own sub-string separated by /, so a1(lSheet+3) returns the active sheet's sub-string.
The first element of that split string is the column index, the second element is the row index.
Now we have a complete cell address with sheet, column and row position.

Under certain circumstances (don't know how and why exactly, can't find proper documentation), the sub-string is separated by + instead of /. This is handled by the error handler.

[cwolan]

Under certain circumstances (don't know how and why exactly, can't find proper documentation), the sub-string is separated by + instead of /. This is handled by the error handler.

Regarding these certain circumstances: Might I assume you've looked at the source code?
I mean the source file viewdata.cxx and the WriteUserData method therein (OpenOffice / LibreOffice).

[Lupp]

Thanks to @cwolan !

Jetzt seh' ich klar ganz wunderbar,
Dass ich schlicht ohne Durchblick war.
Nun muss mir "cwolan" noch verraten,
Das' tödlich' Gift an SC_OLD_TABSEP,
(read: skoldtabsep)
An UrzeitDaten einst gebraten
Vor Rettung durch SC_NEW_TABSEP.
(read: sseenyootabsep)
However,
Clever
I found
Lying around:
http://www.openoffice.org/sc/row-limit.html.

That still didn't make clear to me for what reason the slash had to be replaced as the list delimiter in ViewData when MaxRow could be increased because the RAM-page size was no longer limiting.
Who knows?

[cwolan]

However,
Clever
I found
Lying around:
http://www.openoffice.org/sc/row-limit.html.

Yep, a long time ago (vor Urzeiten) I read this article and didn't grasp much. IIRC

That still didn't make clear to me for what reason the slash had to be replaced as the list delimiter in ViewData when MaxRow could be increased because the RAM-page size was no longer limiting.
Who knows?

Eike Rathke, the author of "Increasing the row limit above 32000 rows"?

[Lupp]

!!! Still Off Topic !!!

Thanks for you comments, @cwolan.
I read the above linked old text only recently, and only partly : ( 'Preliminary' and 'History' what, however may already be >1% of the content). So far I thought I had understood. But I didn't find the keyword 'backward'(s compatibility) with one exception where row information for charts was concerned. Also none of the keywords 'delmiter', 'separator', 'cTabSep' or similar occurred. This looks to me as if the issue of backwards compatibility wasn't actually discussed in the document. It is a serious issue, however, if the code of V 3.0 or earlier would fail finally when finding illegal (>=2^13) values after splitting the respective property of ViewData with "/". Then (probably! I didn't check code from the stone-age)) the usage of a different separator might lead to an error resulting in immediate resume. If so, just luck, but allowing for a workaround. Anyway the issue was evantually discussed and solved in time. The missing information concerning the preselected focus in a sheet would (luckily again) then result in (0, 0) e.g.

Currently LibreOffice was running into a similar trap concerning ConditionalFormatting defined with a version allowing for more than 1024 columns. A CF defined for ranges just including any cells in the enhanced range will Be interpreted as defective, and will be disregarded then. For example I sometimes use a 'ListingPaper' template coming with a CF defined for all sheet. Updating the template with V 7.4 or higher would result in sheets not showing the the CF when opened with any version <7.4. That's bad, but there is no technique for bugfixing old versions. The original mistake was the omission of necessary considerations a long time ago..
So I suggested an ugly workaround that reminded me of the mentioned separator change.
I can't quite decide to "promote" this workaround.
Eike hasn't commented on it, and Mike doesn't seem to have quite understood me yet, exceptionally. See https://bugs.documentfoundation.org/sho ... ?id=152968.
What is your opinion?

[SERG]

I will continue my question.
Everything worked out with the active cell.
There was a need to get the address of the selected range.
Need numbers of all selected lines
OpenOffice experts, how can I do this?

[JeJe]

Have you used MRI with the selection?

https://wiki.openoffice.org/wiki/Current_selection

Edit:

oops that page was for text documents but this bit will work in Calc too:

Code: Select all

mri thiscomponent.GetCurrentSelection

[Lupp]

And I still don't know in what way the one wanted open Calc document among all the opened Calc documents is identified.
If the URL is known, that's trivial. If not, how?
Finding the "active cell" is then trivial again, (although somewhat obscured by the old changes).

[SERG]

Everything turned out to be quite simple.
maybe it will help someone

also with 1 active cell this can be used without getViewData()

Code: Select all

                object xObject = ((XModel)documentGlobal).getCurrentSelection();
                XCellRangeAddressable aRange = (XCellRangeAddressable)xObject;
                CellRangeAddress aRangeAddress = aRange.getRangeAddress();
                MessageBox.Show(aRangeAddress.StartRow.ToString() +"-"+ aRangeAddress.EndColumn.ToString());

[Lupp]

The thread grew very long meanwhile, and I myself have to apologize for some remarks of doubtable quality, but first of all I tried to actually hit the topic "Load currently opened OO files [C#]".
I remind you of

Currently opened OO files are already loaded. This is clearly about getting access.

which is at the center of the subject, but wasn't regarded by anybody.
Then we got a discussion about MRI and the like and about the current selection of a spreadsheet document and the cell having the (next) input focus. But even insofar there isn't any evidence concerning the question if the OQer understood the cases and related terms needing to be distinguished.

Now I can no longer see any reasonable relation between the topic/subject, and what's discussed as a solution or part of it. Frustration.