How to Add Excel Editing Capabilities to your WPF Applications

In this video, we're going to see how to add Excel editing capabilities to your WPF applications using the Infragistics xam spreadsheet control. Let’s take a look.

We’ll start out by adding the XAM spreadsheet control from our Infragistics 15.1 installation to our design surface. I'm going to add this to the second column because as you can see, we have two buttons that we'll be utilizing later on in this demo. If I run this application as is, you'll see that we have a grid that looks just like Microsoft Excel. That's because XAM spreadsheet is supposed to mimic Microsoft Excel. It has all the features you expect, including sheet renaming, and sheet deleting capabilities. We also have a grid-based layout with Excel-like selection, as well as a formula bar. In a quick example, I can set a formula for summing A1 and B1 - let's add a value into those sells and you'll notice that C1 updates based on the formula we created.

Now that's all fine and dandy, but what we really want to do is add support for actually loading Excel documents, editing them, and saving them back out. So let's go ahead and close this and add that functionality. We’ll start by taking a look at the code behind for this view. As you can see, we already have a private variable called filePath. Now this filePath is pointing to an existing workbook created in Excel. Let's take a quick look at this sample workbook we'll be using.

As you can see, it's a basic invoice template. We have some basic information in here: we have data and we also have some formulas. If we update the information, of course, this is inside Excel, so all of our formulas are updated. I'm going to go ahead and close this. What we want to happen, is we want to load that information and load that document into our XAM spreadsheet control in our WPF application. In order to do that, I'm going to handle the LoadButton click event. I'm simply going to add a little bit of code that opens a new file stream using the file path that we've already defined, and then I'm going to create a new workbook by using Workbook.Load, passing in the stream. As you can see, we need to build a reference to our spreadsheet in our code behind. So we have to go back to our view and give the spreadsheet a name. We'll call it “spreadsheet”.

Now let's go ahead and run the application. I'm going to click the Load button and as you can see, the workbook is loaded as you would expect it would load in Excel. We have the same fonts, the same colors, even the same formulas. If I were to click on a formula cell, you can see the formula in the formula bar. If I were to edit the quantity, all of the values are updated accordingly.

Let's say I want to save these changes back to the original worksheet. To do that, let's go ahead and implement the Save functionality. We’ll go to the SaveButton click event handler and this one's even easier. We're simply going to take our spreadsheet.workbook and we're going to call Save, passing in the file path that we've been using. Now we'll run the application again and load the document, and let's make some changes. We'll just change this quantity to one hundred, the other to two hundred. We'll scroll down and see that the total due is quite a bit now - Hopefully I don't ever get a bill like this! And we’ll change the ship to info to Infragistics.

Now, I'm going to save this, close the application and now I'm going to open the file as it exists on disk. Now we're inside Excel. We can see that the changes we made in the XAM spreadsheet control in our WPF application, all those changes we made in the control, have now been saved to disk and are now editable inside of Excel once we open it up again. Here's our company name, Infragistics, our quantities have been updated, and the total is the same.

To see a completed application that highlights all the features of the XAM spreadsheet control, go to https://ko.infragistics.com/resources/sample-applications/wpf-igexcel. Here you can download our IG Excel application with source code to show you what it takes to implement all of the features you would expect Excel to have in your own custom WPF applications.

And that’s it! As you can see, it’s really quite easy to add Excel editing capabilities to your WPF applications using the Infragistics xam spreadsheet control. So go grab your sample source code and get coding!