- #encoding=utf-8
- '''
- excel 基本操作整理
- '''
- #openpyxl 版本 2.5.4
- from openpyxl import *
- import datetime as dt
- from datetime import datetime as dtt
- # 新建 create_sheet,copy_worksheet
- wb=Workbook()
- wb.create_sheet('新创建 sheet')
- ws=wb.active
- print(ws.title)
- ws.title='测试用例'
- ws1=wb.copy_worksheet(ws)
- ws1.title='测试步骤'
- print(ws1.title)
- ws1.sheet_properties.tabColor='FF3030' #设置 sheet 标签颜色
- ws2=wb.copy_worksheet(ws1)
- ws2.title='测试数据'
- # 新建 sheet, 设置了位置 2
- wb.create_sheet('第三个位置 sheet',2)
- #sheet 写入数据
- ws['A2']='大家好'
- ws['B4']=dtt.now()
- ws.append(['nihao','wohao','heihei123',4,5,6])
- wb.save('f:\\pylianxi\\test\\test.xlsx')
- # 加载已存在的 Excel
- wb=load_workbook('f:\\pylianxi\\test\\test.xlsx')
- print('load active_sheet:',wb.active.title)
- print(wb.sheetnames)
- print('测试数据的标题:',wb['测试数据'].title)
- for i in wb['测试用例'].rows:
- for j in i:
- print(j.value,end=' ')
- print()
- print(type(wb))
- print(type(wb['测试用例']))
- for sheet in wb:
- print('**',sheet.title)
- # 使用 cell 读写单元格
- ws=wb['测试用例']
- # 写入
- ws.cell(row=2,column=1).value='大家真的好吗' #行号和列号从 1 开始
- ws.cell(row=5,column=3,value='设么东东')
- wb.save('f:\\pylianxi\\test\\test.xlsx')
- # 读
- print('ws.cell(row=2,column=1).value:',ws.cell(row=2,column=1).value)
- print('ws.cell(row=5,column=3).value:',ws.cell(5,3).value)
- # 小练习
- for i in range(1,4):
- for j in range(1,4):
- ws.cell(i,j).value=i*j
- wb.save('f:\\pylianxi\\test\\test.xlsx')
- # 可以设置默认打开的 sheet 页
- #wb.active=1 默认是 0
- # 注意 cell 的 行列 是从 1,1 开始的, 不是 0,0,rows,columns 的迭代器都是从 0 开始的
- print('#'*20)
- # 取某一列
- for cell in ws["C"]:
- print (cell.value)
- # 取多列的值
- for i in ws['A':'C']:
- #for column in ws["A:C"]:
- for j in i:
- print(j.value)
- # 按列取
- print('*'*20)
- for i in ws[1:3]:
- for j in i:
- print(j.value)
- print('*'*20)
- # 指定区域范围取值
- for i in ws.iter_rows(min_row=1,max_row=2,min_col=1,max_col=2):
- for j in i:
- print(j.value)
- print('*'*20)
- print(list(ws.rows)[1:]) #从第 2 行开始取值
- for i in ws.rows:
- print(i)
- for j in i:
- print(j.value)
- print('*'*20)
- # 按列取值
- for i in ws.columns:
- for j in i:
- print(j.value,end=' ')
- print()
- # 获取最大行列, 最小行列
- print(ws.max_row,ws.min_row,ws.max_column,ws.min_column)
- #
- print(ws.rows) #行生成器
- print(list(ws.rows)[0])# 第一行
- print(list(ws.rows)[0][0])# 第一个单元格
- print(list(ws.rows)[0][0].value)# 第一个单元格的值
- print(ws.columns) #列生成器
- print(list(ws.columns)[0])# 第一列
- print(list(ws.columns)[0][0])# 列第一个单元格
- print(list(ws.columns)[0][0].value)# 列第一个单元格的值
- # 单元格格式 number_format
- import datetime
- wb = load_workbook('e:\\sample.xlsx')
- ws=wb.active
- wb.guess_types = True
- ws["A1"]=datetime.datetime(2010, 7, 21)
- print (ws["A1"].number_format)
- ws["A2"]="12%"
- print (ws["A2"].number_format)
- ws["A3"]= 1.1
- print (ws["A3"].number_format)
- ws["A4"]= "中国"
- print (ws["A4"].number_format)
- # Save the file
- wb.save("e:\\sample.xlsx")
- #guess_types 猜测单元格的类型
- wb=load_workbook('e:\\sample.xlsx')
- wb.guess_types=True #可以猜测 存储的某字段值 是什么类型
- ws=wb.active
- ws['D2']='12%' #如果是 True 则会按 0.12 处理, 否则会按 str 处理
- print(ws['D2'].value)
- print(type(ws['D2'].value))
- wb.save('e:\\sample.xlsx')
- # 单元格输入公式, 打开 Excel 会自动计算
- ws["A4"] = "=SUM(1, 1)"
- ws["A5"] = "=SUM(A1:A3)"
- print (ws["A4"].value) #打印的是公式内容, 不是公式计算后的值, 程序无法取到计算后的值
- print (ws["A5"].value) #打印的是公式内容, 不是公式计算后的值, 程序无法取到计算后的值
- # 合并单元格 (2 种方式)
- #ws.merge_cells('A2:D2') #合并完了之后取值, 按 A2 取值, B2,C2,D2 取不到值
- #ws.unmerge_cells('A2:D2')
- print("ws['A2'].value:",ws['A2'].value)
- # 合并单元格区域
- ws.merge_cells(start_row=1,start_column=1,end_row=2,end_column=4)
- ws.unmerge_cells(start_row=1,start_column=1,end_row=2,end_column=4)
- # 单元格插入图片
- from openpyxl.drawing.image import Image
- img = Image('e:\\Snap1.jpg')
- #ws.add_image(img, 'A15')
- wb.save('e:\\sample.xlsx')
- # 设置单元格字体
- from openpyxl.styles import colors
- from openpyxl.styles import Font
- ft=Font(name="楷体",color=colors.BLUE,size=20,bold=True,underline="single",family=1)
- ws['A1'].font=ft
- ws['A1']='你好吗哈哈'
- wb.save('e:\\sample.xlsx')
- # 比较全面的设置单元格格式
- from openpyxl import Workbook
- from openpyxl.styles import Font
- from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill
- from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
- wb = Workbook()
- ws = wb.active
- ft = Font(name=u'微软雅黑',
- size=11,
- bold=False,
- italic=False,
- vertAlign=None,
- underline='none',
- strike=False,
- color='FF000000')
- #ft=Font(bold=True, size=20,color= "ff0100")
- #fill = PatternFill(fill_type="solid",start_color='FFEEFFFF',end_color='FF001100')
- fill = PatternFill("solid", fgColor="DDDDDD")
- # 边框可以选择的值为:'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin']
- #diagonal 表示对角线
- bd = Border(left=Side(border_style="thin",
- color='FF001000'),
- right=Side(border_style="thin",
- color='FF110000'),
- top=Side(border_style="thin",
- color='FF110000'),
- bottom=Side(border_style="thin",
- color='FF110000'),
- diagonal=Side(border_style=None,
- color='FF000000'),
- diagonal_direction=0,
- outline=Side(border_style=None,
- color='FF000000'),
- vertical=Side(border_style=None,
- color='FF000000'),
- horizontal=Side(border_style=None,
- color='FF110000')
- )
- #bdline = Side(style='thick', color="000000")
- #bd = Border(left=bdline, top=bdline, right=bdline, bottom=bdline)
- alignment=Alignment(horizontal='general',
- vertical='bottom',
- text_rotation=0,
- wrap_text=False,
- shrink_to_fit=False,
- indent=0)
- number_format = 'General'
- protection = Protection(locked=True,hidden=False)
- ws["B5"].font = ft
- ws["B5"].fill =fill
- ws["B5"].border = bd
- ws["B5"].alignment = alignment
- ws["B5"].number_format = number_format
- ws["B5"].value ="glory road"
- # Save the file
- wb.save("e:\\sample.xlsx")
来源: http://www.bubuko.com/infodetail-3057699.html