Create Excel Spreadsheet Using Python and Random Numbers

Locked lesson.

Upgrade

About this lesson

Let's take what we've learned so far and put it to use with an exercise!

Quick reference

Create Excel Spreadsheet Using Python and Random Numbers

Let's take what we've learned so far and put it to use with an exercise!

When to use

Take a few minutes right now and try to do this exercise without refering to this guide.

Instructions

from openpyxl.workbook import Workbook
from openpyxl import load_workbook
from random import randint

 

#Create A Workbook
wb = Workbook()

 

# Set Active Worksheet
ws = wb.active

 

# Name Active Sheet "Salaries"
ws.title = "Salaries"

 

# Create list of 5 worker names
names = ["John", "Mary", "Steve", "Tina", "Bob"]

 

# Randomly generate salaries between $50,000 and $125,000
# from random import randint
# randint(50000, 125000)

# Create 2 Columns headers in A1 and B1: Names, Salaries
ws['A1'] = "Names"
ws['B1'] = "Salaries"

 

# Add Names and Salaries to Columns
starting_row = 2
for name in names:
                ws.cell(row=starting_row, column=1).value = name
                ws.cell(row=starting_row, column=2).value = randint(50000, 125000)
                starting_row = starting_row + 1

 

# Save Spreadsheet as company_salaries.xlsx
wb.save('company_salaries.xlsx')

 

Hints & tips

  • Have fun with this exercise!
Login to download
  • 00:04 Okay, so I think it's time to take a little break, take a step back and
  • 00:07 try an exercise where we can put to use all the stuff we've learned up until now.
  • 00:11 So I've created a file called salary.py, and what I want us to do is create
  • 00:16 a workbook, set an active worksheet, name our worksheet.
  • 00:20 Then create a Python list of five worker names and then randomly generate salaries
  • 00:24 for each of those names between 50,000 and $125,000 each.
  • 00:28 Then create a spreadsheet with two column headers, A1 and B1,
  • 00:32 with the headers being names and salaries.
  • 00:35 Then we want to add our names and salaries to the columns, and
  • 00:37 then save our spreadsheet.
  • 00:39 So pretty simple and straightforward exercise.
  • 00:42 But we should be able to use most of the things we've learned up until now,
  • 00:44 including all that cool Python stuff that we learned at the beginning.
  • 00:47 So let's just knock this out, so let's create a workbook and
  • 00:50 we want to set this equal to a workbook instance, okay?
  • 00:54 Now, let's create our active worksheet, so ws = wb.active, okay?
  • 01:01 So let's name our worksheet, so ws.title, and
  • 01:04 let's set that equal to Salaries, okay?
  • 01:07 Now let's create a Python list and let's just call this names.
  • 01:12 And this is going to be a Python list, and we can pick any five names we want.
  • 01:17 So let's go John,
  • 01:21 Mary, Steve, Tina,
  • 01:27 and Bob, okay?
  • 01:31 So now, let's randomly generate some stuff.
  • 01:33 So, we need to remember import from random import randint.
  • 01:39 And we should probably put this at the top of our program because that's
  • 01:42 where we always import all of our stuff, right?
  • 01:45 So let me just comment this out, so it's here so
  • 01:48 we can remember that's how we did it.
  • 01:50 And then remember to randomly, create things we would call randint,
  • 01:55 and we want this between 50,000 and 125,000, okay?
  • 02:00 And we'll reference this later on.
  • 02:02 So now we want to create two columns in our spreadsheet.
  • 02:06 So let's go ws, and we know these
  • 02:10 are going to be in A1 and B1, right?
  • 02:15 First two columns in the first row of our spreadsheet.
  • 02:18 So the first one, the header will be Names, and the second one,
  • 02:21 the header will be Salaries.
  • 02:23 So now let's come down here and
  • 02:25 save this while we're thinking about it before we work on this last step here.
  • 02:29 So let's go wb.save, and we want to save
  • 02:34 this as company_salaries.xlsx.
  • 02:39 And we want to just save it in the same directory where we've been working so far,
  • 02:43 our Python-xl directory where our salary.py file is saved.
  • 02:46 And now we need to add all of the randomly generated salaries to our Salaries column,
  • 02:52 and we want to use Python for this.
  • 02:55 So let's create a for loop, let's go for name in names.
  • 02:58 Because we want to loop through all of these names, and for
  • 03:02 each name, we want to generate a salary.
  • 03:05 So before we create our for loop, let's create a counter,
  • 03:09 let's go starting_row and let's just set this equal to 2.
  • 03:14 Why?
  • 03:14 Because our spreadsheet already has in the first row our headers, these guys, right?
  • 03:19 So we want this stuff to be generated in row 2, so
  • 03:23 we'll just create a variable to do that.
  • 03:26 So inside of here, we know we want these in the second column,
  • 03:31 but we don't necessarily know where in the second column.
  • 03:36 So we're going to use the second method for
  • 03:38 adding data to cells that we talked about several videos ago.
  • 03:42 So let's go ws.cell, and we want to set the row, and
  • 03:47 we'll start that at starting_row, which is this one, 2.
  • 03:52 And then we want the column to equal 1, and
  • 03:57 then the value, we'll set that equal to name.
  • 04:04 So in the first column,
  • 04:05 we want to print out the name, which is one of these guys, right?
  • 04:09 So every time we loop through here, the next name will get printed in this column.
  • 04:14 But then the next time we loop through, we don't want it to be in the same row,
  • 04:18 we want it to be the next row down.
  • 04:19 So what we need to do is go starting_row = starting_row + 1.
  • 04:26 So every time this loops, starting_row will have 1 added to it,
  • 04:30 and that will push it down the screen and add the next name below it.
  • 04:34 So we don't just want the name, we also want the salary,
  • 04:38 this random thing right here.
  • 04:40 So we can do this a second time, so let's go ws.cell, and
  • 04:44 want the row to equal starting_row, same thing.
  • 04:49 But we want the column to equal 2, because this is going to be the Salaries column,
  • 04:54 which is our second column.
  • 04:55 And we want this to be value equal whatever this thing is.
  • 04:59 So let's just copy this and paste that right in there, and
  • 05:02 let's comment this out up here because we don't need it a second time up here.
  • 05:05 So okay, that looks good.
  • 05:08 So every time we loop through here, the first time in row 2,
  • 05:12 we'll print out the first name here, which is John.
  • 05:15 And then randomly generate a salary next to it in the column next to it.
  • 05:19 Then we'll add 1 to this, loop through it again.
  • 05:22 So now starting_row will be 3, and then that will pronounce Mary and
  • 05:27 then Mary's random salary.
  • 05:29 And it will add 1 so starting row will become 4,
  • 05:31 4 will be Steve, Steve will have a salary.
  • 05:34 Add 1, 5, Tina, add 1, 6, Bob, and we're good to go.
  • 05:41 So okay, let's go ahead and save this, and let's run this guy one more time.
  • 05:48 Okay, so now we can head back over here and run our company salary.
  • 05:51 So now we can open this file again and we have names and salaries.
  • 05:54 We have John, Mary, Steve, Tina, and Bob,
  • 05:56 and we've randomly generated salaries between 50,000 and 125,000 each.
  • 06:01 Very, very cool.
  • 06:02 Now notice these, the first one is 79,000.
  • 06:04 If we close this and run it again, and
  • 06:08 then open this again, you'll notice now it's 90.
  • 06:13 Because we've run it again it's randomly generating different values,
  • 06:16 different salaries, and they're very, very cool.
  • 06:18 So fun little exercise and we spent through that pretty quickly.
  • 06:22 I recommend you start over again and delete everything we did and
  • 06:25 just try this on your own.
  • 06:27 See if you can do it without watching the video, I think that's a great exercise and
  • 06:31 a lot of fun.
  • 06:32 So in the next video, we're going to start to look at formatting and
  • 06:35 styling spreadsheets.

Lesson notes are only available for subscribers.

Gift this course