The modules used to operate excel in python that I have come across so far include: xlrd and xlwt. The former is used to read excel, and the latter is used to write operations. Let’s talk about the xlrd module first.
1. Installation of xlrd module
Open the cmd window, enter pip install xlrd and the installation is complete. Please note: the latest version of xlrd is installed by default, but the latest version no longer supports .xlsx files, so you need to use the following commands to uninstall and install Version 1.2:
pip uninstall xlrd #Uninstall the installed version pip install xlrd==1.2.0 #Install version 1.2.0
2. Use of xlrd module
1. Import the module and open the .xlsx workbook
import xlrdwb = xlrd.open_workbook(filename)#filename is the path name of the file, as follows workbook = xlrd.open_workbook(filename=r'C:\Users\Windows10\Desktop\xlsx file.xlsx\ ')#open_workbook returns an object
On Windows, the file path name uses backslashes, and backslashes have an escaping effect in Python, so add r in front of the string to represent the original string so that the path name does not generate Escape; in addition, if the path contains Chinese, you also need to add r
In fact, you can also use slashes to indicate the path name when calling the open_workbook function. In this case, there is no need to add r, as follows:
wb = xlrd.open_workbook('C:/Users/cheng/Desktop/test.xlsx')
2. Get the sheet that needs to be operated
import xlrdwb = xlrd.open_workbook('C:/Users/cheng/Desktop/test.xlsx')#1. Get the first sheet through the index sheet1 = wb.sheets()[0]# 2. Get sheet2 through index order = wb.sheet_by_index(1)#3. Get sheet3 through sheet name = wb.sheet_by_name('SoC')## The above three functions will return an xlrd.sheet.Sheet() Object #4. Get all sheet names in wb, and return a list names = wb.sheet_names()print(names)# Determine whether a certain sheet exists in wb, return bool value wb.sheet_loaded(sheet_name or index)#Pass sheet name index is_exist = wb.sheet_loaded('SoC')print(is_exist)# Index through sheet index is_exist = wb.sheet_loaded(0)print(is_exist)
The above running results are as follows:
3. Row operations and column operations
Common operations for rows and columns:
#Get the number of valid rows and columns in the sheet, please note that there is no () after row = sheet3.nrowscol = sheet3.ncolsprint(row, col)#Get the number of columns of data in a row, which is actually the number of valid columns num = sheet3.row_len(2)print(num)#Get the data of all cells in the specified row or column and return the data as a list table_list = sheet3.row_values(rowx=1, start_colx=1, end_colx= None)print(table_list)table_list = sheet3.row_values(1)print(table_list)# rowx indicates which row of data is obtained # start_colx indicates which column to start obtaining, end_colx indicates which column to end obtaining, end_colx is None Indicates that there is no limit at the end table_list = sheet3.col_values(colx=0, start_rowx=0, end_rowx=None)print(table_list)table_list = sheet3.col_values(2)print(table_list)# colx indicates which column of data is to be obtained# start_rowx It means starting from the index, end_rowx means ending from the index, end_rowx is None, means there is no limit to the end #Get a list of all cell objects in the row, the format of each element is type: value value = sheet3.row( 0)print(value)# Get the value and type of all cells in a column, form a list and return value = sheet3.col(1)print(value)# Get the value of the cell in a row in the form of a slice and Its type, the first parameter is the number of rows, the second and third parameters are slice parameters, indicating the obtained column number, left closed and right open value = sheet3.row_slice(1, 1, 3)print(value)# Get the cell value and type of a certain column in the form of a slice. The first parameter is the number of columns. The second and third parameters are slice parameters, which represent the obtained row number. Left closed and right open value = sheet3. col_slice(1, 1, 3)print(value)# Get the type of each cell in a certain row value = sheet3.row_types(1)print(value)print(type(value))# Get the type of each cell in a certain column The type value of a cell = sheet3.col_types(1)print(value)print(type(value))
The running results are as follows:
Row-specific operations:
# Get all generators of sheet and return a list. Each element in the list is a list composed of cell objects in each row row = sheet3.get_rows()for one in row: print(one)
The running results are as follows:
4. Cell operations
#Get the value in the cell print(sheet3.cell(0, 0).value)print(sheet3.cell_value(0, 0))#Get the data type of the cell print(sheet3.cell_type(0, 0) )
The running results are as follows:
Need to add here
There are 5 types of ctypes returned by python when reading the contents of cells in excel:
- 0 empty
- 1 string(text)
- 2 numbers
- 3 dates
- 4 boolean
- 5 errors
That is, the ctype of date=3. In this case, you need to use xlrd’s xldate_as_tuple to process it into date format. Only when the ctype of the table is determined is 3, can xldate start the operation;
Reader benefits: If you are interested in Python after reading the article, then this set of python learning materials will definitely be useful to you
For beginners with zero basic knowledge:
If you are a novice and want to get started with Python quickly, you can consider it.
On the one hand, the learning time is relatively short and the learning content is more comprehensive and focused.
The second aspect is that you can plan your study plan and direction based on these materials.
If you need a complete set of Python introductory + advanced learning resource packages, you can click to get it for free (if you encounter problems with scanning the QR code, you can leave a message in the comment area to get it)~
CSDN gift package: “Python entry & advanced learning resource package” free sharing
Friends, if necessary, you can scan the CSDN official certification QR code below on WeChat to receive it↓↓↓
Python learning gift package
Python entry to proficiency memorization manual
Python installation package
Python crawler cheats
Complete set of Python data analysis resources
A complete tutorial on office automation using Python
Python interview highlights and resume templates
Python side job part-time route
Data collection
The above-mentioned complete version of the complete set of Python learning materials has been uploaded to CSDN official. If you need it, friends can scan the CSDN official certification QR code below on WeChat to get it↓↓↓
CSDN gift package: “Python entry & advanced learning resource package” free sharing