Sort a sheet with javascript's macro?
Posted: Tue Apr 17, 2012 10:14 am
I need a example that how sort a range in sheet with javascript. Thanks for everything.
User community support forum for Apache OpenOffice, LibreOffice and all the OpenOffice.org derivatives
https://forum.openoffice.org/en/forum/
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);
Code: Select all
aSortFields = new com.sun.star.util.SortField;
aSortDesc = new com.sun.star.beans.PropertyValue;
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,
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);
}
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;
Code: Select all
//oSortDesc[0].Value = WhatEver;
Code: Select all
uno.Any("[]com.sun.star.table.TableSortField", tuple(oSortField))
Code: Select all
oSortDesc[0].Value <<= oSortField;
Code: Select all
oSortField = java.lang.reflect.Array.newInstance(TableSortField, 1);
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);
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. |