一、寫(xiě)一個(gè)excel
# -*- coding: utf-8 -*-
import locale
from openpyxl import Workbook
wb = Workbook()? ? #創(chuàng)建文件對(duì)象
# grab the active worksheet
ws = wb.active? ? #獲取第一個(gè)sheet
# Data can be assigned directly to cells
ws['A1'] = 42? ? ? #寫(xiě)入數(shù)字
ws['B1'] = "光榮之路"+"automation test" #寫(xiě)入中文
# Rows can also be appended
ws.append([1, 2, 3])? ? #寫(xiě)入多個(gè)單元格
#保存到本地
wb.save("e:\\a.xlsx")
二、寫(xiě)入時(shí)間
import?locale
from?openpyxl?import?Workbook
import?datetime
import?time
import?locale
wb?=?Workbook()
ws?=?wb.active?
ws['A2']?=?datetime.datetime.now()????#寫(xiě)入一個(gè)當(dāng)前時(shí)間
#寫(xiě)入一個(gè)自定義的時(shí)間格式
locale.setlocale(locale.LC_CTYPE,?'chinese')
ws['A3']?=time.strftime("%Y年%m月%d日?%H時(shí)%M分%S秒",
time.localtime())
#?Save?the?file
wb.save("e:\\sample.xlsx")
三、設(shè)置單元格格式
實(shí)例一:新增兩個(gè)sheet頁(yè)面
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet("Mysheet1")
ws1 = wb.create_sheet("Mysheet")
# Save the file
wb.save("e:\\sample.xlsx")
實(shí)例二：
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)? ? ? ? #設(shè)定sheet的插入位置
ws2.title = u"光榮之路自動(dòng)化測(cè)試培訓(xùn)"
ws1.sheet_properties.tabColor = "1072BA"
#獲取某個(gè)sheet對(duì)象
print (wb["光榮之路自動(dòng)化測(cè)試培訓(xùn)"])
print (wb["New Title" ])
print (wb.sheetnames)
for sheet_name in wb.sheetnames:
? ? print (sheet_name)
? ? print(wb[sheet_name])
print("*"*50)
＃遍歷所有的對(duì)象
for sheet in wb:
print (sheet)
＃遍歷所有對(duì)象的名字
for sheet in wb:
? ? print (sheet.title)
#復(fù)制一個(gè)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")
備注：對(duì)象和名字區(qū)別：
對(duì)象：整個(gè)sheet里面的所有單元格數(shù)據(jù)
名字：sheet的名字
四、操作單元格
#?-*-?coding:?utf-8?-*-
#讀取單元格數(shù)據(jù)
from?openpyxl?import?Workbook
wb?=?Workbook()
ws1?=?wb.create_sheet("Mysheet")???????????#創(chuàng)建一個(gè)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")? ? ? ? ? #創(chuàng)建一個(gè)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)
#操作多列,獲取每一個(gè)值
print (ws1["A:C"])
for column in ws1["A:C"]:
? ? for cell in column:
? ? ? ? print (cell.value)
#最大行號(hào)和列號(hào)
print(ws1.max_row,ws1.max_column)
#最小行號(hào)和列號(hào)
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")? ? ? ? ? #創(chuàng)建一個(gè)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")
