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,
    
    
2. I have Excel as below,
    
    
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.

   import openpyxl
   data = openpyxl.load_workbook('desktop/demo.xlsx')
   type(data)

   Output: <class 'openpyxl.workbook.workbook.Workbook'>
    
    
7. Now we have to see all the workbook in excel

   data.get_sheet_names()

   Output : ['Sheet1']
    
    
8. Now we create a sheet objects for fetching cell values through this sheet object.

   sheet = data.get_sheet_by_name('Sheet1')
   sheet['A1'].value

   Output : ‘first_name’
    
    
9. Getting First column value from excel sheet:

   for cell in range(1,17):
   
        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
    
    
    
10. Get maximum row and column in sheet.

    sheet.max_row

    Output: 17

    sheet.max_column

    Output: 7
     
     
11. Getting All results from using python code,

    for r in range(1,17):
       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
     
     
    So openpyxl gives many methods to real excel files that give many features to use excel files in many ways.