November 9th, 2017

Catalin George Festila: Using the xlrd python module.

Programing, Python, by admin.

The issue for today is to deal with xlrd python module and python verison 3.
I try to read and print the xlsx file from the electricity supplier of my mother’s home.
This idea can be used to extract data from files for consumption calculation and forecasting, because the python programming language is very efficient in calculations.
Let’s start with the installation of xlrd python module using python and Windows 10 operating system.

C:\Python27\Scripts>pip install xlrd
Collecting xlrd
Downloading xlrd-1.0.0.tar.gz (2.6MB)
100% |################################| 2.6MB 443kB/s
Installing collected packages: xlrd
Running setup.py install for xlrd ... done
Successfully installed xlrd-1.0.0

The installation of xlrd python module under Linux OS – Fedora distro.

[root@localhost mythcat]# pip install xlrd
Collecting xlrd
  Downloading xlrd-1.0.0.tar.gz (2.6MB)
    100% |████████████████████████████████| 2.6MB 529kB/s
Building wheels for collected packages: xlrd
  Running setup.py bdist_wheel for xlrd ... done
  Stored in directory: /root/.cache/pip/wheels/40/...
Successfully built xlrd
Installing collected packages: xlrd
Successfully installed xlrd-1.0.0

First, my example read a xlsx file downloaded from google drive.
The area of cells start from 0,0 that can be help you to read a cell or a area of cells.
This is my python script that read that file:

import xlrd
#this will open the file and print some cells and rows
def open_file(path):
"""
Open and read an Excel file
"""
book = xlrd.open_workbook(path)
# print number of sheets
print('the number of sheets')
print (book.nsheets)
# print sheet names
print('the names of sheet')
print (book.sheet_names())
# get the first worksheet
first_sheet = book.sheet_by_index(0)
# read a row and print it
print('print row number 4')
print (first_sheet.row_values(4))
# read a cell
print('print cell from 6 and 2')
cell = first_sheet.cell(6,2)
print('print cell')
print('print cell.value')
print (cell)
print (cell.value)
# read a row slice
print('a row with cells')
cells = first_sheet.row_slice(rowx=10,start_colx=0,end_colx=11)
print(cells)
print('first 20 rows with cells')
for cell in range(20):
print (first_sheet.row_values(cell))
#the main function
if __name__ == "__main__":
path = "test2.xlsx"
open_file(path)

The result of this script is this output:

the number of sheets
1
the names of sheet
['Worksheet']
print row number 4
['', 'Istoric valori consum', '', '', '', '', '']
print cell from 6 and 2
print cell
print cell.value
text:'2013 - 2017'
2013 - 2017
a row with cells
[empty:'', text:'Nume Prenume', text:'LUCICA FESTILA', empty:'', empty:'', text:'Cod încasare', text:'00220xx']
first 20 rows with cells
['', '', '', '', '', '', '']
['', '', '', '', '', '', '']
['', '', '', '', '', '', '']
['', '', '', '', '', '', '']
['', 'Istoric valori consum', '', '', '', '', '']
['', '', '', '', '', '', '']
['', 'Perioada:', '2013 - 2017', '', '', '', '']
['', '', '', '', '', '', '']
['', 'Date client', '', '', '', 'Detalii loc de consum ', '']
['', 'Cod abonat', 1002xxx.0, '', '', 'Cod loc consum', 50021xxx.0]
['', 'Nume Prenume', 'LUCICA FESTILA', '', '', 'Cod încasare', '0022xxx']
['', 'Adresa', '1 Mai, 2, SC.B, AP.11', '', '', 'Adresa', 'Suceava, Falticeni (SV), 1 Mai, 2 B, 11']
['', '', '', '', '', '', '']
['', '', '', '', '', '', '']
['', 'No.', 'Anul emiterii', 'Luna emiterii', 'Consum [KWH]', '', '']
['', 1.0, 2017.0, '09', 93.0, '', '']
['', 2.0, 2017.0, '08', 97.0, '', '']
['', 3.0, 2017.0, '07', 97.0, '', '']
['', 4.0, 2017.0, '06', 86.0, '', '']
['', 5.0, 2017.0, '05', 90.0, '', '']

This is a screenshot with this data from my file .

Back Top

Leave a Reply