1 00:00:00,005 --> 00:00:02,001 - [Instructor] Let's start with a simple example 2 00:00:02,001 --> 00:00:03,007 by using XlsxWriter 3 00:00:03,007 --> 00:00:07,005 to create a workbook and add some content to it. 4 00:00:07,005 --> 00:00:12,003 So we're going to open up our sample file xlsx_create. 5 00:00:12,003 --> 00:00:16,002 Alright, so here in my example file, 6 00:00:16,002 --> 00:00:20,008 I have imported the xlsxwriter module along with date time, 7 00:00:20,008 --> 00:00:22,005 which I'm going to need later. 8 00:00:22,005 --> 00:00:24,003 So first let's create the workbook. 9 00:00:24,003 --> 00:00:27,000 And the way that we do this is by 10 00:00:27,000 --> 00:00:28,009 creating a workbook variable. 11 00:00:28,009 --> 00:00:32,006 And then I'm going to use xlsxwriter 12 00:00:32,006 --> 00:00:35,009 and I'm going to use the workbook class 13 00:00:35,009 --> 00:00:37,007 and I'm going to give it a name. 14 00:00:37,007 --> 00:00:46,003 I'm going to call it XlsxBasics.xlsx. 15 00:00:46,003 --> 00:00:51,005 Alright, and then let's add a worksheet. 16 00:00:51,005 --> 00:00:58,002 So on the workbook I'm going to call add worksheet, 17 00:00:58,002 --> 00:01:02,004 and I'm going to call it Test Sheet. 18 00:01:02,004 --> 00:01:05,004 So once the workbook is created, let's add some content, 19 00:01:05,004 --> 00:01:09,009 and we can do this using letter row notation or by using row 20 00:01:09,009 --> 00:01:11,009 and column indexes. 21 00:01:11,009 --> 00:01:16,002 So first, let's try the letter and numbers. 22 00:01:16,002 --> 00:01:18,000 So I'll have worksheet 23 00:01:18,000 --> 00:01:22,005 and I'll call the write function to add content. 24 00:01:22,005 --> 00:01:28,008 And in A1 I'm going to write "Hello World." 25 00:01:28,008 --> 00:01:30,004 And then I'll do the same thing. 26 00:01:30,004 --> 00:01:36,001 But in this case, I'm going to use the worksheet.write function 27 00:01:36,001 --> 00:01:40,009 and I'm going to specify row 1, 28 00:01:40,009 --> 00:01:48,003 column 0, and then the same thing. 29 00:01:48,003 --> 00:01:50,006 Now the write function does what you'd expect. 30 00:01:50,006 --> 00:01:53,002 It writes content into a cell. 31 00:01:53,002 --> 00:01:54,004 The right function tries 32 00:01:54,004 --> 00:01:56,003 to determine the cell type based 33 00:01:56,003 --> 00:01:58,007 upon the data that's being written. 34 00:01:58,007 --> 00:02:00,007 So in this case, I'm writing strings, 35 00:02:00,007 --> 00:02:02,000 but if I were writing numbers 36 00:02:02,000 --> 00:02:05,006 or dates, then the right function would try to evaluate 37 00:02:05,006 --> 00:02:06,007 what data type it was 38 00:02:06,007 --> 00:02:10,005 and set the data type in the worksheet appropriately. 39 00:02:10,005 --> 00:02:13,003 Now there are type specific right functions. 40 00:02:13,003 --> 00:02:15,008 So if you know the data type that you want to be written, 41 00:02:15,008 --> 00:02:17,009 then you can just use those functions. 42 00:02:17,009 --> 00:02:22,000 So for example, I can use worksheet 43 00:02:22,000 --> 00:02:26,003 and I can call write_number, 44 00:02:26,003 --> 00:02:31,002 and I'll put that in row 2, column 0, 45 00:02:31,002 --> 00:02:37,000 and I can use worksheet dot and I'll call write_boolean 46 00:02:37,000 --> 00:02:41,004 and I'll put that in row 3. 47 00:02:41,004 --> 00:02:44,006 And then let's do another example. 48 00:02:44,006 --> 00:02:48,005 I'll call write_url, 49 00:02:48,005 --> 00:02:51,007 and we'll put that in row 4 50 00:02:51,007 --> 00:03:00,008 and we'll put a URL in there. 51 00:03:00,008 --> 00:03:02,004 All right, and let's also 52 00:03:02,004 --> 00:03:06,000 write a date_time object to the sheet. 53 00:03:06,000 --> 00:03:09,006 So I'll create a date_time, 54 00:03:09,006 --> 00:03:14,005 and that's going to be the datetime.datetime. 55 00:03:14,005 --> 00:03:17,009 And I'm going to use the strptime function 56 00:03:17,009 --> 00:03:22,002 to convert a date. 57 00:03:22,002 --> 00:03:28,005 Let's do July 28th, 58 00:03:28,005 --> 00:03:34,006 and we're going to convert that into a year, 59 00:03:34,006 --> 00:03:44,003 oops, year dash month dash day format. 60 00:03:44,003 --> 00:03:50,001 And then I'll specify a date format. 61 00:03:50,001 --> 00:03:52,002 I'm going to use the add format function here, 62 00:03:52,002 --> 00:03:53,007 and we'll learn about this later in the chapter. 63 00:03:53,007 --> 00:03:56,007 So don't worry about this too much right now. 64 00:03:56,007 --> 00:04:03,006 I'm going to specify a num_format, 65 00:04:03,006 --> 00:04:08,001 and that is going to be short day 66 00:04:08,001 --> 00:04:12,007 with the month and the four digit year. 67 00:04:12,007 --> 00:04:17,001 And then I'll tell the worksheet to write, oops, 68 00:04:17,001 --> 00:04:26,003 datetime, and I'll put that in row 5, 69 00:04:26,003 --> 00:04:27,007 and we'll specify the datetime 70 00:04:27,007 --> 00:04:32,001 and the date format to write. 71 00:04:32,001 --> 00:04:33,008 All right? 72 00:04:33,008 --> 00:04:36,004 You can also write multiple values at once, 73 00:04:36,004 --> 00:04:40,000 using the write row and write column functions. 74 00:04:40,000 --> 00:04:42,003 So let's give that a try. 75 00:04:42,003 --> 00:04:45,003 I'll create a little values array 76 00:04:45,003 --> 00:04:55,000 and I'll put some strings in here. 77 00:04:55,000 --> 00:04:55,008 All right. 78 00:04:55,008 --> 00:05:00,007 And then on the worksheet, let's call write_row 79 00:05:00,007 --> 00:05:03,000 and we'll put that in A6. 80 00:05:03,000 --> 00:05:06,003 Proving once again, you can use row column 81 00:05:06,003 --> 00:05:10,008 or you can use letter number and we'll write values. 82 00:05:10,008 --> 00:05:18,007 And then we'll do the same thing with writing the column, 83 00:05:18,007 --> 00:05:25,003 and we'll put that in D1 and we'll write the same values. 84 00:05:25,003 --> 00:05:28,001 Alright, and then finally, let's take advantage of some 85 00:05:28,001 --> 00:05:32,009 of the nice features that XlsxWriter gives us. 86 00:05:32,009 --> 00:05:35,009 Let's set the zoom level of the worksheet 87 00:05:35,009 --> 00:05:38,004 because we can do that easily here. 88 00:05:38,004 --> 00:05:44,000 So on worksheet, I'm going to call set_zoom, 89 00:05:44,000 --> 00:05:47,007 and let's make that 200. 90 00:05:47,007 --> 00:05:52,002 And then finally, let's call the close function, 91 00:05:52,002 --> 00:05:55,002 which we'll save and close the workbook. 92 00:05:55,002 --> 00:05:58,008 Alright, so we've got a bunch of code now that 93 00:05:58,008 --> 00:06:01,008 creates a workbook, adds a worksheet, 94 00:06:01,008 --> 00:06:04,008 puts some content in there. 95 00:06:04,008 --> 00:06:06,006 We're using different writing functions 96 00:06:06,006 --> 00:06:08,007 to write specific data types. 97 00:06:08,007 --> 00:06:12,003 We're writing multiple values, setting the zoom factor, 98 00:06:12,003 --> 00:06:14,007 and then closing the workbook, right? 99 00:06:14,007 --> 00:06:20,004 Let's go ahead and run our code. 100 00:06:20,004 --> 00:06:21,002 All right? 101 00:06:21,002 --> 00:06:22,001 And when we run the code, we can see 102 00:06:22,001 --> 00:06:25,006 that XlsxBasics has been created. 103 00:06:25,006 --> 00:06:30,000 So let's hop on over to Excel 104 00:06:30,000 --> 00:06:32,003 and take a look at our handiwork 105 00:06:32,003 --> 00:06:34,008 and we can see that the output file has been created. 106 00:06:34,008 --> 00:06:36,008 And here's all of our content. 107 00:06:36,008 --> 00:06:39,005 So let's see, we've got data has been entered 108 00:06:39,005 --> 00:06:42,009 into the various cells, we can see that sure enough, 109 00:06:42,009 --> 00:06:46,008 the workbook's zoom level is 200%. 110 00:06:46,008 --> 00:06:50,003 So here are the various different types of data 111 00:06:50,003 --> 00:06:52,007 that we've written into the workbook, 112 00:06:52,007 --> 00:06:57,000 and we've got our multiple values here, right? 113 00:06:57,000 --> 00:06:59,004 So everything is looking pretty good. 114 00:06:59,004 --> 00:07:02,000 So looks like we're off to a good start.