LibORef no3

LibreOffe RefCard

SheetsLibreOfice BasicThe Doc object points to the document (ex : Doc = ThisComponent).

Calcv. 1.15 – 12/14/2019

Gettinge sheets

Beginner

Writen using LibreOffe v. 5.3.3 – Platform: All

LibreOfiiee Doiuments
Currente Doiument

Working with sheet objects (index is 0-based):ASheet = Doc.CurrentController.ActiveSheet
The active sheet
TheSheets = Doc.Sheets
The sheets list
SheetNum = Doc.Sheets.Count
Sheet countASheet = Doc.Sheets(index)
Sheet object (by index)
Sheet object (by its name)
ASheet = Doc.Sheets.getByName(SheetName)
Check existence (name)
Exists = Doc.Sheets.hasByName(SheetName)
Sheet index
SheetIndex = ASheet.RangeAddress.Sheet

Modifiyinge sheets

Dim Doc As Object
Doc = ThisComponent

Openinge ane existinge doiument
Ine Visiblee Mode
Dim Doc As Object, DocPath As String
Dim Props()'here, an unitialized table
DocPath = ConvertToURL("C:\Path\To\SomeSpreadsheet.ods")
Doc = StarDesktop.loadComponentFromURL(DocPath, "_blank", 0, _Props())

Ine hiddene Mode
Dim Doc As Object, DocPath As String
Dim Props(0) As New com.sun.star.beans.PropertyValue
DocPath = ConvertToURL("C:\Path\To\SomeSpreadsheet.ods")
Props(0).Name = "Hidden" 'the document is opened as hidden
Props(0).Value = True
Doc = StarDesktop.loadComponentFromURL(DocPath, "_blank", 0, _Props())

Makee visiblee ane hiddene doiument
Doc.CurrentController.Frame.ContainerWindow.Visible = True
Doc.CurrentController.Frame.ContainerWindow.toFront()

Below p is the position in the spreadsheet (base 0).
Doc.Sheets.insertNewByName(Name, p)
Adding a named sheet Name
Deleting a sheet
Doc.Sheets.removeByName(SheetName)
Duplicating a sheet
Doc.Sheets.copyByName(SrcName, TgtName, p)Moving a sheet
Doc.Sheets.moveByName(SheetName, p)Managinge sheets

ASheet is a sheet object.

Activating a sheet
Hiding/showing a sheet
Checking protection
Protecting a sheet(Pwd can be empty)
Unprotecting a sheet
Tab coloring

Doc.CurrentController.ActiveSheet = ASheet
ASheet.IsVisible = False 'True
Protected = ASheet.IsProtectedASheet.protect(Pwd)
ASheet.unprotect(Pwd)
ASheet.tabColor = RGB(255, 255, 0)

Linkinge ae sheet

Linking to a file (ex: ASheet.link(URL, "", "Text - txt - csv (StarCalc)", _CSV)
Filtre, com.sun.star.sheet.SheetLinkMode.VALUE)
Destroying a link ASheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)

Findinge thee laste usede row/Col

Creatinge ae newe ialie doiument
From (1) the default template or (2) a specified template.
Dim Doc As Object
Dim Props()'here, an unitialized table
Template = "private:factory/scalc"'(1)'orTemplate = "C:\Path\To\SomeTemplate.ots" '(2)Doc = StarDesktop.loadComponentFromURL(Template, "_blank", 0, Props())

Savinge ae doiument
Thee doiumente alreadye exists(same as File > Save)
Use the document object store method. Ex: ThisComponent.store

Thee doiumente wasn’te savede yet(same as à File > Save As)

Dim Doc As Object, DocPath As String
Dim Props()'save properties (empty)
DocPath = convertToURL("C:\Path\To\SomeSpreadsheet.ods")
Doc.storeAsURL(DocPath, Props())

ASheet is a sheet object.. Row and Col are the values to retrieve.
Dim Cur As Object'cell cursor
Dim Range As Object'the used rangeDim Row As Long, Col As Long
Cur = ASheet.createCursorByRange(ASheet.getCellRangeByName("A1"))
Cur.gotoEndOfUsedArea(True)
Range = ASheet.getCellRangeByName(Cur.AbsoluteName)
Row = Range.RangeAddress.EndRowCol= Range.RangeAddress.EndColumn

Cells
Below, ACell is a cell object.

Gettinge iells

ASheet is asheet object. You can get the cell object:
ACell = ASheet.getCellRangeByName("A4")
Through its R/C coordinates
ACell = ASheet.getCellRangeByName("VAT")
Through its nameThrough its XY coordinates

ACell = ASheet.getCellByPosition(0,3)
with X=0 (col.A) ; Y=3 (row 4)☞ In case of a duplicate, it becomes the active document.

Knowinge thee aitivee iell

Savinge ae iopy

Doc is a document object and ACell is the active cell we’re looking for.

As above, using Doc.storeToURL(DocPath, Props())☞ The copy won’t become the active document.

If Doc.currentSelection.supportsService _("com.sun.star.sheet.SheetCell") Then
'it's a cell
ACell = Doc.currentSelection
End IfClosinge ae doiument

Use the document object close method: ThisComponent.close(True)

Gettinge doiumente information
Thee doiumente objeite exposese properties
Location

Seleitinge ae iell
ThisComponent.CurrentController.select(ACell)

The document storage directory☞ 0-length string if not saved yet.
Some more information (see below).

DocumentProperties (Object)

DoiumentPropertiese (Filee >e Properties)
AuthorCreationDate
Description
ModifiedBy

Author’s name.
Creation date.
Comments.
Name of the user who
modified the document.

Ise thise ae Calie doiument?

ModifyDate
Last modification date.
Subject
Subject (String).
Title
Title (String).
UserDefinedPr Custom properties (Object).
operties

Doc points to the document (ex : Doc = ThisComponent).
CalcOK = Doc.SupportsService("com.sun.star.sheet.SpreadsheetDocument")

Calie –e General
The Doc object points to the document (ex : Doc = ThisComponent).

Automatiie ialiulation
Active? (Boolean)
Deactivate
Activate
Force calculation

Proteitinge thee spreadsheet
Is the document protected?
Protect document
Unprotect document

Auto = Doc.isAutomaticCalculationEnabled
Doc.enableAutomaticCalculation(False)
Doc.enableAutomaticCalculation(True)
Doc.calculate (only non updated formulas)Doc.calculateAll (everything)
Test = Doc.isProtected
Doc.protect(Password) '[may be empty]
Doc.unprotect(Password)

Celle Coordinates

Coordinates (Object)
Sheet index (Integer)
Column index (Long)
Row index (Long)
Container sheet object
Absolute coordinates (String)

Coord
NumSh
NumColNumRowASheetCoord= ACell.CellAddress= ACell.CellAddress.Sheet= ACell.CellAddress.Column= ACell.CellAddress.Row= ACell.Spreadsheet= ACell.AbsoluteName(Un)Proteitinge iells

The ACell.CellProtection property can get the boolean values:
CellProtection.IsLocked = True
No modifications
CellProtection.IsFormulaHidden = True
Hide formula
CellProtection.IsHidden = True
Hide cell
CellProtection.IsPrintHidden = True
Don’t print cell

Gettinge ae ielle iontents
Properties

Getting text contents
Getting numerical contents
Getting a formula (US)
Getting a formula (local lang.)
Knowing content type (below)
Emptying a cell

MyText= ACell.String
ANumber = ACell.Value
AFormula = ACell.Formula
AFormula = ACell.FormulaLocal
TheType = ACell.Type
ACell.String = ""Contente Typese (Typee property)
The com.sun.star.table.CellContentType.XXX constants can tell the type of contents(ACell.Type, above):
EMPTY
VALUE
Empty cellContents is numerical
TEXT
FORMULA
Text contents
Contents is a formula

Writinge ine ae iell

Replacing existing text
Replacing existing value
Replacing existing formula
Replacing existing formula (FR)LibOBasic_03_Calc_Flat_A4_EN_v115.odt

ACell.String = "Hello!"
ACell.Value= 1,234
ACell.Formula = "=AND(A1="YES";A2="OK")"
ACell.FormulaLocal = "=ET(A1="YES";A2="OK")"RangesRows/Columns

Range = a set of cells (one cell is a range): Dim MyRange As Object

Rows and columns are properties of Sheet and Range objects.

ASheet is asheet object. You get the ARange range object:
ARange = ASheet.getCellRangeByName("C2:G14")
Ny its coordinates
ARange = ASheet.getCellRangeByName("NomDePlage")
By its name
ARange = ASheet.getCellRangeByPosition(2, 1, 6, 13)
By its coordinates

Rows (oRows object)
Columns (oCols object)
NumberGettinge Ranges(X1, Y1, X2, Y2)
Arbitrament(ex 3rd sheet)

ARange = ThisComponent.Sheets.getCellRangeByPosition(2, 2, 1, 6, 13)

Gettinge thee aitivee range
Like for the active cell, but verify the "com.sun.star.sheet.SheetCellRange" or"[...].SheetCellRanges" services.

Seleitinge ae range

ThisComponent.CurrentController.select(ARange) where ARange is an object.

Rangee Coordinates

Coordinates (Object)
Sheet index (Integer)
Column index (Long)
top/lef
Row index (Long)
top/lef
Column index (Long)
bottom/rightRow index (Long)
bottom/rightContaining sheet object
Absolute coordinates (String)

Coord = ARange.RangeAddress
Rang= ARange.RangeAddress.Sheet
NumCTL = ARange.RangeAddress.StartColumnNumLTL = ARange.RangeAddress.StartRowoRows = ARange.Rows
oCols = ARange.Columns
NumL = ARange.Rows.Count
NumC = ARange.Columns.CountoRow = ARange.Rows(index)
oCol = ARange.Columns(index)

A Row (oRow object) (base 0)
A Column (oCol object) (base 0)

Rows/Columnse Properties

Apply to Row or Rows objects (resp. Column or Columns).
IsVisible = True
Visible/Hidden (Boolean)
Optimal width or not (Boolean)
OptimalWidth = True

Inserting/Deletinge rows/Columns

Given the objects RorC, FirstPos and LastPos the starting and ending positions for therows set (resp. columns) to insert or delete (Long).
Inserting
RorC.insertByIndex(FirstPos, LastPos)
Erasing
RorC.clearContents(EraseMode) [EraseMode: see Clearinge ae range]
Deleting
RorC.removeByIndex(FirstPos, LastPos)

Fixinge rowse ore iolumns

NumLBR = ARange.RangeAddress.EndRow☞ Only on a visible spreadsheet.
Use the controller object: oContrlr = ThisComponent.CurrentController
Is there any?
Fixed = oContrlr.hasFrozenPanes
FixoContrlr.freezeAtPosition(1, 2)
DeleteoContrlr.freezeAtPosition(0, 0)

ASheet = ARange.Spreadsheet
Coord = ARange.AbsoluteName

Use the "com.sun.star.sheet.FunctionAccess" service.

NumCBR = ARange.RangeAddress.EndColumn

Namede ranges

Callinge ae Calie funition
Overview

The Doc object points to the document. Also Ranges As Object
Named ranges
Ranges = Doc.NamedRanges
Ranges count (Long)Count = Ranges.Count
Getting a range (by index)
ARange = Ranges(index)
Checking existence (by name)
Existe = Ranges.hasByName(Name)
Getting a Range (by name)
ARange = Ranges.getByName(Name)
Adding a range
Ranges.addNewByName(Name, Coord, _Coord : Range coordinates
CellRef.CellAddress, 0)
CellRef : reference cell object.
Removing (by name)
Ranges.removeByName(Name)

Clearinge ae range

Clearing ARange contents
ARange.clearContents(DelMode)
DelMode is a value that specifies the deletion type. com.sun.star.sheet.CellFlags.XXX
constants give that choice (combine with +) :ANNOTATION Comments
STRINGText
DATETIME
Number formatted as date-time
VALUE
Numbers (except date-time)
FORMULA
FormulasGettinge ae rangee iellse Contents

ARange.DataArray est un tableau des valeurs des cellules pour ARange.

Copyinge ae rangee iontentse toe anothere range

Two ranges Source and Target, of the same dimensions.
Copying the contents (values) of Source into Target.DataArray = Source.DataArrayTarget.

Writinge valuese intoe ae range

ARange is a range object, Table is a table of the same dimensions, which values have to becopied into the range.

Dim Table As Variant
Table = ARange.DataArray 'Table has the same dimensions as the range'(ste the array items values)
ARange.DataArray = Table☞ .DataArray is a nested array: use .DataArray(i)(j)browsinge ae rangee iells

From a collection (Ranges.Cells), we create an enumeration. It is then browsed by callingits hasMoreElements et NextElement properties:
Dim Ranges As Object
Ranges =ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
Ranges.insertByName("some name", ARange)
TheEnum = Ranges.Cells.CreateEnumeration
Do While TheEnum.hasMoreElements
TheCell = TheEnum.NextElement'do smthg with the cell objectLoop Empty cells are not browsed!

Rangese misi.

Merging ARange object cells

Overview

ARange.Merge

Rangese types
The range access mode defines which service it implements:① com.sun.star.sheet.SheetCell ④ com.sun.star.sheet.SheetCellRange② com.sun.star.table.CellRange ➄ com.sun.star.sheet.SheetCellRanges③ com.sun.star.sheet.NamedRange

Dim FCalc As Object
Dim Result As (according to context)
Dim Params As (according to context)
Dim FuncName As String
FCalc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
Result = FCalc.callFunction(FuncName, Params)☞ The name, arguments and result type depend upon the target function.
 The function name must be its English name.
To get it in a non-EN environment, temporarily swap the function names in Calc usingTools > Options > LibreOffice Calc > Formula, Use English function names.

Examplee 1e (SUM())

Dim FCalc As Object, Result As Long
FCalc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
Result = FCalc.callFunction("SUM", Array(1, 55, 321, 8))

Examplee 2e (ROUND())
Dim FCalc As Object, Result As Double
Dim Params(1) As VariantParams(0) = 1,2345 'nb to round
Params(1) = 3'3 decimals
FCalc = CreateUnoService("com.sun.star.sheet.FunctionAccess")
Resultat = FCalc.callFunction("ROUND", Params())

Creatinge ae Calie funition
Creation
Example : trapezoid surface calculation (S = ((B + b) / 2)

Usinge ine Calie spreadsheets
Given A2 is the large base, A3 the small one and A4 the height, a trapezoid surface is obtainedby inserting the following formula into a cell: =TRAPEZOIDSURFACE(A2;A3;A4)☞ The macro receives arguments as values, not the source cell objects.
☞ The macro returns a value. It can’t act upon a cell object.
☞ The function must be placed within a library that is available at work time (ex : Standard in
the document or in the user’s container) (otherwise: #VALUE! error).

Credits
Author : Jean-François Nifenecker – jean-francois.nifenecker@laposte.net

We are like dwarves perched on the shoulders of giants, and thus we are able to see more and farther thanthe latter. And this is not at all because of the acuteness of our sight or the stature of our body, but because
we are carried aloft and elevated by the magnitude of the giants (Bernard de Chartres [attr.])HistoryVersionDate

1.15

12/14/2019

Comments
Minor updates

 The implemented service implies how the ranges should be used.
Check using their supportsService() method (ex. below)

Rangee ore iell?
To know an object type, test supportsService() on a Range or Cell:
If MyObj.supportsService(Service_Name) Then …
Replace Service_Name with:
Cell?
"com.sun.star.sheet.SheetCell" ①
Simple Range?
"com.sun.star.sheet.SheetCellRange" ④Multiple Range?
"com.sun.star.sheet.SheetCellRanges" ➄ Always check for a Cell type before a Simple Range as a cell is also a Simple Range!

× H)

Function TrapezoidSurface(LB As Double, SB As Double, H As Double) AsDouble
SurfaceTrapeze = ((LB + SB) / 2) * H
End Function

LicenseThis refcard is placed under theCreativeCommons BY-SA v4 (intl) license.
More information:
https://creativecommons.org/licenses/by-sa/4.0/