1. 读数据
安装包
pip install openpyxl
导包
from openpyxl import load_workbook
- 读sheet所有操作
-
加载文件数据
wb=load_workbook('file/p1.xlsx')
-
获取excel文件中的所有sheet名称
print(wb.sheetnames) #['数据导出', '用户列表', 'Sheet1', 'Sheet2']
-
根据名称选择sheet
sheet=wb['数据导出']
-
找单元格
#找第一行第二列单元格 cell=sheet.cell(1,2) print(cell) #<Cell '数据导出'.B1>
-
获取值
print(cell.value)
-
根据索引选择sheet
sheet1=wb.worksheets[0]
-
循环所有sheet
for name in wb.sheetnames: sheet=wb[name] cell=sheet.cell(1,2) print(cell.value) for sheet in wb: cell=sheet.cell(1,1) print(cell.value)
- 读单元格的数据
-
获取第N行第N列的单元格(位置从1开始)
#获取第1行第1列的单元格(位置从1开始) cell=sheet.cell(1,1)
-
获取单元格值
print(cell.value)
-
获取单元格样式
print(cell.style)
-
获取单元格字体
print(cell.font) #返回font对象
-
获取单元格排列情况
print(cell.alignment)
-
获取某个单元格
# A表示列,2表示行 c1=sheet['A2']
-
获取第N行所有的单元格
#获取第1行所有的单元格,表头 for cell in sheet[1]: print(cell.value)
-
获取所有行
for row in sheet.rows: print(row[0].value) #每一行的第一列
-
获取所有列的数据(某一行全取)col[0]是表头
# 获取所有列的数据(某一行全取)col[0]是表头 for col in sheet.columns: print(col) #一整列 print(col[1].value) #第一列的值
- 读合并单元格
#合并默认第一个单元格有值,后面为None
c1=sheet.cell(1,1)
print(c1,c1.value) #<Cell 'Sheet1'.A1> 用户信息
c2=sheet.cell(1,2)
print(c2,c2.value) #<MergedCell 'Sheet1'.B1> None
2. 写excel
修改文件要重新保存
-
原数据基础上写内容(修改内容)
# 找到单元格,并修改单元格的内容 cell=sheet.cell(1,1) cell.value='你好' wb.save('file/p2.xlsx')
-
保存文件
wb.save('file/p2.xlsx')
-
新建文件写内容
from openpyxl import workbook # 创建excel且默认会创建一个sheet(名称为Sheet) wb = workbook.Workbook() #获取创建的sheet sheet = wb.worksheets[0] # 或 sheet = wb["Sheet"] # 找到单元格,并填写单元格的内容 cell = sheet.cell(1, 1) cell.value = "新的开始" # 将excel文件保存到p2.xlsx文件中 wb.save("files/p2.xlsx")
3.操作整合
sheet整体操作
-
修改sheet名称
from openpyxl import workbook #创建sheet,默认名称为Sheet wb=workbook.Workbook() sheet=wb.worksheets[0] sheet.title='王晓宇' wb.save("files/p2.xlsx")
-
创建sheet并设置sheet名称背景颜色
from openpyxl import workbook wb=workbook.Workbook() #create_sheet(sheet名称,sheet位置) sheet=wb.create_sheet('工作计划',0) sheet.sheet_properties.tabColor="000000" wb.save("files/p2.xlsx")
-
设置默认打开的sheet
from openpyxl import load_workbook wb=load_workbook('file/p1.xlsx') wb.active=1 wb.save('file/p10.xlsx')
-
拷贝sheet
from openpyxl import workbook wb=workbook.Workbook() sheet=wb.worksheets[0] sheet.title='王晓宇' new_sheet=wb.copy_worksheet(wb["王晓宇"]) new_sheet.title='新计划' wb.save("file/p40.xlsx")
-
删除sheet
from openpyxl import load_workbook wb=load_workbook('file/p40.xlsx') del wb['新计划'] wb.save("file/p40.xlsx")
-
保存
wb.save("file/p40.xlsx")
单元格操作
-
获取某个单元格,修改值
#方式一 cell=sheet.cell(1,1) cell.value='wxy' wb.save("file/p40.xlsx") #方式二 sheet['A1']='wxy' wb.save("file/p40.xlsx")
-
获取某些单元格,修改值
cell_list=sheet['B2':'C3'] #row一行 for row in cell_list: for cell in row: cell.value="新值"
-
对齐方式
- horizontal,水平方向对齐方式:“general”, “left”, “center”, “right”, “fill”, “justify”, “centerContinuous”, “distributed”
- vertical,垂直方向对齐方式:“top”, “center”, “bottom”, “justify”, “distributed”
- text_rotation,文本旋转角度。
- wrap_text,文本是否自动换行。
cell = sheet.cell(9, 2) cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True)
-
边框
- side(线)的style有如下:dashDot’,‘dashDotDot’, ‘dashed’,‘dotted’,‘double’,‘hair’, ‘medium’, ‘mediumDashDot’, ‘mediumDashDotDot’,‘mediumDashed’, ‘slantDashDot’, ‘thick’, ‘thin’
cell = sheet.cell(9, 2) cell.border = Border( top=Side(style="thin", color="FFB6C1"), bottom=Side(style="dashed", color="FFB6C1"), left=Side(style="dashed", color="FFB6C1"), right=Side(style="dashed", color="9932CC"), diagonal=Side(style="thin", color="483D8B"), # 对角线样式 diagonalUp=True, # 左下 ~ 右上方向 diagonalDown=True # 左上 ~ 右下方向 )
-
字体
cell = sheet.cell(5, 1) #underline下划线 cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single")
-
背景颜色
cell = sheet.cell(5, 1) cell.fill = PatternFill("solid", fgColor="99ccff")
-
渐变背景颜色
cell = sheet.cell(5, 5) cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000"))
-
宽高(索引从1开始)
sheet.row_dimensions[1].height = 50 sheet.column_dimensions["E"].width = 100
-
合并单元格
sheet.merge_cells("B2:D8") sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8)
-
解除合并单元格
sheet.unmerge_cells("B2:D8")
-
写入公式
#乘 sheet = wb.worksheets[3] sheet["D1"] = "合计" sheet["D2"] = "=B2*C2" #加 sheet = wb.worksheets[3] sheet["D3"] = "=SUM(B3,C3)"
-
删除
# idx,要删除的索引位置 # amount,从索引位置开始要删除的个数(默认为1) sheet.delete_rows(idx=1, amount=20)#删1-20行 sheet.delete_cols(idx=1, amount=3)#删1-3列
-
插入
sheet.insert_rows(idx=5, amount=10) sheet.insert_cols(idx=3, amount=2)
-
循环写内容
sheet = wb["Sheet"] cell_range = sheet['A1:C2'] for row in cell_range: for cell in row: cell.value = "xx" for row in sheet.iter_rows(min_row=5, min_col=1, max_col=7, max_row=10): for cell in row: cell.value = "oo"
-
移动
# 将H2:J10范围的数据,向右移动15个位置、向上移动1个位置 sheet.move_range("H2:J10",rows=-1, cols=15)
-
打印区域
sheet.print_area = "A1:D200"
-
打印时,每个页面固定表头
sheet.print_title_cols = "A:D" sheet.print_title_rows = "1:1"