Calc - LibreOffice

1 AMLibO no3. LibreOffe Referenfe Card Sheets (sheets). LibreOfice Basic The Doc object points to the document (ex: Doc=ThisComponent). Access to sheets Calc Work with Sheets objects: Active sheet MySheet = Beginner Sheet list AllSheets = v. January 13, 2018 Number of sheets NumberSheets = Sheet object (by index [base MySheet = (index). Writen with LibreOffe v. Platform : All 0]). Sheet object (by name) MySheet = ("SheetName"). LibreOffice Documents Check existence (name) Exist = ("SheetName"). Sheet index Index = Current document Modify sheets Dim Doc As Object Doc = ThisComponent Add a sheet named Name at (Name, p). Open another existing document position p(base 0). Delete a sheet ("SheetName"). Visible mode Duplicate a sheet ("SourceName","Target- Dim Doc As Object to the position p (base 0) Name", p).

2 Dim PathDoc As String Dim Props() 'here, this table is not initialized Move sheet (SheetName, p). PathDoc = ConvertToURL("C:\Path\To\ ") to the position p (base 0). Doc = (PathDoc, "_blank", 0, _ Manage sheets Props()). MySheet is a sheet object. Invisible mode Activate sheet = MySheet Dim Doc As Object Protect sheet (password). Dim PathDoc As String (password can be empty). Dim Props(0) As New Unprotect sheet (password). PathDoc = ConvertToURL("C:\Path\To\ ") Tab color = RGB(255, 255, 0). Props(0).Name = "Hidden" 'the document will open hidden". Props(0).Value = True Link a sheet Doc = (PathDoc, "_blank", 0, _ Link to a file (ex: (URL, "", "Text - txt - csv (StarCalc)", _. Props()). CSV) Filter, ). Turn visible a posteriori Break a link ( = True ).

3 () Find last row/column used Create a new Calc document MySheet is the sheet object to explore. Row and Col are information to fetch. From (1) the default template or (2) a specific template. Dim Cur As Object ' cursor on the cell Dim Range As Object ' the used range Dim Doc As Object Dim Row As Long Dim Props() 'here, this table is not initialized Dim Col As Long Model = "private:factory/scalc" '(1) Cur = ( ("A1")). 'or (True). Model = "C:\Path\To\ " '(2) Range = ( ). Doc = (Model, "_blank", 0, Props()) Row = Col = Save a document The document already exist Cells (cells). (equivalent to File > Save ) Below Cel is an cell object. Use the method store from the document object. Ex : Access to cells The document was not yet saved MySheet is a sheet object.

4 Access to cell object: (equivalent to File > Save as ) Cel = ("A4"). By cell default notation Dim Doc As Object 'the object document to store By name Cel = ("TVA"). Dim PathDoc As String 'the path for saving By coordinates X and Y Cel = (0,3). Dim Props() 'the saving properties. (empty) Wih X=0 ( ) ; Y=3 ( ). PathDoc = ConvertToURL("C:\Path\To\ "). (PathDoc, Props()) Access to active cell If a copy, it turns to active document Doc is an document object and ActiveCel the active cell object. Save a copy If _. Like above but with (PathDoc, Props()) (" ") Then 'It's a cell The copy does not become the active document. ActiveCel = Close a document End If Use the method close from the document object: (True) Select a cell Document information (Cel).

5 The document object expose properties Cell coordinates Location The folder of the document. Coordinates (Object) Coord = Empty string is not yet saved Sheet index (Integer) NumS = DocumentProperties (Object) Additional properties (below). Columns index (Long) NumC = DocumentProperties Row index (Long) NumL = Author Author's name ModifyDate Last modification date Sheet container object MySheet = CreationDate Creation date. Subject Document subject (string). Absolute coordinates (String) Coord = Description Document description Title Document title (un)protect cells ModifiedBy User name who modified the UserDefined- Custom properties can take boolean values: document. Properties (Object). Prevent modification = True Is it a Calc document? Hide cell formula = True The Doc object points to the document (ex: Doc=ThisComponent).

6 Hide cell = True CalcOK = (" ") Don't print cell = True Calc General functionalities Access cell contents Properties The Doc object points to the document (ex: Doc=ThisComponent). Read text contents MyText = Automatic calculation Read numeric contents aNumeber = Active? (Boolean) Auto = Read cell formula (en-US names) TheFormula = Disable (False) Read cell formula (localized names) LaFormule = Enable (True) Cell type TheType = Force recalculation (only for formulas not updated) Empty a cell = "". (all formulas) Contents type ( Type property). Protect document The constants represent the cell informa- Is document protected? Test = tion type ( , above) : Protect document (password) [password can be EMPTY Empty cell VALUE Numerical value empty] TEXT Text contents FORMULA Formula contents Unprotect document (password).

7 Write in a cell Replace existing text = "Hello !". Replace an existing value = Replace an existing formula = "=AND(A1="YES";A2="OK")". (localized). Replace an existing formula = "=ET(A1="OUI";A2="OK")". (localized). Ranges (ranges) Rows/Columns (rows/columns). Range = set of cells, (including a single one): Dim MyRange As Object Rows and columns are Sheet and Range objects properties. Access to ranges General MySheet is a sheet object. Get a range object Ran>: Rows (TheRows object) TheRows = By cell default notation Ran = ("C2:G14") Columns (TheCols object) TheCols = By name Ran = ("RangeName") Counting NbL = By coordinates Ran = (2, 1, 6, 13) NbC = (X1, Y1, X2, Y2) A row (TheRow object (base 0) TheRow = (index). Randomly Ran = (2, A column (TheCol object (base 0) TheCol = (index).)))

8 (ex third sheet) 2, 1, 6, 13). Row/Columns properties Active range Applies to Row or Rows (resp. Column or Columns). Like active cell, but check " " or "[..].Sheet- Visible / Hidden (Boolean) IsVisible = True CellRanges". Optimal width (Boolean) OptimalWidth = True Range selection Insert/delete rows/columns (MyRange) where MyRange is an objetc. Define object RorC, and FirstPos and LastPos the positions of the begining and end of Range coordinates the row set (resp. columns) to add/delete (Long). Coordinates (Object) Coord = Insert (FirstPos, LastPos). Sheet index (Integer) Ran = Delete (FirstPos, LastPos). Column rank (Long) NumCHG = Freeze row/columns top/left corner Use the Controller object: MyController = Row rank (Long) NumLHG = Is there one?

9 Freeze = top/left corner Freeze (1, 2). Column rank (Long) NumCBD = Delete (0, 0). bottom/right corner Row rank (Long) NumLBD = Call a Calc function bottom/right corner MySheet = Use service " ". Sheet container object Absolute coordinates (String) Coord = Usage Named ranges Dim FCalc As Object Dim Result As (context dependent). The Doc object points to the document. With Dim TheRanges As Object Dim Params As (context dependent). Named ranges TheRanges = Dim FunctionName As String Number (Long) Nb = FCalc = CreateUnoService(" "). Get a range (by index) MyRange = TheRanges(index) Results = (FunctionName, Params). Check existence (name) Exist = ("RangeName") Function name, parameters and type of results depends on the selected function Get range (by name) MyRange = ("Range- The function name must be its English name.)

10 Name") To get the function English name, switch temporarily to English Calc function names Add ("Rangename", Co- display, at Tools > Options > LibreOffice Calc > Formula, Use English function names. Coord : range coordinates ord, _ Example 1 (function SUM()). CellRef : reference cell object , 0) Dim FCalc As Object Delete (by name) ("RangeName") Dim Results As Long FCalc = CreateUnoService(" "). Erase a range Results = ("SUM", Array(1, 55, 321, 8)). Erase MyRange contents (EraseMode). EraseMode is a value that define the type of cleaning. Use Example 2 (function ROUND()). and combine them with +) : Dim FCalc As Object ANNOTATION Comments STRING Text Dim Results As Double DATETIME Date/time formatted numbers VALUE Numbers (except date/time) Dim Params(1) As Variant Params(0) = 1,2345 'number to round FORMULA Formulae Params(1) = 3 '3 places Get cell contents in a range FCalc = CreateUnoService(" ").