Sort a sheet with javascript's macro?
Sort a sheet with javascript's macro?
I need a example that how sort a range in sheet with javascript. Thanks for everything.
OpenOffice 3.3.0
Windows XP Profesional
Windows XP Profesional
Re: Sort a sheet with javascript's macro?
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Sort a sheet with javascript's macro?
I have problems with this macro. Im trying sort the columns with this code
Some idea? Thanks for all.
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);
OpenOffice 3.3.0
Windows XP Profesional
Windows XP Profesional
Re: Sort a sheet with javascript's macro?
Code: Select all
aSortFields = new com.sun.star.util.SortField;
aSortDesc = new com.sun.star.beans.PropertyValue;
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Sort a sheet with javascript's macro?
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
Windows XP Profesional
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Sort a sheet with javascript's macro?
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
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
When that is run as is, it does the indicated reporting in column J, but when it attempts to sort, it gives this error
The problem comes from trying to directly assign the TableSortFields to the SortDescriptor by
Thinking that the problem might be that oSortField isn't a proper UNO Any, I attempt to wrap it up as such with
and it reports in J2 that the string representation of this Any as
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
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
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
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::table::TableSortField > like so
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 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);
}
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;
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]);
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]
Code: Select all
oSortDesc[0].Name = "SortFields";
oSortDesc[0].Value = f;
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;
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))
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::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
Windows XP
Re: Sort a sheet with javascript's macro?
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:
Rhino 1.7R4 has been released 10 days ago, some bug fix and improvements has been done.
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);
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
Apache OpenOffice 4-dev on Xubuntu 14.04
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Sort a sheet with javascript's macro?
Thank you hanya. I had gotten lost in the Mozilla documentation not really knowing what I was looking for.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:Rhino 1.7R4 has been released 10 days ago, some bug fix and improvements has been done.Code: Select all
oSortField = java.lang.reflect.Array.newInstance(TableSortField, 1);
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);
}
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
Windows XP