 Hi, my name is Rafael Lima and in my talk today, I'm going to introduce how to use the script forage library to write Python scripts in LibreOffice calc. So the main idea in my talk is to show how we can combine the strength of LibreOffice calc, the advantages of the Python language, and the new features added by the script forage library. I'd like to point out that all examples are written using Python in this presentation and if anyone wants to download any of the files that I show here, they are all available in my GitHub page. So what is script forage? Script forage library is a set of services that are comprised of methods and properties that seek to make scripting easier and more accessible for LibreOffice users, mainly for those who are not experienced programmers and don't have much experience with the LibreOffice API. So you can see here that calc is one of the services of the script forage library. So this means that the 21 services currently available in script forage cover not only calc, but also many other aspects of macro writing. I'd like to highlight that script forage is available since LibreOffice 7.1, but only with basic support and since LibreOffice 7.2 we have Python support. So this means that if you want to run any of the examples that I show here, you need at least LibreOffice 7.2. So why use script forage? So the idea of script forage is to hide API and uno complexity, which is a challenge for those starting to write macros in LibreOffice. For example, I give here a simple example comparing the usual approach with the script forage approach of doing things. So the usual approach, for example, if we want to test if a document is a calc document. So we would have to get document here and then test if it supports the service spreadsheet document. So if it does, it is a calc document. So the idea of script forage is to hide this part because new users do not understand that we have to test this and they don't understand why this is necessary. So it's simpler to do this. So we can get the document using the create script service calc. So create script service is a method from the script forage library. And when we instantiate it this way, it references the currently open document, the document that's called the macro. And then with the document we can call is calc. And if it's true, then we have a calc document. So it's a much simpler way to do things. Okay, so this is the main philosophy behind script forage. The idea is to make scripting simpler. Okay, now about the script for calc service. The calc service has 30 methods and 11 properties. And it's an extension of the document service, which is another service in the script forage library that provides methods that are general for all types of LibreOffice documents. So today I'm going to focus on the methods that are specific to calc. I would like to point out that the documentation in the help and LibreOffice help, we have finished the documentation of all services with examples for all methods. So if anyone is interested in learning more about this script forage library, you can access the LibreOffice help and there you can find examples for all services and all methods. Well, so now let's start with the examples. And we're going to start with a very simple example that creates a matrix with strings that can be either the word even or the word odd. Okay, so these are the strings that are going to be inserted in a matrix of cells in the current sheet. Okay, so the idea here is very simple. I'm not going to dive into the Python aspect of this script, but I'm going to focus on the script forage library aspects. Okay, so here we have the create script service method that comes from the script forage library. And when we instantiate it using this method, it always refers to the currently open document, which is the document that triggered this macro. Okay, so doc is a reference to the current document. And then we use the method offset. So this method is from the calc service. What it does, it starting from an address, what we do is offset it by an amount of rows and columns. So suppose we have cell A1 here, so we can offset it by a certain amount of rows and columns. And with this, we can traverse a range. So I used here a four by four matrix, okay. And then we use the doc.setValue method to write some string into the target cell. So notice here that when we call doc.offset, it returns a string containing the new address offset according to the arguments here. Okay, and then we use this target cell to write into the cell some value. So here, just to show how everything is working, I created all the examples that I'm going to show here have been implemented in a file. So you can download this in my GitHub page as well. Okay, so this execute V1, it creates a six by six matrix using the setValue method as I have just presented. Okay, so you can see here that it's not very fast. Okay, so I'm going to address this in a minute. But before that, let's go back here to the presentation. And I'm going to show you another feature of the Scriptforge library, which is the basic service. So here you see that in addition to the calc service, I instantiate the basic service. Okay, here. And then I use the input box method. So the basic service, the idea of the service is to provide some methods that are only available in basic, but also to Python scripts. Okay, so the input box function is a function of the basic language that is called from within a Python macro using the basic service. Okay, so I'm using this just to showcase that we can ask some values to the user and then use these as parameters to create a matrix with any size that we want. So going back to the file. So here I'm going to execute this version that calls the input box. So here we can determine how many rows we want. Let's say 15 and eight columns and then okay. And you see that it fills the values as we want. Okay, so as many values as we specified. However, you may have noticed that it is slow because of the set value because when we use the set value and the offset. So we are calling the set value method many times. So it takes some times to run all these calls. So a faster approach. So it's always faster to use set array. Okay, so now I'm going to show you an example of how the set array method can be used and it's very simple. Okay, so the idea of the set array method is two. So it's here. So we specify a starting address and then we pass a matrix which in Python can be a list of lists. So here I create a list of lists containing the odd and even strings randomly. And what it does, the set array, it inserts the values all at once. So we create it separately. We create this matrix separately and then all at once we insert these values into the sheet. So this is why this method is much faster. So just to showcase how faster it is, let's go back to the file. And here we have this V2. It runs the 6x6 matrix using set array which is faster. So see here. So here we have the slow version and here we have the faster version. Okay, so whenever you can use the set array method instead of set value. Okay, so here we have a fourth version where we can specify how many rows, let's say 16 and then 8. So this one uses set array so it's faster. So you see it's almost instantaneous. Okay, so now moving on. So in this example, I show how to use other methods of the calc service. For example, we have the property current selection that returns a string containing the address of the current selection. And what I do here is a simple example that gets the current selection. So I search for values that are smaller than zero and then I write into the cells containing these values the word invalid here. And I also use the set cell style method from the script for library to apply this style to the cell. So this is another method that we can use. Okay, so here what I do is I get the current selection which is a string. And then I use it into the offset method to get the first cell in the current selection. So how do I do this? I offset it by zero rows in zero columns. And then I resize, when I offset I can resize it to a range with one row and one column. So this is what I do here. So with this I get the first cell. And then I can use the height of the current selection to get the number of rows and the width of the current selection to get the number of columns. And with this information what I can do is for each INJ which are determined by the height and width of the current selection. What I do is get the first cell offset it by INJ and then I get the value of the cell here and test if it is smaller than zero. Then I set the value of the cell to invalid and apply the cell style. So let's see this happening in the file. So here we have the file. I'm going to create some values here. And as you can see some of them are smaller than zero. So in my selection I apply that macro. So you see that it only analyzes the selected cells. So this is another example to show some methods that we can use in the calc service. So moving on. Now let's talk about clearing cell contents. So this is something that is challenging using the LibreOffice API because when you use the clear contents method you have to specify the type of content that needs to be cleared. And this requires some knowledge of the LibreOffice API which again is difficult is challenging for new users. So the idea here is to provide three methods. The clear all method. Here it clears everything. Formats, values, everything. Then we have clear formats that clears only formats and clear values which clear only the values in the cells. So here we have the code that I created for this example which is very simple. So first I get access to the current document and then I clear all and then I pass an address to be cleared. So let's go back to the file to see how this works. So here we have clear all. So it's clearing all and I am passing this range as an argument. So clear all clears everything including formats and values. Clear formats remove the formatting and keeps the values. And then clear values remove the values and keep the formats as we would expect. We can use Ctrl Z to undo all these comments. Now moving on. So now we're going to talk about copying and moving ranges. So we have three methods for doing that. So we have copy to cell, copy to range which is a bit different. I'm going to explain this in a minute and move range. So copy to cell we get a source and then we specify a destination. And copy to range it also asks for a source and a destination but it works differently. Copy to range is the same when you copy a source and paste it into a larger destination than the source. And then move range is the same as copy to cell but then we move instead of copy. So the first example that we're going to run is this. So we have here the copy to cell so we have a source and a destination. So the destination can be just a single cell because this works as a Ctrl C, Ctrl V operation. If you specify a range here in the second argument only the top left cell will be considered. And copy to range notice that it takes in a source argument and the destination is a range as well. So we have a range that can be larger and it works as we were pressing Ctrl V after selecting the destination range. I will show this in a minute. So let's go back to the file. So here we have the source and then we have copy to cell. So I'm going to copy a12a4 into say C1 here. So you see that it does exactly as we expect as we expect. And then copy to range we're copying a12a4 into a12f6. So you see that it expands the source as though as we were doing this. We copy it here Ctrl C then we select the destination and press Ctrl V. So this is copy to range. Now let's go back to the slides here. We're going to do a more complex example using copy to cell. So here in this example we're going to copy values from a separate file from a different file. So suppose we have another file which in this example I'm calling data source dot ODS. So this is a separate file. And from this file I'm using the UI service from Scriptforge. So the UI service to access this document that must be open in this example. So suppose this data source dot ODS is open. So I am using the get document method from the UI service to get this document which I'm calling here as source doc. And then I can use from this source doc I can use the range property to get an object containing information about this address from this source document. Ok so now we have this source range and we can use this source range with the copy to cell method. So see here that we are using this source range from the data source dot ODS file as the source and we are pasting the contents into cell A1. Ok so now let's see how this works. Well here we have the data source. So this is the range that we are copying into the current file. So this is data source dot ODS. This is the range that we are copying. And here we are copying those contents. Ok so if I press here it will copy the contents from the other file into this file. So be aware that the data source dot ODS file must be open. It is also possible to do this with the file closed. And I'll show that when we talk about managing sheets. Ok so there is a method to copy from a file that is closed. But we will talk that later. Nice. Now moving on. Well now we are going to talk about managing sheets. So we have many methods for managing sheets. The ones that we are going to use the most are activate, copy sheet, insert sheet, move sheet, remove sheet and rename sheet. So I'm going to show you a few examples of how these methods can be called. Ok so they are very simple and they are meant to be like that. So you see here that for example if we want to create a new sheet. We just call the insert sheet method. So first here we again we instantiate the calc service to access the current document. And then we call the insert sheet method. So it will insert a new sheet called test sheet and it will be created before the second sheet that already exists in the document. So this is an optional argument that defines where this new sheet will be placed. We could have used a string here referencing an existing sheet. And then the new sheet would be placed before the existing sheet that we passed here. And then we can activate this sheet. So activate is like clicking the sheet name to show the sheet. So this is what we are going to do now in the file. So now coming back to the file here we can insert sheet. So you see that it will create the test sheet right here. So after we clicked the button you see that it created the test sheet here. Let's insert a few values here just to use them for the next example. And here we're now going to copy test sheet. And when we create this copy the copy will be named copy test sheet. So the code is here so it's very simple. It's just copy sheet the source sheet and then the name of the copy. So note that it will be placed last in the file because I did not specify the before sheet argument as I had done here. So now copy sheet and you see that a copy was made here. And finally the remove sheet. I'm going to remove the copy test sheet. You see that now it's gone. And here so this is the example. This is the method copy sheet from file that copies a sheet from a file that can be closed or open. It doesn't matter if it's closed or not. So let's see how this works copy sheet from file. So you see that it's very simple to call this. It has three arguments. So the first argument is the file from which a sheet will be copied an entire sheet. Here is the name of the sheet that will be copied. And this is the name of the sheet when it's pasted in the current document. So again it's copying sheet to from data source dot ODS. So we can see here that sheet to has these contents. And we are going to place them at the end of this file. So when I click here it copies the sheet into this file. But beware that using a copy sheet from file you don't need. Where is it? It's here. So when you use copy sheet from file the file doesn't need to be open. So this is the advantage of this method. But it copies the entire sheet. It is also possible to copy a range from a file that is closed. But for that you need to use the UI service open the file copy and then close it. Now let's go back to the slides here. We are now going to discuss these D methods that are used to quickly call calc functions that are very common. Which are average, count, max, min and sum. So the name of the methods are davg, dcount, dmax, min and sum. So it's very simple. And here I also created an instance of the basic service to be able to call a message box from within a Python script. So see here that first I instantiate the document and then I call davg to calculate the average of the values in cells a1 through a e1. So this is the range from which I'm going to calculate the average. And then I'm going to present the result in a message box. So this is the code that does exactly that. Now let's go back to the file. And here we have values in the range a1 to e1. And when I calculate average it applies the davg method and presents a message box with the results. Remember that this is all being done in Python. Now let's go back to the slides to see the last topic of our talk. So I'm going to explain how to import CSV files using the import from CSV file method from the calc service. So it's very simple. You can see here that I just have to specify the file to be imported which in this example is job data view1.csv. So this is the first argument of the import from CSV file. And then I have to specify the cell into which the values will be inserted. So they will be inserted starting at cell a1. Notice here that I am not specifying any additional settings for the CSV import. So it's using the default settings which are the file is a text file encoded in utf8. The field separator can be a comma a semicolon or a tab character. The string limiter is a double quote. All lines are imported and quoted strings are formatted as text. So these are the default settings. So let's see this happening. Well here we have the file that's going to be imported. Notice that this is a very standard CSV file with a comma as a field delimiter. And here notice that I have numbers that use the dot as the decimal separator. So we're going to import this file now. So here we can see the let me just erase this. So if I click here it will load that file correctly. So everything is here. Now I'm going to show you some specifics of this import from CSV file method. So here I have a second CSV file. And notice here that it has a semicolon as the field separator and a comma as the decimal separator. And why is that? Because I'm from Brazil and in Brazil we use commas as the decimal separator. So if we import this file using the default settings it will break the import because it will think that this comma is a field separator. So we have to apply some settings before importing it. And what are these settings? So the settings are defined as a string separated with commas too. So we have this filter option here that can be entered here as the last argument from the import CSV file. And here we specify the ASCII code of the field separator which in this case I'm going to use only the semicolon. So notice here that I'm not telling that the comma is a field separator here. Then I have here some standard values. For example the double quotes as the text delimiter, the character set and the number of the first line. So I am important from the first line until the end of the document. So all records are being imported. So using these filter options let's see what happens. Well first let's see what happens if I don't use the default settings. For example if I load that CSVII file with the default settings you can see here that it separates those values that I wanted to stay together. But if I import that CSV file with the custom settings you can see here that they are imported correctly as I would expect. So okay with this I finished my talk. I'd like to thank you for your attention and thank you and a big thanks to the members of the Scriptforce team Jean-Pierre Le Dure and Alain Homedem. So thank everyone for your attention and if anyone has any questions please let me know.