Summarize
txt -----(automated log)
xlrd only supports reading. xlsx, xls. If there is none, create a new one, and if there is one, modify it. index starts from 0
xlwt only supports writing. xlsx, xls. If there is none, create a new one, and if there is one, modify it. index starts from 0
openpyxl supports reading and writing without overwriting the original content, only supports xlsx, and must operate an existing excel file. Interface Automation - Data. index starts from 1
Summary: open file, read/write content, location, save and exit
from openpyxl import load_workbook # Can read and write existing Excel files
from openpyxl import workbook # can create a new Excel file
# Use load_workbook to realize Excel reading and writing
# 1. Open the Excel file (exists, cases.xlsx is the file name),
wb = load_workbook("cases.xlsx")
# An Excel file consists of those: file name, form, cell
# The first parameter is the file name, which will return a workbook object (equivalent to the entire Excel file)
# 2. Position the form
# method one:
# ws = wb['multiply'] # ws is the worksheet object (equivalent to a form in Excel, multiply is the name of the first sheet in Excel)
# Method Two:
ws = wb.active # get the first form
# 3. Position the cell
one_cell = ws.cell(row=2,column=2) # one_cell is a cell object (equivalent to a cell in the form, get the data of the second row and second column) # without adding value The result is displayed as: <Cell 'login'.A1>
print(one_cell. value)
1. Python operation txt file – write log
file operation
Python provides the file (file) method, which is often used in our daily work. For example, we want to write the automated log into a file, modify the content of the file, etc. Use the method provided by file to achieve. The Python open() method is used to open a file and return the file object, which is needed in the process of processing the file Format: open(file_name, access_mode) 1.file_name: file path, required parameter. 2.access_mode: The mode of opening the file. Read-only, write, append, etc., the default access mode of the file is read-only (r).
Access mode parameter
Write read-write append, read-only. read and write
access_mode parameters are: w Open a file only for writing, if the file exists, clear the text content and then write, otherwise write the content after creating a new file wb opens a file in binary format for writing only w + Open a file for reading and writing. If the file exists, clear the text content and then write, otherwise write the content after creating a new file a Open the file to add content, if there is a file, it will be added after the original content of the file, otherwise it will be newly written a + opens a file for reading and writing. If there is a file, it will be added after the original content of the file, otherwise it will be newly created for reading and writing ab Open a file in binary format for appending, if there is a file, it will be added after the original content of the file, otherwise it will be newly written ab + opens a file in binary format for reading and writing. If there is a file, it will be added after the original content of the file, otherwise it will be newly created for reading and writing r Opens the file read-only. The pointer to the file will be at the beginning of the file. This is the default mode. rb opens a file in binary format for reading only. Generally used for non-text files such as pictures, etc. r + open a file for reading and writing (the file pointer will be at the beginning of the file)
Common methods of file operation
The process of using the file file: 1. Open the file--2. Read or write--3. Close the file
1. Open the file: f=open(file name, mode)
2. Write: f.write (written text content)
3. Read: f.read([number of characters]) f.readline() f.readlines()
4. Close the file: f.close()
5.tell() : the current location of the file, that is, tell is to get the location of the file pointer
6.seek() method format: move the file reading pointer to the specified position #seek(0): the first
ValueError: I/O operation on closed file. The file is closed
Summary: The w mode will cover the content, and the a mode will add new content to the original content
The difference between + is that it can be read and written, pay attention to the position of the mouse pointer
write:
read:
a
b: represents binary
code example
1-w mode, write
Open a file with mode set to w.
w: Indicates that a file is opened for writing only. If the file already exists, the file will be opened and edited from the beginning, ie the original content will be deleted. If the file does not exist, create a new file.
write(): method of writing text content
Case 1: Open a non-existent file for writing, write data, and close the file
Case 2: Open an existing file for writing, write new data, and close the file
Summary: The w and w + modes are the same, after clearing the file, write new content.
File operation w mode: overwriting write, the mouse pointer is at the end after writing
file=open(file='./test.txt', mode='w + ', encoding='utf-8') #file represents a file object txt, excel
file.write('456')
file. seek(0)
print(file. tell())
print('Read all content:', file.read())
file. close()
2-a mode, append. a + : read + write
Open a file with mode set to a:
a: Open a file for appending. If the file already exists, the file pointer will be placed at the end of the file. That is, the new content will be written after the existing content. If the file does not exist, a new file is created for writing.
write(): method of writing text content
Case 1: Open a non-existent file for writing, write data, and close the file
Case 2: Open an existing file by writing, request to write new data without overwriting the original data, close the file
Summary: a is the same as a + mode, adding new content after the original content.
file=open(file='./test.txt', mode='a + ', encoding='utf-8') #file represents a file object txt, excel
# file.write('\
The second line of data')
# file.write('\
The third line of data')
file.write('hjh')
tell=file.tell()#Get the file pointer position
print(tell)
file. seek(0)
print('Read all content:', file.read())
file. close()
3-r mode – read
file=open(file=’./test.txt’, mode=’r + ‘, encoding=’utf-8’) #file represents a file object txt, excel res1=file.read() #read all The content of the file, the parameter n indicates the specified length print (‘all the content of the file:’, res1, type (res1))
Open a file with mode set to r:
r: means to open the file in read-only mode. The file pointer will be placed at the beginning of the file. This is the default mode, r can be omitted. In this mode, there is no write permission, only the text content can be viewed, and the text content cannot be rewritten.
read(n): read the content of the specified byte length, do not specify the length, read all the content by default
readline(): read the file content line by line
readlines(): Read all the contents of the file and return it to a list
Case 1: Read the content of 2 characters in the t4.txt file
Case 2: Read the content of the first line in the t4.txt file
Case 3: Read all the content in the t4.txt file
file=open(file='./test.txt', mode='r + ', encoding='utf-8') #file represents a file object txt, excel
res1=file.read() #Read all the content, the parameter n indicates the specified length
print('All content of the file:', res1, type(res1))
print(file. tell())
file.write('111')
print(file. tell())
res2=file.readline() #Read a line of content, #The result is a string <class 'str'>
print('Read by line:', res2)
res3=file.readlines() #The result is a list ['append 3\
', 'append 4222'] <class 'list'>
print('Read all lines:', res3)
for line in file. readlines():
print(line)
4- r + mode, read and write
For the read and write mode, it must be read first and then write, because the cursor is at the beginning by default, and then write after reading. The mode with the highest frequency is r + Note: In r + mode, if the content is read, no matter how much content is read or what the cursor displays, the operation will be performed at the end when writing or operating the file. Case 1: Read all the content in the t4.txt file and add 'web automation test' at the end Thinking: After adding new content, if you read all the content in the file again file.seek(): move the pointer
Summary – read()/readline()/readlines()
Summary:
read(): The disadvantage is that when the file is very large, all the content in the file is read and stored in the memory, which will cause the memory to crash.
readline(): The read data has a \
at the back, and it will automatically wrap after reading. ! ! ! ! ! Are you sure? ? ?
readlines(): If the file is large, it takes up memory and is prone to crash. \
Large file read:
If the file is very large, it is not recommended to use the two methods of read() and readlines(). The above methods read all the files into the memory at one time.
It is recommended to use readline(), or loop
Reading and writing cannot exist at the same time, otherwise readline and readlines will be empty and cannot be read
file=open(file='./test.txt', mode='r + ', encoding='utf-8') #file represents a file object: txt, excel
# res1=file.read() #Read all the content, the parameter n indicates the specified length
# print('All content of the file:', res1, type(res1))
# print(file. tell())
# file. write('111')
# print(file. tell())
res2=file.readline() #Read a line of content,
print('Read by line:', res2)
res3 = file. readlines()
print('Read all lines:', res3)
for line in file. readlines():
print(line)
Summary of file mode
PPT has pictures
with..as
By using with…as… instead of closing the file manually. Automatically close the file when the content is executed. Execute one and it is closed, and the following code blocks are not executed.
When python operates a file, it needs to open the file, and finally close the file manually.
Grammatical structures:
with open(path, mode) as variable:
code block
code:
with open('test.txt', mode='r') as file:
print(file.readlines()) returns a list. #['1291318937\
', '12\
', '13\
', '111']
2. xlrd/xlwt operate Excel files
Excel operation
Data processing is a major application scenario of Python, and Excel is the most popular data processing software. Therefore, when doing data-related work with Python, it is inevitable to deal with Excel Commonly used libraries are the python-excel series: xlrd (read), xlwt (write), xlutils, openpyxl 1.xlrd - read Excel files, support .xls and .xlsx formats 2.xlwt - write Excel files, support .xls and .xlsx formats 3.xlutils - practical tools for manipulating Excel files, such as copying, splitting, filtering, etc. 4. openpyxl - You can read files, write files, and modify files; however, the openpyxl library does not support Excel documents in xls format. If you want to read and write documents in xls format, you can use Excel to convert the corresponding format, or Use the xlwt library for write operations.
xlwt/xlrd library installation
1. Install the xlwt library: pip3 install xlwt pip install xlwt pip3 install xlwt –i http://pypi.douban.com/simple/ 2. Install the xlrd library: pip3 install xlrd pip install xlrd pip3 install xlrd –i http://pypi.douban.com/simple/ 3. View installed libraries: pip3 list pip list pip show xlwt
2.1 xlwt write operation to xls
xlwt is a toolkit used by Python to write data and format data in Excel. It is used to generate compatible Excel 97/2000/XP/2003 and only supports .xls format. Only supports writing, not reading.
2.1.1xlwt creates xls files and writes data
import xlwt
book=xlwt.Workbook(encoding='utf-8')
sheet_text=book.add_sheet('login')
sheet_text.write(0,0,'username')
sheet_text.write(0,1,'password')
sheet_text.write(0,2,'phone')
book.save('./LOGIN.xlsx')
Case 1: Create a new .xls file, rename the sheet to info, write data, and save it as login.xls
xlwt creates an xls file and writes the operation steps:
1. Import package: import xlwt
2. Create an object (.xls file): book=xlwt.Workbook(encoding='utf-8')
3. Add a sheet worksheet named username_passwd: sheet_test=book.add_sheet('username_passwd')
4. Add content:
Method 1: Add a value to each cell, and the cell row and column start from 0 respectively
# sheet_test.write(0,0,'username')#3. Add a value to each cell, the cell row and column start from 0 respectively (0,0) represents the cell in the first row and first column
# sheet_test.write(0,1,'password')#4. Add a value to each cell, the cell row and column start from 0 respectively (0,0) represents the cell in the first row and first column
# sheet_test.write(0,2,'phone')#3. Add a value to each cell, the cell row and column start from 0 respectively (0,0) represents the cell in the first row and first column
Method 2: Add value to each row
row2=['muzi','123456']
for i in range(len(row2)):
sheet_test.write(1,i,row2[i]) #The second row 1.0 1.1 ,1.2
5. Save the file: book.save(filename.xls)
2.1.2xlwt opens the existing xls file and appends and writes data to be used in conjunction with xlutils (rarely used)
Python can use xlrd to read excel, use xlwt to write excel, but if you want to write data into existing excel, you need another library xlutils to use together. General idea: 1. Open the existing xsl file with xlrd.open_workbook 2. Pay attention to adding the parameter formatting_info=True to save the format of the previous data 3. Then use from xlutils.copy import copy;, and then copy to copy a copy from the opened xlrd Book variable to become a new xlwt Workbook variable 4. Then for the Workbook variable of xlwt, it is normal: 5. Obtain the corresponding sheet through get_sheet, after getting the sheet variable, you can write new data into the sheet 6. After writing the new data, finally save it Case 2: Open the file userinfo.xls, write data, and save the source file
Open the file userinfo.xls, write data, and save
import xlrd
from xlutils.copy import copy
rb = xIrd.open_workbook('userinfo.xls', formatting_info=True)
wb = copy(rb)
# Get sheet by index, index starts from 0
ws = wb. get_sheet(0)
# Get the sheet by the sheet name, the sheet name must be unique
# ws = wb. get_sheet('teacher info')
ws.write(4.0."hello world')
wb.save('userinfo.xIs')
2.2xlrd library reads excel
Using xlrd can easily read the content of excel files, and this is a cross-platform library that can be used on windows, linux/unix, and other platforms. xlrd supports reading Excel files in .xls and .xlsx formats, `only supports reading, does not support writing`. Notice: Using xlrd to read the data of the .xlsx file has an error (raise XLRDEroor...xlrd.biffh.XLRDError: Excel xlsx file; not supported), the reason is that the 2.0.1 version of xlrd does not support the reading of xlsx format data, solve it Method: Uninstall the 2.0.1 version of xlrd and reinstall the specified version (1.2.0) of xlrd The command to specify the version installation library: pip install xlrd==1.2.0
xlrd reads xls/xlsx files, xlrd: only supports reading, does not support writing, specify version 1.2.0 during installation
xlrd reads xls/xlsx file operation steps:
1. Import package: import xlrd
2. Open a file (.xls or .xlsx file): book=xlrd.open_workbook('login.xls')
3. Use the sheet worksheet:
sh1=book.sheet_by_name('login') or
sh1=book.sheet_by_index(0) The index of the worksheet starts from 0
4. Read the attribute information of the sheet worksheet
Current sheet name: sh1.name;
The total number of rows of the current sheet: sh1.nrows;
The total number of columns in the current sheet: sh1.ncols
All sheet names of xls/xlsx files: book.sheet_names()
5. Read the text content stored in the sheet worksheet
1) Read a row: row1=sh1.row_values(0)
2) Read a column: col1=sh1.col_values(0)
3) Read a certain cell:
cell1=sh1.cell_value(0,0)
cell2=sh1.cell(0,1).value
Case 1: Read the contents of userinfo.xls and api.xlsx files respectively
the code
import xlrd
excel=xlrd.open_workbook(filename='./test.xlsx') #Open a file (.xls or .xlsx file)
sheet_test=excel.sheet_by_name('test1') #use the sheet worksheet named test1
sheet_test1=excel.sheet_by_index(0) #Use the sheet worksheet, 0 represents the first table
print(sheet_test.name,sheet_test1.name)
sheet_all=excel.sheet_names() #xls/xlsx file all sheet names: a list is returned
print(sheet_all)
The total number of rows of the current sheet: sh1.nrows; the total number of columns of the current sheet: sh1.ncols
print(sheet_test.nrows,sheet_test.ncols)
Take a row: row1=sh1.row_values(0) #first row
print(sheet_test.row_values(1,0,3)) #Read the second row, from the first to the third end. Left close and open. Back to list
sh1.cell_value(0,0) #read a certain cell
print(sheet_test. cell_value(1,1))
3. Openpyxl operates Excel files
‘openpyxl: can read and write, and will not overwrite the original data:
The difference with xlrd, xlwt
Only supports xlsx format, supports excel function formula
Must operate an existing excel file
Indexes start at 1! ! ! ! ! ! ! ! Pay attention to the index! ! ! ! ! ! ! ! ! ! ! ! ! ! !
openpyxl install
Install the openpyxl library: pip3 install openpyxl pip install openpyxl pip3 install openpyxl –i http://pypi.douban.com/simple/
openpyxl reads and writes xlsx files
The openpyxl module is a Python library for reading and writing Excel 2010 documents (.xlsx format). If you want to process Excel documents (.xls) in earlier formats, you need to use additional libraries. openpyxl is a relatively comprehensive tool. Ability to read and modify Excel documents simultaneously. Many other Excel-related projects basically only support the function of reading or writing Excel. openpyxl does not support .xls format, but supports reading and writing in .xlsx format, and supports writing formulas, etc.
3.1: openpyxl step – read
1. Import package: import openpyxl
2. Open the file (.xlsx file): book = openpyxl.load_workbook(filename)
3. Use the sheet worksheet: sh1=book.active or sh1=book['Sheet1']
4. Read the attribute information of the sheet worksheet
The name of the current sheet: sh1.title
The total number of rows of the current sheet: sh1.max_row
The total number of columns of the current sheet: sh1.max_column
Read cell: cell1 = sh1['A1'].value A1 represents table A row 1 column (A,1)
sh1.cell(row=1, column=1).value represents table A row 1 column (A,1)
All sheet names of the xlsx file: book.sheetnames
The operation flow of openpyxl reading xlsx file data:
5. Read data
1) Read by cell: cell1 = sh1['A1'].value or cell2= sh1.cell(1,2).value
Row and column index values start from 1
3.2-openpyxl opens xlsx file to write data
openpyxl opens xlsx file to write data operation process: 1. Import package: import openpyxl 2. Open the file (.xlsx file): book = openpyxl.load_workbook(filename) 3. Use the sheet worksheet: sh1=book.active or sh1=book['Sheet1'] 4. Write data 1) Cell writing: sh1['F2'] = 'PASS' row and column index values start from 1 2) Write the whole line: new_row = ['post-xml interface', 'post', 'https://httpbin.org/post'] sh1.append(new_row) 5. Save the file: book.save(filename.xlsx) Case-openpyxl opens xlsx file to write data
Case code: openpyxl step – write one or one line of data
import openpyxl
book = openpyxl.load_workbook('test.xlsx') #Open the file (.xlsx file), book table excel object
# #sheet_test=book.active #book.active # Get the first form
sheet_test=book['test1'] #book object (quite like a form in Excel
# print(sheet_test.title) #table name
# #4. Write data
# Writing method 1: sheet_test['F4'] = 'daomingsi'
# Writing method 2: sheet_test.cell(1,2).value='success'
#
# #Write a row of data
# new_row = ['post-xml interface', 'post', 'https://httpbin.org/post']
# sheet_test.append(new_row)
#
# #5. Save data
# book.save(filename='test.xlsx')
3.3-openpyxl creates a new xlsx file to write data
Openpyxl creates a new xlsx file and writes data into the operation process: 1. Import package: import openpyxl 2. Create a new workbook object: wb=openpyxl.Workbook() 3. Create a new worksheet: sheet=wb.create_sheet(**index=0,title='First Sheet'**) 4. Write data 1) Cell writes: sh1['F2'] = 'PASS' or sh1.cell(3,6).value='FAIL' row and column index values start from 1 2) Write the whole line: new_row = ['post-xml interface', 'post', 'https://httpbin.org/post'] sh1.append(new_row) 5. Save the file: book.save(filename.xlsx) code-openpyxl create xlsx file write data Case 2: Create a new xlsx file, write data (custom), and save it as login.xlsx
Report an error
xlwt: Excel table writing, does not support reading, overwrite writing, not appending Error: [Errno 13] Permission denied: './test.xlsx' indicates the state of your file open, save and close the file first
Exercise
1.homework: write a column of data ['a', 'b', 'c',]
2. Read the excel file through xlrd
username password phone
admin 123456 1638749385094
Write a function that returns the value
[
{
"username": "admin",
"password": "123456",
"phone":"1638749385094",
},
]