Back
Excel

Building an Asset Tracking Application in Excel Services – Part 5 of 5

Today’s author, Dan Parish, concludes his discussion of building an Excel Services solution.

In Part 4, I walked through creating the web part pages for the solution. At this point, you should have a fully functional asset tracking application up and running. The last piece, that we’ll build today, is a simple web part that will allow your admin to archive all of the information they have collected.

Overview

At this point we now have the entire application up and running. We can collect information entered by users, and they can see how their machines compare against their coworkers. Our admin can at a glance see who has (and hasn’t) filled out the survey, and can see who needs upgrades or new machines.

However, there is still one problem with our application: there is no real way to archive the information. Archiving information is important for transparency, accountability, and sometimes for government compliance. This scenario is no different. With the current model someone could change their information at any point, even after the data collection period was supposed to end. There is also no good way to do year over year comparisons.

To rectify this, we are going to build a small web part that goes on the admin’s page. The admin can at any point just click this button to create an archive of all of the information. The web part will look like this:

After clicking “Archive”, the web part will show a completion message, like this:

The archive will be an Excel workbook that is a snapshot of the information in our asset tracking workbook. You can learn more about snapshots in the Open a workbook or snapshot in Excel from Excel Services article on Office Online, but basically, a snapshot is just a copy of the workbook that only contains the values and formatting of objects and sheets that the user is allowed to see. Since a snapshot contains no formulas or data connections, they are perfect for this scenario since they remove the link between the workbook and the source data, and so whenever they are opened in the future they will always only contain the data that they had when they were created.

The code for this web part is included at the end of this post.

Creating the Archive Document Library

The first thing we need to do is create a place to store the archived reports. To do this, we’ll need to create a new Document Library. The steps to do this are very similar to when we created the SharePoint List in Part 3a.

Navigate to your site and click on Site Actions, and then Create. If you don’t see a Site Actions menu, or if there is no Create in the Site Actions menu, then you aren’t an owner of the site.

The next page that appears is a page showing all of the different types of things you can create. We want to create a Document Library, so under Libraries on the left, click Document Library.

You’ll then be prompted to enter a name for the Document Library. Call it Archived Workbooks, select Microsoft Office Excel spreadsheet for the Document Template, and then click Create. Once created, you’ll be taken to your new Document Library.

Creating the archive web part

To get started, create a new Visual C# Web Control Library, and add references to the following:

  • Microsoft.SharePoint
    • Since web parts need to inherit from Microsoft.SharePoint.WebPartPages.WebPart
  • System.Xml    
    • We will be using this to add a custom web part property
  • Microsoft.Office.Excel.Server.WebServices.dll
    • This is Excel Services’ web service which we will be calling into to get a snapshot of the workbook

If you’ve never created a web part before, take a look at the Using Excel Web Services in a SharePoint Web Part and Deploying and Optimizing a SharePoint Web Part That Calls Excel Web Services articles on MSDN. They walk through all of the steps to create your web part, sign it, and build it into a CAB package that can be deployed into SharePoint. For this post, I will assume that you’ve either already done all of that setup work to your new project, or that you’ve simply opened the completed project included at the end of this post.

To get started, first create a new class and inherit from WebPart, like so:

public class ArchiveMachineReport : WebPart

{

…

The next thing we need to do is declare some global variables and add a new event handler to call our own custom function on load:

…

// Declare global variables
protected Label lblResult = new Label();
protected Button cmdArchive = new Button();
private string wbLocation = null

protected override void OnInit(EventArgs e)
{

// Call our own load event
this.Load += new EventHandler(ArchiveMachineReport_Load);
base.OnInit(e);

}

protected override void RenderWebPart(HtmlTextWriter output)
{

base.RenderWebPart(output);

}

…

Our global variables reference three things which are, in order:

  1. lblResult: The label that will show if the web part is ready, or provide error strings or a link to the archived report if the archiving process completed successfully
  2. cmdArchive: The button that the user will click on
  3. wbLocation: A string specifying the location of the Asset Tracking workbook that we will be archiving. This is a class variable because it will be specified by the site admin as a web part property.

During load, we want to call our own method because we need to create the UI for the web part. We do this in the ArchiveMachineReport_Load method.

Building the UI for the web part

Our web part is basically just a table with two rows and two columns. In the first column are the labels Archive Report, and Status. In the second column is the Archive button, and the actual status. So, all we need to do is write some C# that creates this simple table. We start by creating the function and defining the table itself:

…

private void ArchiveMachineReport_Load(object sender, EventArgs e)
{

// Create the HTML table to display in our web part
Table tbl = new Table();
TableRow actionRow = new TableRow();
actionRow.Cells.Add(new TableCell());
actionRow.Cells.Add(new TableCell());

TableRow resultRow = new TableRow();
resultRow.Cells.Add(new TableCell());
resultRow.Cells.Add(new TableCell());

…

As you can see, we created a row and added two columns to it, and then created another row and added two columns to it.

Next, we’ll add controls to the first row:

…

// Add archive row controls to the table
actionRow.Cells[0].Text = “Archive Report:”;
actionRow.Cells[0].Font.Bold = true;
cmdArchive.Text = “Archive”;
actionRow.Cells[1].Controls.Add(cmdArchive);

…

In the first cell we put the text Archive Report:, and in the second we set the text on our button and add it to the cell. Remember that we already created the cmdArchive button as a global variable.

Now we’ll add controls to the second row:

…

// Add result row controls to the table
resultRow.Cells[0].Text = “Status:”;
resultRow.Cells[0].Font.Bold = true;
lblResult.Text = “Ready”;
resultRow.Cells[1].Controls.Add(lblResult);

…

In the first cell we put the text Status:, and in the second we set the default label text to Ready and added the label to the cell. Remember also that we already created the lblResult label as a global variable.

Next, we want to actually assign an action to the button, like so:

…

// Add button action
cmdArchive.Click += new EventHandler(ArchiveReportUsingWebService);

…

What we did was say that when the cmdArchive button is clicked, call the ArchiveReportUsingWebService function, which we’ll write in just a moment.

Finally, we need to actually add these two rows we just created to the table, and add the table to the web part:

…

// Add everything to the table
tbl.Rows.AddRange(new TableRow[] {

actionRow,
resultRow });

// Add the table to the web part
this.Controls.Add(tbl);

}

…

That’s it. If you built and deployed your web part now it would show the UI you see in the first screenshot above, but of course it wouldn’t actually do anything yet. Let’s change that by building the function that is executed when you click the button.

Archiving the report

The first thing we need to do is declare our function and our variables, like so:

…

private void ArchiveReportUsingWebService(object sender, EventArgs e)
{

// Declare variables
Status[] status;
string sessionid = null;
string archiveName = null;
ExcelService es = new ExcelService();

…

The variables are, in order:

  1. status: All calls to Excel Services’ web service return a status that can contain things such as error messages, etc.
  2. sessionid: All calls to Excel Services’ web service require passing a session id (other than OpenWorkbook, which returns a session id). The session id uniquely identifies the session that you are working on.
  3. archiveName: This will be the name of the archived workbook we will create.
  4. es: An ExcelService object to call into Excel Services’ web service

We’ll start by opening the Asset Tracking workbook and getting the session id for this session:

…

// Get the workbook stream and save it to the specified SharePoint List
try
{

// Open a session with the workbook
sessionid = es.OpenWorkbook(wbLocation, “en-us”, “en-us”, out status);

…

Everything is within a try block in case something goes wrong. If something does go wrong, this will allow us to catch it and notify the user. The OpenWorkbook method takes three in parameters and one out parameter. They are, in order:

  1. The full path to the workbook to open. In this case, wbLocation is the global variable we created earlier and that the page creator will set as a web part property.
  2. The UI culture for the workbook.
  3. The data culture for the workbook.
  4. Any status messages that are returned from this call.

If it succeeds, the method returns a session id that must be used in all subsequent calls to this session.

So, now that we’ve opened the workbook, let’s get a file stream of it and save it into memory:

…

// Get workbook into memory
byte[] workbookContent;
workbookContent = es.GetWorkbook(sessionid, WorkbookType.PublishedItemsSnapshot, out status);
MemoryStream file = new MemoryStream(workbookContent);

…

Here we call Excel Services’ GetWorkbook method. GetWorkbook is used to retrieve a full workbook that can be opened in Excel. It takes two in parameters, and one out parameter. They are, in order:

  1. The session id of the session to get the workbook from. This is important since if you had also set some cells, refreshed some external data, etc., the workbook GetWorkbook would return would be the modified version in your session.
  2. The type of workbook to retrieve. This can be one of three things:
    1. FullSnapshot: This is a snapshot of the entire workbook, not just the published items. It still only contains the values and formatting, but it contains sheets that you may have marked as not viewable on the server. You must have at least Read permissions to the workbook to call this method.
    2. FullWorkbook: This is the full workbook, formulas and all. You must have at least Read permissions to the workbook to call this method.
    3. PublishedItemsSnapshot: This is what we actually are using. It is a snapshot of the workbook that is restricted to just the sheets or objects you specifically marked as viewable on the server.
  3. Any status messages returned from this call.

The next thing we need to do is find the location we are going to save it to:

…

// Get the folder to save it to
SPSite site = new SPSite(http://server);
SPWeb web = site.OpenWeb(“site”);
web.AllowUnsafeUpdates = true;
SPFolder folder = web.GetFolder(“archive folder”);

…

This could also be a web part property, but for simplicity we are hardcoding it here. Simply change server to your server name, site to your site name, and archive folder to the folder you want to archive the workbook to (Archived Workbooks if you named it as specified above).

Now we just need to actually save the workbook:

…

// Save workbook to SharePoint List
SPFileCollection files = folder.Files;
archiveName = “Archived Report “ + (files.Count + 1) + “.xlsx”;
files.Add(archiveName, file, true);

…

Here you can see that we call the workbook Archived Report #.xlsx where # is one greater than the last number used. So, it will start as Archived Report 1.xlsx, and increase from there. That way, you never overwrite any existing archives. We then add the file to the Document Library.

Finally, we just need to close the file, update the status message for the user, and catch any errors that could have occurred:

…

// Finish off
file.Close();
lblResult.Text = “Report successfully archived (<a href=’http://server/_layouts/xlviewer.aspx?id=http://server/site/archived folder/” + archiveName + “&DefaultItemOpen=1′>view</a>)”;

}
catch (Exception ex)
{

// Failed to get workbook stream or save the workbook
lblResult.Text = ex.Message;

}

}

…

Again, we have hardcoded in the link to the archived workbook that the admin will see for simplicity. You’ll have to update server, site, and archived folder here as well.

We then catch anything that could have gone wrong in our try block, and update the status message in the web part with what went wrong (if anything).

That’s the end of the method! The last thing we need to do is create the web part property that I have referenced several times.

Creating the web part property

Because we want this web part to work no matter where the Asset Tracking workbook is, we need to add a simple web part property so that the page creator can paste in the URL of the workbook they’ve already created. The code for this is:

…

#region Public Properties

[Browsable(true),
Category(“Miscellaneous”),
WebPartStorage(Storage.Personal),
FriendlyName(“Workbook Location”),
Description(“Enter the location of the workbook to archive.”)]

public string WorkbookLocation
{

get
{

return wbLocation;

}
set
{

wbLocation = value;

}

}

#endregion

}

}

This adds a web part property into the Miscellaneous section of the web part tool pane, with the specified name and description. It sets the wbLocation variable that we use when calling the OpenWorkbook method.

That also completes our class. Now all we need to do is deploy the web part and add it to our web part page.

Deploying the web part

This part is simple. If your web part is created as per the referenced articles (or if you are using the attached code sample), when it is built it will generate a CAB file. All you need to do is copy it to your server and run the following STSADM command on your server:

“<drive>:Program FilesCommon FilesMicrosoft Sharedweb server extensions12BINstsadm.exe” -o addwppack -filename “<location of built CAB file>ArchiveMachineReportCAB.CAB” -url http://localhost -globalinstall -force

Don’t forget to replace <drive> with the drive you have MOSS installed on, and <location of built CAB file> with wherever the CAB file is on your server. It’s often easiest just to put it in the same folder as stsadm.exe.

Adding the web part to your web part page

Once you’ve executed the command, navigate to the admin’s web part page and put it into Edit Mode by selecting Site Actions > Edit Page. In the right hand column, click Add a Web Part. If you scroll to the Miscellaneous section, you will see a web part called Report Archive Web Part (or whatever you named it). Check the box next to it and click Add.

The web part should now be added to your web part page. All you need to do now configure it. Click the edit dropdown at the top right of the web part and then click Modify Shared Web Part. When the web part tool pane appears, first select that the web part should have a fixed width, and set it to 200 pixels. Next, set the Chrome Type to None, and then expand the Miscellaneous section and enter the location of the Asset Tracking workbook into our custom web part property. When you are done, click OK.

The web part should now be ready to use. Exit Edit Mode by clicking Exit Edit Mode at the top right of the page and give it a try!

Conclusion

I hope you’ve enjoyed this series. This solution shows you how to leverage Excel Services to build rich SharePoint applications, and really focuses on three key areas:

  1. Extending the capabilities of Excel Services using User Defined Functions
  2. Integrating Excel Services into your existing dashboards
  3. Building custom applications using Excel Services Web Service

Using the basics described here, you should be well equipped to branch out and build the custom solutions your company has been waiting for.