- [Instructor] Now that we've seen how to load
and explore an existing workbook,
let's try our hand at creating one from scratch.
So let's go ahead and open up the code
in create_workbook.py.
And here in my code I've imported the openpyxl library
as well as some other imports
we're going to be using in a few moments.
So to create a new workbook,
I just need to use the constructor for the class.
So I'm going to make a variable named wb
and I'm going to set that to the result
of calling the Workbook function.
So that creates a new empty workbook
with a default worksheet in it.
So to write content into the workbook,
we have to get access to a worksheet,
and one way to do that is to get the active sheet.
So I'll make a variable called sheet
and I'll set that to wb.active.
And then I'm going to name that sheet
by setting the title property equal to First.
All right, so now that we've created our first workbook
and first worksheet, let's add some data.
We can treat the worksheet
as an indexable object using square brackets.
So, for example, I can just refer to cells
using regular letter, column, and row number notation.
So I can set the value of A1 to be a string.
And then let's set B1
equal to, let's try a number.
And then finally,
for cell C1,
let's make that a date time.
So I've already imported the datetime module here.
Let's insert a datetime
and we'll just pick some date in the future.
We'll just choose April Fool's day.
So Excel intelligently figures out what kind of data type
to make the cell based on the content.
So in this case I'll have text, a number, and a date.
And in this case, I'm using the letter and number notation,
but I can also use the cell function
to write data in the worksheet using index values.
So let's add a range of random numbers.
So I'm going to write a for loop,
so four i in range, and we'll start at 1 and go to 11.
I'm going to call the sheet.cell function,
and I'm going to put these random numbers in row 5
starting at column = i.
And I'll call the value function to put the value in there.
No, actually it's a value, it's a property,
it's not a function,
so I'll set the value property equal to,
and you can see I've imported the random module
up here in my import statement.
I'll just fill in some random integers.
So I'll call the randint function
to get a random integer between 1 and 50.
All right, so let's go ahead and save this code.
And, of course, nothing will happen
until we save the workbook,
so let's actually save the workbook using the save function.
So on the workbook object, I'm going to call the save function
and we'll give it a name, we'll call it NewWorkbook
.xlsx.
And then we'll print out a message that the workbook
has been created successfully!
All right, so let's go ahead and run this code.
Going to right click here,
run my Python in the terminal.
And sure enough, we get the message
that the workbook has been created successfully!
We can see it right here in the sidebar.
And once again, if you have the extension installed,
you can go ahead and click on that
and we can see that sure enough,
we've got rows A, B, and C with the Test Data.
So here's the string, here's the number,
and you can see that the date isn't properly being displayed
in the regular viewer,
and here are the row of random numbers I added.
So let's close this, let's go over to the folder
and let's open this up in Excel.
So you can see that here in Excel
the results are a little bit different
because Excel intelligently realizes
that this is a string, this is a number, this is a date,
and then here are all the random numbers
that I've added so far.
All right, okay, so let's continue.
Let's go ahead and close this.
I'm not going to save it.
All right, let's add another worksheet
using the create worksheet function.
So I'll make a variable named sheet2
and I'll call create_sheet
and I'll name this Second sheet.
And in sheet 2 I'm going to use the cell function,
and at row=2,
column=2,
I'm going to set the value
to be the string More Data.
And there's another convenient way
to add data to a worksheet,
and that's by using the append function.
So this function will add a new row of data
at the end of the existing data in the worksheet,
which means you don't need to figure out
where the data ends in order to add new content,
openpyxl will just do that for you.
So I'm going to call sheet2.append
and I'm going to pass a row of data,
so I'll pass in some strings.
All right, and we'll do that a few times
just to add a few rows.
All right, and you can see that we're saving the code still,
so let's save our updated example
and then let's run this again,
which will overwrite the existing file.
All right, so it's been created successfully.
Let's go ahead and open that up.
All right, and now we have two worksheets.
So we have the First worksheet,
which we saw in the previous iteration of the code,
so all that data is still here.
And now in our Second worksheet,
we have the string we added
along with the data added with the append rows function.