Microsoft 365 Community Office Add-ins, 23 Oct 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: PnP: Open in Excel

Description

David Chesnut, Microsoft Senior Dev Writer demonstrates a new PnP article and sample code showing how to open your add-in in Excel from your SaaS web application.

View the details here https://aka.ms/openinexcel

Demo taken from the October Office Add-ins community call https://youtu.be/ab2QrLNosdk

Stay connected
Twitter https://twitter.com/microsoft365dev
YouTube https://aka.ms/M365DevYouTube
Blogs https://aka.ms/M365DevBlog

A

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.

A

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.

A

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

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.

A

So let me get over to some demo stuff, let's start with um okay, so here's the article.

A

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.

A

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.

A

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.

A

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.

A

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.

A

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.

A

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?

A

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.

A

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.

A

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?

A

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.

A

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.

A

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.

A

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.

A

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.

A

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.

A

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.

A

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.

A

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.

A

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.

A

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.

A

One of the things, though, that you're gonna you might probably run into is that the open, xml sdk is based on.net.

A

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.

A

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.

A

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.

A

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?

A

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.