Range Processing using Macro in LibreOffice Calc – Part 1

3 min


In many automation tasks we need to access spreadsheet using range to reduce processing time. One can access each cells using “row, column” way which is expensive in terms of processing time and complexity. Thus Range is sometimes preferred on top of the individual cell processing.

In this two part tutorial series I will talk about how to process “Range” in LibreOffice (LO) Calc spreadsheet application.

Part 1 would consists of writing in spreadsheet ranges.

Define a Range

A range is typically defined using “start_cell_address:stop_cell_address”. In Calc all columns are identified using letters “A”, “B”, “C”, …. and all rows are numbers starting from 1 and so on.

So a typical range – “A1:D5” refers all cells starting from (1,1) to (4,5). See below:

A simple Range
A simple Range

Create a Macro

Create a new macro in LibreOffice Calc. Add a function say rang_processing_demo() with below lines which would provide a hold of the spreadsheet before we start processing range.
If you need guidance on how to create a basic macro – click here.

Sub range_processing_demo
      dim my_doc   as object
      Dim my_sheets as object
      Dim my_range as object
      my_doc = ThisComponent
      my_sheets = my_doc.Sheets 
End Sub

We will start with a single cell fill-up using range, then go on to more complex range processing.

Set a single cell using range

Ranges can be set using a function setDataArray(array). The argument of this function is a 2D array of same size of the range.

If you choose a range “A1” which is nothing but a single cell, then the correcponsing array should have only value. Thus define an array with one element and set its value.

Dim my_data(0,0) 			
my_data(0,0) = "Apple"

Now, define a range of 1 cell i.e. “A1” and put the defined array inside setDataArray() function.

my_range = ThisComponent.Sheets(0).getCellRangebyName("A1")
my_range.setDataArray(my_data)

If you run the above sets of code, the result would be:

single cell fill using Range
single cell fill using Range

Set a row of cells using range

Now, get on with more complex range processing. We want to fill a row of cells using range. We want to fill the cells from A3 to C3. For this, we need to define an array with (0,2) where 0=row and 2=column. Note that columns starts with 0 thus 0 to 2 is 3 columns that we want to fill.

' Set a row of cells using range
	ReDim my_data(0,2) 			
	my_data(0,0) = "Apple"
	my_data(0,1) = "1000"
	my_data(0,2) = "Red"
	my_range = ThisComponent.Sheets(0).getCellRangebyName("A3:C3")
	my_range.setDataArray(my_data)	

The result of above code:

Fill Rows Using Range
Fill Rows Using Range

Set a columns of cells using range

On the opposite side, we want to fill 1 single column with multiple rows. Define an array with (2,0) and have this code in place.

' Set a column of cells using range
	ReDim my_data(2,0) 			
	my_data(0,0) = "Apple"
	my_data(1,0) = "1000"
	my_data(2,0) = "Red"
	my_range = ThisComponent.Sheets(0).getCellRangebyName("C1:C3")
	my_range.setDataArray(my_data)	

The result:

Fill Columns using Range
Fill Columns using Range

Set a row and column of cells of same size using range

Lets fill both row and column ranges using same size. Similar to above examples, define an array with size (2,2).

' Set row and column of same size using range
	ReDim my_data(2,2) 			
        my_data(0,0) = "Apple"
	my_data(0,1) = "1000"
	my_data(0,2) = "Red"
	
        my_data(1,0) = "Apple-1"
	my_data(1,1) = "1000-1"
	my_data(1,2) = "Red-1"

        my_data(2,0) = "Apple-2"
	my_data(2,1) = "1000-2"
	my_data(2,2) = "Red-2"
		
	my_range = ThisComponent.Sheets(0).getCellRangebyName("B2:D4")
	my_range.setDataArray(my_data)	

Output:

Fill Rows and Columns of Same Size using Range
Fill Rows and Columns of Same Size using Range

Set a row and column of cells of different size using range

To fill the range of different size of column and row, define an array of different size.

' Set row and column of different size using range
	ReDim my_data(3,1) 			
        my_data(0,0) = "Apple"
	my_data(0,1) = "1000"
	
        my_data(1,0) = "Apple-1"
	my_data(1,1) = "1000-1"

        my_data(2,0) = "Apple-2"
	my_data(2,1) = "1000-2"
	
        my_data(3,0) = "Apple-3"
	my_data(3,1) = "1000-3"
		
	my_range = ThisComponent.Sheets(0).getCellRangebyName("B2:C5")
	my_range.setDataArray(my_data)

Output:

Fill Rows and Columns of Different  Size using Range
Fill Rows and Columns of Different Size using Range

Conclusion:

Define your array size using (row, col) and use setDataArray () function to fill out the range using macro basic.

SEE ALSO:   String Processing in LibreOffice Calc Macro with Examples

 

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!

Join our Telegram channel and stay informed on the move.

Also Read


Like it? Share with your friends!

Arindam

Creator of debugpoint.com. All time Linux user and open-source supporter. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
2
0
Would love your thoughts, please comment.x
()
x