一、寫(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')