Calc Macros Dev

LibreOffice Workbook Worksheet and Cell Processing using Macro

by
Arindam
7.3k 2

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.

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:

Sheet1 contents
Sheet2 contents
Sheet3 contents

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:

Macro Tutorial Index
Exit mobile version