Python automated testing, Excel data-driven reading xlrd actual combat (super detailed)

Table of Contents: Guide

- foreword
- 1. From entry to mastery of Python programming
- 2. Interface automation project actual combat
- 3. Actual Combat of Web Automation Project
- 4. Actual Combat of App Automation Project
- 5. Resume of first-tier manufacturers
- 6. Test and develop DevOps system
- 7. Commonly used automated testing tools
- Eight, JMeter performance test
- 9. Summary (little surprise at the end)

Foreword

xlrd module installation

pip install xlrd

xlrd common methods

1. Import module
import xlrd
2. Open the file

x1 = xlrd.open_workbook("data.xlsx")

3. Get sheet
Get all sheet names: x1.sheet_names()
Get the number of sheets: x1.nsheets
Get all sheet objects: x1.sheets()
Search by sheet name: x1.sheet_by_name(“test”)
Find by index: x1.sheet_by_index(3)

# -*- coding:utf-8 -*-

import xlrd
import os

filename = "demo.xlsx"
filePath = os.path.join(os.getcwd(), filename)

print(filePath)


# 1. Open the file
x1 = xlrd.open_workbook(filePath)

# 2. Get the sheet object
print 'sheet_names:', x1.sheet_names() # Get all sheet names
print 'sheet_number:', x1.nsheets # Get the number of sheets
print 'sheet_object:', x1.sheets() # Get all sheet objects
print 'By_name:', x1.sheet_by_name("test") # Find by sheet name
print 'By_index:', x1.sheet_by_index(3) # search by index

output:

sheet_names: [u' plan', u'team building', u'modile', u'test']
sheet_number: 4
sheet_object: [<xlrd.sheet.Sheet object at 0x10244c190>, <xlrd.sheet.Sheet object at 0x10244c150>, <xlrd.sheet.Sheet object at 0x10244c110>, <xlrd.sheet.Sheet object at 0 x10244c290>]
By_name: <xlrd.sheet.Sheet object at 0x10244c290>
By_index: <xlrd.sheet.Sheet object at 0x10244c290>

4. Obtain the summary data of the sheet

Get sheet name: sheet1.name
Get the total number of rows: sheet1.nrows
Get the total number of columns: sheet1.ncols

# -*- coding:utf-8 -*-

import xlrd
import os
from datetime import date, datetime

filename = "demo.xlsx"
filePath = os.path.join(os.getcwd(), filename)
print(filePath)

# open a file
x1 = xlrd.open_workbook(filePath)

# Get the summary data of the sheet
sheet1 = x1.sheet_by_name("plan")
print "sheet name:", sheet1.name # get sheet name
print "row num:", sheet1.nrows # get sheet all rows number
print "col num:", sheet1. ncols # get sheet all columns number

output:

sheet name: plan
row num: 31
col num: 11

5. Cell batch reading
Operation:

sheet1.row_values(0) # Get all the contents of the first row, merge the cells, display the values in the first row, and leave the others empty.
sheet1.row(0) # Get cell value type and content
sheet1.row_types(0) # Get cell data type

# -*- coding:utf-8 -*-

import xlrd
import os
from datetime import date, datetime

filename = "demo.xlsx"
filePath = os.path.join(os.getcwd(), filename)

x1 = xlrd.open_workbook(filePath)
sheet1 = x1.sheet_by_name("plan")

# Cell batch read
print sheet1.row_values(0) # Get all the contents of the first row, merge the cells, display the value in the first row, and leave the others empty.
print sheet1.row(0) # Get cell value type and content
print sheet1.row_types(0) # get cell data type

output:

[u'learning plan', u'', u'', u'', u'', u'', u'', u\ '', 123.0, 42916.0, 0]
[text:u'learning plan', empty:u'', empty:u'', empty:u'', empty:u'', empty:u'' , empty:u'', empty:u'', number:123.0, xldate:42916.0, bool:0]
array('B', [1, 0, 0, 0, 0, 0, 0, 0, 2, 3, 4])

Table operations:

sheet1.row_values(0, 6, 10) # Take row 1, columns 6~10 (excluding sheet 10)
sheet1.col_values(0, 0, 5) # Take column 1, row 0~5 (excluding row 5)
sheet1.row_slice(2, 0, 2) # Get cell value type and content
sheet1.row_types(1, 0, 2) # Get cell data type

# -*- coding:utf-8 -*-

import xlrd
import os
from datetime import date, datetime

filename = "demo.xlsx"
filePath = os.path.join(os.getcwd(), filename)

print filePath

# 1. Open the file
x1 = xlrd.open_workbook(filePath)
sheet1 = x1.sheet_by_name("plan")

# column operations
print sheet1.row_values(0, 6, 10) # Take row 1, columns 6~10 (excluding sheet 10)
print sheet1.col_values(0, 0, 5) # Take column 1, row 0~5 (excluding row 5)
print sheet1.row_slice(2, 0, 2) # Get cell value type and content, same as sheet1.row(0)
print sheet1.row_types(1, 0, 2) # Get cell data type

output:

[u'', u'', 123.0, 42916.0]
[u'learning plan', u'\\编\\号', 1.0, 2.0, 3.0]
[number:1.0, text:u'\\管\\理\\学\\习']
array('B', [1, 1])

6. Specific cell reading
Get cell value:

sheet1. cell_value(1, 2)
sheet1.cell(1, 2).value
sheet1.row(1)[2].value

Get cell type:

sheet1.cell(1, 2).ctype
sheet1. cell_type(1, 2)
sheet1.row(1)[2].ctype

# -*- coding:utf-8 -*-

import xlrd
import os
from datetime import date, datetime

filename = "demo.xlsx"
filePath = os.path.join(os.getcwd(), filename)

x1 = xlrd.open_workbook(filePath)
sheet1 = x1.sheet_by_name("plan")

# Specific cell read
# value
print sheet1. cell_value(1, 2)
print sheet1.cell(1, 2).value
print sheet1.row(1)[2].value

#get type
print sheet1.cell(1, 2).ctype
print sheet1. cell_type(1, 2)
print sheet1.row(1)[2].ctype

7. (0,0) convert A1

xlrd.cellname(0, 0) # (0,0) converted to A1
xlrd.cellnameabs(0, 0) # (0,0) converted to $A$1
xlrd.colname(30) # Convert columns from numbers to letters

# -*- coding:utf-8 -*-

import xlrd
import os

filename = "demo.xlsx"
filePath = os.path.join(os.getcwd(), filename)

# open a file
x1 = xlrd.open_workbook(filePath)
sheet1 = x1.sheet_by_name("plan")

# (0,0) converted to A1
print xlrd.cellname(0, 0) # (0,0) converted to A1
print xlrd.cellnameabs(0, 0) # (0,0) converted to $A$1
print xlrd.colname(30) # convert the column from numbers to letters

output:

A1
$A$1
AE

8. Data type

empty: 0
String: 1
number: 2
Date: 3
Boolean: 4
error: 5

The following is the most complete software test engineer learning knowledge architecture system diagram in 2023 I compiled