python 读取XLSX文件的模块方法和一般方法对比

最新推荐文章于 2024-09-06 13:51:16 发布

Arthur54271

最新推荐文章于 2024-09-06 13:51:16 发布

阅读量1.3k

点赞数

分类专栏： xlrd Python3 文章标签： xlrd xlsx

本文链接：https://blog.csdn.net/zbrj12345/article/details/81010626

版权

Python3 同时被 2 个专栏收录

104 篇文章 0 订阅

订阅专栏

xlrd

0 篇文章 0 订阅

订阅专栏

本文对比了使用Python读取XLSX文件的两种方法：List Comprehension和nested loop。通过示例展示了如何获取特定单元格的数据，如拉丁美洲和加勒比海的相关信息。此外，还介绍了如何获取工作表的行数、列数以及单元格的值，以及如何处理日期数据，将Excel格式的时间转换为Python datetime元组。

摘要由CSDN通过智能技术生成

import xlrd,os

datadir='/Users/apple/Downloads/tableau_files'
datafile='World Bank CO2 - Tableau Public Learning Resource_zh-CN.xlsx'

def parse_file(datafile):
    workbook=xlrd.open_workbook(datafile)
    sheet=workbook.sheet_by_index(0)

    #二维数据，内层每一个维度都是一行数据
    data=[[sheet.cell_value(r,col)
                for col in range(sheet.ncols)]
                    for r in range(sheet.nrows)]
    #print(data)
    print('\nList Comprehension')
    print('data[2][2]:')
    print(data[2][2])

    print('\nCells in a nested loop:')
    for row in range(sheet.nrows):
        for col in range(sheet.ncols):
            if row==50:
                print(sheet.cell_value(row,col))

    #other useful methods:
    print('\nROWS,COLUMNS,AND CELLS')
    print('Number of rows in the sheet:')
    print(sheet.nrows)
    print('Type of data in celll (row 3,col 2)：')
    #单元类型(ctype):0 empty,1 string,2 number, 3 date,4 boolean, 5 error
    print(sheet.cell_type(3,2))
    print('Value in cell (row 3,col 2):')
    print(sheet.cell_value(3,2))
    print('Get a slice of values in column 3,from rows 1-3')
    print(sheet.col_values(3,start_rowx=1,end_rowx=4))

    print('\nDATES')
    print('Type of data in cell  (row1,col 0):')
    print(sheet.cell_type(1,3))
    exceltime=sheet.cell_value(1,3)
    print('Time in Excel format:')
    print(exceltime)
    print('Convert time to a Python datetime tuple,from the Excel float:')
    print(xlrd.xldate_as_tuple(exceltime,0))
    data={
        'maxtime':(0,0,0,0,0,0),
        'maxvalue':0,
        'mintime':(0,0,0,0,0,0),
        'minvalue':0,
        'avgcoast':0
    }
    return data
if __name__=='__main__':
    datafile=os.path.join(datadir,datafile)
    parse_file(datafile)

/Library/Frameworks/Python.framework/Versions/3.6/bin/python3.6 /Users/apple/PycharmProjects/udacity/read_xlsx01.py

List Comprehension
data[2][2]:
拉丁美洲和加勒比海

Cells in a nested loop:
ABW
阿鲁巴
拉丁美洲和加勒比海
2009.0
2522.896
24.876705845

ROWS,COLUMNS,AND CELLS
Number of rows in the sheet:
11128
Type of data in celll (row 3,col 2)：
1
Value in cell (row 3,col 2):
拉丁美洲和加勒比海
Get a slice of values in column 3,from rows 1-3
[1960.0, 1961.0, 1962.0]

DATES
Type of data in cell (row1,col 0):
2
Time in Excel format:
1960.0
Convert time to a Python datetime tuple,from the Excel float:
(1905, 5, 13, 0, 0, 0)

Process finished with exit code 0