Libreoffice integration struggles

[Pierre le Riche 23]

I am battling with LibreOffice integration. I have managed to get the loading of a writer document from disk working, but for the life of me I cannot figure out how to load a document from in-memory data. A small test case is attached with the line on which it crashes indicated in the source. You just need LibreOffice installed and the test app should run.

 

The line on which it crashes is where I am trying to get the in-memory data into the stream from which to load the document. If I comment out that line then it runs through to the end (where it complains that the stream contains no data, as expected).

 

The Delphi code is a loose translation of the Python code I found here, circa line 163:

https://github.com/unoconv/unoserver/blob/master/src/unoserver/comparer.py

 

I also found this seemingly similar question on the LibreOffice forums, but I had no success following their suggestions:

https://ask.libreoffice.org/t/macro-basic-sequenceinputstream-createstreamfromsequence-runtime-error-not-found/110235

 

Anyone know what I am doing wrong? I have tried every permutation of input to SequenceInputStream.initialize that I could think of without success. 

 

Thanks!

LibreOfficeLoadingFromStream.dpr

[timfrost 81]

I don't know Python to see where you are coming from, and it also confuses me to see your variables and functions named with the word 'stream' declared as variants. But I recommend the Winsoft (www.winsoft.sk) "Libre" component (which I use) which exposes the whole Libre API to Delphi (and has a demo you can try). There is a Libre API call: loadComponentFromURL("private:stream", ...) which may help (but I have not tried this). And ChatGPT may help you find a better Libre API for this purpose which you can access through the Winsoft library.

[Kas Ob. 151]

23 hours ago, Pierre le Riche said:

Anyone know what I am doing wrong?

This works fine

program LibreOfficeLoadingFromStream;

{$APPTYPE CONSOLE}

uses
  System.SysUtils,
  System.Win.ComObj,
  Winapi.ActiveX;

procedure LoadWriterDocumentFromStream(const ADocumentData: TBytes);
var
  LServiceManager, LDesktop, LStream, LDocData, LProperties: Variant;
  LPropertyArray: TArray<Variant>;
  WrittenBytes: TBytes;
begin
  {Connect to LibreOffice}
  LServiceManager := CreateOleObject('com.sun.star.ServiceManager');
  LDesktop := LServiceManager.createInstance('com.sun.star.frame.Desktop');

  {Instantiate in input stream to load the document data from}
  //LInputStream := LServiceManager.createInstance('com.sun.star.io.SequenceInputStream');
  LStream := LServiceManager.createInstance('com.sun.star.io.SequenceOutputStream');       // <-

  {Populate the input stream with the document data}
  LDocData := ADocumentData;
  //LInputStream.createStreamFromSequence(LDocData); // <- error here
  LStream.writeBytes(LDocData);
  WrittenBytes := LStream.getWrittenBytes;
  Writeln(StringOf(WrittenBytes));

  // test again
  Writeln('Test again');
  LStream.writeBytes(Variant(BytesOf(#13#10'Another TExt')));
  Writeln(StringOf(LStream.getWrittenBytes));
end;

var
  LDocData: TBytes;
begin
  CoInitialize(nil);
  //SetLength(LDocData, 10000); //In an actual application LDocData would contain the .odt document file content
  LDocData := BytesOf('Sample text 123!@#');
  try
    LoadWriterDocumentFromStream(LDocData);
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Readln;
end.

the output is 

Quote

Sample text 123!@#
Test again
Sample text 123!@#
Another TExt
 

 

The problem is in API naming and assumption of simple logical naming, it seems the Output/Input words (and meaning) are reversed in the whole SDK, so you should look at them in reverse (from the binary/exe perspective)

https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1io.html

and the one you need is

https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1io_1_1SequenceOutputStream.html

[Pierre le Riche 23]

3 hours ago, Kas Ob. said:

The problem is in API naming and assumption of simple logical naming, it seems the Output/Input words (and meaning) are reversed in the whole SDK, so you should look at them in reverse (from the binary/exe perspective)

https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1io.html

and the one you need is

https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1io_1_1SequenceOutputStream.html

 

Thanks for looking into this.

 

When I replace SequenceInputStream with SequenceOutputStream in the attached example then the loadComponentFromURL crashes with this error:

Project LibreOfficeLoadingFromStream.exe raised exception class EOleException with message 'com.sun.star.lang.IllegalArgumentException: Unsupported URL <private:stream>: "from LoadEnv::startLoading"'.

 

It appears that it doesn't like a SequenceOutputStream specified for the InputStream property. If I use a SequenceInputStream and I skip the step of populating the stream then it gets to the point of loading the document before it complains that it cannot seek in the stream, which is not surprising given that the stream is empty.

 

The documentation for the media description used by loadComponentFromURL (https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1document_1_1MediaDescriptor.html#a7d6a9c7512c158efd9e12a78b1258be4) states that SequenceInputStream is for loading documents and SequenceOutputStream is for saving.

[Pierre le Riche 23]

6 hours ago, timfrost said:

But I recommend the Winsoft (www.winsoft.sk) "Libre" component

Thanks, I sent the author an e-mail earlier this week, but he hasn't responded yet. I got loading from disk working, but loading from a memory buffer is turning out to be quite tricky. I took a look at the demo application for "Libre", but there was no example for loading from memory. I'll download the trial and poke around in the list of calls to see if it is able to do it.

Edited Sunday at 07:12 PM by Pierre le Riche

[Pierre le Riche 23]

Just an update on this. It turns out it's not an OLE Automation compatibility issue between Delphi and LibreOffice as I was starting to fear. Apparently the inner array that you pass to the initialize method of SequenceInputStream must be a "strongly typed uno value", so you have to massage the input a little bit. 

 

For the benefit of anyone stumbling upon this thread in future, here is how you load a LibreOffice document from a memory buffer:

function LoadLibreOfficeDocumentFromMemory(const ADocumentFileData: TBytes): Variant;
var
  LServiceManager, LStrictlyTypedUnoValue, LInputStream, LDesktop: Variant;
  LArgumentsArray, LPropertyArray: TArray<Variant>;
begin
  LServiceManager := CreateOleObject('com.sun.star.ServiceManager');

  {A SequenceInputStream is used to load the document from memory.  In order to get the bytes into the
  SequenceInputStream the initialize method is called.  This method takes an array of arguments, in which it
  expects to find a single entry which must be a strictly typed uno value - a byte array.}

  {Create the argument array for SequenceInputStream.initialize}
  LStrictlyTypedUnoValue := LServiceManager.Bridge_GetValueObject;
  LStrictlyTypedUnoValue.Set('[]byte', Variant(ADocumentFileData));
  SetLength(LArgumentsArray, 1);
  LArgumentsArray[0] := LStrictlyTypedUnoValue;

  {Create and initialize the SequenceInputStream}
  LInputStream := LServiceManager.createInstance('com.sun.star.io.SequenceInputStream');
  LInputStream.initialize(Variant(LArgumentsArray));

  {Specify the stream to load from in the property array for loadComponentFromURL.}
  SetLength(LPropertyArray, 1);
  LPropertyArray[0] := LServiceManager.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
  LPropertyArray[0].Name := 'InputStream';
  LPropertyArray[0].Value := LInputStream;

  {Load and display the document}
  LDesktop := LServiceManager.createInstance('com.sun.star.frame.Desktop');
  Result := LDesktop.loadComponentFromURL('private:stream', '_blank', 0, Variant(LPropertyArray));
end;

 

Edited Monday at 08:13 AM by Pierre le Riche

[timfrost 81]

Thanks; might be useful one day!

[Kas Ob. 151]

23 minutes ago, Pierre le Riche said:

It turns out it's not an OLE Automation compatibility issue between Delphi and LibreOffice as I was starting to fear.

This is exactly what i do understand now, UNO only exist for Java and C++, and it is higher level abstraction, like there is no Initialize you tried to use in the API but it is only exist in UNO API.

 

Also, the usage i mentioned about Input/Output is there, and it is confusing, as in my example above only Output can write and read, but the Input is to receive only.

 

What get to mind, is the similarity between of this get/load from memory in Open/LibreOffice and embedded Internet Explorer, it doesn't allow to load/navigate to stream or memory data, but you should navigate to an blank then load the content form memory, this lead me now to this working example with SWriter

program LibreOfficeFromDelphi;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  System.Win.ComObj,
  Winapi.ActiveX,
  Variants;

procedure LoadSWriterDocumentFromTBytes(const ADocumentData: string);
var
  LServiceManager, LDesktop, LDocument, LText, LTextCursor: Variant;
begin
  {Connect to LibreOffice}
  LServiceManager := CreateOleObject('com.sun.star.ServiceManager');
  LDesktop := LServiceManager.createInstance('com.sun.star.frame.Desktop');

  LDocument := LDesktop.loadComponentFromURL('private:factory/swriter', '_blank', 0, VarArrayCreate([0, -1], varVariant));

  LText := LDocument.Text;
  LTextCursor := LText.createTextCursor;
  LText.insertString(LTextCursor, ADocumentData, False);
end;

var
  SomeTextForWriter: string;

begin
  CoInitialize(nil);
  try
    SomeTextForWriter := ' Hello, from Delphi';
    LoadSWriterDocumentFromTBytes(SomeTextForWriter);
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Writeln('Done.');
  Readln;
end.

The result

 

But the core is inserting text !, simple text, may be there is a way to insert formatted text !?

 

And for SCalc, also opening blank is working fine, but there is no text but sheets and stuff.

 

The whole thing is very similar to DOM, may be it is XComponent , but really can't find useful information here, all internet resources leads to the blocked road with UNO API.

[Kas Ob. 151]

At last ! 😎

@Pierre le Riche found a solution, by using Pipe (com.sun.star.io.Pipe) instead of stream, one can get your needed result.

https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1io_1_1Pipe.html

 

program LibreOfficeLoadingFromStream;

{$APPTYPE CONSOLE}

uses
  System.SysUtils,
  System.Win.ComObj,
  Winapi.ActiveX;

procedure LoadWriterDocumentFromBytes(const ADocumentData: TBytes; const aFilterName: string = 'Text'; aFilterOptions: string = '');
var
  LServiceManager, LDesktop, LProperties: Variant;
  LPipe: Variant;
  LPropertyArray: TArray<Variant>;
begin
  {Connect to LibreOffice}
  LServiceManager := CreateOleObject('com.sun.star.ServiceManager');
  LDesktop := LServiceManager.createInstance('com.sun.star.frame.Desktop');

  LPipe := LServiceManager.createInstance('com.sun.star.io.Pipe');
  LPipe.writeBytes(Variant(ADocumentData));
  LPipe.closeOutput; // Important: close output to signal end of data (adding and simulating EOF)

  SetLength(LPropertyArray, 2);
  LPropertyArray[0] := LServiceManager.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
  LPropertyArray[0].Name := 'InputStream';
  LPropertyArray[0].Value := LPipe;

  LPropertyArray[1] := LServiceManager.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
  LPropertyArray[1].Name := 'FilterName';
  LPropertyArray[1].Value := aFilterName;

  if aFilterOptions <> '' then
  begin
    SetLength(LPropertyArray, 3);
    LPropertyArray[2] := LServiceManager.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
    LPropertyArray[2].Name := 'FilterOptions';
    LPropertyArray[2].Value := aFilterOptions;
  end;
  LProperties := LPropertyArray;

  LDesktop.loadComponentFromURL('private:stream', '_blank', 0, LProperties);
end;

const     {(*}
  TXTData = 'Hi From Delphi !.';
  HTMLData = '<!DOCTYPE html><html><body><h1>My First Heading</h1><p>My first paragraph.</p></body></html>'; // don't know how !
  RTFData = ' {\rtf1\ansi{\fonttbl\f0\fswiss Helvetica;}\f0\pard This is some {\b bold} text.\par }';        // also still missing something
  CSVData = 'Name,Age,City,Country' + #13#10 +
            'John Doe,30,New York,USA' + #13#10 +
            'Jane Smith,25,London,UK' + #13#10 +
            'Bob Johnson,35,Toronto,Canada' + #13#10 +
            'Alice Brown,28,Sydney,Australia';{*)}
  CSVFilterOption = '44,34,76,1,1/5/2/1/3/5/4/5,0,true,true,true';    // for the above CSVData

begin
  CoInitialize(nil);

  try
    LoadWriterDocumentFromBytes(BytesOf(TXTData));
    //LoadWriterDocumentFromBytes(BytesOf(HTMLData),'HTML Document (Writer)');         // missing something may be
    //LoadWriterDocumentFromBytes(BytesOf(RTFData),'Rich Text');                       // also
    LoadWriterDocumentFromBytes(BytesOf(CSVData), 'Text - txt - csv (StarCalc)', CSVFilterOption);       // for some reason 'csv' is not enough
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Writeln('Done.');
  Readln;
end.

The result is smooth loading of two instances SWriter and SCalc 

 

Tried to load HML and RTF but didn't work, and i really got bored with this, and believe this is a solution for your obstacle for now.

 

ps if you managed to make it load html or pdf, then please share !

extra info

the opened instances has document named as "private:stream" but this is easy to rename

the most complete supported filters is this

https://help.libreoffice.org/25.2/en-US/text/shared/guide/convertfilters.html

 

Good luck !

[Pierre le Riche 23]

Thank you, that import via pipe code will definitely come in handy as well. I've currently got all imports and exports between formats going via temporary disk file, which is not ideal. 

 

My next task is going to be to be able to intercept when the user clicks the save button and then store the updated document in the database. I still need to figure out how to intercept the click of the button so I know when the user has attempted to save the document (I will search for some examples online), but I think using a pipe might solve both the actual loading and saving. I did a quick test specifying a SequenceOutputStream as "OutputStream" in the arguments when loading the document, and then when the user clicks the save button it works the first time, but raises an exception on the second. (The error on the second save attempt is not unexpected, given that I didn't retrieve the data of the first save.)

 

[Kas Ob. 151]

3 hours ago, Pierre le Riche said:

I did a quick test specifying a SequenceOutputStream as "OutputStream" in the arguments when loading the document, and then when the user clicks the save button it works the first time, but raises an exception on the second.

I would leave this to the last, as from what i got once pipe is closed then it is closed and need replacing, but ... who knows.

 

3 hours ago, Pierre le Riche said:

I still need to figure out how to intercept the click of the button so I know when the user has attempted to save the document (I will search for some examples online),

For few hours i also was trying to do the same, but the code is not working fully because i am stuck with again UNO layer, which is causing this, anyway you need to call ".addDocumentEventListener(EventListener as IDispatch);" on either 'com.sun.star.frame.GlobalEventBroadcaster' or LDocument, both working the same from what i see, yet the problem is in EventListener implementation

 

It should be

  TDocumentEventListener = class(TInterfacedObject, IDispatch, ITypeInfo)

And i see callbacks being triggering GetTypeInfo and GetFuncDesc, but filling and answering GetFuncDesc is really confusing, Invoke on other hand being called once at least but i thinkit is the one belongs to IDispathc, ... don't know what to say more, but hope that put you on the road and i might try again later but for today i am now in full hate mode for LibreOffice and its documentation, also its source, it is hard to read and follow.

 

Also debugging is hard, on top of LB is crashing and stop responding, don't know if this happen with your setup, but after some crashes it stop to respond and i need full system restart to bring it up again, though it might be failure on OS side to clean OLE machine.

 

I am stuck at GetFuncDesc, tried many things but it still causing AV, as it went all the way to execute Invoke, yet i am failing to point/pass/fill it right.

[Pierre le Riche 23]

3 minutes ago, Kas Ob. said:

i am now in full hate mode for LibreOffice and its documentation, also its source, it is hard to read and follow.

Haha, I am with you there 100%. It is poorly documented and the error messages are not helpful at all. I spend hours getting a few lines of code working... it really makes me feel unproductive.

 

Thank you for all your help so far. I think I'm starting to see the light at the end of the tunnel. I'll post my code here once I get it working.

[Kas Ob. 151]

Nice to hear and you are welcome.

 

The good news is that i solved the GetFuncDesc, use it, it is working now fine

function TDocumentEventListener.GetFuncDesc(index: Integer; out pfuncdesc: PFuncDesc): HResult;
var
  FuncDesc: TFuncDesc;
  ElemDescList: PElemDescList;
begin
  Writeln('EventListener GetFuncDesc: index=', index);
  if index in [0, 1] then
  begin
    FillChar(FuncDesc, SizeOf(TFuncDesc), 0);
    // Allocate array of one
    ElemDescList := CoTaskMemAlloc(SizeOf(TElemDesc));
    FillChar(ElemDescList^, SizeOf(TElemDesc), 0);
    ElemDescList^[0].tdesc.vt := VT_VARIANT;
    ElemDescList^[0].paramdesc.wParamFlags := PARAMFLAG_FIN;
    if index = 0 then
    begin
      FuncDesc.memid := 1;
      FuncDesc.lprgelemdescParam := ElemDescList;
      FuncDesc.cParams := 1;
    end
    else
    begin
      FuncDesc.memid := 4;
      FuncDesc.lprgelemdescParam := ElemDescList;
      FuncDesc.cParams := 1;
    end;
    FuncDesc.funckind := FUNC_DISPATCH;
    FuncDesc.invkind := INVOKE_FUNC;
    FuncDesc.elemdescFunc.tdesc.vt := VT_HRESULT;
    pfuncdesc := CoTaskMemAlloc(SizeOf(TFuncDesc));
    Move(FuncDesc, pfuncdesc^, SizeOf(TFuncDesc));
    Result := S_OK;
  end
  else
  begin
    pfuncdesc := nil;
    Result := DISP_E_BADINDEX;
  end;
end;

I feel like i am implementing the whole UNO interface with this one 🤬

 

Anyway stopped at handling Invoke (as it is called now smoothly) and HandleDocumentEvent (my own field in the eventlister), HandleDocumentEvent is hell to handle, and can't find a real nice documentation for events and their parameters, its like shooting darts in the dark.

 

ps still using two separated CoTaskMemAlloc and they can be merged in one by aligning the memory, as will be released together, yet not touching it for now.

[Kas Ob. 151]

Here is the output of what seems working capture of all the events/actions from LB, though tested in one instance with text file

Document loaded successfully
Global event listener registered
Document loaded. Press Enter to exit.
Event: OnLayoutFinished
Other event: OnLayoutFinished
Event: OnTitleChanged
Other event: OnTitleChanged
Event: OnModifyChanged
Other event: OnModifyChanged
Event: OnLayoutFinished
Other event: OnLayoutFinished
Event: OnSave
Save event detected!
Event: OnCopyTo
Other event: OnCopyTo
Event: OnCopyToDone
Other event: OnCopyToDone
Captured data: Hi From Delphi !.1

Event: OnSaveFailed
SaveFailed event detected!
Event: OnModeChanged
Other event: OnModeChanged
Event: DialogExecute
Other event: DialogExecute
Event: OnModeChanged
Other event: OnModeChanged
Event: DialogClosed
Other event: DialogClosed
Event: OnLayoutFinished
Other event: OnLayoutFinished
Event: OnSave
Save event detected!
Event: OnCopyTo
Other event: OnCopyTo
Event: OnCopyToDone
Other event: OnCopyToDone
Captured data: Hi From Delphi !.13

Event: OnSaveFailed
SaveFailed event detected!
Event: OnModeChanged
Other event: OnModeChanged
Event: DialogExecute
Other event: DialogExecute
Event: OnModeChanged
Other event: OnModeChanged
Event: DialogClosed
Other event: DialogClosed
Event: OnPrepareViewClosing
Other event: OnPrepareViewClosing
Event: OnPrepareUnload
Other event: OnPrepareUnload
Event: OnModeChanged
Other event: OnModeChanged
Event: DialogExecute
Other event: DialogExecute
Event: OnModeChanged
Other event: OnModeChanged
Event: DialogClosed
Other event: DialogClosed
Event: OnViewClosed
Other event: OnViewClosed
Event: OnUnload
Other event: OnUnload
Event: OnUnfocus
Other event: OnUnfocus
Event: OnCloseApp
Other event: OnCloseApp

@Pierre le Riche i am DM my work, and can't guarantee it to be error/mistake free, yet it looks working and didn't dig more in the dialog handling, (like how to stop them )

and i expected the pipe for saving (output) is for one use.

[limelect 53]

This worked for me a while ago

 

 

(*
  // query the XComponentLoader interface from the desktop        XComponentLoader xComponentLoader = (XComponentLoader)UnoRuntime.queryInterface(            XComponentLoader.class, desktop);        // create empty array of PropertyValue structs, needed for loadComponentFromURL        PropertyValue[] loadProps = new PropertyValue[0];                // load new calc file        XComponent xSpreadsheetComponent = xComponentLoader.loadComponentFromURL(            "private:factory/scalc", "_blank", 0, loadProps);        // query its XSpreadsheetDocument interface, we want to use getSheets()        XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface(            XSpreadsheetDocument.class, xSpreadsheetComponent);        // use getSheets to get spreadsheets container        XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();        //insert new sheet at position 0 and get it by name, then query its XSpreadsheet interface        xSpreadsheets.insertNewByName("MySheet", (short)0);        Object sheet = xSpreadsheets.getByName("MySheet");        XSpreadsheet xSpreadsheet = (XSpreadsheet)UnoRuntime.queryInterface(            XSpreadsheet.class, sheet);        // use XSpreadsheet interface to get the cell A1 at position 0,0 and enter 21 as value        XCell xCell = xSpreadsheet.getCellByPosition(0, 0);        xCell.setValue(21); // enter another value into the cell A2 at position 0,1        xCell = xSpreadsheet.getCellByPosition(0, 1);        xCell.setValue(21); // sum up the two cells        xCell = xSpreadsheet.getCellByPosition(0, 2);        xCell.setFormula("=sum(A1:A2)");        // we want to access the cell property CellStyle, so query the cell's XPropertySet interface         XPropertySet xCellProps = (XPropertySet)UnoRuntime.queryInterface(            XPropertySet.class, xCell);        // assign the cell style "Result" to our formula, which is available out of the box        xCellProps.setPropertyValue("CellStyle", "Result");        // we want to make our new sheet the current sheet, so we need to ask the model        // for the controller: first query the XModel interface from our spreadsheet component        XModel xSpreadsheetModel = (XModel)UnoRuntime.queryInterface(           XModel.class, xSpreadsheetComponent);                // then get the current controller from the model        XController xSpreadsheetController = xSpreadsheetModel.getCurrentController();        // get the XSpreadsheetView interface from the controller, we want to call its method        // setActiveSheet        XSpreadsheetView xSpreadsheetView = (XSpreadsheetView)UnoRuntime.queryInterface(           XSpreadsheetView.class, xSpreadsheetController);        // make our newly inserted sheet the active sheet using setActiveSheet        xSpreadsheetView.setActiveSheet(xSpreadsheet);         }    catch( com.sun.star.lang.DisposedException e ) { //works from Patch 1        xRemoteContext = null;        throw e;    }
*)

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.ExtCtrls;

type
  TForm1 = class(TForm)
    Panel1: TPanel;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}
uses System.Win.ComObj;

procedure TForm1.Button1Click(Sender: TObject);
var
  xSpreadsheet,sheet,xCell,xSpreadsheets ,args,objDocument,objDesktop,Excel, Book: OleVariant;
  RowNumber: integer;
  ColNumber: integer;
const
  xlCellTypeLastCell = $0000000B; // ExcelXP
begin
//excel := CreateOleObject('Excel.Application');
excel := CreateOleObject('com.sun.star.ServiceManager');
 objDesktop:=excel.createInstance('com.sun.star.frame.Desktop');
 args := VarArrayCreate([0,1], varVariant);
// objDocument:=objDesktop.loadComponentFromURL('private:factory/swriter','_blank', 0, args) ; //load empty documemt
 // objDocument:=objDesktop.loadComponentFromURL('private:factory/swriter','G:\Delphi Projects\engineertips\Excel Row and Column Count\world_gps_map_database.xls', 0, args) ;
 objDocument:=objDesktop.loadComponentFromURL('private:factory/scalc','_blank', 0, args) ; //load new excel
 xSpreadsheets:=  objDocument.getSheets;
 xSpreadsheets.insertNewByName('MySheet', 0);
 sheet := xSpreadsheets.getByName('MySheet');
// xSpreadsheet := (XSpreadsheets).queryInterface(            XSpreadsheet.class, sheet);
 //xCell := xSpreadsheets.getCellByPosition(0, 0);
  xCell :=  sheet.getCellByPosition(0, 0);
 xCell.setValue(21);
  xCell :=  sheet.getCellByPosition(1, 0);
 xCell.setValue(521);
 // RowNumber := Sheet.UsedRange.EntireRow.Count;

 (*
    excel.Workbooks.Open( 'G:\Delphi Projects\engineertips\Excel Row and Column Count\world_gps_map_database.xls' );
  //  Sheet := excel.ActiveWorkbook.Worksheets[SheetName];


  Book := Excel.Workbooks.Open('G:\Delphi Projects\engineertips\Excel Row and Column Count\world_gps_map_database.xls',
                               False,   // ConfirmConversions
                               True );  // ReadOnly

  Sheet := Book.Worksheets[1];

  //RowNumber := Sheet.UsedRange.EntireRow.Count;    // May be wrong!
  //ColNumber := Sheet.UsedRange.EntireColumn.Count; // May be wrong!
  RowNumber := Sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row;
  ColNumber := Sheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column; *)
end;

end.

 

Edited Wednesday at 12:58 PM by Sherlock
Inserted code formatting tags...

[limelect 53]

Tried the above source, and those lines did nothing

 

   LoadWriterDocumentFromBytes(BytesOf(TXTData));
//    LoadWriterDocumentFromBytes(BytesOf(HTMLData),'HTML Document (Writer)');         // missing something may be
//    LoadWriterDocumentFromBytes(BytesOf(RTFData),'Rich Text');                       // also
    LoadWriterDocumentFromBytes(BytesOf(CSVData), 'Text - txt - csv (StarCalc)', CSVFilterOption);       // for some reason 'csv' is not enough

 

Edited Wednesday at 12:59 PM by Sherlock
Inserted code formatting tags...