May 30, 2021 Article blog
Openpyxl is a third-party python library for working with xlsx-formatted Excel table files that supports most of the basic operations of Excel tables.
Where the pip installation method, the command line input: pip install openpyxl
The first step is to import the openpyxl module
import openpyxlFile read by calling the method load_workbook, which also has a read_only parameter to set how the file is opened, which defaults to readable and writeable, and eventually returns a workbook data object
# 文件必须是xlsx格式，如果是其他格式在执行前可利用win32辅助转化
wb = openpyxl.load_workbook(‘example.xlsx’)There are many sheet sheets in each Excel table, and you need to select one before you can work on the table
# 获取所有工作表名(返回一个列表)
sheets = wb.get_sheet_names()
# 获取某一特定的工作表
sheet = wb.get_sheet_by_name('Sheet2')
# 获取工作表的表名
sheet_name = sheet.title
# 一般来说，表格大多数用到的是打开时显示的工作表，这时可以用active来获取当前工作表
sheet = wb.activeThe operation on the Excel table ends up in the operation on the cell, and there are two ways to get the cell: sheet (column name) and sheet.cell (row, column)
# 通过sheet[列行名]获取
a = sheet['A2']
# 通过sheet.cell(row,column)获取
b = sheet.cell(1, 2) # 即sheet['B1']
# 获取单元格内容
print(a.value)
# 获取单元格所在列和行
print(‘a is ’+str((a.column,a.row)))
Note that the parameters in sheet.cell (row, column) are rows and columns, and must be integers, and if listed in English letters, you can convert alphanumeric numbers using
the column_index_from_string
(char) in
openpyxl.utils.
By the way,
the same can be said of get_column_letter for digital alphabet conversion
from openpyxl.utils import get_column_letter, column_index_from_string
# 对列进行字母/数字转化
c_num = column_index_from_string('B') # c_num = 2
c_char = get_column_letter(5) # c_char = 'E‘When working with Excel tables, which sometimes may require traversal lookups, openpyxl provides a row and column generator (sheet.rows and sheet.columns) that contain data for each row (or column) and each row (or column) is wrapped in a tuple wrap, making it easy to traverse rows and columns
# 对行进行遍历,输出A1,B1,C1
for row in sheet.rows:
for cell in row:
print(cell.value)
# 对列进行遍历,输出A1,A2,A3
for column in sheet.columns:
for cell in column:
print(cell.value)
Learning also found that a row or column can also be traversed through the list (sheet.rows) index, and it is worth noting here that because
sheet.rows(或sheet.columns)
are generator types that cannot be called directly, they need to be converted into a list type and then traversed through the index
# 对某一特定的行进行遍历
for cell in list(sheet.rows)[0]:
print(cell.value)
At the same time, you can traverse a given range of cells by using sheet:row values
# 对某一单元格范围进行遍历
for spaces in sheet['A1':'B2']:
for cell in spaces:
print(cell.value)
In addition, sometimes we may need to determine the size of the table, that is, get the maximum value of the table rows and columns, which can be obtained with
max_row
and
max_column
# 获得最大列和最大行
print(sheet.max_row)
print(sheet.max_column)As already described at the beginning of the read, the default opening method is readable and writeable, so you can write directly after reading an Excel document with load_workbook." I n addition, if you need to create a new Excel file, you can use the Workbook() method, which automatically provides a sheet worksheet. For deleting a worksheet, you can delete it using the delete (sheet) method of the workbook object
# 新建一个Excel文档
wb = openpyxl.Workbook()
# 删除某个工作表
wb.remove(sheet)Getting a worksheet is the same as before, and if you read with load_workbook, you can write to the cell directly from the sheet (rank value) after you get the worksheet. When learning, there is information that can also be passed into Excel formulas for assignment, but note that when reading the file you need to add the parameter data_only- true, in order to return a number, otherwise the string, that is, the formula itself, will be returned
# 直接赋值
sheet['A1'].value = 2
# 公式赋值
sheet['A6'].value = '=SUM(A1:A5)'
Alternatively, you can write one or more lines using sheet.appends
# 写入一行
row = [1 ,2, 3, 4, 5]
sheet.append(row)
# 写入多行
rows = [
['ID', 'Name', 'Department'],
['001', 'Lee','CS'],
['002', 'John','MA'],
['003', 'Amy','IS']
]
sheet.append(rows)After you've finished writing the file, save it using workbook.save, but be aware that the file extension must be in xlsx format
# 保存文件至当前目录
wb.save('new_file.xlsx')Cell styles include fonts, borders, colors, and alignment, all in the openpyxl.styles library
# 导入字体、边框、颜色以及对齐方式相关库
from openpyxl.styles import Font, Border, Side, PatternFill, colors, AlignmentSet the font style with the sheet cell font property
# 设置字体风格为Times New Roman，大小为16，粗体、斜体，颜色蓝色
sheet['A1'].font = Font(name='Times New Roman', size=16, bold=True, italic=True, color=colors.BLUE)Set the text alignment style with the sheet cell alignment property
# 通过参数horizontal和vertical来设置文字在单元格里的对齐方式，此外设置值还可为left和right
sheet['B1'].alignment = Alignment(horizontal='center',vertical='center')Set the font style with the sheet cell border property
# 首先设置边框四个方向的线条种类
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
# 再将各方向线条作为参数传入Border方法
sheet['C1'].border = Border(left=left, right=right, top=top, bottom=bottom)The length size of rows and columns can be set by row_dimensions the hashtags.height and column_dimensions.width
# 设置行高
sheet.row_dimensions[1].height = 25
# 设置列宽
sheet.column_dimensions['D'].width = 15.5The merging and splitting of cells is mainly achieved through the merge_cells of sheet (args1:args2) and unmerge_cells (args1:args2).
Of course, in addition to diagonal rectangular regionalization, you can also merge one row or column, just modify the parameters accordingly. However, it should be noted here that the text content displayed by the merged cells is the contents of the top left cell before the merge, while the contents of the other cells are automatically cleared.
# 合并单元格
sheet.merge_cells('A1:B2')
# 拆分单元格
sheet.unmerge_cells('A1:B2')