GitHub open source: https://github.com/python-excel
2.xlrd module
2.1 Note
The xlrd module is a module library used by Python to read Excel files in ".xls" and ".xlsx" formats.
The API we mainly use is xlrd.open_workbook(filename): used to open our Excel workbook file [that is, the workbook object]
The main objects/classes used are: xlrd.book.Book, xlrd.sheet.Sheet, xlrd.sheet.Cell
Corresponding to Excel: workbook, worksheet[sheet], cell
The xlrd.book.Book object mainly uses 1 attribute and 4 methods: see section 2.3 for details.
The xlrd.sheet.Sheet object mainly uses 3 attributes and 6 methods: see section 2.4 for details
The xlrd.sheet.Cell object mainly uses 2 attributes and 1 method: see section 2.5 for details.
filename specifies the file path to open the file
on_demand--True loads forms on demand/False loads all forms directly [can be omitted]
2.3 Class xlrd.book.Book
Properties
Property
Description
wb.nsheet
The number of sheet objects contained in the workbook object
Function
Functions
Description
sheetlist[ ]=wb.sheets( )
Returns a list containing all sheet objects
sheetnamelist[ ]=wb.sheet_names()
Returns the list of all sheet objects Name
sheet = wb.sheet_by_index(0)
Use serial number to index sheet object
sheet = wb.sheet_by_name(name)
Index sheet objects by name
2.4 Class xlrd sheet.Sheet
Property
Property
Description
sheet.name
Form name
sheet.nrows
Maximum number of rows in the form
sheet.ncols
Maximum number of columns in the form
Function
Function
Description
cell= sheet.cell (n,m)
Returns the cell object of row n and column m
sheet.cell_value(n,m)
Returns the value of the cell object in row n and column m
sheet.cell_type(n,m)
Returns Type of cell object in row n and column m
cell_list[ ] = sheet.row(n)
Return A list of Cell objects in the entire nth row
sheet.row_value()
Returns the values of Cell objects in several columns in the specified row
sheet.row_type()
Returns the type of Cell object of several columns in the specified row
cell_list[ ] = sheet.col(n)
Returns a list of Cell objects in the nth column
sheet.col_value( )
Returns the value of Cell object in several rows in the specified column
sheet.col_type()
Returns the value of several rows in the specified column The type of Cell object of the row
2.5 Class xlrd.sheet.Cell
Property
Property
Description
cell.value
Return the value of the cell
cell.ctype
Return the type of the cell
Function
function
Description
cell.dump()
Print cell information
2.6 example
import xlrd
#Open workbbok
wb = xlrd.open_workbook("workbookname.xlsx")
#Check how many worksheets there are
print(f'workbook has {<!-- -->wb.nsheets} sheets')
print(f'The name of the sheet in Excel is: {<!-- -->wb.sheet_names()}')
print(wb.sheets())
#Three ways to get worksheet
sh1 = wb.sheet_by_index(0)
sh2 = wb.sheet_by_name('sheetname')
sh3 = wb.sheets()[0]
#View worksheet object
print(sh1)
print(sh2)
print(sh3)
#Sheet name, how many rows and columns
print(f'The name of the sheet is {<!-- -->sh1.name}')
print(f'sheet has {<!-- -->sh1.nrows} rows')
print(f'sheet has {<!-- -->sh1.ncols} columns')
#Get the value of Cell
print(f'The data in the first row and third column is {<!-- -->sh1.cell_value(0,2)}')
print(f'The data in the first row and third column is {<!-- -->sh1.cell(0,2).value}')
print(f'The cell type in the first row and third column is {<!-- -->sh1.cell_type(0,2)}')
#Get the value of row & col
print(sh1.row_values(0))
print(sh1. col_values(1))
print(f'The cell type of the first row is {<!-- -->sh1.row_types(0)}')
print(f'The cell type of the second column is {<!-- -->sh1.col_types(1)}')
#Loop through all data
for r in range(sh1.nrows):
for c in range(sh1.ncols):
print(f'The data in row {<!-- -->r} and column {<!-- -->c} is {<!-- -->sh1.cell(r,c)}\ ')
3.xlutils.copy module
nwb = copy(wb)
Convert xlrd.book.Book object to xlwt.workbook.Workbook object
example
import xlrd
from xlutils.copy import copy
#read workbook
wb = xlrd.open_workbook('workbookname.xlsx')
#Output the object type of workbook in xlrd
print(f"The object type of xlrd's workbook is: {<!-- -->type(wb)}")
#Convert the object type of workbook
nwb = copy(wb)
#Output the object type of the new workbook
print(f"The object type of newworkbook after conversion is: {<!-- -->type(nwb)}")
The output result is as follows:
4.xlwt module
4.1 Note
xlwt is used by Python to create and write Excel documents in '.xls' format.
The main API is xlwt.Workbook() used to create a workbook object.
Mainly use xlwt.XFStyle() to set the cell format, font, border, alignment, background and protection. See 4.5 for details.
The main objects/classes used are: xlwt.Workbook.Workbook, xlrd.Worksheet.Worksheet
Corresponding to Excel respectively: workbook, worksheet[sheet]
The xlrd.book.Book object mainly uses three methods: see section 4.3 for details.
The xlrd.sheet.Sheet object mainly uses one method: see section 4.4 for details.
4.2 xlwt.Workbook()
wb= xlwt.Workbook() #Create a new workbook object
4.3 Class xlwt.workbook.Workbook
function
Description
wb.save(Filename)
Save workbook object
wb.add_sheet(sheetname,cell_overwrite_ok=False)
Create a new sheet object-cell_overwrite_ok=False[can be omitted ]
sheet=wb.get_sheet(index/sheetname)
Get the sheet object based on the index or table name
4.4 Calss xlwt.worksheet.Worksheet
sheet.write(r,c,label='',style=<xlwt.style.xFstyleobject>)
r - the row number of the cell
c - the column number of the cell
label - the data value to be written
style - the format applied to the cell [can be omitted]
4.5 xlwt.XFStyle()
#Set the height of the cell
sh.row(3).height_mismatch = True
sh.row(3).height = 10*256
#Set the width of the cell
sh.col(3).width = 20*256
#Method 2: Create font object
font = xlwt.Font()
#Set object properties
font.name = 'Microsoft Yahei' #Font format
font.colour_index= 4 #Font color
font.height = 12*20 #Font size *20 as unit
font.bold = True #Font bold
font.underline = True #Font underline
font.italic= True #Font italic
#Assign the font object to style
style = xlwt.XFStyle()
style.font = font
#Create Alignment object
Alig=xlwt.Alignment()
#Set object properties
Alig.horz = 2 #Horizontal centering
Alig.vert = 1 #Vertically centered
#Assign the Align object to style1
style1 = xlwt.XFStyle()
style1.alignment = Alig
#Create border object
border = xlwt.Borders()
#Set object properties
border.left = 1
border.right = 1
border.top = 1
border.bottom = 1
border.left_colour = 1
border.right_colour = 2
border.top_colour = 3
border.bottom_colour = 4
#Assign the Align object to style1
style2 = xlwt.XFStyle()
style2. borders = borders
