This article is from the official account of the project: "AirtestProject".
Copyright notice: reprint is allowed, but the original link must be retained; Do not use it for commercial or illegal purposes

1. Case introduction

We were here before Data separation practice This paper describes in detail the application case of the combination of Poco and reading Excel.

Today, let's talk about an application case of reading and writing Poco and Excel. First, we prepared an Excel table containing the names of multiple singers in advance, named read.xls:

We will read the singer's name in this form, then search the Netease cloud music home page in turn, and find the singer's single;

After that, we will get the song names of the top 10 singers in the single row and save the song names in another Excel table write.xls.

This case can help you automatically get the names of the top 10 songs of the specified singer. It seems very happy. Let's start!

2. Realize the function of finding the singer's single name

Let's first implement the process of finding the singer's single name (assuming that the initial state of Netease cloud music is the home page):

• Click the search box on the home page
• Enter the name of the singer, such as Zhang Yixing
• After displaying the search results, click the "single" tab
• Then get the names of the first 10 songs under "single"
• Go back to the homepage 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 the search box on the home page
poco("com.netease.cloudmusic:id/searchBar").click()
sleep(1.0)

# Enter artist name
text("Zhang Yixing")
sleep(3.0)

# Click the "single" tab
poco(name="android.widget.TextView",text="single").click()
sleep(1.0)

# Get the names 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.netease.cloudmusic:id/songName")
        song_name = title.get_text()
        print(song_name)

# Back to Netease cloud music Homepage
keyevent("BACK")
keyevent("BACK")
sleep(1.0)

After the process is OK, we can package the script for finding the song name into a function, because we need to search the names of several singers later, so we need to repeat this part:

def find_song(name):
    song_list = []
    
    poco("com.netease.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_list

3. Implement the function of reading singer name in Excel

Next, we need to read the names of the singers we are looking for from the Excel cell. Here, the library we use to read Excel is still the one we talked about xlrd:

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)  
        # Take the content of the second cell in each row (i.e. singer name)
        data=cells[1]
        dat.append(data) 
    # Save the obtained singer names to the list, and this function returns the list of singer names
    return dat

Note that xlrd is a third-party library for Python. We need to install it into the current Python environment before import ing:

pip install xlrd

4. Save the single name to the specified Excel

You can get the list of singer names in Excel and get the names of the top 10 songs according to the singer names. Then we just traverse the list of singer names read from excel and pass it to the function looking for songs to get the names of 10 songs of all singers:

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

Finally, we can write the song names of all singers 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()

Don't forget to install this library in the current Python environment before import ing:

pip install xlwings

5. Summary

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

Or we can save some key data obtained during the test to the specified Excel file for use, etc.

Here, we only read the singer's name in Excel, then find the name of the first ten songs of the singer's single in Netease cloud music and save them to the specified excel example to provide a simple idea for the students. (PS: students who need to learn the whole case and learn the Excel from the official account can get the answer).

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

Airtest official website: http://airtest.netease.com/
Airtest tutorial website: https://airtest.doc.io.netease.com/
Build enterprise private cloud services: https://airlab.163.com/b2b

Official Q-group: 654700783

Ah, I've seen it so seriously. Please give me a favor or collect it. Thank you very much~