1、新建表格
from openpyxl import Workbook
# 实例化
wb = Workbook()
# 激活 worksheet
ws = wb.active
# 保存表格
wb.save("C:/TEMP/test1.xlsx")
2、读取已有表格
from openpyxl import load_workbook
wb2 = load_workbook("C:/TEMP/test2.xlsx")
print(wb2.sheetnames)
3、创建工作簿
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 新建的工作簿插到末尾
ws1 = wb.create_sheet("Myshee1")
print(wb.sheetnames)
# 新建的工作簿插到首部
ws2 = wb.create_sheet("Mysheet2", 0)
print(wb.sheetnames)
# 新建的工作簿插到倒数第二个位置
ws3 = wb.create_sheet("Mysheet3", -1)
print(wb.sheetnames)
wb.save("C:/TEMP/test3.xlsx")
4、更改工作簿名称
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
print(wb.sheetnames)
ws.title = "New Title"
print(wb.sheetnames)
wb.save("C:/TEMP/test4.xlsx")
5、访问单元格
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['A4'] = 'A4单元格'
c=ws['A4'].value
print(c)
d=ws.cell(4,2,'第四行第二列') #第4行第二列
print(d.value)
wb.save("C:/TEMP/test5.xlsx")
6、访问多个单元格
from openpyxl import Workbook
import random
wb = Workbook()
ws = wb.active
for i in range(1,40):
for j in range(1,60):
ws.cell(i,j,random.randint(0, 60))
# 使用切片访问
range = ws['A1':'D40']
# 使用列访问
colC=ws['C']
col_range=ws['C:D']
#使用行访问
row10=ws[10]
row_range=ws[5:10]
wb.save("C:/TEMP/test6.xlsx")
7、插入行和列
# openpyxl.worksheet.worksheet模块
# insert_cols(idx,amount = 1 )
# 在col == idx之前插入一列或多列
# insert_rows(idx,amount = 1 )
# 在row == idx之前插入一行或多行
from openpyxl import Workbook
import random
wb = Workbook()
ws = wb.active
for i in range(1,5):
for j in range(1,5):
ws.cell(i,j,random.randint(0, 60))
ws.insert_rows(1) #在第一行前插入1行
ws.insert_cols(3,3) #在第三列前面插入3列
wb.save("C:/TEMP/test7.xlsx")
8、删除行和列
# openpyxl.worksheet.worksheet模块
#delete_cols(idx,amount = 1 )
#从col == idx删除一列或多列
#delete_rows(idx,amount = 1 )
#从row == idx删除一行或多行
from openpyxl import Workbook
import random
wb = Workbook()
ws = wb.active
for i in range(1,9):
for j in range(1,9):
# ws.cell(i,j,random.randint(0, 60))
ws.cell(i,j,'{},{}'.format(i,j))
# ws.delete_rows(1) # 删除第一行
ws.delete_cols(3,2) #删除第三列和第四列
wb.save("C:/TEMP/test8.xlsx")
9、合并单元格
from openpyxl import load_workbook
wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active
ws.merge_cells('B2:B5')
ws.merge_cells('C2:C5')
ws.merge_cells('D2:D5')
wb.save("C:/TEMP/test9.xlsx")
10、单元格格式
from openpyxl import load_workbook
from openpyxl.styles import Alignment
wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active
ws.merge_cells('a2:a18') #合并单元格
cell = ws.cell(row=2, column=1) #注意,在openpyxl的逻辑里面,编号是从1开始的,而非0;
cell.value = '医院名称' # 对于合并的单元格而言,能被赋值等操作的,只有合并前最左上角的单元格
cell.alignment = Alignment(horizontal='center', vertical='center')
wb.save("C:/TEMP/test10.xlsx")
11、调整列宽
from openpyxl import load_workbook
wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active
ws.column_dimensions['E'].width=30
ws.column_dimensions['F'].width=50
wb.save("C:/TEMP/test11.xlsx")
12、设置自动换行
from openpyxl import load_workbook
from openpyxl.styles import Alignment
wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active
ws.column_dimensions['E'].width=30
ws.column_dimensions['F'].width=50
ws.column_dimensions['G'].width=36
ws.cell(row=1, column=1, value='调整自动换行').alignment = Alignment(horizontal='center',vertical='center',wrapText=True)
align = Alignment(horizontal='left',vertical='center',wrap_text=True)
for i in range(1,19):
ws['E{}'.format(i)].alignment = align
ws['F{}'.format(i)].alignment = align
ws['G{}'.format(i)].alignment = align
wb.save("C:/TEMP/test12.xlsx")
13、函数
from openpyxl import Workbook
import random
wb = Workbook()
ws = wb.active
for i in range(1,8):
for j in range(1,3):
ws.cell(i,j,random.randint(0, 60))
ws['A8']="=SUM(A1:A7)"
ws['B8']="=AVERAGE(B1:B7)"
wb.save("C:/TEMP/test13.xlsx")
14、边框设置
from openpyxl import load_workbook
from openpyxl.styles import Alignment
wb = load_workbook("C:/TEMP/test2.xlsx")
ws = wb.active
ws.column_dimensions['E'].width=30
ws.column_dimensions['F'].width=50
ws.column_dimensions['G'].width=36
ws.cell(row=1, column=1, value='调整自动换行').alignment = Alignment(horizontal='center', vertical='center',wrapText=True)
align = Alignment(horizontal='left',vertical='center',wrap_text=True)
for i in range(1,19):
ws['E{}'.format(i)].alignment = align
ws['F{}'.format(i)].alignment = align
ws['G{}'.format(i)].alignment = align
# 边框
from openpyxl.styles import Border,Side
border = Border(left=Side(border_style='thin',color='000000'),
right=Side(border_style='thin',color='000000'),
top=Side(border_style='thin',color='000000'),
bottom=Side(border_style='thin',color='000000')
)
ws['F3'].border = border
wb.save("C:/TEMP/test14.xlsx")
15、背景设置
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
wb = load_workbook("C:/TEMP/征像模板01.xlsx")
ws = wb.active
for i in range(1,ws.max_row+1):
ws["L{}".format(i)].fill = PatternFill("solid", fgColor="1874CD")
ws["N{}".format(i)].fill = PatternFill("solid", fgColor="FFFF00")
ws["O{}".format(i)].fill = PatternFill("solid", fgColor="FF7E00")
# ws["L{}".format(i)].fill = PatternFill("lightGrid", fgColor="1874CD")
# ws["N{}".format(i)].fill = PatternFill("lightVertical", fgColor="FFFF00")
# ws["O{}".format(i)].fill = PatternFill("lightDown", fgColor="FF7E00")
wb.save("C:/TEMP/test15.xlsx")