操作excel

一、写一个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')