excel操作
#coding=utf-8
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]) #写入多个单元格
# Python types will automatically be converted
import datetime
import time
ws['A2'] = datetime.datetime.now() #写入一个当前时间
#写入一个自定义的时间格式
ws['A3'] =time.strftime("%Y年%m月%d日 %H时%M分%S秒",time.localtime())
# Save the file
wb.save("d:\sample.xlsx")
练习：在一列生成1-100个数
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook() #创建文件对象
# grab the active worksheet
ws = wb.active #获取第一个sheet
x=1
cell_data = map(lambda x:"A"+str(x),range(1,101))
for i in cell_data:
ws[i] = x
x+=1
# Save the file
wb.save("e:\sample.xlsx")
for i in range(1,100):
ws.append([str(i)])
创建一个sheet
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1.title = "New Title"#设定一个sheet的名字
ws2 = wb.create_sheet("Mysheet", 0) #设定sheet的插入位置
ws2.title = u"光荣之路自动化测试培训" #设定一个sheet的名字
ws1.sheet_properties.tabColor = "1072BA"#设定sheet的标签的背景颜色
#获取某个sheet对象
print wb.get_sheet_by_name(u"光荣之路自动化测试培训" )
print wb["New Title"]#获取wb对象中sheet名的sheet
#获取全部sheet 的名字，遍历sheet名字
print wb.sheetnames
for sheet_name in wb.sheetnames:
print sheet_name
print "*"*50
#和上边是一样功能
for sheet in wb:
print sheet.title
#复制一个sheet
wb["New Title"]["A1"]="gloryroad"
source = wb["New Title"]
target = wb.copy_worksheet(source)
# Save the file
wb.save("d:\sample.xlsx")
c:Python27Scripts>python task_test.py
task_test.py:12: DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
print wb.get_sheet_by_name(u"鍏夎崳涔嬭矾鑷�姩鍖栨祴璇曞煿璁? )
<Worksheet "u5149u8363u4e4bu8defu81eau52a8u5316u6d4bu8bd5u57f9u8bad">
<Worksheet "New Title">
[u'u5149u8363u4e4bu8defu81eau52a8u5316u6d4bu8bd5u57f9u8bad', u'Sheet', u'New Title']
光荣之路自动化测试培训
Sheet
New Title
**************************************************
光荣之路自动化测试培训
Sheet
New Title
练习：#生成一个excel文件，生成3个sheet，名称包含中文
#每个sheet的a1写一下sheet的名称。每个sheet有个底色
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet(u"mysheet夏", 0) #创建一个sheet
ws2 = wb.create_sheet(u"mysheet晓", 1) #设定sheet的插入位置
ws3 = wb.create_sheet(u"mysheet旭", 2) #设定sheet的插入位置
ws1.sheet_properties.tabColor = "1072BA"#设定sheet的标签的背景颜色
ws1["A1"]=ws1.title
ws2["A1"]=ws2.title
ws3["A1"]=ws3.title
# Save the file
wb.save("d:\sample.xlsx")
吴老：
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet(u"我的sheet1",0)
ws2 = wb.create_sheet(u"我的sheet2",1)
ws3 = wb.create_sheet(u"我的sheet3",2)
ws1.sheet_properties.tabColor = "1072BA"
ws2.sheet_properties.tabColor = "1072BA"
ws3.sheet_properties.tabColor = "1072BA"
ws1["A1"]=ws1.title
ws2["A1"]=ws2.title
ws3["A1"]=ws3.title
# Save the file
wb.save("e:\sample.xlsx")
#生成一个excel文件，生成3个sheet，名称包含中文
#每个sheet的a1写一下sheet的名称。每个sheet有个底色
操作单元格
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("d:\sample.xlsx")
c:Python27Scripts>python task_test.py
123.11
光荣之路
10
10
遍历某一列的值ws1["A"]
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["B1"]=4
ws1["B2"]=5
ws1["B3"]=6
ws1["C1"]=7
ws1["C2"]=8
ws1["C3"]=9
#操作单列
print ws1["A"]
for cell in ws1["A"]:
print cell.value
# Save the file
wb.save("d:\sample.xlsx")
c:Python27Scripts>python task_test.py
(<Cell u'Mysheet'.A1>, <Cell u'Mysheet'.A2>, <Cell u'Mysheet'.A3>)
1
2
3
遍历ws1[‘A:C’]每一列的每一行的值
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["B1"]=4
ws1["B2"]=5
ws1["B3"]=6
ws1["C1"]=7
ws1["C2"]=8
ws1["C3"]=9
print ws1["A:C"]
for column in ws1["A:C"]:
for cell in column:
print cell.value
# Save the file
wb.save("d:\sample.xlsx")
c:Python27Scripts>python task_test.py
((<Cell u'Mysheet'.A1>, <Cell u'Mysheet'.A2>, <Cell u'Mysheet'.A3>), (<Cell u'Mysheet'.B1>, <Cell u'Mysheet'.B2>, <Cell u'Mysheet'.B3>), (<Cell u'Mysheet'.C1>, <Cell u'Mysheet'.C2>, <Cell u'Mysheet'.C3>))
1
2
3
4
5
6
7
8
9
遍历WS1[1:3]按行遍历,按列遍历
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["B1"]=4
ws1["B2"]=5
ws1["B3"]=6
ws1["C1"]=7
ws1["C2"]=8
ws1["C3"]=9
row_range = ws1[1:3]
print row_range
for row in row_range:
for cell in row:
print cell.value
# Save the file
wb.save("d:\sample.xlsx")
c:Python27Scripts>python task_test.py
((<Cell u'Mysheet'.A1>, <Cell u'Mysheet'.B1>, <Cell u'Mysheet'.C1>), (<Cell u'Mysheet'.A2>, <Cell u'Mysheet'.B2>, <Cell u'Mysheet'.C2>), (<Cell u'Mysheet'.A3>, <Cell u'Mysheet'.B3>, <Cell u'Mysheet'.C3>))
1
4
7
2
5
8
3
6
9
Pip list看版本
c:Python27Scripts>pip list
DEPRECATION: The default format will switch to columns in the future. You can use --format=(legacy|columns) (or define a format=(legacy|columns) in your pip.conf under the [list] section) to disable this warning.
certifi (2018.1.18)
chardet (3.0.4)
et-xmlfile (1.0.1)
idna (2.6)
jdcal (1.3)
openpyxl (2.5.2)
pip (9.0.1)
requests (2.18.4)
setuptools (28.8.0)
urllib3 (1.22)
You are using pip version 9.0.1, however version 9.0.3 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.
指定某一个范围的遍历
ws1.iter_rows(min_row=1,min_col=1,max_col=3, max_row=3):
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["B1"]=4
ws1["B2"]=5
ws1["B3"]=6
ws1["C1"]=7
ws1["C2"]=8
ws1["C3"]=9
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
# Save the file
wb.save("d:\sample.xlsx")
c:Python27Scripts>python task_test.py
1
4
7
2
5
8
3
6
9
遍历所有行，所有列
ws1.rows,ws1.columns
# -*- coding: utf-8 -*-
from openpyxl import Workbook
wb = Workbook()
ws1 = wb.create_sheet("Mysheet") #创建一个sheet
ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["B1"]=4
ws1["B2"]=5
ws1["B3"]=6
ws1["C1"]=7
ws1["C2"]=8
ws1["C3"]=9
#获取所有行
print ws1.rows
for row in ws1.rows:
print row
print "*"*50
#获取所有列
print ws1.columns
for col in ws1.columns:
print col
# Save the file
wb.save("d:\sample.xlsx")
c:Python27Scripts>python task_test.py
<generator object _cells_by_row at 0x0532C0A8>
(<Cell u'Mysheet'.A1>, <Cell u'Mysheet'.B1>, <Cell u'Mysheet'.C1>)
(<Cell u'Mysheet'.A2>, <Cell u'Mysheet'.B2>, <Cell u'Mysheet'.C2>)
(<Cell u'Mysheet'.A3>, <Cell u'Mysheet'.B3>, <Cell u'Mysheet'.C3>)
**************************************************
<generator object _cells_by_col at 0x0532C0A8>
(<Cell u'Mysheet'.A1>, <Cell u'Mysheet'.A2>, <Cell u'Mysheet'.A3>)
(<Cell u'Mysheet'.B1>, <Cell u'Mysheet'.B2>, <Cell u'Mysheet'.B3>)
(<Cell u'Mysheet'.C1>, <Cell u'Mysheet'.C2>, <Cell u'Mysheet'.C3>)