python excel操作

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:\Python27\Scripts>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 "\u5149\u8363\u4e4b\u8def\u81ea\u52a8\u5316\u6d4b\u8bd5\u57f9\u8bad">

<Worksheet "New Title">

[u'\u5149\u8363\u4e4b\u8def\u81ea\u52a8\u5316\u6d4b\u8bd5\u57f9\u8bad', 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:\Python27\Scripts>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:\Python27\Scripts>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:\Python27\Scripts>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:\Python27\Scripts>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:\Python27\Scripts>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:\Python27\Scripts>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:\Python27\Scripts>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>)