一、写一个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")
二、写入时间
import locale
from openpyxl import Workbook
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")
三、设置单元格格式
实例一:新增两个sheet页面
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet("Mysheet1")
ws1 = wb.create_sheet("Mysheet")
# Save the file
wb.save("e:\\sample.xlsx")
实例二：
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)
print(wb[sheet_name])
print("*"*50)
＃遍历所有的对象
for sheet in wb:
print (sheet)
＃遍历所有对象的名字
for sheet in wb:
print (sheet.title)
#复制一个sheet
wb["New Title" ]["A1"]="gloryroad"
source = wb["New Title" ]
target = wb.copy_worksheet(source)
target.title="New copy Title"
#删除sheet
del wb["New Title" ]
# Save the file
wb.save("e:\\sample.xlsx")
备注：对象和名字区别：
对象：整个sheet里面的所有单元格数据
名字：sheet的名字
四、操作单元格
# -*- 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")
五、批量操作单元格
# -*- 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["A"])
for cell in ws1["A"]:
print (cell.value)
#操作多列,获取每一个值
print (ws1["A:C"])
for column in ws1["A:C"]:
for cell in column:
print (cell.value)
#最大行号和列号
print(ws1.max_row,ws1.max_column)
#最小行号和列号
print(ws1.min_row,ws1.min_column)
#操作多行
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)
# Save the file
wb.save("e:\\sample.xlsx")
六、获取所有行和所有列
# -*- 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("e:\\sample.xlsx")
