openpyxl 模块
pip install openpyxl
读sheet
from openpyxl import load_workbook
?
#打开excel文件
workbook = load_workbook("文件名")
?
#1.获取所有的sheet名称
name_list = workbook.sheetnames
print(name_list)
?
#2.根据名称获取sheet
sheet_object = workbook["sheetname"]
print(sheet_object)
?
#3.根据索引获取sheet
sheet = worksheets[0]
?
#4.循环获取所有的sheet
for sheet_object in wcrkbook:
print(sheet_object)
#不加.sheetnames内部也是自动获取sheetnames的
读sheet中单元格的数据
from openpyxl import load_workbook
?
workbook = load_workbook("文件路径")
sheet = workbook.worksheets[1]
?
#1.获取 第n行 &第n列 的单元格(起始位置是1)
cell = sheet.cell(1,1)
print(cell)
print("内容",cell.value)
print("样式",cell.style)
print("字体",cell.font)
print("排序",cell.alignment)
?
#2.根据Excel的位置获取数据
cell_al = sheet["A1"]
print(cell_al.value)
?
#3.获取第n行的单元格
cell_list = sheet[1]
?
for cell in sheet[1]:
print(cell.value)
#4.循环获取所有行数据
for row in sheet.rows:
print(row[0].value)
#5.循环获取所有列
for col in sheet.columns:
print(col[0].value)
?
读合并的单元格
from openpyxl import load_workbook
?
workbook = load_workbook("文件路径")
sheet = workbooks[2]
?
#1.获取单元格
c1 = sheet.cell(1,1)
print(c1) #
print(c1.value) #用户信息
#第一个和第二个合并单元格，第一个显示值，第二个显示null
c1 = sheet.cell(1,2)
print(c1) #
print(c1.value) #None
? |
写Excel
-
原Excel文件基础上写内容
from openpyxl import load_workbook
?
workbook = load_workbook("文件路径")
sheet = worksheets[0]
?
#1.找到单元格，并修改单元格的内容
cell = sheet.cell(1,1)
cell.value = "值"
?
#将excel文件保存到p2.xlsx文件中
workbook.save("文件路径") -
新创建Excel文件写内容
from openpyxl import workbook
?
#创建excel且默认会创建一个sheet（名称为sheet）
workbook = workbook.Workbook()
?
sheet = worksheets[0]
?
#找到单元格，并修改单元格的内容
cell = sheet.cell(1,1)
cell.value = "值"
?
#将excel文件保存到p2.xlsx文件中
workbook.save("文件路径")
from openpyxl import workbook
?
workbook = workbook.workbook()
?
#1.修改sheet名称
sheet = worksheets[0]
sheet.title = "值"
workbook.save("p2.xlsx")
?
#2.创建sheet并设置sheet颜色
sheet = workbook.create_sheet("工作计划",0)#零代表位置
sheet.sheet_properties.tabColor = "1072BA"
workbook.save("P2.xlsx")
?
#3.默认打开的sheet
workbook.active = 0
workbook.save("p2.xlsx")
?
#4.拷贝sheet
sheet = workbook.create_sheet("工作计划")
sheet.sheet_properties.tabColor = "1072BA"
?
new_sheet = workbook.copy_worksheet(workbook["Sheet"])
new_sheet.title = "新的计划"
workbook.save("p2.xlsx")
?
#5.删除sheet
del workbook["用户列表"]
workbook.save("files/p2.xlsx")
操作单元格
from openpyxl import load_workbook
from openpyxl.styles import Alignment,Border,Side,Font,PatternFill,GradientFill
?
workbook = load_workbook("文件")
sheet = workbook.worksheet[1]
#1.获取某个单元格，修改值
cell = sheet.cell(1,1)
cell.value = "开始"
?
#2.获取某个单元格，修改值
sheet["V3"] = "Alex"
vb.save("p2.xlsx")
?
#3.获取某些单元格，修改值
cell_list = sheet[B2:C3]
#b2到c3拿到的值是(b2,b3,c2,c3)
for row in cell_list:
for cell in row :
cell.value = "新的值"
vb.save("p2.xlsx")
?
#4.对齐方式
cell = sheet.cell(1,1)
#herizontal,水平方向对齐方式：“general”,"left","center"句中,"right","fill","justify","centercontinuous","distributed"
#vertieal,垂直方向对齐方式："top","center","bottom","justify","distributed"
#text_rotation,旋转角度
#wrap_text,是否自动换行
cell.alignment = Alignment(horizontal="center",vertical="distributed",text_rotation=45,wrap_text=True)
wb.save("p2.xlsx")
?
#5.边框
#side的style有如下：dashDot,dashDotDot,dashed,dotted,double,hair,mediun,mediumDashDot,mediumDashDotdot,mediumDashed,slantDashDot,thick,thin.
sell = sheet.cell(9.2)
cell.border = Border(
top = Side(style="thin",color="FFB6C1"),
bottom=Side(style="dashed",color="9932cc"),
left=Side(style="dashed",color="9932cc"),
right=Side(style="dashed",color="9932cc"),
diagonal=Side(style="thin",color="483D8B"),#对角线
diagonalUp=True,#左下-右上
#diagonalDown=True #左上-右下
)
wb.save("p2.xlsx")
?
#6.字体
cell = sheet.cell(5,1)
cell.font = Font(name="微软雅黑",size=45，color="ff0000",underline="single") #underline下划线
wb.save("p2.xlsx")
?
#7.背景色
cell = sheet.cell(5,3)
cell.fill = PatternFill("solid",fgColor="99ccff")
wb.save("p2.xlsx")
?
#8.渐变背景色
cell = sheet.cell(5,5)
cell.fill = GradientFill("linear",stop=("FFFFFF","99ccff","000000"))#从左到右的三个颜色
wb.