Sorting in OpenOffice Calc via Delphi

Question

I've spent several hours trying to sort data in OpenOffice Calc in Delphi. I tried to follow the examples in basic (from "OpenOffice.org Macros Explained"*) or C#, but still no result. Data wont change at all. What am i doing wrong? Maybe the problem is with data types?

*https://www.pitonyak.org/OOME_3_0.pdf, page 488.

Minimal reproducible example:

program OpenOfficeCalcSortingIssue;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, Variants, ComObj, ActiveX;

var
  StarOffice:     OleVariant;
  SODesktop:      OleVariant;
  SOCalc:         OleVariant;
  CalcSheets:     OleVariant;
  CalcSheet:      OleVariant;
  SortFields:     OleVariant;
  SortDescriptor: OleVariant;

begin
  CoInitialize(nil);
  try
    try
      StarOffice:=CreateOleObject('com.sun.star.ServiceManager');
      SODesktop:=StarOffice.CreateInstance('com.sun.star.frame.Desktop');
      SOCalc:=SODesktop.LoadComponentFromURL('private:factory/scalc', '_blank', 0, VarArrayCreate([0, -1], varVariant));
      CalcSheets:=SOCalc.GetSheets;
      CalcSheet:=CalcSheets.GetByIndex(0);

      CalcSheet.GetCellByPosition(0, 0).SetValue(2);
      CalcSheet.GetCellByPosition(0, 1).SetValue(1);
      CalcSheet.GetCellByPosition(0, 2).SetValue(4);
      CalcSheet.GetCellByPosition(0, 3).SetValue(3);

      SortFields:=VarArrayCreate([0, 0], varVariant);
      SortFields[0]:=StarOffice.Bridge_GetStruct('com.sun.star.util.SortField');
      SortFields[0].Field:=0;
      SortFields[0].SortAscending:=True;

      SortDescriptor:=VarArrayCreate([0, 0], varVariant);
      SortDescriptor[0]:=StarOffice.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
      SortDescriptor[0].Name:='SortFields';
      SortDescriptor[0].Value:=SortFields;

      CalcSheet.GetCellRangeByName('A1:A4').Sort(SortDescriptor);
    except
      on E: Exception do
        Writeln(E.ClassName, ': ', E.Message);
    end;
  finally
    CoUninitialize;
  end;
end.

loltrol · Accepted Answer · 2021-11-09 12:34:16Z

0

As usual, solution came shortly after exposing the problem to the world :-) I've found an example from the depths of Internet. Hope this topic on SO will be googled a way more easily by people who stumbled into this issue. The answer is: SortFields is not just a variants array. It is a "Value Object" and must be set like this:

ValueObject:=StarOffice.Bridge_GetValueObject;
ValueObject.Set('[]com.sun.star.table.TableSortField', SortFields);

In example that i found it is commented "you must specify which type of sequence is transmitted to SortFields property".

So the whole code should be:

      StarOffice:=CreateOleObject('com.sun.star.ServiceManager');
      SODesktop:=StarOffice.CreateInstance('com.sun.star.frame.Desktop');
      SOCalc:=SODesktop.LoadComponentFromURL('private:factory/scalc', '_blank', 0, VarArrayCreate([0, -1], varVariant));
      CalcSheets:=SOCalc.GetSheets;
      CalcSheet:=CalcSheets.GetByIndex(0);

      CalcSheet.GetCellByPosition(0, 0).SetValue(2);
      CalcSheet.GetCellByPosition(0, 1).SetValue(1);
      CalcSheet.GetCellByPosition(0, 2).SetValue(4);
      CalcSheet.GetCellByPosition(0, 3).SetValue(3);

      SortFields:=VarArrayCreate([0, 0], varVariant);
      SortFields[0]:=StarOffice.Bridge_GetStruct('com.sun.star.table.TableSortField');
      SortFields[0].Field:=0;
      SortFields[0].IsAscending:=True;

      ValueObject:=StarOffice.Bridge_GetValueObject;
      ValueObject.Set('[]com.sun.star.table.TableSortField', SortFields);

      SortDescriptor:=VarArrayCreate([0, 0], varVariant);
      SortDescriptor[0]:=StarOffice.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
      SortDescriptor[0].Name:='SortFields';
      SortDescriptor[0].Value:=ValueObject;

      CalcSheet.GetCellRangeByName('A1:A4').Sort(SortDescriptor);

answered Nov 9, 2021 at 12:34

loltrol

1036 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AmigoJack Over a year ago

Please link to "the depths of Internet" instead of obfuscating your source(s) of knowledge.

loltrol Over a year ago

I have downloaded the example from sourceforge.net/projects/ooomacros/files/Delphi OOo/Version 1.2/Delphi_OOo_v12en.zip It seems i found that link in another SO question (not about sorting though): stackoverflow.com/questions/9274300/…

Collectives™ on Stack Overflow

Sorting in OpenOffice Calc via Delphi

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related