1 00:00:00,005 --> 00:00:02,000 - [Instructor] Using XlsxWriter, 2 00:00:02,000 --> 00:00:03,009 you can also programmatically set 3 00:00:03,009 --> 00:00:06,004 the document properties of a workbook, 4 00:00:06,004 --> 00:00:09,001 and these properties contain information about the workbook 5 00:00:09,001 --> 00:00:11,003 such as the title, the author, 6 00:00:11,003 --> 00:00:13,007 descriptive keywords, and so on. 7 00:00:13,007 --> 00:00:16,000 You can see these properties when you're in Excel 8 00:00:16,000 --> 00:00:18,004 by going to the Properties Viewer, 9 00:00:18,004 --> 00:00:22,004 so let's run our example code as it currently is 10 00:00:22,004 --> 00:00:23,006 before we make any changes, 11 00:00:23,006 --> 00:00:26,000 just to create an empty workbook 12 00:00:26,000 --> 00:00:29,000 so we can see what we're talking about. 13 00:00:29,000 --> 00:00:30,009 So I'm going to run this 14 00:00:30,009 --> 00:00:36,005 and you can see that properties.xlsx has been created, 15 00:00:36,005 --> 00:00:40,005 so let's go ahead and open that in Excel. 16 00:00:40,005 --> 00:00:42,001 So the way you do this on the Mac 17 00:00:42,001 --> 00:00:43,008 is by going to the File menu 18 00:00:43,008 --> 00:00:47,006 and choosing properties about the the workbook. 19 00:00:47,006 --> 00:00:48,006 Here on Windows, 20 00:00:48,006 --> 00:00:53,003 I'm going to go to the File tab and click on Info, 21 00:00:53,003 --> 00:00:55,004 and then right here where it says Properties, 22 00:00:55,004 --> 00:00:58,008 I'm going to click on this and choose Advanced Properties. 23 00:00:58,008 --> 00:01:00,007 And you can see that here in the Summary tab, 24 00:01:00,007 --> 00:01:05,004 there's title, subject, author, manager, company, so on. 25 00:01:05,004 --> 00:01:07,009 There's a hyperlink base property, 26 00:01:07,009 --> 00:01:10,001 and then over here in the Custom tab, 27 00:01:10,001 --> 00:01:15,003 there's a whole bunch of other properties that you can add. 28 00:01:15,003 --> 00:01:16,009 Now, currently these are blank, 29 00:01:16,009 --> 00:01:21,007 so we're going to set these in our workbook, alright? 30 00:01:21,007 --> 00:01:24,003 Let's cancel out of this and close our workbook 31 00:01:24,003 --> 00:01:27,008 and go back to our code. 32 00:01:27,008 --> 00:01:30,002 So these properties don't have any effect 33 00:01:30,002 --> 00:01:31,009 on your workbook whatsoever, 34 00:01:31,009 --> 00:01:35,001 but they can be read and used by external applications 35 00:01:35,001 --> 00:01:37,003 for a variety of purposes, 36 00:01:37,003 --> 00:01:41,001 including custom workflows or search indexing. 37 00:01:41,001 --> 00:01:43,007 The workbook class defines two methods you can use 38 00:01:43,007 --> 00:01:46,005 to set properties on the Excel files. 39 00:01:46,005 --> 00:01:48,001 The Set Properties function 40 00:01:48,001 --> 00:01:51,005 takes a dictionary object with predefined values 41 00:01:51,005 --> 00:01:53,009 for the standard document properties, 42 00:01:53,009 --> 00:01:55,009 and the set custom property function 43 00:01:55,009 --> 00:01:58,002 can be used to store property values 44 00:01:58,002 --> 00:02:00,002 that are not within the standard set 45 00:02:00,002 --> 00:02:03,006 covered by the more general set properties function. 46 00:02:03,006 --> 00:02:06,004 In this example, we're going to use both. 47 00:02:06,004 --> 00:02:08,004 Alright, so let's update our code 48 00:02:08,004 --> 00:02:11,005 to define some standard properties, 49 00:02:11,005 --> 00:02:15,006 and I'm going to define those as a dictionary. 50 00:02:15,006 --> 00:02:17,006 So I'll add a title 51 00:02:17,006 --> 00:02:24,004 and that's going to be the Document Properties Example. 52 00:02:24,004 --> 00:02:28,001 And let's see the subject. 53 00:02:28,001 --> 00:02:29,000 We'll just say, 54 00:02:29,000 --> 00:02:40,008 "Shows how to use document properties in XlsxWriter." 55 00:02:40,008 --> 00:02:43,002 The author. 56 00:02:43,002 --> 00:02:48,008 Well, I guess that's going to be me. 57 00:02:48,008 --> 00:02:55,008 The manager, let's set that to Colonel Monogram. 58 00:02:55,008 --> 00:02:56,008 If you know, you know. 59 00:02:56,008 --> 00:03:00,005 If you don't, go ahead and do a search. 60 00:03:00,005 --> 00:03:03,008 For the category, 61 00:03:03,008 --> 00:03:10,004 I'll just make that the Example Spreadsheets category. 62 00:03:10,004 --> 00:03:17,006 Let's specify some keywords. 63 00:03:17,006 --> 00:03:25,002 Let's see, Properties, Sample, XlsxWriter. 64 00:03:25,002 --> 00:03:29,005 That should be good. 65 00:03:29,005 --> 00:03:34,001 And let's add some comments. 66 00:03:34,001 --> 00:03:46,005 Created using XlsxWriter as a LinkedIn Learning example. 67 00:03:46,005 --> 00:03:49,008 Okay, that should do it. 68 00:03:49,008 --> 00:03:53,004 Let's go ahead and write these into the workbook, 69 00:03:53,004 --> 00:03:57,009 and the way that we do that is by using the workbook 70 00:03:57,009 --> 00:04:04,007 and calling set properties with our dictionary. 71 00:04:04,007 --> 00:04:08,003 And let's also set a couple of custom properties. 72 00:04:08,003 --> 00:04:14,000 So on the workbook, I'm going to call set_custom_property. 73 00:04:14,000 --> 00:04:17,002 Let's set the checked by property, 74 00:04:17,002 --> 00:04:21,000 which is one of the ones that was already there, 75 00:04:21,000 --> 00:04:24,006 and that'll be checked by Perry P. 76 00:04:24,006 --> 00:04:28,004 And now let's set a custom property 77 00:04:28,004 --> 00:04:31,004 that doesn't already exist, 78 00:04:31,004 --> 00:04:34,008 and I'll make that the Approved property 79 00:04:34,008 --> 00:04:38,008 and I'll set that to True. 80 00:04:38,008 --> 00:04:40,009 Okay, so let's save our code. 81 00:04:40,009 --> 00:04:42,009 So now we're using both the standard 82 00:04:42,009 --> 00:04:44,003 and non-standard properties. 83 00:04:44,003 --> 00:04:48,009 Let's run our code again. 84 00:04:48,009 --> 00:04:51,003 Alright, seems to have worked. 85 00:04:51,003 --> 00:04:57,001 Let's jump over to Excel. 86 00:04:57,001 --> 00:05:01,004 Go to the Info tab, choose the Advanced Properties, 87 00:05:01,004 --> 00:05:03,003 and now we can see that we filled out 88 00:05:03,003 --> 00:05:04,004 most of these properties, right? 89 00:05:04,004 --> 00:05:07,002 Here's the title, the subject, the author, right? 90 00:05:07,002 --> 00:05:09,008 All of these properties that we filled in. 91 00:05:09,008 --> 00:05:12,002 So these are all the standard ones, okay? 92 00:05:12,002 --> 00:05:13,008 And you can see that these are all the values 93 00:05:13,008 --> 00:05:15,002 that we set in the code. 94 00:05:15,002 --> 00:05:16,006 And if we go to the custom tab, 95 00:05:16,006 --> 00:05:20,008 we could see that the custom properties have also been set. 96 00:05:20,008 --> 00:05:23,004 So the checked by value is Perry P. 97 00:05:23,004 --> 00:05:24,004 Approved is yes, 98 00:05:24,004 --> 00:05:27,005 and we can see the types over here, alright? 99 00:05:27,005 --> 00:05:29,001 So these properties can be used 100 00:05:29,001 --> 00:05:32,002 by both Office and external applications, 101 00:05:32,002 --> 00:05:33,009 so if you have a custom workflow 102 00:05:33,009 --> 00:05:35,005 that needs to use these properties, 103 00:05:35,005 --> 00:05:39,000 you can create them programmatically using XlsxWriter.