[Python] How to Use "xlrd" package to read XLS file

Last Updated on 2021-05-31 by Clay

Introduction

I always meet any problem everyday, to use a new tool to solve problems has become part of my life.

Loading a Excel file is my problem today. I used to read csv if I can, but today I have to think about how to open a XLS file.

God, and it is a many tables XLS file.

Fortunately, there is a package "xlrd" in Python, so I can load the XLS file just like csv file. The following is my note about using this tool.

How to use xlrd

xlrd is a third-party package, so, if you are not install it, maybe you have to use this command:

pip3 install xlrd

And then, we start!

Suppose we have a xlsx document like this:

Code for show the tables (sheet_name):

# -*- coding: utf-8 -*-
import xlrd

# Load xls file
data = xlrd.open_workbook('test.xlsx')

for n in range(len(data.sheet_names())):
    print(n)

Output:

0
1

First, we need to "import xlrd". and we use "open_workbook()" function to open our test xlsx file.

data.sheet_names() can show our all table name. In here, I just only print the index of table.

# -*- coding: utf-8 -*-
import xlrd

# Load xls file
data = xlrd.open_workbook('test.xlsx')

for n in range(len(data.sheet_names())):
    table = data.sheets()[n]

    for i in range(table.nrows):
        print('Page {}: '.format(n), end='')
        print(table.row_values(i))

Output:

Page 0: ['昨天', '今天', '明天']
Page 0: ['下雨', '晴天', '颱風']
Page 1: ['過去', '現在', '未來']
Page 1: ['15年', '1年', '100年']

data.sheets()[n] can decide "nth" table (sheet) we want to load.

"table.nrows" can show how many rows we have, and I use "row_values(i)" to print all row in every table (sheet).

Of course you can print "column", too.

# -*- coding: utf-8 -*-
import xlrd

# Load xls file
data = xlrd.open_workbook('test.xlsx')

for n in range(len(data.sheet_names())):
    table = data.sheets()[n]

    for i in range(table.ncols):
        print('Page {}: '.format(n), end='')
        print(table.col_values(i))

Output:

Page 0: ['昨天', '下雨']
Page 0: ['今天', '晴天']
Page 0: ['明天', '颱風']
Page 1: ['過去', '15年']
Page 1: ['現在', '1年']
Page 1: ['未來', '100年']

That's it, we're done! Basically reading data can never be more difficult for us!