Styjun

Find this cartoon

Why does the hash of infinity have the digits of π?

Writing style before Elements of Style

Why did Jon Snow do this immoral act if he is so honorable?

What could a self-sustaining lunar colony slowly lose that would ultimately prove fatal?

Why are Stein manifolds/spaces the analog of affine varieties/schemes in algebraic geometry?

How to cut a climbing rope?

On San Andreas Speedruns, why do players blow up the Picador in the mission Ryder?

Of strange atmospheres - the survivable but unbreathable

Can I tell a prospective employee that everyone in the team is leaving?

Gravitational effects of a single human body on the motion of planets

The art of clickbait captions

Why A=2 and B=1 in the call signs for Spirit and Opportunity?

Are runways booked by airlines to land their planes?

How to deal with a colleague who is being aggressive?

What Armor Optimization applies to a Mithral full plate?

Must a warlock replace spells with new spells of exactly their Pact Magic spell slot level?

If a (distance) metric on a connected Riemannian manifold locally agrees with the Riemannian metric, is it equal to the induced metric?

My players want to grind XP but we're using milestone advancement

Do photons bend spacetime or not?

便利な工具 what does な means

Need to read my home electrical Meter

Why would a rational buyer offer to buy with no conditions precedent?

What does kpsewhich stand for?

how to loop through each row in excel spreadsheet using openpyxl

How to iterate through two lists in parallel?How to iterate over rows in a DataFrame in Pandas?Using openpyxl to copy from one workbook to another results in error when savingiterate through previously filtered rows openpyxlHow to add dictionaries to a DataFrame as a row?openpyxl causing loss of existing DataValidation in excelOptimal way to use tkinter and openpyxl to iterate through a spreadsheet?'NoneType' object has no attribute 'max_row'Iterating through top row of excel sheetPython elements in nested lists withing a dictionary

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-1

I would like to make the first column of each row in an excel spreadsheet as a key and rest of the values in that row as its value so that I can store them in a dictionary.
The problem is, when I loop through the rows and columns all the row values are getting stored in every key.

import openpyxl

from openpyxl import load_workbook

file = "test.xlsx"

#load the work book
wb_obj = load_workbook(filename = file)

wsheet = wb_obj['test']

#dictionary to store data
dataDict = 

value = []

row_count = wsheet.max_row
col_count = wsheet.max_column

# loop to get row and column values
for i in range(2, row_count+1):
 for j in range(i, col_count+1):
 key = wsheet.cell(row=i, column=1).value
 print (key)
 value.append(wsheet.cell(row=i, column=j).value)
 print (value)

 dataDict[key] = value

#prompt user for input
userInput = input("Please enter an id to find a person's details: ")

print (dataDict.get(int(userInput)))

data in spreadsheet:

Result I'm expecting:
1: ['John', 'Doe', 4567891234, 'johndoe@jd.ca'], 2: ['Wayne 'Kane', 1234567891, 'wd@wd.ca']

Result I got:
1: ['John', 'Doe', 4567891234, 'johndoe@jd.ca', 'Kane', 1234567891, 'wd@wd.ca'], 2: ['John', 'Doe', 4567891234, 'johndoe@jd.ca', 'Kane', 1234567891, 'wd@wd.ca']

python openpyxl

share|improve this question

asked Mar 24 at 1:06

BharathBharath

259

add a comment | 

-1

I would like to make the first column of each row in an excel spreadsheet as a key and rest of the values in that row as its value so that I can store them in a dictionary.
The problem is, when I loop through the rows and columns all the row values are getting stored in every key.

import openpyxl

from openpyxl import load_workbook

file = "test.xlsx"

#load the work book
wb_obj = load_workbook(filename = file)

wsheet = wb_obj['test']

#dictionary to store data
dataDict = 

value = []

row_count = wsheet.max_row
col_count = wsheet.max_column

# loop to get row and column values
for i in range(2, row_count+1):
 for j in range(i, col_count+1):
 key = wsheet.cell(row=i, column=1).value
 print (key)
 value.append(wsheet.cell(row=i, column=j).value)
 print (value)

 dataDict[key] = value

#prompt user for input
userInput = input("Please enter an id to find a person's details: ")

print (dataDict.get(int(userInput)))

data in spreadsheet:

Result I'm expecting:
1: ['John', 'Doe', 4567891234, 'johndoe@jd.ca'], 2: ['Wayne 'Kane', 1234567891, 'wd@wd.ca']

Result I got:
1: ['John', 'Doe', 4567891234, 'johndoe@jd.ca', 'Kane', 1234567891, 'wd@wd.ca'], 2: ['John', 'Doe', 4567891234, 'johndoe@jd.ca', 'Kane', 1234567891, 'wd@wd.ca']

python openpyxl

share|improve this question

asked Mar 24 at 1:06

BharathBharath

259

add a comment | 

I would like to make the first column of each row in an excel spreadsheet as a key and rest of the values in that row as its value so that I can store them in a dictionary.
The problem is, when I loop through the rows and columns all the row values are getting stored in every key.

import openpyxl

from openpyxl import load_workbook

file = "test.xlsx"

#load the work book
wb_obj = load_workbook(filename = file)

wsheet = wb_obj['test']

#dictionary to store data
dataDict = 

value = []

row_count = wsheet.max_row
col_count = wsheet.max_column

# loop to get row and column values
for i in range(2, row_count+1):
 for j in range(i, col_count+1):
 key = wsheet.cell(row=i, column=1).value
 print (key)
 value.append(wsheet.cell(row=i, column=j).value)
 print (value)

 dataDict[key] = value

#prompt user for input
userInput = input("Please enter an id to find a person's details: ")

print (dataDict.get(int(userInput)))

data in spreadsheet:

Result I'm expecting:
1: ['John', 'Doe', 4567891234, 'johndoe@jd.ca'], 2: ['Wayne 'Kane', 1234567891, 'wd@wd.ca']

Result I got:
1: ['John', 'Doe', 4567891234, 'johndoe@jd.ca', 'Kane', 1234567891, 'wd@wd.ca'], 2: ['John', 'Doe', 4567891234, 'johndoe@jd.ca', 'Kane', 1234567891, 'wd@wd.ca']

python openpyxl

share|improve this question

asked Mar 24 at 1:06

BharathBharath

259

import openpyxl

from openpyxl import load_workbook

file = "test.xlsx"

#load the work book
wb_obj = load_workbook(filename = file)

wsheet = wb_obj['test']

#dictionary to store data
dataDict = 

value = []

row_count = wsheet.max_row
col_count = wsheet.max_column

# loop to get row and column values
for i in range(2, row_count+1):
 for j in range(i, col_count+1):
 key = wsheet.cell(row=i, column=1).value
 print (key)
 value.append(wsheet.cell(row=i, column=j).value)
 print (value)

 dataDict[key] = value

#prompt user for input
userInput = input("Please enter an id to find a person's details: ")

print (dataDict.get(int(userInput)))

share|improve this question

asked Mar 24 at 1:06

BharathBharath

259

share|improve this question

asked Mar 24 at 1:06

BharathBharath

259

asked Mar 24 at 1:06

BharathBharath

259

asked Mar 24 at 1:06

BharathBharath

259

1 Answer
1

active

oldest

votes

1

Openpyxl already has a proper way to iterate through rows using worksheet.iter_rows(). You can use it to unpack the first cell's value as the key and the values from the other cells as the list in the dictionary, repeating for every row.

from openpyxl import load_workbook

file = "test.xlsx" #load the work book
wb_obj = load_workbook(filename = file)
wsheet = wb_obj['test']
dataDict = 

for key, *values in wsheet.iter_rows():
 dataDict[key.value] = [v.value for v in values]

share|improve this answer

answered Mar 24 at 1:15

jfaccionijfaccioni

724212

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How to omit first row in excel from this loop if we have names for columns like id, first name, last name, phone, etc
  
  – Bharath
  Mar 24 at 1:34
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Use the keyword argument min_row to specify from which row to start iterating: wsheet.iter_rows(min_row=2).
  
  – jfaccioni
  Mar 24 at 12:20
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55319851%2fhow-to-loop-through-each-row-in-excel-spreadsheet-using-openpyxl%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Openpyxl already has a proper way to iterate through rows using worksheet.iter_rows(). You can use it to unpack the first cell's value as the key and the values from the other cells as the list in the dictionary, repeating for every row.

from openpyxl import load_workbook

file = "test.xlsx" #load the work book
wb_obj = load_workbook(filename = file)
wsheet = wb_obj['test']
dataDict = 

for key, *values in wsheet.iter_rows():
 dataDict[key.value] = [v.value for v in values]

share|improve this answer

answered Mar 24 at 1:15

jfaccionijfaccioni

724212

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How to omit first row in excel from this loop if we have names for columns like id, first name, last name, phone, etc
  
  – Bharath
  Mar 24 at 1:34
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Use the keyword argument min_row to specify from which row to start iterating: wsheet.iter_rows(min_row=2).
  
  – jfaccioni
  Mar 24 at 12:20
  
  

  
  
  
  

add a comment | 

1

Openpyxl already has a proper way to iterate through rows using worksheet.iter_rows(). You can use it to unpack the first cell's value as the key and the values from the other cells as the list in the dictionary, repeating for every row.

from openpyxl import load_workbook

file = "test.xlsx" #load the work book
wb_obj = load_workbook(filename = file)
wsheet = wb_obj['test']
dataDict = 

for key, *values in wsheet.iter_rows():
 dataDict[key.value] = [v.value for v in values]

share|improve this answer

answered Mar 24 at 1:15

jfaccionijfaccioni

724212

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How to omit first row in excel from this loop if we have names for columns like id, first name, last name, phone, etc
  
  – Bharath
  Mar 24 at 1:34
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Use the keyword argument min_row to specify from which row to start iterating: wsheet.iter_rows(min_row=2).
  
  – jfaccioni
  Mar 24 at 12:20
  
  

  
  
  
  

add a comment | 

Openpyxl already has a proper way to iterate through rows using worksheet.iter_rows(). You can use it to unpack the first cell's value as the key and the values from the other cells as the list in the dictionary, repeating for every row.

from openpyxl import load_workbook

file = "test.xlsx" #load the work book
wb_obj = load_workbook(filename = file)
wsheet = wb_obj['test']
dataDict = 

for key, *values in wsheet.iter_rows():
 dataDict[key.value] = [v.value for v in values]

share|improve this answer

answered Mar 24 at 1:15

jfaccionijfaccioni

724212

from openpyxl import load_workbook

file = "test.xlsx" #load the work book
wb_obj = load_workbook(filename = file)
wsheet = wb_obj['test']
dataDict = 

for key, *values in wsheet.iter_rows():
 dataDict[key.value] = [v.value for v in values]

share|improve this answer

answered Mar 24 at 1:15

jfaccionijfaccioni

724212

answered Mar 24 at 1:15

jfaccionijfaccioni

724212

answered Mar 24 at 1:15

jfaccionijfaccioni

724212