PnP: Open in Excel

Key Takeaways

[Music] um all right so i'm going to talk about a new patterns and practices uh sample that we have called open in excel just a real quick background what the scenario is about is if you have a sas web application uh where folks you know are working with data on your website uh there's often a point where they'll want to take that data into excel to continue work out in some way you know maybe doing additional analysis or creating charts and uh you know the way this would work in the past is uh you'd let them export a csv file traditionally this is how it would work you know you create a csv file the user then has to go import that into excel and then if they want to remain connected to your services like say you have an office add-in they have to go find that office add-in in the store or through central deployment and get it set up so there's a number of steps for them to go through and the idea of this scenario is like hey let's simplify that so it's as close to one click as possible where someone can just say hey take my data open in excel your office add-in is already there and as we'll see this goes to the steps of using uh office open xml to embed your add-in and create the file and then using microsoft graph you can upload the file to onedrive this stereo was actually demoed a year ago in september of last year by jake armstrong at venice corporation they've actually implemented this office add-in very successfully and that was a great demo he showed so basically this pattern is uh taking that same approach and showing you how to do it so let me get over to some demo stuff let's start with um okay so here's the article for opening excel and what this does is it uses an existing sample on github called the office ooxml embed add-in so this has actually been out there for a couple of years now i think uh i guess three years according to the history here and what this does is it allows you to take an existing file and insert the script lab add-in into it and uh so basically what we did is we took this approach and showed you how you could extend this to uh you know make it more into in kind of scenario so the way the sample works is um let me go and run it oh sorry i'm like there we go so this is the sample it's an asp.net web application i'm going to go ahead and run it and you can see what the web page looks like and basically it just has steps one two and three we're gonna go find a file and embed script lab into it and then we're gonna download it back and then we'll see what that looks like so the first step here is i want to go choose which file i want i already have this contoso workbook spreadsheet that i'll use and open that now i'm going to click on this upload button which is going to read that spreadsheet into memory so now it's stored in memory in the web application the next step is i'm going to embed script lab into that file there's an option here to specify which code snippet you want to see in there i'll just use the default one so i'll just do it this way and then the final step is to click download to download that file and you'll see here it's downloading and this is the interesting part when i open this you're going to see excel open up and then you'll see that script lab is already in there and it opens up the task pane for script lab as well so that's pretty cool uh then you can imagine you know you like you could do this with your own office ad and you could modify this code to instead open it open up your office data and you could put whatever data you know the user had selected over here in the spreadsheet you know there's additional parts you can do now just to show how this works so i'm going to close this and i think the easiest way to show what's going on here is to go to that balance folder where it created the new spreadsheet instance and as you probably know these are always zip files so if you just rename a spreadsheet to a zip then it becomes a compressed file and you can go into it and see the entire open xml structure of it the important parts of this are if you go down to this excel folder inside the file and you look at the web extensions there's these two files here task panes xml web extension xml so the way you insert your office add-in is you basically create this xml let me open this up and show you what it looks like and you can see uh a lot of this is pretty boilerplate like it just never changes once it's to the next the key parts to understand or like you want to put in your in this case like the store id of where your that identifies it in the your alpha set in the store and your store type um you can do this with uh the store or essentially deployed add-ins um then you'll want to pass some properties this part's important here you want to have this office.auto show task pane with document set to true that's what causes the task pane open and then this is a custom property here just for a script lab that says hey here's the snippet to import and we pass it that this is that default snippet id so you can put whatever custom properties you need for your office add-in into this basically what's a property bag there's also the task panes xml and this is pretty much the same every time it just specifies hey i have a task pane here's out stalking here's my visibility and so on so how does this part get created this is where open xml comes into play and if i go back to the code i have opened up in the asp.net project this ooxml helper.cs file and in here there's helper functions such as validating the office file uh which basically you use the openxml to open the file and if it opens it's valid if it throws an exception it's not valid that's pretty simple how that works now to actually embed the um office add-in that happens here this create web ex task panes part and it's going to call two other functions one which creates the web extension xml that you saw and then this generate part content generates that task panes xml so that's kind of the two key pieces there so i just like to explain this by saying like basically all this code here is creating that file you just saw that's all it's doing so we can see here's that the my store id store type and so on all these values so if you're using your own office add-in this is where you'd want to make your change to use your particular id here's where you make sure your auto show task pane is document set to true if you have custom properties you want to add here's where you do that you throw them into this property bag anything you need and that's pretty much it and then this part for the task pane this is always the same this doesn't change so let's see that's pretty much how that works let me go back over to the article so this is basically a part one of this sample uh we're going to do a part two that's more end to end because as you can see right basically what we're doing is saying okay here's this sample you can start with and then you'll want to do some additional things to it for example uh you should use the fluent ui and if you go look at fluid ui i'll put all these links out later by the way you get access to icons that you can use to represent opening up various documents so i use the excel icon in the sample then you'll want to upload to onedrive if your users are using onedrive it's more convenient if you just integrate that right into onedrive for them so there's some steps here to create a microsoft graph project that shows you the code for uploading to onedrive uh that you can add into your solution also because this this sample uses an existing spreadsheet you'll probably want to create a brand new spreadsheet and so there are some links here and i'd realize it's more like this link's broken so i'll go back and fix this but there's additional articles that show hey here's how you create a spreadsheet document using openxml and then if you want to read the custom properties if you're running how does my office add-in read those properties out of that property bag there's an article on that which i will fix that link as well by the way the links are at the very bottom of the page they work you can use it now at the bottom you'll probably want to initialize your documents data there's a couple approaches for doing that you could use the open office xml sdk to write that data into the document or you could just pass a query in the property bag to your office add-in so that when the item opens it just runs the query against the back-end database and then it just inserts the data into the spreadsheet kind of depends on which approach you want to take there one of the things though that you're gonna you might probably run into is that the open xml sdk is based on.net so if your web application is not using.net you're using node.js or some other platform then you're going to have to find an alternative so one option is you there is an open xml sdk for javascript that you could use uh another approach if that doesn't work is you could isolate the open xml code part that like embeds the add-in into say like an azure function some kind of web function that that part runs.net but it's separate from your web application so you can continue to use uh whatever platform you're using with your web application then of course we recommend using single sign-on uh to simplify things for users they don't have to keep signing into stuff let's see let me go back i think there's anything else i need to show on that uh oh so let us know i'm gonna go create i'm gonna start an issue in the pnp repo and if folks want to give us feedback on that issue like one of the questions we have is like you know which of these approaches if we've been in scenario like would you want to see you know putting out oxml and azure function as an approach or would you want to see one of these other approaches right this is kind of where we're wondering like you know what what would be the simplest best way to show this as sort of an indian instead of like right now we just have pointers like here's all the puzzle pieces and here's what the picture looks like on the front of the box and you know and now you can put it together but we kind of want to give you a finished puzzle if we can so i will create that and if uh folks want to comment on the issue that would be great [Music]