CODEWITHSUNDEEP
pythonexcelrowxlsxwriter
user3264821
user3264821

Reputation: 185

python xlsxwriter write cell according to row data

I have python dictionaries: 

student_age = {'bala':20,'raju':21}
student_id = {'bala':289,'raju':567}

and ten more similar dictionaries with key as student name and value different field.

Expected excel result: enter image description here

Instead of 

worksheet.write(0,2,20)

I want to write according to student names like write("bala"-> "age" ,20)

Updated code:

import xlsxwriter
workbook = xlsxwriter.Workbook('student_data.xlsx')
worksheet = workbook.add_worksheet()

student_age={'bala':20,'raju':21,'ram':22}
student_id={'bala':289,'ram':567,'raju':654}
students = student_id.keys()
print(len(student_age.keys()))
fields = [student_age, student_id] # add other dicts here...

for row, student in enumerate(students):
    worksheet.write(row, 0, student) # name column
    for col, student_data in enumerate(fields):
        col = col + 1
        worksheet.write(row, col, student_data[student])
workbook.close()

The above code is working fine but with following disputes:

  1. The output starts from row 0,column 0 but I want the headings on row 1 and need the output from row 1
    1. If student_id keys is 20 and i have one dictionary with just 15 students , how to leave blank for those items without getting error and traverse it?

Upvotes: 1

Views: 3693

Answers (2)

lemonhead
lemonhead

Reputation: 5518

You could simply add all your python dictionaries to a list so you can iterate through them for each student to populate each column for every row. Something like this:

Updated to address OP's comments

For #1, simply write the headers in the first row, and then offset the other rows by one

For #2, you can simply take advantage of dict.get() instead of the [] notation to assign a default if the id is not in that dictionary:

import xlsxwriter
workbook = xlsxwriter.Workbook('student_data.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})

students = student_id.keys()
fields = [student_age, student_id] # add other dicts here...
headers = ['name', 'age', 'id'] # ...

# write out a header row
for i, header in enumerate(headers):
    worksheet.write(0, i, header, bold)

for row, student in enumerate(student):
    row = row + 1
    worksheet.write(row, 0, student) # name column
    for col, student_data in enumerate(fields):
        col = col + 1
        worksheet.write(row, col, student_data.get(student, ''))

Upvotes: 1

Elliott Addi
Elliott Addi

Reputation: 380

Check this link to learn about dictionaries in python : http://learnpythonthehardway.org/book/ex39.html

Without providing some sample code it's hard to see what you did, so please Edit your question with sample code and I'll Edit my answer as well.

Did you iterate throws your dictionaries correctly and precise the row, columns of the excel file to write in?

Upvotes: 0

Related Questions