Python_Openpyxl 简单说明
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 locale
from openpyxl import Workbook
wb = Workbook() #创建文件对象
# grab the active worksheet
ws = wb.active #获取第一个sheet
# Data can be assigned directly to cells
ws['A1'] = 42 #写入数字
ws['B1'] = "光荣之路"+"automation test" #写入中文
# Rows can also be appended
ws.append([1, 2, 3]) #写入多个单元格
wb.save("e:\\a.xlsx")
3、单元格写入时间
import datetime
import time
import locale
wb = 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 file
wb.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 file
wb.save("e:\\sample.xlsx")
**********************************
10、读取某个单元格的值
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=123.11
ws1["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 file
wb.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 Workbook
from openpyxl import load_workbook
wb = load_workbook('e:\\sample.xlsx')
wb.guess_types = False
ws=wb.active
ws["D1"]="12%"#false是百分比，true是小数
print (ws["D1"].value)
# Save the file
wb.save("e:\\sample.xlsx")
