Reading Excel File In python

Introduction

 
Python has many repositories that give the reading feature in python to read Excel files easily. So, I will use openpyxl repositories for reading data from excel file.openpyxl have many methods for reading data from excel file.
  1. Install the repositories using command prompt type command: pip install openpyxl,
     
    cmd
     
  2. I have Excel as below,
     
    excel
     
  3. Now we will do some operation on this excel file that is saved on desktop.
     
  4. Now we open Excel file using python code.
     
  5. Write python in the command line.
     
  6. It will open python terminal then write some code for reading xlsx file.
    1. import openpyxl  
    2. data = openpyxl.load_workbook('desktop/demo.xlsx')  
    3. type(data) 
    Output: <class 'openpyxl.workbook.workbook.Workbook'>
     
    output
     
  7. Now we have to see all the workbook in excel
    1. data.get_sheet_names() 
    Output : ['Sheet1']
     
    output
     
  8. Now we create a sheet objects for fetching cell values through this sheet object.
    1. sheet = data.get_sheet_by_name('Sheet1')  
    2. sheet['A1'].value 
    Output : ‘first_name’
     
    output
     
  9. Getting First column value from excel sheet:
    1. for cell in range(1,17):  
    2.    
    3.      print(cell,sheet.cell(row=cell,column=1).value)  
    Output:
     
    1 first_nam
     
    2 James
     
    3 Josephine
     
    4 Art
     
    5 Lenna
     
    6 Donette
     
    7 Simona
     
    8 Mitsue
     
    9 Leota
     
    10 Sage
     
    11 Kris
     
    12 Minna
     
    13 Abel
     
    14 Kiley
     
    15 Graciela
     
    16 Cammy
     
    output
     
     
  10. Get maximum row and column in sheet.
    1. sheet.max_row 
    Output: 17
    1. sheet.max_column 
    Output: 7
     
    output
     
  11. Getting All results from using python code,
    1. for r in range(1,17):  
    2.    print(r,sheet.cell(row=r,column=1).value,sheet.cell(row=r,column=2).value,sheet.cell(row=r,column=3).value,sheet.cell(row=r,column=4).value,sheet.cell(row=r,colun=5).value,sheet.cell(row=r,column=6).value,sheet.cell(row=r,column=7).value) 
    Output :
     
    1 first_name last_name company_name address city county state
     
    2 James Butt Benton, John B Jr 6649 N Blue Gum St New Orleans Orleans LA
     
    3 Josephine Darakjy Chanay, Jeffrey A Esq 4 B Blue Ridge Blvd Brighton L
     
    4 Art Venere Chemel, James L Cpa 8 W Cerritos Ave #54 Bridgeport Glouces
     
    5 Lenna Paprocki Feltz Printing Service 639 Main St Anchorage Anchorage
     
    6 Donette Foller Printing Dimensions 34 Center St Hamilton Butler OH
     
    7 Simona Morasca Chapman, Ross E Esq 3 Mcauley Dr Ashland Ashland OH
     
    8 Mitsue Tollner Morlong Associates 7 Eads St Chicago Cook IL
     
    9 Leota Dilliard Commercial Press 7 W Jackson Blvd San Jose Santa Clara
     
    10 Sage Wieser Truhlar And Truhlar Attys 5 Boston Ave #88 Sioux Falls Mi
     
    11 Kris Marrier King, Christopher A Esq 228 Runamuck Pl #2808 Baltimore
     
    12 Minna Amigon Dorl, James J Esq 2371 Jerrold Ave Kulpsville Montgomery
     
    13 Abel Maclead Rangoni Of Florence 37275 St Rt 17m M Middle Island Suf
     
    14 Kiley Caldarera Feiner Bros 25 E 75th St #69 Los Angeles Los Angeles
     
    15 Graciela Ruta Buckley Miller & Wright 98 Connecticut Ave Nw Chagrin F
     
    16 Cammy Albares Rousseaux, Michael Esq 56 E Morehead St Laredo Webb TX
     
    output
     
    So openpyxl gives many methods to real excel files that give many features to use excel files in many ways.