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).

2 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). 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).

3 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 ). () 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.

4 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.

5 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.

6 ActiveCel = Close a document End If Use the method close from the document object: (True) Select a cell Document information (Cel). 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.

7 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). 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).

8 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).

9 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).)

10 Randomly Ran = (2, A column (TheCol object (base 0) TheCol = (index). (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).)