Calc¶

Get sheet¶

Get all sheets names.

util = createUnoService("org.universolibre.EasyDev")

'Get current doc
doc = util.getDoc("")
names = util.getSheetsNames(doc)
util.msgbox(names)

Get active sheet.

Important

Current doc can be IDE, and IDE not have sheets

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

'Get active sheet from active doc
sheet = util.getSheet(address)
msgbox sheet.Name

Is better use explicit doc

doc = ThisComponent
address.Doc = doc
sheet = util.getSheet(address)
MsgBox sheet.Name

Get active sheet from other doc

address.Doc = util.getDoc("Untitled 3.ods")
sheet = util.getSheet(address)
MsgBox sheet.Name

Get sheet by name.

address.Doc = ThisComponent
address.Sheet = "Sheet2"
sheet = util.getSheet(address)
MsgBox sheet.Name

Get sheet by index. Index start in 0.

address.Doc = ThisComponent
address.Sheet = 1
sheet = util.getSheet(address)
MsgBox sheet.Name

Get last sheet by index.

address.Doc = ThisComponent
address.Sheet = -1
sheet = util.getSheet(address)
MsgBox sheet.Name

Set active sheet¶

Active sheet by name.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

doc = ThisComponent
address.Doc = doc
address.Sheet = "Sheet2"

util.sheetActivate(address)

Active sheet by index. Last sheet index == -1

doc = ThisComponent
address.Doc = doc
address.Sheet = -1

util.sheetActivate(address)

Insert sheets¶

Insert sheet in pos. Get error if sheet exists

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "New Sheet"
util.sheetInsert(address, 0, False)

Rename sheet if exists. Get name “New Sheet_1”, “New Sheet_2”, etc...

address.Doc = ThisComponent
address.Sheet = "New Sheet"
util.sheetInsert(address, 0, True)

Insert in last pos.

address.Doc = ThisComponent
address.Sheet = "New Sheet"
util.sheetInsert(address, -1, True)

Insert more one sheet.

address.Doc = ThisComponent
address.Sheet = Array("One", "Two", "Other")
util.sheetInsert(address, 0, True)

Insert sheet from document¶

Only LibreOffice, for OpenOffice use: Copy sheet to document. Insert all sheets from source document to target document.

util = createUnoService("org.universolibre.EasyDev")
source = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")
target = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

doc = ThisComponent
source.Doc = doc

new_doc = util.newDoc("")
target.Doc = new_doc

util.sheetInsertFromDoc(source, target, -1, False)

Remove sheet¶

Important

The index of sheets change if remove some sheet.

Remove sheet by name

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "Sheet2"
util.sheetRemove(address, False)

Remove sheet by index

address.Doc = ThisComponent
address.Sheet = 1
util.sheetRemove(address, False)

Remove sheet by index, last sheet

address.Doc = ThisComponent
address.Sheet = -1
util.sheetRemove(address, False)

First get sheet and remove by object

address.Doc = ThisComponent
address.Sheet = "Sheet6"
sheet = util.getSheet(address)
address.Sheet = sheet
util.sheetRemove(address, False)

Remove more one sheet, by index, name or object

address.Doc = ThisComponent
address.Sheet = Array(0, "Sheet4", "Sheet2", sheet, -1)
util.sheetRemove(address, False)

Remove all sheet, except name

address.Doc = ThisComponent
address.Sheet = "Sheet3"
util.sheetRemove(address, True)

Remove all sheet, except more one

address.Doc = ThisComponent
address.Sheet = Array("Sheet2", "Sheet6")
util.sheetRemove(address, True)

Move sheet¶

Move sheet by name to pos.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "Sheet2"
util.sheetMove(address, 0)

Move first sheet to last pos

address.Doc = ThisComponent
address.Sheet = 0
util.sheetMove(address, -1)

Move sheet by object

address.Doc = ThisComponent
address.Sheet = 1
sheet = util.getSheet(address)
address.Sheet = sheet
util.sheetMove(address, -1)

Move more one sheet

address.Doc = ThisComponent
address.Sheet = Array ("Sheet2", "Sheet4")
util.sheetMove(address, -1)

Sort sheets¶

Sort sheets by name.

util.sheetSort(ThisComponent, True)

Sort in reverse.

util.sheetSort(ThisComponent, False)

Copy sheet¶

Important

if you not use rename argument, you first valid if new name sheet not exists with method: hasByName

Copy sheet by name to last pos.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "Sheet2"
util.sheetCopy(address, "NewName", -1, False)

Copy sheet by index to pos.

address.Doc = ThisComponent
address.Sheet = 0
util.sheetCopy(address, "OtherName", -1, False)

Copy sheet by index to pos and rename if exists

address.Doc = ThisComponent
address.Sheet = 0
util.sheetCopy(address, "NameX", -1, True)

Copy more one sheet, rename is ommited, always is true

address.Doc = ThisComponent
address.Sheet = Array("Sheet1", "Sheet2")
util.sheetCopy(address, "NameY", -1, True)

Copy all sheets, rename is ommited, always is true

address.Doc = ThisComponent
address.Sheet = ""
util.sheetCopy(address, "NameNext", -1, False)

Copy sheet to document¶

Important

The origin document must be saved before copy sheet

sheetCopyToDoc(SOURCE, TARGET, POSITION, RENAME, BY_VALUE)

Source and targe are struct org.universolibre.EasyDev.CellRangeAddress

SOURCE: Document and sheet origin
TARGET: Target document
POSITION: Target position for example first position (0) or last position (-1)
RENAME: If exists sheet name in target, rename sheet origin
BY_VALUE: False, copy by formula, True, copy by values.

Copy all sheets from source to target document.

util = createUnoService("org.universolibre.EasyDev")
source = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")
target = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

doc = ThisComponent
source.Doc = doc

new_doc = util.newDoc("")
target.Doc = new_doc

util.sheetCopyToDoc(source, target, -1, True, False)

Copy sheet by name.

doc = ThisComponent
source.Doc = doc
source.Sheet = "Source"
new_doc = util.newDoc("")
target.Doc = new_doc

util.sheetCopyToDoc(source, target, -1, True, False)

Copy sheet by index

doc = ThisComponent
source.Doc = doc
source.Sheet = 1
new_doc = util.newDoc("")
target.Doc = new_doc

util.sheetCopyToDoc(source, target, -1, True, False)

Copy more one sheet

doc = ThisComponent
source.Doc = doc
source.Sheet = Array(0, "Source")
new_doc = util.newDoc("")
target.Doc = new_doc

util.sheetCopyToDoc(source, target, -1, True, False)

Copy sheet by value

doc = ThisComponent
source.Doc = doc
source.Sheet = "Source"
new_doc = util.newDoc("")
target.Doc = new_doc

util.sheetCopyToDoc(source, target, -1, True, True)

Rename sheet¶

Rename sheet by name.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "Sheet2"
util.sheetName(address, "Two")

Rename sheet by index.

address.Doc = ThisComponent
address.Sheet = 0
util.sheetName(address, "First")

Rename more one sheet.

address.Doc = ThisComponent
address.Sheet = Array(0, "Sheet3")
util.sheetName(address, "NewName")

Show or hidden sheet¶

Important

If you try hide all sheets, not get error, but always at least one sheet will be visible

Hidden sheet by name.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "Sheet2"
util.sheetVisible(address, False)

Hidden first sheet.

address.Doc = ThisComponent
address.Sheet = 0
util.sheetVisible(address, False)

Hidden last sheet.

address.Doc = ThisComponent
address.Sheet = -1
util.sheetVisible(address, False)

Hidden all sheets, except one

sheets = util.getSheetsNames(ThisComponent)
'First show sheet
address.Doc = ThisComponent
address.Sheet = sheets(0)
util.sheetVisible(address, True)
'Remove sheet from array
sheets = util.delete(sheets, 0)
'And hide the rest of sheets
address.Sheet = sheets
util.sheetVisible(address, False)

Show al sheets

sheets = util.getSheetsNames(ThisComponent)
address.Doc = ThisComponent
address.Sheet = sheets
util.sheetVisible(address, True)

Sheet password¶

Set password by name.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "Sheet2"
util.sheetPassword(address, "letmein", False)

Set password by index.

address.Doc = ThisComponent
address.Sheet = -1
util.sheetPassword(address, "letmein", False)

Set password all sheet.

address.Doc = ThisComponent
address.Sheet = ""
util.sheetPassword(address, "letmein", False)

Remove password all sheet.

address.Doc = ThisComponent
address.Sheet = ""
util.sheetPassword(address, "letmein", True)

Cells¶

Get active cell, always back only one cell

Sub getCellDoc()
    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    address.Current = True
    cell = util.getCell(address)
    msg = util.format("{} {}", Array(cell.ImplementationName, cell.AbsoluteName))
    util.msgbox(msg)
End Sub

Get active cell from document by title name, document must be open

address.Doc = "test.ods"    'Title doc
address.Current = True
cell = util.getCell(address)
msg = util.format("{} {}", Array(cell.ImplementationName, cell.AbsoluteName))
util.msgbox(msg)

Get active cell from object doc

doc = util.getDoc("")
address.Doc = doc
address.Current = True
cell = util.getCell(address)
msg = util.format("{} {}", Array(cell.ImplementationName, cell.AbsoluteName))
util.msgbox(msg)

Get cell from object doc, sheet by name, cell by name

doc = util.getDoc("")
address.Doc = doc
address.Sheet = "Sheet2"
address.Name = "B5"
cell = util.getCell(address)
msg = util.format("{} {}", Array(cell.ImplementationName, cell.AbsoluteName))
util.msgbox(msg)

Get cell from object doc, sheet by name, cell by col, row

doc = util.getDoc("")
address.Doc = doc
address.Sheet = "Sheet2"
address.Name = ""
address.Col = 1
address.Row = 4
cell = util.getCell(address)
msg = util.format("{} {}", Array(cell.ImplementationName, cell.AbsoluteName))
util.msgbox(msg)

Get cell from object sheet, object doc is ommited

sheet = doc.getCurrentController().getActiveSheet()
address.Sheet = sheet
address.Name = "D5"
cell = util.getCell(address)
msg = util.format("{} {}", Array(cell.ImplementationName, cell.AbsoluteName))
util.msgbox(msg)

Ranges¶

Get active range

Sub getRangeDoc()
    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    address.Current = True
    range = util.getRange(address)
    msg = util.format("{} {}", Array(range.ImplementationName, range.AbsoluteName))
    util.msgbox(msg)
End Sub

Get active range from document by title name, document must be open

address.Doc = "test.ods"    'Title doc
address.Current = True
range = util.getRange(address)
msg = util.format("{} {}", Array(range.ImplementationName, range.AbsoluteName))
util.msgbox(msg)

Get active range from object doc

doc = util.getDoc("")
address.Doc = doc
address.Current = True
range = util.getRange(address)
msg = util.format("{} {}", Array(range.ImplementationName, range.AbsoluteName))
util.msgbox(msg)

Get range from object doc, sheet by name, range by name

doc = util.getDoc("")
address.Doc = doc
address.Sheet = "Sheet2"
address.Name = "B5:C10"
range = util.getRange(address)
msg = util.format("{} {}", Array(range.ImplementationName, range.AbsoluteName))
util.msgbox(msg)

Get range from object doc, sheet by name, range by position

address.Sheet = "Sheet2"
address.Name = ""
address.Col = 1
address.Row = 4
address.EndCol = 3
address.EndRow = 9
range = util.getRange(address)
msg = util.format("{} {}", Array(range.ImplementationName, range.AbsoluteName))
util.msgbox(msg)

Get range from object sheet, object doc is ommited.

sheet = doc.getCurrentController().getActiveSheet()
address.Sheet = sheet
address.Name = "D5:E10"
range = util.getRange(address)
msg = util.format("{} {}", Array(range.ImplementationName, range.AbsoluteName))
util.msgbox(msg)

Add ranges into container.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

address.Doc = ThisComponent
address.Sheet = "Sheet1"
address.Name = "A1:B2"
range = util.getRange(address)
'Create container, first argument is a document
container = util.addRanges(ThisComponent, range)
MsgBox container.getRangeAddressesAsString()

'Add range to container
address.Name = "A4:B5"
range = util.getRange(address)
'First argument is a container, previously created
container = util.addRanges(container, range)
MsgBox container.getRangeAddressesAsString()

Initial container from more one cell range

address.Doc = ThisComponent
address.Sheet = "Sheet1"
address.Name = "B1:C2"
r1 = util.getRange(address)
address.Name = "B4:C4"
r2 = util.getRange(address)
address.Name = "D6:E6"
r3 = util.getRange(address)
ranges = Array(r1, r2, r3)

'Create container, first argument is a document
container = util.addRanges(ThisComponent, ranges)
MsgBox container.getRangeAddressesAsString()

Remove ranges from container.

r = util.removeRanges(container, r2)
MsgBox container.getRangeAddressesAsString()

Get all columns from cell range, get a container.

doc = ThisComponent

address.Doc = doc
address.Sheet = "Sheet1"
address.Name = "B1:E1"
range = util.getRange(address)

columns = util.getColumns(ThisComponent, range)

util.selectRange(doc, columns)

Get all rows from cell range, get a container.

doc = ThisComponent

address.Doc = doc
address.Sheet = "Sheet1"
address.Name = "A5:A10"
range = util.getRange(address)

rows = util.getRows(ThisComponent, range)

util.selectRange(doc, rows)

Select range¶

Sub SelectRange()
    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    doc = util.getDoc("")
    address.Doc = doc
    address.Sheet = "Sheet2"
    address.Name = "B5:C10"
    range = util.getRange(address)

    'Select
    util.selectRange(doc, range)

End Sub

Current region¶

Sub getCurrentRegion()

    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    address.Current = True
    cell = util.getCell(address)
    msg = util.format("{} {}", Array(cell.ImplementationName, cell.AbsoluteName))
    util.msgbox(msg)

End Sub

Only one cell into current region is necesary, get cursor

cursor = util.getCurrentRegion(cell, True)
msg = util.format("{} {}", Array(cursor.ImplementationName, cursor.AbsoluteName))
util.msgbox(msg)

Only one cell into current region is necesary, get range

range = util.getCurrentRegion(cell, False)
msg = util.format("{} {}", Array(range.ImplementationName, range.AbsoluteName))
util.msgbox(msg)

Get empty cells¶

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

'Get active cell, always get one cell
address.Current = True
cell = util.getCell(address)

ranges = util.getEmpty(cell)
util.selectRange(ThisComponent, ranges)

Get visible cells¶

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

'Get active cell, always get one cell
address.Current = True
cell = util.getCell(address)

ranges = util.getVisible(cell)
util.selectRange(ThisComponent, ranges)

Last row¶

Get last row into current region.

Sub getLastRow()
    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    address.Current = True
    cell = util.getCell(address)
    row = util.getLastRow(cell)
    util.msgbox(row)
End Sub

Next value¶

In range values. Get nex value in relative column (0), or (1), etc.

Sub getNextID()
    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    address.Current = True
    cell = util.getCell(address)
    value = util.getNextID(cell, 0)
    util.msgbox(value)
End Sub

Cell value¶

Automatically detect type: string, value or formula.

Sub CellValue()
    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    address.Current = True
    cell = util.getCell(address)

    value = "String"
    util.setValue(cell, value)
    value = util.getValue(cell)
    util.msgbox(value)

    value = 12345
    util.setValue(cell, value)
    value = util.getValue(cell)
    util.msgbox(value)

    value = "=SUM(A1:C1)"
    util.setValue(cell, value)
    value = util.getValue(cell)
    util.msgbox(value)

End Sub

Set data¶

Automatically calculate width and height of data size.

Sub SetData()
    util = createUnoService("org.universolibre.EasyDev")
    address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

    'Get active cell, always get one cell
    address.Current = True
    cell = util.getCell(address)

    data = Array( _
        Array(1, "Uno", "Tres"), _
        Array(2, "Dos", "Cuatro"), _
        Array(3, "Tres", "Cinco"), _
    )
    'Automatically calculate width and height of data size
    util.setData(cell, data)

End Sub

Get data¶

Get data array from cell, automatically get current region. Get hidden cells inclusive.

util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")

'Get active cell, always get one cell
address.Current = True
cell = util.getCell(address)

data = util.getData(cell, False)
util.msgbox(data)

Get data array, only visible cells.

data = util.getData(cell, True)
util.msgbox(data)

Calc¶

Get sheet¶

Set active sheet¶

Insert sheets¶

Insert sheet from document¶

Remove sheet¶

Move sheet¶

Sort sheets¶

Copy sheet¶

Copy sheet to document¶

Rename sheet¶

Show or hidden sheet¶

Sheet password¶

Cells¶

Ranges¶

Select range¶

Current region¶

Get empty cells¶

Get visible cells¶

Last row¶

Next value¶

Cell value¶

Set data¶

Get data¶

Table Of Contents

Related Topics

This Page