Software automation
In the RPA process, we often need to automate office software such as Excel and word, or common software such as browsers. Of course, these software all have UI, and UI elements can also be obtained. Theoretically, learning UI elements automation In this chapter, you can automate the operation of these software, but it will be cumbersome. Therefore, Laiye Automation Platform specially encapsulates the automatic operation of Excel, word, outlook, browser, database and other software into special commands, which will be more efficient and convenient than the simulation on the interface. For example, although we can simulate the operation of real people through the UI simulation, open, read and write a Excel document, it is very troublesome, and only one or two commands are needed through the commands of Excel automation.
Before using Laiye Automation Platform to automate the operation of these software, your computer needs to install the corresponding software. For Excel and word automation, you need to install Office 2007 or above ; For browser automation, you need to install Internet Explorer（IE） , Google Chrome or Firefox.
This chapter assumes that the reader has a preliminary understanding of browser, word, Excel, database and other software and related knowledge, and it is better to use these software in work. If there is still a lack of understanding, there are a large number of books on the market that can be referred to, and this article will not introduce them separately.
Excel automation
Excel is an important component of office software. It has powerful calculation, analysis and chart functions. It is also one of the most commonly used and popular spreadsheet processing software. Automation of Excel is a common scenario in RPA process.
Before realizing Excel automation, let's first clarify several concepts: workbook and Worksheet . A workbook is a file that processes and stores data. One Excel file corresponds to one workbook. The title bar of Excel software displays the name of the current workbook. A worksheet is a table in a workbook. Each workbook contains three worksheets by default, called Sheet1、Sheet2、Sheet3 , Of course, you can also delete or add worksheets, which means that workbooks and worksheets have a one-to-many relationship.
The worksheet in Excel is a two-dimensional table, which contains many Cell , The specific position of a cell can be determined by using the row number and column number. The row number is usually represented by a numerical sequence of 1, 2, 3, 4; Train number (common) A,B,C,D …… Such an alphabetic sequence. So you can use Column number + row number For example, cell B3 refers to the cell at the junction of row 3 and column 2.
When you use Laiye Automation Platform to automate the operation of Excel tables, you first need to open the workbook. All operations on worksheets or cells are performed on an open workbook. In addition, after the automatic operation of Excel table is finished, you need to close the open workbook.
Let's try to open a workbook with Laiye Automation Platform. In the command list of Laiye Automation Platform creator, select "software automation" and expand it, then select "Excel" and open it. The first command is "open Excel". You can open an Excel Workbook with this command.
This command has five attribute, as shown in the following figure. Let's first look at the "file path" attribute. Here, you need to specify a path to the Excel workbook file. The file can be xls、xlsx、xlsm Etc. As mentioned above, this path can be absolute or can be switched to Expert Mode, such as @res"Demo.xlsx" Refers to files in a relative path in the format of res, which is the folder named res in the folder where your process is located. Please note that if absolute path is used, it is recommended to switch to Normal Mode and click the button of folder icon on the right to directly select files, which is relatively simple and error free. Otherwise, if you switch to Expert Mode, you need to write according to the format of the string. You need to not only use quotation marks to indicate that this is a string, but also put the \ The symbol is written as: \\ .
If the workbook file we specified exists, it will be operated on when the process is running. If the file does not exist, a blank Excel workbook file will be automatically created when the process is running, and the newly created file will be operated.
The next attribute is "visible", which is a Boolean attribute. Its value can only be "Yes (true)" or "no (false)". When "yes" is selected, this command will open Excel software and display this workbook. Otherwise, you can read or modify the contents of this workbook file normally without displaying the Excel software interface.
The other two attribute specify the password of the Excel workbook to be opened. If there is no password, save it blank.
In the "output to" attribute above, you must fill in a variable name. This variable refers to the Excel workbook we open, which we call a "workbook object". Later, when reading and modifying the workbook, you still need to fill this variable into the "workbook object" attribute of the corresponding command to indicate that the operation is aimed at the workbook. For example, in the above figure, when we open the workbook, the "output to" variable is objExcelWorkBook , For subsequent Excel operation commands, the "workbook object" attribute needs to be filled in objExcelWorkBook .
Let's try to read the contents of the "A1" cell in the "Sheet1" worksheet of this workbook. Insert a "read cell" command, and you can see the contents of this command as shown in the following figure:
As mentioned above, the "workbook object" attribute here should be the same as the "output to" attribute of the "open Excel command", so we need to fill in objExcelWorkBook , It indicates that we read the cell contents from the workbook just opened.
In addition, we need to specify the "worksheet" and "cell" attribute to tell Laiye Automation Platform which cell in which worksheet to read. There are also two ways to specify "worksheet" and "cell": one is to use Excel "Sheet1" and "A1" , Refers to the worksheet named "Sheet1" and the cell named "A1".
The second way is to use an integer to refer to the worksheet. Laiye Automation Platform is usually numbered from 0, so the integer 0 represents the first worksheet, the integer 1 represents the second worksheet, and so on, regardless of the actual name of the worksheet. You can also use two integers in square brackets, for example [x, y] This way (actually an array, if you don't know the concept of array, you can forget about this detail for the time being) refers to cells. among x and y They are integers starting with 1 (here, starting with 1 is to adapt to the habit of Excel), x Represents a row, y Represents a column. for example [1, 1] Actually "A1" Cells, [1, 2] Actually "B1" Cell.
It is in line with the general habits of Excel to refer to worksheets and cells in the form of strings, which is easier for business personnel to understand. Use the integer form to refer to the worksheet and cell. You can save the integer in a variable and change it at any time according to the needs of business logic. For example [x, y] To refer to a cell, here x and y It can be a variable name, which increases in sequence with the loop, so that multiple cells can be read out in sequence. Obviously, it is more flexible. The specific form can be selected according to actual needs.
In the "output to" attribute, you need to fill in a variable name to output the read cell contents to this variable. If the content of the cell is a value, the value of this variable will also be a value; If the content of the cell is a string, the value of the variable is naturally a string.
In our work, we often need to read the data in multiple cells of Excel workbooks. If we use Laiye Automation Platform to read one cell at a time, it will be inefficient and troublesome. In fact, Laiye Automation Platform has considered the need to read a region and provided the "read region" command, which can read all the contents of all cells in a rectangular range All. We try to insert a "read area" command, and its contents are shown in the following figure.
As can be seen from the above figure, the "read range" command has two attribute that are identical to the "read cell" command, namely "workbook object" and "worksheet". These two attribute indicate which workbook and which worksheet to read.
The "range" attribute is also in the form of a string (double quotation marks are required to indicate that it is a string). It is also filled in according to the habits of Excel. Here is "A1:B2" , It means that the reading is from cell A1 in the upper left corner to cell B2 in the lower right corner, with a total of 2 rows, 2 columns and 4 pieces of data.
Of course, in addition to using strings, you can also use a "two-dimensional array" to refer to the area to be read, for example "A2:B6" Can be written as: [[2,1], [6,2]] , Several integers here can be written as variables, so that the reading range can be changed according to the business logic, and the flexibility is better. Of course, if you don't know about two-dimensional arrays, you can ignore this reference method for the time being and use strings to refer to them first.
A variable name is filled in the "output to" attribute arrayRet , The read content will be output to this variable. For example, for the table shown in the following figure, we require Laiye Automation Platform to read "A1:F2" And after the "read area" command, add a "output debug information" command to arrayRet The value of this variable is printed out.
It can be seen from the output information that the "read area" command outputs a two-dimensional array. In the example above, the output result is: [[ "Joe", "Jack", "James", "Jay", "John" ], [ "123456", "654321", "987654", "741258", "951753" ]]
Readers unfamiliar with the concept of programming may not know what "array" and "two-dimensional array" are. These concepts will be explained in detail later. Now we only need to know that with the "read range" command of Excel, All data in a certain range of an Excel table can be read out and put into a variable arrayRet Middle.
Since it can be read, it can also be written. Laiye Automation Platform provides a series of Excel write commands to modify the contents of the workbook. Let's try to convert the Sheet1 In the worksheet A1 The content of the cell is written as: "zhangsan" . Insert a "write cell" command after the "open Excel" command. You can see the contents of this command as shown in the following figure:
Among them, the meanings of the three attributes "workbook object", "worksheet" and "cell" are the same as those of the "read cell" command, which indicates which "worksheet" and which "cell" of which "workbook object" is operated by this command.
The "data" attribute is filled with the data to be written to the cell. If this attribute is filled in the normal mode, it will be written to the cell of Excel in text format. If you switch to advanced mode, you can also fill in numeric values, strings, variables or expressions.
Another important attribute of the Excel write command is "save now". If "yes" is selected for this attribute, the write operation will be saved immediately, just like pressing "yes" immediately after manually modifying the contents of the Excel file "Ctrl+S" The same as saving; If "no" is selected for this attribute, the write operation will not be saved immediately unless the "save Excel" command is called separately, or "yes" is selected for the "save now" attribute of the "close Excel" command. Both methods have the same effect, and the modified contents of Excel can be saved.
The usage of other Excel write commands is similar to that of the write cell command, and will not be repeated here. It should be noted that the "data" attribute of each write command must be consistent with the write range of this write command, so as to ensure that data can be written correctly. That is, when writing a cell, the "data" attribute should be the data of a cell; When writing a row, the "data" attribute should be the data of a row of cells (one-dimensional array), and the length of the array is equal to the number of columns of the worksheet data; The "data" attribute of the write area should be the data of several rows and columns of cells (two-dimensional array). If they are inconsistent, it is easy to report errors or misplace the data written into Excel. Laiye Automation Platform also provides the command "create multi-dimensional array", which can quickly create one-dimensional, two-dimensional or even higher dimensional arrays for writing.
After the Excel operation is completed, it is recommended to use the command "close Excel Workbook" of Laiye Automation Platform to close the currently operated Excel workbook. Otherwise, even if the process of Laiye Automation Platform is finished, Excel is still open, which will consume system resources. Especially when we select "invisible" when opening the Excel workbook, although the UI of Excel is hidden, it is still open all the time, which not only consumes system resources, but also is not easy to find.
Word automation
Similar to Excel, word is also an important component of office software. Document in word format are almost the de facto standard of office document, and it is often encountered in RPA process to automate word.
Similar to Excel, when using Laiye Automation Platform to automatically operate a word document, you first need to open the word document, and then all operations on the contents of the document are carried out on the opened document. After operating word document, you need to close the opened document.
Let's try to open a word document with Laiye Automation Platform. In the command list of Laiye Automation Platform creator, select "software automation" and expand it, then select "word" and open it. The first command is "open document". You can open a word document with this command.
This command has five attribute, as shown in the following figure. Let's look at the "file path" attribute first. Here, you need to specify a path to a word file. The file can be doc、docx Other precautions are consistent with the "file path" attribute of the "open Excel" command in the previous section. Here we open the "res" directory Demo.docx File.
The next two attribute are "password when accessing" and "password when editing". Sometimes, for the sake of privacy, we don't want others to open our document or modify it after opening it, so we Settings a password for the word document. The password is divided into two parts: one is called "access password", and you can open the document by entering the correct access password; One is called "Edit password". You can modify this document by entering the correct edit password. The two attribute "password when accessing" and "password when editing" are used to access word document with passwords automatically. If the word document you are operating on does not have a Settings password, the two attribute can be left blank.
The "visible" attribute has the same meaning as the "visible" attribute of "open Excel", which indicates whether to display the UI of word software when automating word document.
There is also the last "output to" attribute, which has the same meaning as the "output to" attribute of "open Excel". Here, you must fill in a variable name. This variable refers to the word document we open. Later, when reading and modifying the document, you still need to fill this variable into the "document object" attribute of the corresponding command, indicating that the operation is carried out against the open document. For example, in the above figure, when we open a document, the "output to" variable is objWord , For subsequent word operation commands, the "document object" attribute needs to be filled in objWord .
Next, we read the contents of this word document. After the "open document" command, insert a "read document" command. The contents of this command are shown in the following figure:
As mentioned above, the attribute of "document object" is the same as the "output to" attribute of "open document", both of which are objWord , It indicates that we read the content from the document just opened.
A variable name is filled in the "output to" attribute sRet , Indicates that the read content is output to the variable sRet in Let's add a "output debugging information" command, and sRet After running, you can see the following results:
When we open the original document for comparison, we can see that the original word document includes text, tables and pictures, and the text has format information. The "read document" command will read All of the text content in the document, but it does not support reading the text format, table status and pictures for the time being.
The command "read document" operates on the entire document. Similar commands include "rewrite document", "save document", "save document as", "close document" and "get document path". These commands operate on the entire document. If you need to perform more fine-grained operations on document, you need to involve an important concept in word: focus . The focus refers to the currently selected area, which is usually highlighted in word; If no area is selected, the current cursor position is the focus. Word operations are mostly focused on the focus. For example, to change the font of a text, you must first select the text before modifying the size, color, style, etc; To insert text, pictures and other contents in word, you also need to move the cursor to the insertion point first.
Let's take a look at how to achieve Settings and switching of focus in Laiye Automation Platform. Insert a "Settings cursor position" command, which can Settings the cursor focus to the specified position. This command has three attribute: "document object" attribute, which is the document object described above objWord ; The "movement times" attribute needs to be used in conjunction with the "movement method" attribute in the optional attribute, which refers to how many times the cursor moves according to the "movement method". The "movement method" attribute has three options, namely "character", "line" and "paragraph", which respectively represent that the light pointer moves one character to the right, one line down and one paragraph down. Here, "moving method" is Settings as "line" and "moving times" is Settings as "2", which means that the focus Settings is two lines down from the initial focus, that is, the third line. It should be noted that the number of movements cannot be negative, that is, the cursor cannot move left or up.
Let's insert a "select line" command, which can select a specific line. This command has three attribute: "document object" attribute, which is the document object described above objWord ; The "start line" attribute and the "end line" attribute define the selected range. Here, the "start line" is Settings to 1 and the "end line" is Settings to 2, which means that the first to second lines, a total of 2 lines, are selected.
However, in practical applications, the "set cursor position" command and the "select line" command alone are not effective in setting the cursor focus. Why? It turns out that although word is a WYSIWYG visual graphic mixing software, there are also some invisible format marks in word, which will more or less affect the calculation of "characters", "lines" and "paragraphs" in word document, resulting in inaccurate focus positioning. So how to solve this problem? Here is a trick to teach you: first, we Settings a special mark in the word document where we need to insert or edit. For example, when inserting a name field, we Settings $Name$ ; Then, use the command "Settings cursor position after finding text". This command has two key attribute, one is "text content" attribute. Fill in the previous $Name$ One is the relative position attribute. Select "selected text" to find $Name$ Select the mark and select the content of the mark; Finally, use the write text command to replace the selected content with the desired content. We can Settings more such special marks in the word document, and then reuse the command "Settings cursor position after finding text" to fill in the word document.
Continue with the above content. After moving the cursor to the specified position or selecting the specified content, you can perform specific editing operations, including inserting content, reading content, deleting content, Settings content format, cutting / copying / pasting, etc. here, we take the command of "Settings text size" as an example. After the select line command, insert a Settings text size command. This command has two attribute: "document object" attribute, which is the document object described above objWord ; The font size attribute specifies the font size of the selected text. Here, the font size is Settings to 9, which means that the font size of the selected text is uniformly Settings to 9.
Mail client automation
In our daily work, we often need to send or receive emails. It is a common requirement for RPA process to send or receive emails automatically. In order to realize the automatic sending and receiving of mail, there are usually two methods: one is to directly SMTP/POP3/IMAP E-mail protocol, and e-mail client. The former can be completed without installing any client software on the computer, but the configuration is cumbersome. The latter needs to rely on the mail client software, but it is relatively simple. This chapter first introduces the latter, that is, the way to rely on the mail client. Laiye Automation Platform supports two common mail clients: Microsoft Outlook and IBM Notes .
Outlook is Microsoft's main mail transmission and collaboration client product, and is also one of the components of the office software suite. The following figure shows the UI of writing a new email using outlook 2019:
Using Laiye Automation Platform to automatically operate the outlook client, you can directly use the commands "send mail", "get mail list", "response mail" and "download attachment", which are basically the same as the user's usual habit of operating mail on the outlook client. You can fill in the sender, recipient, title, body, attachment and other information, but when filling in the "sender mailbox" and "mailbox address" attribute, This email address must be bound in outlook in advance (binding multiple email addresses is supported).
For example, you can insert a "send mail" command in Laiye Automation Platform, and set the attribute of the command as appropriate Settings. These Settings are almost indistinguishable from the contents you fill in when sending emails manually. They are very simple.
If you want to receive mail, you can insert a command of "get mail list" and Settings the attribute of the command appropriately. Multiple emails can be received with one command. The "quantity of emails" attribute specifies the quantity of emails you need to receive. If it is 0, it means that all emails in the mailbox are received.
In addition, it also supports relatively low-frequency mail operations, such as the "move mail" and "delete mail" commands. It should be noted that the current outlook automation commands are mainly adapted to the 2010, 2013, 2016 and 2019 versions of outlook.
Like outlook, Laiye Automation Platform also supports IBM Notes The mail client performs automated operations, such as sending mail, response mail, obtaining mail, and downloading attachments. These operations can also be directly realized by using the commands "send mail", "response mail", "obtain mail list", and "download attachments"; The precondition is the same. The sender's mailbox needs to be bound in advance, but the difference is that the attribute are configured: IBM Notes There is no need to fill in the sender's mailbox, but there is a "password mode". All automation commands need to configure passwords, including the "move mail" and "delete mail" commands. The following figure shows "sending mail" as an example. Other commands are similar and will not be repeated.
When Laiye Automation Platform operates IBM notes automatically, it also has certain requirements for the version of IBM notes. At present, it mainly adapts to 9.0.1 and 11.1 versions. The version number of IBM notes can be found in the following UI.
Browser automation
Browser automation is an important part of software automation. Any business system that grabs data from a specific website and operates automatically in the form of web needs to operate automatically based on the browser.
First, we need to open a browser. This function is realized by the command "start new browser". Of course, if the computer has opened a browser at this time, we can also directly use the opened browser for subsequent operations. At this time, we only need a "bind browser" command, which has the same effect as the "start new browser" command.
The attribute of the command "start new browser" are as follows: "browser type" attribute specifies which browser to start. Laiye Automation Platform currently supports Internet Explorer, Google Chrome browser, Laiye Automation Platform browser and other types of browsers. Among them, Laiye Automation Platform browser is self-contained by Laiye Automation Platform and does not require additional installation. Here, we choose "Laiye Automation Platform Brower", that is, Laiye Automation Platform's own browser. Compared with the other three browsers, Laiye Automation Platform browser has the following advantages: first, you can select the target element without installing any browser Extension (both Google Chrome and Firefox need to install Extension, and in the process, sometimes some unexpected situations occur, such as being intercepted by anti-virus software); Second, the Laiye Automation Platform browser can select the target element in the cross domain web page (the user name and password input box cannot be found when logging in Netease, QQ and other mailboxes with other browsers); Third, the Laiye Automation Platform browser can directly call JavaScript methods in the visited page. Based on the above advantages, we recommend giving priority to the use of Laiye Automation Platform browser. Of course, there are also some special websites that can only be opened and operated correctly with a specific browser, such as Only IE browser can be used to open and operate correctly. At this time, "browser type" attribute can only be "IE browser".
The "open link" attribute indicates which URL is opened when the browser is opened. Fill in here as follows: "www.bing.com" , Indicates that the Bing website is opened at the same time when the browser is opened. Of course, you can leave it blank for the time being, and then use the "open web page" command to open a web address separately.
The "timeout" attribute means that if an exception occurs, such as the browser cannot find it or the specified link cannot be opened, Laiye Automation Platform will try repeatedly until the specified time is exceeded, that is, the "timeout".
Two optional attribute are also commonly used: one is the "browser path" attribute. Sometimes, we install two different versions of browser software on the same computer. At this time, we can open a specific version of the browser by specifying the "browser path" attribute. If this attribute is not specified, the system will go to the default installation directory of the browser to find and start the browser software; The other is the "browser Parameter" attribute. As we know, the browser is actually very powerful. In addition to being able to start by default, the browser can also start a personalized browser by customizing the startup Parameter, including opening some web pages by default, presentation mode (full screen, etc.), enabling or disabling some functions, etc. For specific startup Parameter that can be configured for each browser, please refer to the corresponding document.
After starting the browser, we can perform a series of operations on the browser and the web page displayed in the browser. We can browse the web page, input text in the web page, click on links and buttons in the web page, etc. For example, after opening the Bing website, we can enter "Laiye Automation Platform" in the input box of the Bing homepage and click the "search" button to get the search results of "Laiye Automation Platform" in Bing. These operations can be completed through the "targeted command" in the previous chapter. Search results can also be processed through the "data processing" command to complete Scraping, data analysis and other functions. These functions will be explained in detail in the subsequent tutorial "data processing" and will not be expanded here.
Database automation
In an information system, the most important thing is data. Now, almost all information systems store data in databases. In addition to using the client to access the database, sometimes it is also necessary to directly access and operate the database. Therefore, the automatic operation of the database has become an indispensable part of RPA. The so-called automatic operation of database refers to directly logging in the database with user name and password and operating the database with SQL statements on the premise of ensuring data security.
Let's take a look at how to operate the database. First, you need to connect to the database. Under "database" directory of "software automation", select and insert a "create database object" command, which will create a database object connected to the specified database.
The "create database object" command has three attribute: the "database type" attribute specifies the type of database object to be created. Laiye Automation Platform currently supports five database types: MySQL, SQL server, Oracle, SQLite3 and PostgreSQL. The "database configuration" attribute describes some key information when creating a database object. This information is long and not easy to understand, but it's OK. Click the "paper and pen" button on the right to pop up a window displaying more attribute of "database configuration", as shown in the following figure:
"Host" and "port" refer to the IP address and port number of the database. Here, "192.168.0.1" and "3306" are filled in, indicating that the database can be accessed through the address "192.168.0.1:3306"; "User" and "password" refer to the user name and password for accessing the database; "Database" refers to the name of the database to which we connect; "Charset" refers to the character set of the database. Usually, the default "utf8" is maintained. The specific configuration of the database is different. For the configuration of the above information, you can ask the administrator of the database you want to access.
Of course, each type of database may have different configuration attribute. For example, Oracle database has no "database" Parameter but only "Sid" Parameter, but its meaning is similar. 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" attribute of SQLite3 has only one sub attribute, "filepath", which indicates the path of the SQLite3 database file being operated. For PostgreSQL database, the "database configuration" attribute is slightly different from that of MySQL. However, at present, it only supports the automatic operation of relational features, and other modern features of PostgreSQL do not support it for the time being.
Similar to other software automation commands, you can fill in a variable name in the "output to" attribute, which will save the created database object. Here we fill in objDatabase , All subsequent database operations are directed to objDatabase Database object.
After the database object is successfully created, you can operate on the database. Laiye Automation Platform provides two database operations: one is to query data, corresponding to the commands "execute single SQL query" and "execute full SQL query"; One is to modify the database, table and data in the table, corresponding to the commands "execute SQL statement" and "batch execute SQL statement".
Let's take a look at the command "execute single SQL query". This command can execute an SQL query statement and return the first result of the query. Insert a "execute single SQL query" command. You can see that this command has three attribute: one is the "database object" attribute, and this attribute fills in the just obtained database object objDatabase ; One is the "SQL statement" attribute. This attribute is used to fill in the query statement to be executed. Here is the "select * from table1" , It means query table1 All the data in the table and return the first result; The third attribute is the "output to" attribute, where a variable IRET is filled to represent the execution result of the SQL statement. We judge whether the SQL statement is successfully executed by judging the value of IRET.
Finally, remember to use the "close connection" command to close the database connection. The only attribute of this command - "database object" attribute, fill in the database object objDatabase , The database connection can be closed.
