Openpyxl 简单说明
1、 安装
pip install openpyxl==2.5.4
想要在文件中插入图片文件,需要安装pillow,安装文件:PIL-fork-1.1.7.win-amd64-py2.7.exe
2、 创建一个excel 文件,并写入不同类的内容
# -*- coding: utf-8 -*-
import localefrom openpyxl import Workbookwb = Workbook() #创建文件对象# grab the active worksheetws = wb.active #获取第一个sheet# Data can be assigned directly to cellsws['A1'] = 42 #写入数字ws['B1'] = "光荣之路"+"automation test" #写入中文# Rows can also be appendedws.append([1, 2, 3]) #写入多个单元格wb.save("e:\\a.xlsx")
3、单元格写入时间
import datetime
import timeimport localewb = Workbook()ws = wb.active ws['A2'] = datetime.datetime.now() #写入一个当前时间#写入一个自定义的时间格式locale.setlocale(locale.LC_CTYPE, 'chinese')ws['A3'] =time.strftime("%Y年%m月%d日 %H时%M分%S秒",time.localtime())# Save the filewb.save("e:\\sample.xlsx")9、多个sheet
from openpyxl import Workbook
wb = Workbook()ws = wb.create_sheet("Mysheet1") ws1 = wb.create_sheet("Mysheet")
ws1.title = "New Title"
ws2 = wb.create_sheet("Mysheet", 0) #设定sheet的插入位置
ws2.title = u"光荣之路自动化测试培训"
ws1.sheet_properties.tabColor = "1072BA" #设定颜色码
#获取某个sheet对象
print (wb["光荣之路自动化测试培训"])print (wb["New Title" ])
print (wb.sheetnames)
for sheet_name in wb.sheetnames:
print (sheet_name)
for sheet in wb:
print (sheet)
wb["New Title" ]["A1"]="gloryroad"
#复制一个sheet
wb["New Title" ]["A1"]="gloryroad"
source = wb["New Title" ]
target = wb.copy_worksheet(source)
# Save the file
wb.save("e:\\sample.xlsx")
del wb["New Title" ]
#复制一个sheet
wb["New Title" ]["A1"]="gloryroad"source = wb["New Title" ]target = wb.copy_worksheet(source)target.title="New copy Title"del wb["New Title" ]# Save the filewb.save("e:\\sample.xlsx")
**********************************
10、读取某个单元格的值
# -*- coding: utf-8 -*-
from openpyxl import Workbookwb = Workbook()ws1 = wb.create_sheet("Mysheet") #创建一个sheetws1["A1"]=123.11ws1["B2"]="光荣之路"d = ws1.cell(row=4, column=2, value=10)print (ws1["A1"].value)print (ws1["B2"].value)print (d.value)print (ws1.cell(row=4,column=2).value)# Save the filewb.save("e:\\sample.xlsx")
#打印最大行号,最大列号
print(ws1.max_row,ws1.max_column)
print(ws1.min_row,ws1.min_column)
11、遍历每个单元格
12、通过制定有效范围取值
print ("*"*50)
for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3): for cell in row: print (cell.value)13、print自动调用str,交互模式自动调用repr方法
>>> class P:
... def __str__(self):
... return "zhenzhen"
... def __repr__(self):
... return "huanghuang"
...
>>> p =P()
>>> p
huanghuang
>>> print(p)
zhenzhen
>>> repr(p)
'huanghuang'
14、获取所有行列
#获取所有行
print (ws1.rows)for row in ws1.rows: print (row)print ("*"*50)#获取所有列print (ws1.columns)for col in ws1.columns: print (col)15、百分比
# -*- coding: utf-8 -*-
from openpyxl import Workbookfrom openpyxl import load_workbookwb = load_workbook('e:\\sample.xlsx')wb.guess_types = Falsews=wb.activews["D1"]="12%"#false是百分比,true是小数print (ws["D1"].value)# Save the filewb.save("e:\\sample.xlsx")