CODEWITHSUNDEEP
pythonexcelopenpyxl
user3603524
user3603524

Reputation:

How to print all column values from excel using python

I have an Excel sheet like this

Listener_name   Listener IP     Listener Service
server1         12.xx.xx.xx       java
server2         12.xx.xx.xx       java
server3         127.0.0.1        oracle

and I want to print all values for Listener IP column.

My current Python code looks like this:

book = openpyxl.load_workbook('report-201808March0709AM522986.xlsx')

sheet = book.active

a1 = sheet['J1']
a2 = sheet['J2']
a3 = sheet['J3']

print(a1.value)
print(a2.value)
print(a3.value)

but it's only 3 columns of values and I have more than 200 values. Is there any way we can print all values using a loop?

Upvotes: 3

Views: 27687

Answers (2)

ShivaGaire
ShivaGaire

Reputation: 2811

I hope this will help you,

from openpyxl import load_workbook
book = load_workbook('report-201808March0709AM522986.xlsx')
sheet = book['sheet name']

The rows of the sheet can be accessed using sheet.rows. To iterate over the rows in a sheet, use:

for row in sheet.rows:
    print row[1].value

As each row in rows is a list of Cells, use Cell.value to get the contents of the Cell.

Edit: Added output

Listener IP
12.xx.xx.xx
12.xx.xx.xx
127.0.0.1

Upvotes: 1

jpp
jpp

Reputation: 164623

You can use pandas for this and avoid a loop.

import pandas as pd

pd.options.display.max_rows = 999

df = pd.read_excel('file.xlsx')

print(df['Listener IP'])

Upvotes: 1

Related Questions