python

当前位置:首页 > openpyxl教程 > 当前文章

openpyxl教程

openpyxl读取所有行数据之rows属性

2020-04-18 83赞 python中国网
每篇文章努力于解决一个问题!python高级、python面试全套、操作系统经典课等可移步文章底部。

  openpyxl读取一个sheet的所有行数据可以用rows属性,官方文档如下:

  If you need to iterate through all the rows or columns of a file, you can instead use the Worksheet.rows property:

  sheet的rows属性返回的是一个生成器,如下:

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

wb = load_workbook('test.xlsx',data_only=True)

for sheet in wb:
    sheet_name = sheet.title
    data_all = sheet.rows
    print('sheet名:',sheet_name)
    print(data_all)


sheet名: 北京
<generator object Worksheet._cells_by_row at 0x0000024F1B2E7570>
sheet名: 上海
<generator object Worksheet._cells_by_row at 0x0000024F1B2E75C8>
sheet名: 杭州
<generator object Worksheet._cells_by_row at 0x0000024F1B2E7570>


  可以直接遍历生成器也可以使用tuple将生成器变为元组来输出行:

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

wb = load_workbook('test.xlsx',data_only=True)

for sheet in wb:
    sheet_name = sheet.title
    data_all = sheet.rows
    print('sheet名:',sheet_name)
    data_tuple = tuple(data_all)
    for row in data_tuple:
        print(row)


sheet名: 北京
(<Cell '北京'.A1>, <Cell '北京'.B1>)
(<Cell '北京'.A2>, <Cell '北京'.B2>)
(<Cell '北京'.A3>, <Cell '北京'.B3>)
(<Cell '北京'.A4>, <Cell '北京'.B4>)
(<Cell '北京'.A5>, <Cell '北京'.B5>)
(<Cell '北京'.A6>, <Cell '北京'.B6>)
(<Cell '北京'.A7>, <Cell '北京'.B7>)
sheet名: 上海
(<Cell '上海'.A1>, <Cell '上海'.B1>)
(<Cell '上海'.A2>, <Cell '上海'.B2>)
(<Cell '上海'.A3>, <Cell '上海'.B3>)
(<Cell '上海'.A4>, <Cell '上海'.B4>)
(<Cell '上海'.A5>, <Cell '上海'.B5>)
(<Cell '上海'.A6>, <Cell '上海'.B6>)
(<Cell '上海'.A7>, <Cell '上海'.B7>)
(<Cell '上海'.A8>, <Cell '上海'.B8>)
(<Cell '上海'.A9>, <Cell '上海'.B9>)
(<Cell '上海'.A10>, <Cell '上海'.B10>)
(<Cell '上海'.A11>, <Cell '上海'.B11>)
sheet名: 杭州
(<Cell '杭州'.A1>, <Cell '杭州'.B1>)
(<Cell '杭州'.A2>, <Cell '杭州'.B2>)
(<Cell '杭州'.A3>, <Cell '杭州'.B3>)
(<Cell '杭州'.A4>, <Cell '杭州'.B4>)
(<Cell '杭州'.A5>, <Cell '杭州'.B5>)
(<Cell '杭州'.A6>, <Cell '杭州'.B6>)
(<Cell '杭州'.A7>, <Cell '杭州'.B7>)
(<Cell '杭州'.A8>, <Cell '杭州'.B8>)
(<Cell '杭州'.A9>, <Cell '杭州'.B9>)
(<Cell '杭州'.A10>, <Cell '杭州'.B10>)
(<Cell '杭州'.A11>, <Cell '杭州'.B11>)


  输出元格的数据,需要value属性:

# -*- coding: utf-8 -*-
from openpyxl import load_workbook

wb = load_workbook('test.xlsx',data_only=True)

for sheet in wb:
    sheet_name = sheet.title
    data_all = sheet.rows
    print('sheet名:',sheet_name)
    data_tuple = tuple(data_all)
    for row in data_tuple:
        print('===一行数据如下===:')
        for cell in row:
            value = cell.value
            print(value)

sheet名: 北京
===一行数据如下===:
贡院6号
https://bj.5i5j.com/xiaoqu/117460.html
===一行数据如下===:
万年花城二期
https://bj.5i5j.com/xiaoqu/369118.html
===一行数据如下===:
铁医新楼
https://bj.5i5j.com/xiaoqu/428075.html
===一行数据如下===:
百万庄大街乙21号楼
https://bj.5i5j.com/xiaoqu/14042.html
===一行数据如下===:
观澳园
https://bj.5i5j.com/xiaoqu/3464.html
===一行数据如下===:
天鹅湾南区
https://bj.5i5j.com/xiaoqu/10522.html
===一行数据如下===:
报国寺1号院
https://bj.5i5j.com/xiaoqu/6250.html
sheet名: 上海
===一行数据如下===:
静安豪景苑
https://sh.5i5j.com/xiaoqu/79364.html
===一行数据如下===:
新泾苑
https://sh.5i5j.com/xiaoqu/138741.html
===一行数据如下===:
兆嘉苑
https://sh.5i5j.com/xiaoqu/367631.html
===一行数据如下===:
天山怡景苑
https://sh.5i5j.com/xiaoqu/385850.html
===一行数据如下===:
金星绿苑
https://sh.5i5j.com/xiaoqu/390212.html
===一行数据如下===:
申晖小区
https://sh.5i5j.com/xiaoqu/436536.html
===一行数据如下===:
共康四村
https://sh.5i5j.com/xiaoqu/197400.html
===一行数据如下===:
崧泽华城青湖苑
https://sh.5i5j.com/xiaoqu/377290.html
===一行数据如下===:
新昌里公寓
https://sh.5i5j.com/xiaoqu/47394.html
===一行数据如下===:
建国西路158弄
https://sh.5i5j.com/xiaoqu/125420.html
===一行数据如下===:
蔷薇七村
https://sh.5i5j.com/xiaoqu/388767.html
sheet名: 杭州
===一行数据如下===:
复兴城市家园
https://hz.5i5j.com/xiaoqu/100000000001038.html
===一行数据如下===:
木材新村
https://hz.5i5j.com/xiaoqu/100000000004309.html
===一行数据如下===:
万寿亭街
https://hz.5i5j.com/xiaoqu/100000000002688.html
===一行数据如下===:
瓶窑镇凤溪路
https://hz.5i5j.com/xiaoqu/100000000000367.html
===一行数据如下===:
孝子坊
https://hz.5i5j.com/xiaoqu/100000000002902.html
===一行数据如下===:
永和坊
https://hz.5i5j.com/xiaoqu/100000000002147.html
===一行数据如下===:
教工路131号
https://hz.5i5j.com/xiaoqu/100000000001423.html
===一行数据如下===:
星星港湾琴海居
https://hz.5i5j.com/xiaoqu/100000000005718.html
===一行数据如下===:
文二路98号
https://hz.5i5j.com/xiaoqu/100000000001269.html
===一行数据如下===:
龙悦湾
https://hz.5i5j.com/xiaoqu/100000000006161.html
===一行数据如下===:
意盛花苑
https://hz.5i5j.com/xiaoqu/100000000002797.html



文章评论

openpyxl读取所有行数据之rows属性文章写得不错,值得赞赏