catalogue
openpyxl package in python can be used to operate Excel
openpyxl package in python can be used to operate Excel
Import openpyxl package
import openpyxl
Workbook operation
The workbook is an Excel document. In the example, wb is used, that is, workbook
1. Create a new workbook
a=openpyxl.Workbook("/Users/practice/1.xlsx")The new workbook cannot be used directly. It needs to be saved and reopened
2. Save Workbook
a.save("/Users/practice/1.xlsx")3. Open an existing workbook
wb = openpyxl.load_workbook("/Users/practice/1.xlsx")4. Get the default worksheet
ws = wb.active
5. Get worksheet by name
ws = wb['Sheet']
6. Get worksheets in order
ws = wb.worksheets[0]
7. Get all worksheets
ws = wb.worksheets
Worksheet operation
The worksheet is the sheet page in the Excel document. In the example, ws is used, that is, worksheet
1. Create a worksheet
ws = wb.create_sheet('Sheet3')2. Delete worksheet
wb.remove(wb['Sheet3'])
3. Get worksheet name
ws.title
4. Copy worksheet
ws = wb.copy_worksheet(wb["Sheet1"])
5. Modify worksheet name
ws.title = 'fuZhi'
Cell operation
1. Get the content of a cell
# Method 1 a = ws.cell(row=1, column=1).value # Method 2 b = ws['A1'].value
2. Get a range of cells
A cell to a cell
ws.['A1:C10']
Some lines
ws.['1:10']
Some columns
ws.['A:C']
List slice, second row to last row
list(ws.values)[2:]
Start from a row and end at a row, start from a column and end at a column. Values are taken by row
ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=10)
Start from a row, end from a row, start from a column, end from a column, and value by column
ws.iter_cols(min_row=1, max_row=10, min_col=1, max_col=10)
All rows in the worksheet
ws.rows
All columns in the worksheet
ws.columns
3. Read data dynamically
Maximum number of rows in the worksheet
ws.max_row
Maximum number of columns in the worksheet
ws.max_column
Row or column of a cell
ws['A1'].row ws['A1'].column
Get all data of a column
c = []
for i in ws['A']:
c.append(i.value)
print(c)4. Write and insert data
Write data
ws['A1']="value" ws.cell(1,1,'value')
Insert column
# Start with column 2 (including column 2) and insert a table with 5 columns ws.insert_cols(idx=2,amount=5)
Insert row
# Insert a 5-Row table starting from row 2 (including the second row) ws.insert_rows(idx=2,amount=5)
Delete column
# Starting with column 2, delete the 5-column table ws.delete_cols(idx=2,amount=5)
Delete row
# Starting with line 2, delete the 5-line table ws.delete_rows(idx=2,amount=5)
Other operations
1. Alphanumeric conversion
Number to letter conversion
openpyxl.utils.get_column_letter(2)
Alphanumeric conversion
openpyxl.utils.column_index_from_string('D')2. Move and freeze cells
Move cell
# Cells A1 to C3 move down 5 rows and right 10 columns
ws.move_range("A1:C3",row=5,cols=10)Freeze cell
# The row above cell C3 and the column to the left are frozen ws.freeze.panes='C3'
3. Modify cell style
Font style font
# Import Font module
from openpyxl.styles import Font
a = ws['A1']
# Name font name size font size bold bold italic strike strikethrough color
ft = Font(name='Blackbody', size=20, bold=True, italic=False, vertAlign=None, underline='none', strike=True, color='FF000000')
# Black "000000" red "FF0000" green "00FF00" blue "0000FF"
a.font = ft
wb.save("/Users/practice/1.xlsx")Adjust column width and row height
# Adjust column width ws.column_dimensions['A'].width = 35 # Adjust row height ws.row_dimensions[1].height = 40
Cell background fill
# Import PatternFill module
from openpyxl.styles import PatternFill
# Initialize fill object
fill_new = PatternFill(
fill_type='darkGray',
start_color='ffff00'
)
# Set the fill of B1 cell to fill_ The style defined by the object
ws['B1'].fill = fill_new
wb.save("/Users/practice/1.xlsx")