laiye rpa
streamline your operations, improve productivity
in rpa processes, we often need to automate interactions with common software programs like microsoft office programs (word, excel, etc.) and various web browsers. of course, these software programs all have interface, and you can interact with them directly through laiye rpa with the knowledge you’ve learned in chapter 3, “command with target.” however, because of how common these software automations are needed, laiye rpa provides special commands that encapsulate automated interactions with excel, word, outlook, browsers, and databases. working with these special commands are vastly more efficient and convenient than simulating interface interactions. for example, while we can simulate interactions with the excel interface to open and read an excel file, it’s quite complicated. however, using special excel automation commands, we can achieve the same goal using only one command.
before we can get started using laiye rpa to automate these software programs, you must install the programs on your local computer. for excel and word automation, you need to install office 2007 or above, or wps 2016 or above. for browser automation, you need to install internet explorer (ie), google chrome, or firefox.
in this chapter, we assume that the reader has a preliminary understanding of browser, word, excel, database, and working knowledge with all these programs is even better. if you need to learn more about these tools before we get started, resources on these topics are easily accessible on the internet through a simple google search.
excel is an important member of office software suite. it provides powerful tools for making calculations, analyses, and visualizing data, and it is one of the most used software for processing spreadsheets. this makes it a common target for rpa platforms to automate.
before we start automating excel, let us define a few concepts. every excel file corresponds to a workbook, which the user can use to modify and store data in the file. whenever you open an excel file, the title on top is the name of the workbook. a workbook has multiple worksheets, and a workbook contains three worksheets by default, named sheet1, sheet2, and sheet3. of course, you can always add or delete worksheets.
figure 53: workbook and worksheet of excel
an excel worksheet is a two-dimensional table made up of cells. each cell is determined by a row and column number. row numbers are represented by a sequence of numbers such as 1, 2, 3, 4, etc. column numbers are represented by a sequence of uppercase letters such as a, b, c, d, etc. therefore, you can refer to a cell by its column number row number. for example, cell b3 refers to the cell intersected by the third row and the second column of the worksheet.
figure 54: rows and columns of excel
when using laiye rpa to automate operations on an excel table, you first need to open a workbook. the ensuing operations to a particular worksheet or cell are all within the scope of the opened workbook. when you are done using a workbook, you need to close it using a command.
let's try to open a workbook with laiye rpa. click to expand the software automation category in the command area and expand the item excel. insert the first command listed, open excel file, into the assembly area.
notice that this command takes three properties (figure 55). the path parameter takes the file location of the intended excel file (we support .xls, .xlsx, .xlsm files). as we have mentioned earlier, the path parameter can be the absolute path to the file or a relative path like @res"example.xlsx", which points to the "res" directory under the directory of the current process. moreover, whenever the "\" appears in a string in laiye rpa, rewrite it as "\\".
figure 55: open excel workbook
if the given workbook file exists, we will operate on that file when running the process. if it does not exist, then laiye rpa will create an empty excel workbook file in that directory and operate on the created file.
the visible property takes a boolean value (true or false) and indicates if laiye rpa will display the opened file using excel. if the value is false, laiye rpa will operate on the file without displaying the excel software interface.
the output to parameter takes a variable name to take on a value that refers to the excel workbook we have opened. the value is a workbook object, and when we use other commands to operate on this workbook, we need to pass in the workbook object to indicate that this is the workbook to operate on. for example, in figure 56, we store the workbook object in the variable objexcelworkbook. in subsequent excel commands, we will pass in objexcelworkbook to the workbook parameter.
let's try to read the content of cell a1 in sheet1 of this workbook. insert a read cell command. its properties are shown in figure 56.
figure 56: read cells
as we have said, the workbook property here should be the same as variable name we chose for the output to parameter in open excel. therefore, we use objexcelworkbook to indicate that we want to read the cell content from the workbook we have just opened.
the properties worksheet and cell take in strings (text values surrounded by double quotes to indicate that they are strings) that specify which worksheet and cell to read. for our example, enter "sheet1”"and “"1”" respectively.
this is great, but in our daily work, we often need to read in data from multiple cells. if we can only use laiye rpa to read one cell at a time, this would quickly become cumbersome and inefficient. luckily, laiye rpa provides a read area command, which allows you to read the content of all the cells in a rectangular area. insert a read area commands. its properties are shown in figure 57.
figure 57: read area
the read area command shares two common parameters with the read cell command: workbook and worksheet, which refer to the workbook and worksheet to read data from.
the area property takes a string that indicates the area to read, specified by the top left cell and the bottom right cell of the area, joined by a colon. for example, filling in "a2:b6" will read an area from the worksheet that starts with the a2 cell as the top left corner and ends with the b6 cell as the bottom right corner, with a total of 12 cells distributed in 6 rows and 2 columns.
the output to property has the variable name arrayret, which will take on the values of the cells read. inserting an output debug information command and printing the value of arrayret show that the output of read area is a two-dimensional array, with values like [["joe", 123456 ],["jack"， 654321 ],["james"，987654 ],["jay"，741258 ],["john"，951753 ]].
even though we haven’t formally introduced arrays and, in particular, two-dimensional arrays, we will get to know them in detail later. for now, you only need to know that we can use the read area command to read a rectangular area of an excel worksheet and store it in a variable arrayret.
in addition to reading the content, laiye rpa also provides a series of excel commands to modify the content of a workbook. let's try to write "jake" in cell a7 of sheet1 of the example workbook. insert the open excel command, and then insert a write cell command. figure 58 shows the properties of the write cell command.
figure 58: write cell
the properties workbook, worksheet, and cell mean the same thing as their counterparts in the read cell command, selecting which cell of which worksheet of which workbook to write to. the data property specifies the actual content to be written into the selected cell. it can be a number, a string, a variable, or an expression.
commands that write data into an excel file all share an important property: save. selecting yes will cause laiye rpa to save the change immediately, just like when we manually modify the content of an excel file and press ctrl-s to save it. selecting no results in the change not being saved immediately, and we have to use the save excel command or set the save property to true on the close excel command to save the changes. both methods save the changes, and it’s just a matter of personal preference.
other commands that write data into an excel file work similarly, so we refrain from going into details here. do keep in mind that the data property of each write command must be consistent with its write area for the data to be written correctly. specifically, when writing into a cell, the data property should be the data of that cell. when writing into a row of cells, the data property should be a onedimensional array representing a row of data points. when writing into an area, the data property should be a two-dimensional array representing several rows and columns of data points.
similar to excel, word is also an important member of office software suite. word files are the standard medium for digitally encoding work documents. therefore, automating word is a necessity for a mature rpa platform.
like in excel, we need to first open a word document to operate on it, and subsequent operations are all done on that opened document. after we are done making changes, we need to close the opened file.
let’s try and open a word document using laiye rpa. in the command area of laiye rpa creator, under the software automation category, click on word to expand it and select the first command,open file, which allows us to open a word file.
this command has five properties, as shown in figure 59. let's first look at the "file path" property. here we need to specify to path a word document (we support .doc and .docx files). otherwise, the details are exactly the same as the open excel command. here we open a test document example.docx, located under the “res” directory.
figure 59: open a word document
now, pay attention the two new properties access password and edit password. what do these mean? sometimes, due to privacy considerations, we do not want other people to open our documents, or we do not want others to modify the document after opening it, so we set a password. there are two types of passwords. the first type is an access password, which you need to enter in order to open the document. the second type is an edit password, which you need to enter in order to modify the document. laiye rpa will use the provided passwords to open the document. if the document has no password, simply set these properties to empty strings "".
the visible property here has the same meaning as the visible property of open excel, indicating whether to open the word software interface when operating on the document.
the output to property is similar to the output to property of open excel. it expects a variable name, and this variable will refer to the word document we open. when performing various read and write operations on the document, we need to supply this variable to the document property of the command to indicate which document to read or write to. as in our example in figure 59, the output to variable is objword, and in subsequent word commands, we need to provide objword to the document property.
now, let's read the content of this word document. insert a read document command after the open document command. the properties of this command are shown in figure 60.
figure 60: read the word document
the document property should be the same variable as the output to property in open document:objword. this indicates that we’re reading from the document we just opened.
the output to property is filled with a variable named sret, which means that content we read will be outputted to the variable sret. insert an output debug information command to print the content of sret. after it runs, we can see something like figure 61.
figure 61: the output of reading a word document
when we open the original document, we notice that the original word document includes text, tables, and pictures, and the text is formatted in specific ways. the read document command will read all the text in a document, but it does not support parsing text formatting, tables, and figures.
figure 62: original word document
the read document command operates on the entire document. similar commands include rewrite document, save document, save document as, close document, get document path, etc, all of which operate on the entire document. if we need to perform more fine-grained operations on the document, we need to use an important concept in word: focus. focus refers to the currently selected area in a word document, and it is usually highlighted by the software. if no area is selected, the current cursor position is the focus. therefore, the focus can be either a cursor position or a selected area. word operations usually revolve around the focus. for example, if we want to change the font of a paragraph, we must select the text first, and then we can modify its size, color, style, etc. if we want to insert some text, picture, or other content, we also need to move the cursor to the insertion point first.
let's see how to control the focus using laiye rpa. insert a set cursor position command, which can move the cursor focus to a specified position. this command has three properties: the document property is the objword document object we have created earlier; the move times property is used with the optional move method property to indicate how many times to move; the move method property can be one of “character”, “line”, and “paragraph”, each corresponding to moving the cursor to the right by 1 character, moving it down by one line, and moving it down by 1 paragraph respectively. here, we set move method to “line” and move times to 2, which instruct our command to move the focus down by two lines onto the third line. please note that move times cannot be a negative number. this means that we cannot use this command to move up or back.
figure 63: set focus
let's insert a select line command to highlight a specific line. this command has three properties: the document property is the same as before, which takes the document object objword; the start line and end line properties indicate the selected area. here, we set start line to 1 and end line to 2, selecting lines 1 to 2 (2 lines in total).
figure 64: select row
in practical application, just using set cursor position and select line does not work well. why is that? even though word is a what you see is what you get software that allows you to format text and images, it often has some hidden formatting markings that affect the calculation of the positions of each character, line, and paragraph. this makes locating the focus difficult and leads to unexpected results. here’s a little tip. we can mark the locations in a word document we want to operate with text segments. for example, if we want to insert a name somewhere, then we can add the text “name” in that location of the word document. use the select text command to find the location of the special marker “name” you have added and use the write text command to replace the selected marker with the actual content. we can use this same technique to setup multiple special markers in a word document and repeatedly use the select text and set cursor command to fill out a word document.
back to our example. after we have moved our cursor to the specified position or after we have selected the specified content, we can start executing edit operations. available operations include inserting content, reading content, deleting content, setting content format, cut/copy/paste content, etc. here, we demonstrate the set font size command as an example. insert a set font size command after the select line. it has two properties: the document property is set as the document object objword we have created before, and the font size property specifies the font size to change the text to. here, we set font size to 9, which would change the selected text’s font size to 9 points.
figure 65: set font size
browser automation is an important part of software automation. automating actions like retrieving data from a certain website and interacting with web-based service systems is dependent on automating browser interactions.
first of all, we need to open a browser by using the start browse" command. if there is already a browser program open on the computer, we can directly use that browser by invoking the bind browser command, which gives you the same output object to work with as the start browser command.
figure 66: start a new browser
figure 66 lists the properties of start browser. the browser type property specifies which browser to start. laiye rpa currently supports three browsers: internet explorer (ie), google chrome, and firefox. using these browsers requires the corresponding browser to be installed on your computer. for this example, we use google chrome for its great support of html standards and javascript and its reliability. however, there are legacy websites that can only be used on a specific browser. for example, certain government websites or online banking sites can only function on internet explorer. in this case, we have to select internet explorer for the browser property.
the url property specifies which link to open on the browser. supplying "www.google.com", for example, will instruct the browser to open google. you can always leave the link blank and open a website later using the open webpage command.
when the open browser command behaves unexpectedly, such as if the browser cannot be found or the specific url cannot be opened, laiye rpa will try repeatedly until some maximum time limit is exceeded. this time limit is set using the timeout property.
there are two commonly used optional properties. the browser path property links to the executable file of the intended browser. this is useful when you have multiple versions of the same browser software installed on one computer. this property allows you to select the intended version to use. if this property is empty, laiye rpa will search in the default installation directory of the browser and use that version. the browser parameters property allows us to pass in additional specifications when launching a browser. besides just the default startup method when we launch a browser by doubleclicking its shortcut, browsers can actually be instructed to launch in very specific ways, like what website(s) to open by default, whether to full screen the browser window, whether to enable certain features, etc. we can specify these browser-specific parameters through the browser parameters parameter. to learn more about the supported parameters of each browser, please consult the relevant documentation.
after launching the browser, we can perform a series of operations to the browser itself and the webpages displayed on the browser. we can browse the web, enter texts in webpages, click on links and buttons, etc. for example, we can open google’s homepage, enter "laiye rpa" on the search bar, and click the "google search" button to retrieve the search results of "laiye rpa". we can complete these steps using commands with target, which we have introduced in chapter 3. moreover, we can process the search results—scraping data, parsing data, etc.--using the commands under the data processing category. we will introduce data processing commands later.
in an information system, the most important content is its data. nowadays, almost all information systems store their data inside databases. besides using software clients to access the database, sometimes we also need to access and make changes to the database directly. therefore, automating database operations is an indispensable part of rpa. specifically, automating database operation allows us to login to a database using our username and password and interact with the database using sql queries, all through a secure connection.
let’s see how we can access a database through laiye rpa. first, we need to establish a connection to the database. insert a create database object command, located under software automation – database. this command will connect to a specific database and create a database object for that database.
figure 67: create database object
the create database object command has three properties. the database type property specifies the type of the database we are connecting to. laiye rpa currently supports mysql, sqlserver, oracle, and sqlite3 databases. the database configuration property is a string that describes some key information used to create a database object. this string is relatively long and difficult to parse, but that is no problem. we can click on the button to the right of this property to view the property value broken down as a list of sub-properties (figure 68).
figure 68: database configuration
charset refers to the character set of the database, and normally we can just keep the default "utf8". database refers to the name of the database we are connecting to. host and port refer to the ip address and port number of the database. in this tutorial, we connect to a database located on port 3306 of ip address 192.168.0.1, which can also be access through
"http://192.168.0.1:3306". keep in mind that this database is not accessible to the public, so the connection would fail for you. if you want to try this command on your own databases, please change the configuration accordingly. the username and password sub-parameters refer to the username and password used to access the database. by configuring these parameters, we have created a database object.
different types of databases often have different parameters. for example, an oracle database does not have the database sub-parameter, but it has a sid parameter, which amounts to a similar meaning. a sqlite 3 database is a file database, which differs from the other three types of supported databases, which are relational databases. therefore, of database may have different parameters. for example, the oracle database does not have the "database" parameter but only the "sid" parameter with a similar meaning. the sqlite3 database is quite different from the other three databases: mysql, sql server, and oracle are typical relational databases, while sqlite3 is a file database. therefore, the database configuration property of sqlite3 only has a sub-property filepath, indicating the location of the sqlite3 database file.
the output to property specifies which variable to assign the created database object to. here, we create a variable objdatabase, and we will perform all subsequent database operations on this object. now that we have created the database object, we can operate on the database. laiye rpa supports two database operations: retrieving data from the database and modifying the database. to retrieve data, we can use the sql get one and sql get all commands. to modify the database, we can use the execute sql query and execute sql queries commands.
let’s take a look at the sql get one command. this command executes an sql query command and returns the first query result. insert an sql get one command. for the database object parameter, supply our newly created database object objdatabase. for the query parameter, write the sql query statement to execute. here, we write "select * from table1", which selects all data from the table table1 and returns the first result. the output to parameter indicates a variable to assign the query result to. here we supply iret. we can use the value of iret to determine whether the sql query executed successfully.
figure 69: perform a single sql query
finally, remember to use close connection command to close our database connection. the only property of this command is database. we supply our database object objdatabase to close our connection to that database.
figure 70: close connection