Sort a sheet with javascript's macro?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Frost87
Posts: 16
Joined: Fri Apr 13, 2012 3:13 pm

Sort a sheet with javascript's macro?

Post by Frost87 »

I need a example that how sort a range in sheet with javascript. Thanks for everything.
OpenOffice 3.3.0
Windows XP Profesional
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort a sheet with javascript's macro?

Post by Villeroy »

You do not sort a sheet. What you sort is a cell range on a sheet. In JavaScript you do that in exactly the same way as in any other programming language.
http://www.openoffice.org/api/docs/comm ... ptor2.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Frost87
Posts: 16
Joined: Fri Apr 13, 2012 3:13 pm

Re: Sort a sheet with javascript's macro?

Post by Frost87 »

I have problems with this macro. Im trying sort the columns with this code

Code: Select all

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheets);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.awt.XWindowPeer);
importClass(Packages.com.sun.star.awt.XMessageBoxFactory);
importClass(Packages.com.sun.star.awt.Rectangle);
importClass(Packages.com.sun.star.awt.MessageBoxButtons);
importClass(Packages.com.sun.star.text.XTextRange);
importClass(Packages.com.sun.star.util.SortField);
importClass(Packages.com.sun.star.beans.PropertyValue);


oDoc = XSCRIPTCONTEXT.getDocument();
xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
xSpreadsheets = xSpreadsheetDocument.getSheets();
xIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSpreadsheets);
oSheet = xIndexAccess.getByIndex(0);
xCellRange = UnoRuntime.queryInterface(XCellRange, oSheet);

xCellRango = xCellRange.getCellRangeByName("A2:D7");

aSortFields = new com.sun.star.util.SortField;
aSortDesc = new com.sun.star.beans.PropertyValue;

aSortFields(0).Field = 0
aSortFields(0).SortAscending = True
aSortFields(1).Field = 2
aSortFields(1).SortAscending = False  
aSortFields(2).Field = 1
aSortFields(2).SortAscending = True    
aSortDesc(0).Name = "SortFields"
aSortDesc(0).Value = aSortFields()

xCellRango.sort(aSortDesc);
Some idea? Thanks for all.
OpenOffice 3.3.0
Windows XP Profesional
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort a sheet with javascript's macro?

Post by Villeroy »

Code: Select all

aSortFields = new com.sun.star.util.SortField;
aSortDesc = new com.sun.star.beans.PropertyValue;
I don't really know much about JavaScript but aSortFields seems to be one SortField where you need an array of SortFields.
Same with the property value aSortDesc which should be an array of property values. UNO is picky about data types. On the other hand, it makes debugging easy since the required data type is always perfectly clear.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Frost87
Posts: 16
Joined: Fri Apr 13, 2012 3:13 pm

Re: Sort a sheet with javascript's macro?

Post by Frost87 »

Yes, i don't know how i can to instantiate the structs SortField and SortDesc in javascript. Well..., I keep trying it and in a while, I will post something
OpenOffice 3.3.0
Windows XP Profesional
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Sort a sheet with javascript's macro?

Post by Charlie Young »

I haven't solved this problem, but I may have clarified it some, so I'm posting this. It would be nice if someone could find a solution, but if not, I think an issue needs to be filed about JavaScript macros. I looked the other day to see if any such thing had already been filed, but I didn't see anything among the 55 JavaScript items that showed up in the issue tracker.

In the attached spreadsheet, I have formulas in D2:D101 generating a random capital letter, and in E2:E101 a random integer from 0-5. I then have (or attempt to have) a macro: 1) recalculate the random entries, 2) copy the values to columns A and B, then, 3) sort columns A and B either by the numbers then letters, or letters then numbers. Which field to sort first is specified in H2:H3; if H2 is 0 and H3 is 1, first the letters then the numbers will be sorted, and if H2 is 1 and H3 is 0, the numbers then the letters. I'm also using column J to report some information from the JavaScript macro. More on this below.

I have Basic, Python, and JavaScript macros to do the sorting. For the purposes of this post I have embedded the Python macro in the Calc file. To do this I used a trick posted by karolus here.

Here is the Python

Code: Select all

import uno
import os
import unohelper
from com.sun.star.beans import PropertyValue
from com.sun.star.table import TableSortField
from com.sun.star.table.TableSortFieldType import AUTOMATIC

context = XSCRIPTCONTEXT
def SortCellRange(*dummy):
    oDoc = context.getDocument()
    oSheet = oDoc.getSheets().getByIndex(0)
    inRange = oSheet.getCellRangeByName("D1:E101")
    oSortRange = oSheet.getCellRangeByName("A1:B101")
    OrderRange = oSheet.getCellRangeByName("H2:H3")
    oDoc.calculateAll()
    oSortRange.setDataArray(inRange.getDataArray())
    SortRange(oSortRange,OrderRange.getData())

def SortRange(oRange,cols):
     
    oSortDesc = []
    oSortField = []
   
    FieldSpecs = ((cols[0][0],True,AUTOMATIC,False),(cols[1][0],True,AUTOMATIC,False))
    
    for spec in FieldSpecs:
        SF = TableSortField()
        SF.Field, SF.IsAscending, SF.FieldType, SF.IsCaseSensitive = [spec[i] for i in range(len(spec))]
        oSortField.append(SF)
    
    DescriptorSpecs = (("SortFields",uno.Any("[]com.sun.star.table.TableSortField", tuple(oSortField))),
                       ("ContainsHeader",True),
                       ("IsSortColumns",False),
                       ("BindFormatsToContent",True))
                       
    for desc in DescriptorSpecs:
        SD = PropertyValue()
        SD.Name, SD.Value = desc[0], desc[1]
        oSortDesc.append(SD)
 
    oRange.sort(tuple(oSortDesc))

g_exportedScripts = SortCellRange,
The embedded file containing the above code is called SortRange.py.

The spreadsheet offers the options in H9 to do the sorting via Basic, Python, or JavaScript. The first two work, but there are problems with the jscript.

Note first however, that com.sun.star.util.SortField is deprecated, and we are to use com.sun.star.table.TableSortField instead, though that isn't the problem. Here is the JavaSCript macro

Code: Select all

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheets);
importClass(Packages.com.sun.star.sheet.XCellRangeData);
importClass(Packages.com.sun.star.chart.XChartDataArray);
importClass(Packages.com.sun.star.sheet.XCalculatable);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.beans.PropertyValue);
importClass(Packages.com.sun.star.table.TableSortField);
importClass(Packages.com.sun.star.table.TableSortFieldType);
importClass(Packages.com.sun.star.util.XSortable);
importClass(Packages.com.sun.star.text.XTextRange);

// get document XModel and get specific sheet
oDoc = XSCRIPTCONTEXT.getDocument();
xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
oCalculate = UnoRuntime.queryInterface(XCalculatable, oDoc);
xSpreadsheets = xSpreadsheetDocument.getSheets();
xIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSpreadsheets);
oSheet = xIndexAccess.getByIndex(0);

oCalculate.calculateAll();
xCellRange = UnoRuntime.queryInterface(XCellRange, oSheet);
inRange = xCellRange.getCellRangeByName("D1:E101");
inRangeData = UnoRuntime.queryInterface(XCellRangeData, inRange);
OrderRange = xCellRange.getCellRangeByName("H2:H3");
TestRange = xCellRange.getCellRangeByName("J1:J100");
OrderRangeData = UnoRuntime.queryInterface(XChartDataArray, OrderRange);
xSortRange = xCellRange.getCellRangeByName("A1:B101");
xSortRangeData = UnoRuntime.queryInterface(XCellRangeData, xSortRange);
xSortRangeData.setDataArray(inRangeData.getDataArray());
SortRange(xSortRange,OrderRangeData.getData(),TestRange)

function SortRange(oRange,cols,TestRange)
{
	oSortRange = UnoRuntime.queryInterface(XSortable, oRange);
	oSortField = new Array(2);

	for(i = 0;i < oSortField.length;i++)
	{
		oSortField[i] = new TableSortField();
	}
	
	oSortField[0].Field = cols[0][0];
	oSortField[0].IsAscending = true;
	oSortField[0].FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC;
	oSortField[0].IsCaseSensitive = false;
	
	oSortField[1].Field = cols[1][0];
	oSortField[1].IsAscending = true;
	oSortField[1].FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC;
	oSortField[1].IsCaseSensitive = false;
	
	oSortDesc = new Array(4);
	for(i = 0;i < oSortDesc.length;i++)
	{
		oSortDesc[i] = new PropertyValue();
	}
	f = new Array(2);
	f[0] = new com.sun.star.uno.Any(TableSortField,oSortField[0]);
	f[1] = new com.sun.star.uno.Any(TableSortField,oSortField[1]);
	oSortDesc[0].Name = "SortFields";
	oSortDesc[0].Value = oSortField;
	oSortDesc[1].Name = "ContainsHeader";
	oSortDesc[1].Value = true;
	oSortDesc[2].Name = "IsSortColumns";
	oSortDesc[2].Value = false;
	oSortDesc[3].Name = "BindFormatsToContent";
	oSortDesc[3].Value = false;
	xTextRange = UnoRuntime.queryInterface(XTextRange, TestRange.getCellByPosition(0,1));
	xTextRange.setString(f.toString());
	xTextRange = UnoRuntime.queryInterface(XTextRange, TestRange.getCellByPosition(0,2));
	xTextRange.setString(f[0].getType().getTypeName());
	TestRange.getCellByPosition(0,3).setValue(f[0].getObject().Field);
	TestRange.getCellByPosition(0,4).setValue(f[1].getObject().Field);
	
	oSortRange.sort(oSortDesc);
}

When that is run as is, it does the indicated reporting in column J, but when it attempts to sort, it gives this error
javaerror.jpg
The problem comes from trying to directly assign the TableSortFields to the SortDescriptor by

Code: Select all

	oSortDesc[0].Name = "SortFields";
	oSortDesc[0].Value = oSortField;
Thinking that the problem might be that oSortField isn't a proper UNO Any, I attempt to wrap it up as such with

Code: Select all

	f = new Array(2);
	f[0] = new com.sun.star.uno.Any(TableSortField,oSortField[0]);
	f[1] = new com.sun.star.uno.Any(TableSortField,oSortField[1]);
and it reports in J2 that the string representation of this Any as

Code: Select all

Any[Type[com.sun.star.table.TableSortField], com.sun.star.table.TableSortField@42a6eb],Any[Type[com.sun.star.table.TableSortField], com.sun.star.table.TableSortField@1dcc4cd]
J3 shows the correct type, and J4 and J5 show that the Field values match H2 and H3. This looks promising, but if I assign f as the fields with

Code: Select all

	oSortDesc[0].Name = "SortFields";
	oSortDesc[0].Value = f;
I get the same error.

I have tried about a hundred other possibilities, such as using java.lang.Object (which maps to com.sun.star.uno.Any), and I've gotten results ranging from the oSortDesc[0].Value being accepted quietly but not working, to getting crashes and recoveries.

I will note two other things:

If I simply comment out the line

Code: Select all

	//oSortDesc[0].Value = WhatEver;
The jscript macro performs the sort based on the last successful sort, whether that was done via Basic, Python, or Data > Sort, and the sort will also work according to the boolean values (ContainsHeader, etc.) specified in the SortDescriptor.

In the Python, it seems I need to resort to this to get the correct assignment of the fields to the descriptor

Code: Select all

uno.Any("[]com.sun.star.table.TableSortField", tuple(oSortField))
Just using tuple(oSortField) doesn't work. It sorts without an error, but doesn't work with the fields specified in J2 and J3.

Finally, it looks to me like the fundamental problem with the JavaScript here is that a PropertyValue just won't accept a JavaScript NativeArray. With c++ automation one inserts a Sequence < ::com::sun::star::table::TableSortField > like so

Code: Select all

oSortDesc[0].Value <<= oSortField;
Attachments
SortByMacros.ods
(15.73 KiB) Downloaded 256 times
Apache OpenOffice 4.1.1
Windows XP
hanya
Volunteer
Posts: 885
Joined: Fri Nov 23, 2007 9:27 am
Location: Japan

Re: Sort a sheet with javascript's macro?

Post by hanya »

Current JavaScript engine is Rhino 1.7R3 (Apache OpenOffice), therefore please read its documentation: http://www.mozilla.org/rhino/ScriptingJ ... tingarrays

Rhino is implemented in Java and it uses office API through Java bridge. Java-UNO bridge requires well typed value to convert value from Java to UNO. In Rhino, typed Java array can be created as follows, described in the above document:

Code: Select all

oSortField = java.lang.reflect.Array.newInstance(TableSortField, 1);
Rhino 1.7R4 has been released 10 days ago, some bug fix and improvements has been done.
Please, edit this thread's initial post and add "[Solved]" to the subject line if your problem has been solved.
Apache OpenOffice 4-dev on Xubuntu 14.04
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Sort a sheet with javascript's macro?

Post by Charlie Young »

hanya wrote:Current JavaScript engine is Rhino 1.7R3 (Apache OpenOffice), therefore please read its documentation: http://www.mozilla.org/rhino/ScriptingJ ... tingarrays

Rhino is implemented in Java and it uses office API through Java bridge. Java-UNO bridge requires well typed value to convert value from Java to UNO. In Rhino, typed Java array can be created as follows, described in the above document:

Code: Select all

oSortField = java.lang.reflect.Array.newInstance(TableSortField, 1);
Rhino 1.7R4 has been released 10 days ago, some bug fix and improvements has been done.
Thank you hanya. I had gotten lost in the Mozilla documentation not really knowing what I was looking for.

Here is the working macro, eliminating the now unnecessary stuff about the Anys and printing to column J.

Code: Select all

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.sheet.XSpreadsheets);
importClass(Packages.com.sun.star.sheet.XCellRangeData);
importClass(Packages.com.sun.star.chart.XChartDataArray);
importClass(Packages.com.sun.star.sheet.XCalculatable);
importClass(Packages.com.sun.star.table.XCellRange);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.beans.PropertyValue);
importClass(Packages.com.sun.star.table.TableSortField);
importClass(Packages.com.sun.star.table.TableSortFieldType);
importClass(Packages.com.sun.star.util.XSortable);

// get document XModel and get specific sheet
oDoc = XSCRIPTCONTEXT.getDocument();
xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument, oDoc);
oCalculate = UnoRuntime.queryInterface(XCalculatable, oDoc);
xSpreadsheets = xSpreadsheetDocument.getSheets();
xIndexAccess = UnoRuntime.queryInterface(XIndexAccess, xSpreadsheets);
oSheet = xIndexAccess.getByIndex(0);

oCalculate.calculateAll();
xCellRange = UnoRuntime.queryInterface(XCellRange, oSheet);
inRange = xCellRange.getCellRangeByName("D1:E101");
inRangeData = UnoRuntime.queryInterface(XCellRangeData, inRange);
OrderRange = xCellRange.getCellRangeByName("H2:H3");
OrderRangeData = UnoRuntime.queryInterface(XChartDataArray, OrderRange);
xSortRange = xCellRange.getCellRangeByName("A1:B101");
xSortRangeData = UnoRuntime.queryInterface(XCellRangeData, xSortRange);
xSortRangeData.setDataArray(inRangeData.getDataArray());
SortRange(xSortRange,OrderRangeData.getData())

function SortRange(oRange,cols)
{
	oSortRange = UnoRuntime.queryInterface(XSortable, oRange);
	oSortField = java.lang.reflect.Array.newInstance(TableSortField, 2);
	
	for(i = 0;i < oSortField.length;i++)
	{
		oSortField[i] = new TableSortField();
	}
	
	oSortField[0].Field = cols[0][0];
	oSortField[0].IsAscending = true;
	oSortField[0].FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC;
	oSortField[0].IsCaseSensitive = false;
	
	oSortField[1].Field = cols[1][0];
	oSortField[1].IsAscending = true;
	oSortField[1].FieldType = com.sun.star.table.TableSortFieldType.AUTOMATIC;
	oSortField[1].IsCaseSensitive = false;
	
	oSortDesc = new Array(4);
	for(i = 0;i < oSortDesc.length;i++)
	{
		oSortDesc[i] = new PropertyValue();
	}
	
	oSortDesc[0].Name = "SortFields";
	oSortDesc[0].Value = oSortField;
	oSortDesc[1].Name = "ContainsHeader";
	oSortDesc[1].Value = true;
	oSortDesc[2].Name = "IsSortColumns";
	oSortDesc[2].Value = false;
	oSortDesc[3].Name = "BindFormatsToContent";
	oSortDesc[3].Value = false;
	
	oSortRange.sort(oSortDesc);
}

I have another question about JavaScript, but I'll try to find the answer given the new hints before I ask (in a new thread).
 Edit: I think a moderator could tag this as solved, I don't know if Frosty87 is still around. 
Apache OpenOffice 4.1.1
Windows XP
Post Reply