Spreadsheet applications like Calc consists of workbooks, worksheets and individual Cells. Many times it is required to process those using Macro to automate various tasks.
In this tutorial, we will demonstrate basic processing of worksheets, cells which is a foundation of many complex macros.
Exercise Statement
We will read a Calc spreadsheet with 3 sheets, one after another and read the contents of it. After read, we will show the formatted read contents in a message window.
Sheet1, Sheet2 and Sheet3 contains below data:
Code Details
First, we will declare 3 Objects to define the Calc workbook, collection of sheets and a cell.
dim my_doc as object
Dim my_sheets as object
Dim my_cell as object
Set my_doc using ThisComponent which refers to the current Calc workbook is open. Once this is done, the Sheets collection is assigned to my_sheets object to access all the sheets of the workbook. All the sheets is assigned to object my_sheets as an array. To access them, we have to use subscript like Sheets(0), Sheets(1) so on. Note that subscript starts at zero.
my_doc = ThisComponent
my_sheets = my_doc.Sheets
sheet_count = my_sheets.Count
Once we get a handle of a sheet using Sheets(subscript), we can access each individual cells using getCellByPosition method. Typical way of accessing a cell as below.
my_cell = ThisComponent.Sheets(i).getCellByPosition(col,row)
Note that first arguments of getCellByPosition is column and then row.
After we get the cell object my_cell is set, we have to access its contents. LibreOffice has a different take on it. Each individual cell is defined by its content type. If you put a number on a cell it becomes number type and if you put chracters on it, it becomes of type text. LibreOffice provides a list of enums for Cell.Type as below:
com.sun.star.table.CellContentType.VALUE ' Used for cells containing numbers
com.sun.star.table.CellContentType.TEXT ' Used for cells containing characters
com.sun.star.table.CellContentType.EMPTY ' Used for empty cells
com.sun.star.table.CellContentType.FORMULA ' Used for cells containing formula
To access the cell values in this example, we would use below snippet.
Select Case my_cell.Type
Case com.sun.star.table.CellContentType.VALUE
cell_value = my_cell.Value
Case com.sun.star.table.CellContentType.TEXT
cell_value = my_cell.String
End Select
Run
I am going to concatenate each cell values in a formatted way and show in a message box. After the run, all cell values from all sheets shown as below.
Refer ‘Complete Code’ section below.
Complete Code
Sub processing_sheets_cells
dim my_doc as object
Dim my_sheets as object
Dim my_cell as object
Dim sheet_count, i, row, col, cell_value, str
my_doc = ThisComponent
my_sheets = my_doc.Sheets
sheet_count = my_sheets.Count
for i = 0 to sheet_count - 1
str = str & chr(13) & "--------" & chr(13)
for row=1 to 4
for col=0 to 1
my_cell = ThisComponent.Sheets(i).getCellByPosition(col,row)
Select Case my_cell.Type
Case com.sun.star.table.CellContentType.VALUE
cell_value = my_cell.Value
Case com.sun.star.table.CellContentType.TEXT
cell_value = my_cell.String
End Select
str = str & " " & cell_value
next col
str = str & Chr(13)
next row
next i
msgbox str
End Sub
Looking for Something Else?
If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:
LibreOffice Macro Tutorial Index
