Reading Excel File In python

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 method for read 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 in desktop.

  4. Now we open Excel file using python code.

  5. Write python in 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'>

    output

  7. Now we have to see all workbook in excel

    data.get_sheet_names()

    Output : ['Sheet1']

    output

  8. Now we create sheet object for fetching cell value through this sheet object.

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

    Output : ‘first_name’

    output

  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

    output

  10. Get maximum row and column in sheet.

    sheet.max_row

    Output : 17

    sheet.max_column

    Output : 7

    output

  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,colu
    n=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.