How to read a microsoft excel file using python ?


To read an excel file using python, a solution is to use the python module called xlrd. An example with a file called 'read_excel_file_with_python.xlsx':

How to read a microsoft excel file using python ? How to read a microsoft excel file using python ?
How to read a microsoft excel file using python ?

Get the spreadsheet names

import xlrd
import numpy as np

workbook = xlrd.open_workbook('read_excel_file_with_python.xlsx')
SheetNameList = workbook.sheet_names()
for i in np.arange( len(SheetNameList) ):
    print( SheetNameList[i] )

here the document has two spreadsheets:

Personal Data
Public Data

Select a spreadsheet:

worksheet = workbook.sheet_by_name(SheetNameList[0])
num_rows = worksheet.nrows 
num_cells = worksheet.ncols 
print( 'num_rows, num_cells', num_rows, num_cells )

returns

num_rows, num_cells 6 4

Read a spreadsheet

curr_row = 0
while curr_row < num_rows:
    row = worksheet.row(curr_row)
    #print row, len(row), row[0], row[1]
    print( 'Row: ', curr_row )
    print( row, len(row), row[0] )
    curr_cell = 0
    while curr_cell < num_cells:
        # Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank
        cell_type = worksheet.cell_type(curr_row, curr_cell)
        cell_value = worksheet.cell_value(curr_row, curr_cell)
        print( ' ', cell_type, ':', cell_value )
        curr_cell += 1
    curr_row += 1

returns

    Row:  0
[text:'First Name', text:'Last Name', text:'Age', text:'Height'] 4 text:'First Name'
  1 : First Name
  1 : Last Name
  1 : Age
  1 : Height
Row:  1
[text:'John', text:'Doe', number:24.0, number:190.0] 4 text:'John'
  1 : John
  1 : Doe
  2 : 24.0
  2 : 190.0
Row:  2
[text:'Elijah', text:'Baley', number:31.0, number:169.0] 4 text:'Elijah'
  1 : Elijah
  1 : Baley
  2 : 31.0
  2 : 169.0
Row:  3
[text:'Paul', text:'Edison', number:22.0, number:185.0] 4 text:'Paul'
  1 : Paul
  1 : Edison
  2 : 22.0
  2 : 185.0
Row:  4
[text:'Martin', text:'Cage', number:41.0, number:176.0] 4 text:'Martin'
  1 : Martin
  1 : Cage
  2 : 41.0
  2 : 176.0
Row:  5
[text:'Robert', text:'Lemon', number:32.0, number:195.0] 4 text:'Robert'
  1 : Robert
  1 : Lemon
  2 : 32.0
  2 : 195.0

References

Links Site
xlrd pypi.org
Lien externe How to read els file in python ? youlikeprogramming.com
Python - Write to Excel Spreadsheet stackoverflow