In this tutorial, you will learn how to access the workbook, worksheet and Cell contents using LibreOffice Calc basic macros.
Spreadsheet applications like Calc consist of workbooks, worksheets and individual Cells. It is often required to process those using Macro to automate various tasks.
This tutorial will demonstrate the basic processing of worksheets and cells, which is the foundation of many complex macros.
Note: This tutorial assumes you have the initial set up on creating a basic Macro in LibreOffice Calc.
Table of Contents
Traverse Workbook, Worksheet and Cell using LibreOffice Macro
Exercise Statement
We will read a Calc spreadsheet with three sheets, one after another and read the contents of it. After reading, we will show the formatted read contents in a message window.
Sheet1, Sheet2 and Sheet3 contains below data:
Define variables
First, we will declare 3 Objects to define the Calc workbook, a 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 active Calc workbook.
Once this is done, the Sheets collection is assigned to my_sheets object to access all the workbook sheets. All the sheets are assigned to object my_sheets as an array. To access them, we have to use subscripts like Sheets(0), Sheets(1) so on. Note that the subscript starts at zero in Basic.
my_doc = ThisComponent my_sheets = my_doc.Sheets sheet_count = my_sheets.Count
Once we get a handle on sheet using Sheets(subscript), we can access each cell using getCellByPosition method. A typical way of accessing a cell is as below:
my_cell = ThisComponent.Sheets(subscript).getCellByPosition(col,row)
Tip: If you think accessing Sheet with a subscript is difficult, you can also use the sheet name (e.g. Sheet1, Sheet2, etc.) to refer them. So, the same statement above can also be written as below:
my_cell = ThisComponent.Sheets.getByName("Sheet1").getCellByPosition(col,row)
Note that the first argument of getCellByPosition is a column and then a row.
After getting the cell object my_cell set, we have to access its contents.
Access cell contents
LibreOffice has a different take on it. Each cell is defined by its content type. If you put a number on a cell, it becomes number type; if you put characters on it, it becomes 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
We would use the snippet to access the cell values in this example. The switch-case block can take care of the type at runtime, and you get the values you need.
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
Running the macro
I will concatenate each cell value in a formatted way and show it in a message box, which is an easier way to read and display. After the run, all cell values from all sheets are shown 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 the below link for the complete Macro Tutorials Index:
