1
00:00:00,005 --> 00:00:01,007
- [Instructor] Now that we've seen how to load

2
00:00:01,007 --> 00:00:03,006
and explore an existing workbook,

3
00:00:03,006 --> 00:00:06,009
let's try our hand at creating one from scratch.

4
00:00:06,009 --> 00:00:08,005
So let's go ahead and open up the code

5
00:00:08,005 --> 00:00:12,002
in create_workbook.py.

6
00:00:12,002 --> 00:00:18,000
And here in my code I've imported the openpyxl library

7
00:00:18,000 --> 00:00:19,003
as well as some other imports

8
00:00:19,003 --> 00:00:21,003
we're going to be using in a few moments.

9
00:00:21,003 --> 00:00:22,007
So to create a new workbook,

10
00:00:22,007 --> 00:00:25,006
I just need to use the constructor for the class.

11
00:00:25,006 --> 00:00:27,007
So I'm going to make a variable named wb

12
00:00:27,007 --> 00:00:29,003
and I'm going to set that to the result

13
00:00:29,003 --> 00:00:32,006
of calling the Workbook function.

14
00:00:32,006 --> 00:00:36,000
So that creates a new empty workbook

15
00:00:36,000 --> 00:00:38,004
with a default worksheet in it.

16
00:00:38,004 --> 00:00:40,003
So to write content into the workbook,

17
00:00:40,003 --> 00:00:42,000
we have to get access to a worksheet,

18
00:00:42,000 --> 00:00:48,006
and one way to do that is to get the active sheet.

19
00:00:48,006 --> 00:00:50,007
So I'll make a variable called sheet

20
00:00:50,007 --> 00:00:54,000
and I'll set that to wb.active.

21
00:00:54,000 --> 00:00:56,003
And then I'm going to name that sheet

22
00:00:56,003 --> 00:01:01,009
by setting the title property equal to First.

23
00:01:01,009 --> 00:01:04,004
All right, so now that we've created our first workbook

24
00:01:04,004 --> 00:01:07,000
and first worksheet, let's add some data.

25
00:01:07,000 --> 00:01:08,004
We can treat the worksheet

26
00:01:08,004 --> 00:01:12,004
as an indexable object using square brackets.

27
00:01:12,004 --> 00:01:15,003
So, for example, I can just refer to cells

28
00:01:15,003 --> 00:01:20,000
using regular letter, column, and row number notation.

29
00:01:20,000 --> 00:01:25,008
So I can set the value of A1 to be a string.

30
00:01:25,008 --> 00:01:31,004
And then let's set B1

31
00:01:31,004 --> 00:01:36,006
equal to, let's try a number.

32
00:01:36,006 --> 00:01:40,005
And then finally,

33
00:01:40,005 --> 00:01:44,000
for cell C1,

34
00:01:44,000 --> 00:01:45,004
let's make that a date time.

35
00:01:45,004 --> 00:01:48,009
So I've already imported the datetime module here.

36
00:01:48,009 --> 00:01:54,002
Let's insert a datetime

37
00:01:54,002 --> 00:01:59,003
and we'll just pick some date in the future.

38
00:01:59,003 --> 00:02:01,004
We'll just choose April Fool's day.

39
00:02:01,004 --> 00:02:05,006
So Excel intelligently figures out what kind of data type

40
00:02:05,006 --> 00:02:08,007
to make the cell based on the content.

41
00:02:08,007 --> 00:02:13,004
So in this case I'll have text, a number, and a date.

42
00:02:13,004 --> 00:02:18,000
And in this case, I'm using the letter and number notation,

43
00:02:18,000 --> 00:02:20,006
but I can also use the cell function

44
00:02:20,006 --> 00:02:24,009
to write data in the worksheet using index values.

45
00:02:24,009 --> 00:02:28,004
So let's add a range of random numbers.

46
00:02:28,004 --> 00:02:31,006
So I'm going to write a for loop,

47
00:02:31,006 --> 00:02:36,003
so four i in range, and we'll start at 1 and go to 11.

48
00:02:36,003 --> 00:02:40,005
I'm going to call the sheet.cell function,

49
00:02:40,005 --> 00:02:44,003
and I'm going to put these random numbers in row 5

50
00:02:44,003 --> 00:02:50,004
starting at column = i.

51
00:02:50,004 --> 00:02:54,009
And I'll call the value function to put the value in there.

52
00:02:54,009 --> 00:02:56,001
No, actually it's a value, it's a property,

53
00:02:56,001 --> 00:02:57,003
it's not a function,

54
00:02:57,003 --> 00:03:00,000
so I'll set the value property equal to,

55
00:03:00,000 --> 00:03:03,002
and you can see I've imported the random module

56
00:03:03,002 --> 00:03:05,006
up here in my import statement.

57
00:03:05,006 --> 00:03:08,002
I'll just fill in some random integers.

58
00:03:08,002 --> 00:03:12,001
So I'll call the randint function

59
00:03:12,001 --> 00:03:18,004
to get a random integer between 1 and 50.

60
00:03:18,004 --> 00:03:22,004
All right, so let's go ahead and save this code.

61
00:03:22,004 --> 00:03:24,000
And, of course, nothing will happen

62
00:03:24,000 --> 00:03:25,007
until we save the workbook,

63
00:03:25,007 --> 00:03:29,007
so let's actually save the workbook using the save function.

64
00:03:29,007 --> 00:03:32,006
So on the workbook object, I'm going to call the save function

65
00:03:32,006 --> 00:03:37,009
and we'll give it a name, we'll call it NewWorkbook

66
00:03:37,009 --> 00:03:42,001
.xlsx.

67
00:03:42,001 --> 00:03:45,006
And then we'll print out a message that the workbook

68
00:03:45,006 --> 00:03:52,005
has been created successfully!

69
00:03:52,005 --> 00:03:56,004
All right, so let's go ahead and run this code.

70
00:03:56,004 --> 00:03:58,003
Going to right click here,

71
00:03:58,003 --> 00:04:00,006
run my Python in the terminal.

72
00:04:00,006 --> 00:04:01,007
And sure enough, we get the message

73
00:04:01,007 --> 00:04:03,007
that the workbook has been created successfully!

74
00:04:03,007 --> 00:04:06,005
We can see it right here in the sidebar.

75
00:04:06,005 --> 00:04:09,000
And once again, if you have the extension installed,

76
00:04:09,000 --> 00:04:11,000
you can go ahead and click on that

77
00:04:11,000 --> 00:04:13,006
and we can see that sure enough,

78
00:04:13,006 --> 00:04:16,002
we've got rows A, B, and C with the Test Data.

79
00:04:16,002 --> 00:04:18,000
So here's the string, here's the number,

80
00:04:18,000 --> 00:04:21,000
and you can see that the date isn't properly being displayed

81
00:04:21,000 --> 00:04:23,002
in the regular viewer,

82
00:04:23,002 --> 00:04:27,001
and here are the row of random numbers I added.

83
00:04:27,001 --> 00:04:30,009
So let's close this, let's go over to the folder

84
00:04:30,009 --> 00:04:36,002
and let's open this up in Excel.

85
00:04:36,002 --> 00:04:38,003
So you can see that here in Excel

86
00:04:38,003 --> 00:04:39,006
the results are a little bit different

87
00:04:39,006 --> 00:04:42,004
because Excel intelligently realizes

88
00:04:42,004 --> 00:04:46,006
that this is a string, this is a number, this is a date,

89
00:04:46,006 --> 00:04:49,002
and then here are all the random numbers

90
00:04:49,002 --> 00:04:51,008
that I've added so far.

91
00:04:51,008 --> 00:04:55,001
All right, okay, so let's continue.

92
00:04:55,001 --> 00:04:57,009
Let's go ahead and close this.

93
00:04:57,009 --> 00:05:00,009
I'm not going to save it.

94
00:05:00,009 --> 00:05:04,000
All right, let's add another worksheet

95
00:05:04,000 --> 00:05:07,003
using the create worksheet function.

96
00:05:07,003 --> 00:05:10,003
So I'll make a variable named sheet2

97
00:05:10,003 --> 00:05:15,001
and I'll call create_sheet

98
00:05:15,001 --> 00:05:19,003
and I'll name this Second sheet.

99
00:05:19,003 --> 00:05:24,002
And in sheet 2 I'm going to use the cell function,

100
00:05:24,002 --> 00:05:28,001
and at row=2,

101
00:05:28,001 --> 00:05:32,000
column=2,

102
00:05:32,000 --> 00:05:34,002
I'm going to set the value

103
00:05:34,002 --> 00:05:38,001
to be the string More Data.

104
00:05:38,001 --> 00:05:39,009
And there's another convenient way

105
00:05:39,009 --> 00:05:41,008
to add data to a worksheet,

106
00:05:41,008 --> 00:05:44,004
and that's by using the append function.

107
00:05:44,004 --> 00:05:46,009
So this function will add a new row of data

108
00:05:46,009 --> 00:05:49,003
at the end of the existing data in the worksheet,

109
00:05:49,003 --> 00:05:51,000
which means you don't need to figure out

110
00:05:51,000 --> 00:05:54,003
where the data ends in order to add new content,

111
00:05:54,003 --> 00:05:56,007
openpyxl will just do that for you.

112
00:05:56,007 --> 00:06:01,002
So I'm going to call sheet2.append

113
00:06:01,002 --> 00:06:03,008
and I'm going to pass a row of data,

114
00:06:03,008 --> 00:06:10,001
so I'll pass in some strings.

115
00:06:10,001 --> 00:06:12,000
All right, and we'll do that a few times

116
00:06:12,000 --> 00:06:14,006
just to add a few rows.

117
00:06:14,006 --> 00:06:17,004
All right, and you can see that we're saving the code still,

118
00:06:17,004 --> 00:06:19,008
so let's save our updated example

119
00:06:19,008 --> 00:06:21,005
and then let's run this again,

120
00:06:21,005 --> 00:06:25,006
which will overwrite the existing file.

121
00:06:25,006 --> 00:06:28,000
All right, so it's been created successfully.

122
00:06:28,000 --> 00:06:32,006
Let's go ahead and open that up.

123
00:06:32,006 --> 00:06:34,005
All right, and now we have two worksheets.

124
00:06:34,005 --> 00:06:35,009
So we have the First worksheet,

125
00:06:35,009 --> 00:06:39,004
which we saw in the previous iteration of the code,

126
00:06:39,004 --> 00:06:41,008
so all that data is still here.

127
00:06:41,008 --> 00:06:43,009
And now in our Second worksheet,

128
00:06:43,009 --> 00:06:46,003
we have the string we added

129
00:06:46,003 --> 00:06:50,000
along with the data added with the append rows function.