LibreOffice Workbook Worksheet and Cell Processing using Macro

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

We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!