xlwt library-write data
Common methods
| Function name | Meaning |
| xlwt.Workbook() | Create a new Excel file |
| xlwt.add_sheet(sheet_name) | Create a new workbook |
| sheet.write(row,col,data) | Write data into cells |
| workbook.save(path ) | Save data to file |
""" xlwt writes data """
import xlwt
# Create a new Excel file
wb = xlwt.Workbook()
# Create a workbook
ws = wb.add_sheet("Test Sheet")
# Write content into the cell
ws.write(0,0,"Cell(1,A1)")
ws.write(1,0,"Cell(2,A1)")
ws.write(2,0,"Cell(3,A1)")
ws.write(0,1,"cell(1,B1)")
ws.write(0,2,"Cell(1,C1)")
ws.write(2,2,"123")
# save document
wb.save(r"./xlwttest.xlsx")
Document writing effect:
Set font style
Commonly used methods and properties
| Function name&properties | Meaning | Function name & attributes | Meaning | |
| xlwt.Font() | Create font style | xlwt.Borders() | Create border style | |
| font.name | Set font type | |||
| td> | border.left |
Set left border |
||
| font.color_index | Set font color | border.right | Set right Border | |
| font.height | Set the font size | border.top | Set the top border | td> |
| font.bold | Set the font to be bold | border.bottom | Set the bottom border | |
| font.underline | Set font underline | border.left_colour | Set left border color | |
| font.italic | Set the font italic | border.right_colour | Set the right border color | |
| xlwt.Alignment() | Create font position style | border.top_colour | Set the top border color | |
| alignment.horz |
Set font horizontal position 1 left 2 middle 3 right |
border.bottom_colour | Set the bottom border color | |
| alignment.vert |
Set font vertical position 0 up 1 middle 2 down |
xlwt.Pattern() | Create background color style | |
| row.height_mismatch |
Set whether to start row height set up True is on |
pattern.pattern | Set background style | |
| row.height |
Set the cell row height, The value is 256*pixels |
xlwt.XFStyle() |
Set the overall style object, for applying to cells |
|
| col.width |
Set cell width, The value is 256*pixels |
xlwt.easyxf() | Set the total style object |
import xlwt
#Create excel file
wb = xlwt.Workbook()
# Create a workbook
ws = wb.add_sheet('Style Test')
#Create a font style
font = xlwt.Font()
font.name = "Microsoft Yahei" # Set font
font.height = 30*20 # Set font size
font.bold = True # Font bold
font.underline = True # Underline
font.italic = True # italic
font.colour_index = 2 # Set font color
#Create a style object
style = xlwt.XFStyle()
style.font = font
ws.write(1,1,"Bamboo tube rice")
ws.write(1,3,"Bamboo Tube Rice",style)
wb.save("./style test.xlsx")
Set border style
import xlwt
#Create an excel file
wb = xlwt.Workbook()
# Create a workbook
ws = wb.add_sheet("Border Test")
#Create a border style
border = xlwt.Borders()
"""
Thin solid line: 1 Small thick solid line: 2
Thin dashed line: 3 Medium thin dashed line: 4
Thick solid line: 5 Double line: 6
Thin dotted line: 7
"""
border.top = 1
border.bottom = 6
border.left = 5
border.right = 7
# Set the line color
border.top_colour = 3
border.bottom_colour = 4
border.left_colour = 5
border.right_colour = 2
#Create a style object
style = xlwt.XFStyle()
style.borders = border
ws.write(1,1,"Bamboo tube rice")
ws.write(1,3,"Bamboo Tube Rice",style)
wb.save("./border test.xlsx")
Set content position and background color
import xlwt
#Create an excel file
wb = xlwt.Workbook()
# Create a workbook
ws = wb.add_sheet("position and background color test")
#Create a position style
align = xlwt.Alignment()
# Set the upper and lower positions
# 0 up 1 middle 2 down
align.vert = 1
# Set left and right positions
# 1 left 2 middle 3 right
align.horz = 2
# Set cell height
ws.row(1).height_mismatch = True # Enable permission to set height
ws.row(1).height = 50*50
ws.col(1).width = 50*50
#Create a color style
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # solid color
pattern.pattern_fore_colour = 5
#Create a style object
style = xlwt.XFStyle()
style.alignment = align
style.pattern = pattern
# Set the style through easyxf()
style1 = xlwt.easyxf('font:bold on,colour_index 2;align:vert center,horiz center')
ws.write(1,1,"Bamboo tube rice")
ws.write(1,3,"Bamboo Tube Rice",style)
ws.write(1,5,"Bamboo Rice",style1)
wb.save("./position and background color test.xlsx")
xlrd library-read data
Commonly used methods and properties
| Function name & amp;Attributes | Meaning |
| xlrd.open_workbook (path) | Open an excel file |
| workbook.nsheets | Get the number of excel workbooks |
| workbook.sheets() | Get all workbooks in excel |
| workbook.sheet_names() | Get the workbook name of excel |
| sheet.sheet_by_name(name) | Get the workbook based on the workbook name |
| sheet.sheet_by_index(num) | Get the num workbook |
| sheet.nrows | Get the work Total number of rows in the book |
| sheet.row_values(num) | Get the num row data |
| sheet .col_values(num) | Get the num column data |
| sheet.cell(row,col) | Get the specified cell |
| sheet.row(num)[col] | Get the specified cell |
| cell. ctype | Get the data type of the cell content |
| sheet.cell_value(row,col) | Get the value of the specified cell |
| cell.value() | Get the value of the cell |
Table example:
import xlrd2
#Open excel file
wb = xlrd2.open_workbook("./xlrdtest.xlsx")
# Get the number of excel workbooks
print(wb.nsheets) # 3
# Get all workbooks, traverse and print
all_ws = wb.sheets()
for i in all_ws:
print(i)
"""
Sheet 0:<Sheet1>
Sheet 1:<Sheet2>
Sheet 2:<Sheet3>
"""
# Get all workbook names
ws_names = wb.sheet_names()
print(ws_names) # ['Sheet1','Sheet2','Sheet3']
# Get the workbook based on the workbook name
ws1 = wb.sheet_by_name("Sheet1")
print(ws1) #Sheet 0:<Sheet1>
# Get the workbook based on the workbook index
ws2 = wb.sheet_by_index(2)
# Get the total number of rows in the workbook
sheet1_rows = ws1.nrows
print(sheet1_rows) # 7
# Get the data of row 1 of the workbook
sheet1_row_data = ws1.row_values(1)
print(sheet1_row_data) # ['A2', 'B2', 'C2', 'D2', '']
# Get the data in column 1 of the workbook
sheet1_col_data = ws1.col_values(1)
print(sheet1_col_data) # ['B1', 'B2', 'B3', 'B4', 'B5', 'B6', '']
# Get the data of cell E7
cell_E7 = ws1.cell(6, 4)
print(cell_E7) # text:'E7'
# Get the data type of cell content
print(cell_E7.ctype) # 1
# Get the data of the cells in row 1 and column 2
print(ws1.row(0)[1]) # text:'B1'
print(ws1.row(0)[1].value) # B1
# Get the value of the cell
print(cell_E7.value) # E7
# Get all cell data
for row in range(ws1.nrows):
for col in range(ws1.ncols):
print(f"{row} row {col} column data: {ws1.cell_value(row,col)}")
xlutils-update data
Common methods
| Function name | Meaning |
| xlutils.copy.copy(workbook) | Copy the specified excel file |
Table example: Complete the data in the red box
import xlrd2
from xlutils.copy import copy
#Read excel
wb = xlrd2.open_workbook("./xlrdtest.xlsx")
#Copy excel
wb_copy = copy(wb)
""" Start modifying data """
# Get the first Sheet
ws = wb_copy.get_sheet(0)
# Add data
for row in range(0,6):
ws.write(row, 4, f"E{row + 1}")
for col in range(0,4):
ws.write(6, col, f"{chr(ord('A') + col)}7")
wb_copy.save("./xlrd test-copy.xlsx")
Update effect: Newly generated xlrd test-copy.xlsx
