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.
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.
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
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(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
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: