This article is from the official account of the project: “AirtestProject” Copyright notice: It is allowed to be reproduced, but the original link must be retained. Do not use it for commercial or illegal purposes

1. Case introduction

Earlier in the data Separation practice article, we detailed the application case of combining Poco and Excel reading.

Today we will talk about an application case that combines Poco and Excel reading and writing. First, we prepared an Excel spreadsheet with multiple artists’ names in advance and named it read-.xls:

We will read the name of the singer in this table, and then search on the home page of netease Cloud Music and find the single of the singer.

After that, we get the top 10 song titles of the artist’s singles and save them to another Excel spreadsheet called write.xls.

This example will help you automatically get the top 10 song titles of a given artist. It looks like fun, so let’s get started!

2. Implement the function to find the name of the singer’s single

Let’s first implement the process of finding the name of a singer’s single (assuming that the initial state of netease Cloud Music is the home page) :

  • Click on the search box on the home page
  • Enter the name of the singer, such as Yixing
  • When the search results are displayed, click the single TAB
  • Then go under singles to get the names of the top 10 songs
  • Return to the home page of netease Cloud Music (waiting to search for the next singer)
# -*- encoding=utf8 -*- __author__ = "AirtestProject" from airtest.core.api import * auto_setup(__file__) from poco.drivers.android.uiautomation import AndroidUiautomationPoco poco = AndroidUiautomationPoco(use_airtest_input=True, # screenshot_each_action = False) click on the home page of the search box poco (" com.net help ease. Cloudmusic: id/searchBar "). Click () sleep (# 1.0) enter the name of the singer Text (" zhang ") sleep(3.0) # Click "single" TAB poco(name=" Android.widget.textView ",text=" single ").click() sleep(1.0) # get the name of the top 10 songs count = 0 for li in poco("com.netease.cloudmusic:id/recyclerView").child("com.netease.cloudmusic:id/musicListItemContainer"): count += 1 if count <= 10: Title = li. Offspring (" com.net help ease. Cloudmusic: id/songName ") song_name = title. Get_text () print (song_name) # back to netease cloud music home page Keyevent keyevent (" BACK ") (the "BACK") sleep (1.0)Copy the code

Once the process is ok, we can encapsulate the script to find the name of the song as a function, because we need to search several artists’ names, and need to use this part of the content repeatedly:

def find_song(name): Song_list = [] poco (" com.net help ease. Cloudmusic: id/searchBar "). Click () sleep (1.0) text (name) sleep (3.0) Poco (name=" android.widget.textView ",text=" single ").click() sleep(1.0) count = 0 for li in poco("com.netease.cloudmusic:id/recyclerView").child("com.netease.cloudmusic:id/musicListItemContainer"): count += 1 if count <= 10: title = li.offspring("com.netease.cloudmusic:id/songName") song_name = title.get_text() song_list.append(song_name) # Print (song_name) keyevent("BACK") keyevent("BACK") sleep(1.0) return song_listCopy the code

3. Implement the function of reading singer name in Excel

The next step is to read the names of the singers we are looking for from the cells of Excel. Here, the library we use to read Excel is still the XLRD we talked about before:

import xlrd def get_excel(): ex = xlrd.open_workbook(r'D:\demo\read.xls') sheet = ex.sheet_by_name('music') dat = [] for row in range(sheet.nrows): Cells = sheet. Row_values (row) # Data =cells[1] dat.append(data) # This function returns a list of singer names return DATCopy the code

Note that XLRD is a third party library for Python, and we need to install it into the current Python environment before importing it:

pip install xlrd
Copy the code

4. Save the single name in the specified Excel

If we can get the list of artists’ names in Excel and get the names of the top 10 songs based on the names of artists, we can get the names of all artists’ 10 songs by iterating the list of artists’ names read from Excel and passing it to the function that finds songs:

li = []    
for d in get_excel():
    li.append(find_song(d))
Copy the code

Finally, we can write the names of all artists into the specified table. Here we use another Python third-party library, Xlwings:

import xlwings as xw

app = xw.App(visible=True, add_book=True)
wb = xw.Book(r'D:\demo\read.xls')
sheet = wb.sheets('music')


sheet.range('C1:C4').value=li

wb.save(r'D:\demo\write.xls')
wb.close()
app.quit()
Copy the code

Don’t forget to install the library in your current Python environment before importing:

pip install xlwings
Copy the code

5. Summary

In fact, there are many practices about the combination of Airtest or Poco project and Excel reading and writing. For example, we can save the test account set in a specific Excel table, and then read the account password in the account set table to use during the test.

Or we will get some key data in the test process, save to the specified Excel file for use and so on.

Here we just read the name of the singer in Excel, then find the top ten song names of the singer’s single in netease Cloud Music, and save them into the example of designated Excel, to provide a simple idea for students. (PS: Students who need a complete script to learn the case can go to the background of the public account to reply “Excel” to obtain)

In the actual application process, students can write more complex and professional scripts according to their own needs.

AirtestIDE download: airtest.netease.com/ Airtest tutorial website: airtest.doc.io.netease.com/ build enterprise private cloud service: airlab.163.com/b2b

Official Q group: 654700783

Ah, so serious all see here, help in the left side of the article click on the likes and favorites, give me a support, ash often thank ~