Freigeben über

How to Build a VSTO-Based PowerPivot Workbook

  • Artikel

Microsoft Visual Studio Tools for Office (VSTO) is an awesome development suite for building advanced Office solutions, such as Excel applications that go above and beyond default features and capabilities. Even our PowerPivot for Excel add-in is based on VSTO. However, it is important to note that VSTO does not define application programming interfaces (APIs) for applying customizations on top of customizations. In other words, you can develop applications on top of Excel by using VSTO but you cannot develop applications on top of PowerPivot because VSTO does not include any APIs for this purpose and the PowerPivot add-in does not add any such APIs either. Appropriate APIs are only available with PowerPivot for SharePoint and SQL Server Analysis Services.

While it is not supported to interact with PowerPivot for Excel programmatically, you can very well interact with the usual elements of an Excel workbook by using VSTO and therefore—given that every PowerPivot workbook is essentially an Excel workbook—you can use VSTO to build advanced PowerPivot solutions. Just don’t attempt to go beyond programming Excel features. The Microsoft sample solutions Calendar Analytics Tool and Analytics for Twitter highlight this limitation. These solutions rely on VSTO to pull source data into the workbook and then remind the user to update the embedded PowerPivot database and PivotTable data manually, as the following figure illustrates. It would be great to automate these steps, but this requires a PowerPivot API that isn’t available, as mentioned before.

Another important limitation that you must keep in mind when building PowerPivot workbooks by using VSTO is that you cannot place datasets with more than 1 million rows on a worksheet because the maximum worksheet size in Excel 2010 is 1,048,576 rows by 16,384 columns. Furthermore, VSTO-based workbooks are not fully functioning without their VSTO components. For example, you cannot just copy the Analytics for Twitter.xlsx file to another computer or upload this workbook to a document library in SharePoint and expect the VSTO-based code to work. You can interact with the workbook’s slicers and PowerPivot data, but you cannot import updated source data if the corresponding VSTO code isn’t available. If you want to share a VSTO-based PowerPivot workbook, you must publish the workbook as a ClickOnce application (see ClickOnce Deployment Overview) or provide a Windows Installer package for deployment (see Deploying a Visual Studio 2010 Tools for Office Solution Using Windows Installer). (In another blog post, I’m going to show you how to redesign the Analytics for Twitter workbook so that it doesn’t depend on VSTO in order to share and update it in SharePoint.)

Having pointed out these limitations, what’s cool about VSTO is that it can bring data into a PowerPivot workbook even if there is no suitable data provider, such as Outlook items, tweets, or perhaps Windows Event Log entries, as I’m going to show you next. Without VSTO, you might have to export the data into an intermediary text file and then import the data into PowerPivot (see the blog post Analyzing Event Log Entries in PowerPivot). This is inconvenient. With VSTO, custom code can pull the data into one or multiple tables in an Excel workbook, linked to one or multiple PowerPivot tables, as illustrated in the following figure.

In the Analytics for Twitter solution, the data flows as follows:

  1. When you click on the search button in the workbook, VSTO code pulls data from Twitter, processes the data, and places the results in a number of Excel tables on a hidden TweetData worksheet. (You can display this worksheet, by right-clicking on the tabs at the bottom of the workbook and selecting Unhide. In the Unhide dialog box, select TweetData and click OK.)
  2. When you click on Update All on the PowerPivot ribbon, PowerPivot pulls the data from the Excel tables into linked PowerPivot tables.
  3. When you click on Refresh All on the Data ribbon, Excel updates the data in its workbook cache based on the data from the linked PowerPivot tables. The PivotTables and PivotCharts on the worksheets now display the most recent data.

This data flow is not unique to the Analytics for Twitter solution. Aaron Meyers, creator of the Analytics for Twitter solution, was kind enough to include the TwitterSearch.cs source code on the Sample Code worksheet, which demonstrates all the details to get the job done. So, let’s see how these pieces fit together by building a VSTO-based PowerPivot workbook that follows the Analytics for Twitter approach but gets data from the Windows Event Log.

The first step is to create a workbook by using Visual Studio Tools for Office:

  1. Make sure you have Excel 2010, PowerPivot add-in, and VSTO installed on your computer.
  2. Create a New Project in Visual Studio. In the New Project dialog box, under Visual C# and Office select 2010, and then select Excel 2010 Workbook, as in the following screenshot. Click OK.
  3. In the document selection dialog box, click OK to accept the defaults.
  4. If a warning is displayed that you must explicitly enable access to the Visual Basic for Applications project system to create a VSTP project, click OK to allow access.
  5. Verify that the workbook is opened in Visual Studio.

So far so good! Now, let’s add a table to the workbook and populate it by using VSTO-based code. Note that Excel assigns the table a generic name of Table1. The code below uses this name. If you want to use a different name, select a cell in the table and then on the Design ribbon, in the Properties group, type the desired name in the Table Name textbox. Make sure you update the table reference in the code (Globals.Sheet1.Range["Table1"]) accordingly.

Follow these steps to add a table and populate it programmatically:

  1. In the workbook on Sheet1, opened in Visual Studio, type the following values in the cells of the first row:

A1

B1

C1

D1

E1

F1

G1

H1

I1

J1

K1

Log

Category

CategoryNumber

EntryType

InstanceId

MachineName

Message

Source

TimeGenerated

TimeWritten

UserName
  1. Select cells A1 through K1, and then on the Home ribbon, under Styles, click Format as Table, and select a table format of your choice.
  2. In the Format As Table dialog box, select the checkbox My Table Has Headers, and then click OK.
  3. Switch to Sheet2, display the Visual Studio Toolbox, and drag a button onto the worksheet. Place the button in the upper left corner.
  4. Right-click the button and then click Properties.
  5. Change the Text property to Get Data, change the (Name) property to btnGetData, and then double-click the Get Data button to add a btnGetData_Click method to Sheet2.cs.
  6. Replace the empty btnGetData_Click method with the following test code:

  private void btnGetData_Click(object sender, EventArgs e)
  {
   // Show an hourglass while Excel refreshes the source data table.
   this.Application.Cursor = Excel.XlMousePointer.xlWait;

   // Grab the linked table on Sheet1, named "Table1" by default.
   Excel.Range linkedTable = Globals.Sheet1.Range["Table1"] as Excel.Range;

   // Retrieve Event Log data.
   object[,] eventData = GetEventData();

   // Replace the values in the Excel table.
   linkedTable.ClearContents();
   linkedTable = linkedTable.Resize[eventData.Length / 11];
   linkedTable.Value = eventData;

   // Tell the user what to do next.
   MessageBox.Show("Next Steps:\n" +
    "1. Click PowerPivot --> Update All\n2. Click Data --> Refresh All",
    "Next Steps", MessageBoxButtons.OK, MessageBoxIcon.Information);

   // Restore the default mouse cursor.
   this.Application.Cursor = Excel.XlMousePointer.xlDefault;
  }

  /// <summary>
  /// Reformats the list of event log entries for use as content of an Excel table.
  /// </summary>
  /// <returns>An object of rows and columns.</returns>
  private object[,] GetEventData()
  {
   List<object[]> dataRows = GetDataRows();

   int rows = dataRows.Count;
   int columns = 11;
   object[,] eventData = new object[rows, columns];

   int row = 0;
   foreach (object[] row_ar in dataRows)
   {
    for (int col = 0; col < columns; col++)
    {
     eventData[row, col] = row_ar[col];
    }
    row++;
   }

   return eventData;
  }

  /// <summary>
  /// Retrieves the event data from the local Windows Event Log.
  /// </summary>
  /// <returns>A List object with a separate row for each event log entry.</returns>
  private List<object[]> GetDataRows()
  {
   // Create a list object for the event log data.
   List<object[]> dataRows = new List<object[]>();

   // Iterate through all the event logs available on the local computer.
   foreach (System.Diagnostics.EventLog evtLog in System.Diagnostics.EventLog.GetEventLogs())
   {
    try
    {
     // Add the contents of the currently selected event log to the list of data rows.
     foreach (System.Diagnostics.EventLogEntry logEntry in evtLog.Entries)
     {
      object[] dataRow = new object[11];

      dataRow[0] = evtLog.Log;
      dataRow[1] = logEntry.Category;
      dataRow[2] = logEntry.CategoryNumber;
      dataRow[3] = logEntry.EntryType.ToString();
      dataRow[4] = logEntry.InstanceId;
      dataRow[5] = logEntry.MachineName;
      dataRow[6] = logEntry.Message;
      dataRow[7] = logEntry.Source;
      dataRow[8] = logEntry.TimeGenerated.ToString();
      dataRow[9] = logEntry.TimeWritten.ToString();
      dataRow[10] = logEntry.UserName;

      dataRows.Add(dataRow);
     }
    }
    catch { /* On Error Resume Next */ }
   }

   return dataRows;
  }

  1. Press F5 to test the solution. Visual Studio opens a new Excel application window. On Sheet2, click on the Get Data button, wait until Excel has finished importing data, and then in the Next Steps dialog box, click OK. Verify that the table on Sheet1 is filled with data and then close the Excel workbook. When prompted, save the changes.


At this point, the VSTO portion to retrieve data from the local Windows Event Log is finished. Now, let’s engage in some PowerPivot plumbing outside of the VSTO environment. This is the tricky part. Note that you must perform this work in the customized (“compiled”) version of the workbook. Subsequently, you must remove the VSTO customization from this workbook so that you can use it as the project workbook in Visual Studio. If you forget to remove the VSTO customization, Visual Studio won’t be able to build the customized version of the workbook anymore. If you forget to replace the original project workbook, rebuilding the workbook in Visual Studio will overwrite your PowerPivot version. Note also that I am specifically not performing the PowerPivot work on the worksheet in Visual Studio because PowerPivot for Excel is not designed to run within a Visual Studio OLE container.

Follow these steps to create a linked PowerPivot table, finish the workbook, remove the VSTO customization, and replace the original project workbook with the PowerPivot version:

  1. In Visual Studio Solution Explorer, right-click the project ExcelWorkbook1, and then click on Open Folder in Windows Explorer.
  2. Close Visual Studio and, if you are prompted to save any changes, click Yes.
  3. In Windows Explorer, expand the Bin and Debug folders, and then open the ExcelWorkbook1.xlsx file in Excel. This is the customized version of the workbook.
  4. If you receive an error message that the workbook is already opened, click OK, close the Excel workbook without saving any changes, and then open the workbook again.
  5. On Sheet1, verify that the table contains the data imported in the previous test run, and then on the PowerPivot ribbon click Create Linked Table.
  6. Verify that the PowerPivot window is displayed with the event data imported from the Excel table. Close the PowerPivot window.
  7. Back in the Excel window, right-click Sheet1 at the bottom, and then click Hide to make the source data table disappear from the user’s view.
  8. Switch to Sheet2 and then on the PowerPivot ribbon, in the Report section, click on the little triangle underneath PivotTable. Select the option Chart and Table (Horizontal).
  9. In the Create PivotChart and PivotTable (Horizontal) dialog box, select Existing Worksheet, specify 'Sheet2'!$A$3 as the Location, and then click OK.
  10. PowerPivot places an empty chart and an empty PivotTable on the worksheet. Select the chart and then, in the PowerPivot Field List to the right, drag the following fields to the field areas:

Field Area

Fields

∑ Values:            

Message (automatically changes to Count of Message)

Axis Fields (Categories):

TimeGenerated

Slicers Vertical:

Log, EntryType
  1. On the Excel worksheet, click in the area of the PivotTable, and then in the PowerPivot Field List drag the fields Source and Message onto the Row Labels field area.
  2. On the Excel worksheet, right-click the column that contains the PivotTable, such as column D, and click Column Width. Under Column Width, type 66, and then click OK.
  3. Right-click the column that contains the PivotTable again and click PivotTable Options, and then in the PivotTable Options dialog box, clear the checkbox Autofit Column Widths On Update. Click OK.
  4. On the Excel menu, click the File tab, then click Properties on the right and select Advanced Properties.
  5. In the ExcelWorkbook1.xlsx Properties dialog box, switch to the Custom tab, and then delete the custom properties called _AssemblyName and _AssemblyLocation (see Custom Document Properties Overview on MSDN). These custom document properties associate the workbook with the VSTO customization assembly. Removing these properties is a prerequisite to using this PowerPivot version as the project workbook in Visual Studio. Click OK.
  6. Switch back to the Home tab, save your work, and close Excel.
  7. Copy the workbook from the \Bin\Debug folder to the parent folder ExcelWorkbook1, which contains the project workbook with the same name (ExcelWorkbook1.xlsx). Confirm that you want to replace the original project workbook with the PowerPivot version.
  8. Start Visual Studio and open the ExcelWorkbook1 project. Note that Visual Studio now displays the project workbook with PivotChart and PivotTable.
  9. Press F5 to verify that Visual Studio can build the workbook successfully and then test the solution by clicking on the Get Data button. Wait until Excel has finished importing data and then click OK in the Next Steps dialog box. Click Update All on the PowerPivot ribbon and then click Refresh All on the Data ribbon. Save the changes if desired and then close the Excel workbook.

That’s it. The VSTO-based PowerPivot workbook is now complete. What’s left to do is to create a Windows Installer package for deployment, but this is beyond the scope of this blog post. See Deploying a Visual Studio 2010 Tools for Office Solution Using Windows Installer for details.

Comments

  • Anonymous
    August 04, 2011
    Can VSTO based PowerPivot Sheet be uploaded to SharePoint 2010 and viewed with PowerPivot of SharePoint ... in browser?
  • Anonymous
    August 05, 2011
    Hi Abhishek,Excel Services is going to render the workbook with a yellow bar at the top to inform the user that external links and advanced features are disabled. You can analyze the data that's in the workbook. PowerPivot does not depend on the VSTO customiztions.Cheers,Kay
  • Anonymous
    August 06, 2011
    Hi KayThis is exactly what I was looking for!Great post.Thanks  Lou
  • Anonymous
    October 26, 2011
    Hi Kay,Can you please provide the link to where i can find more info on APIs available with PowerPivot for SharePoint ?Thanks
  • Anonymous
    October 28, 2011
    Would be great to have it, no doubt about that, but, unfortunately, there is no public PowerPivot API in SQL Server 2008 R2 or in the upcoming Denali release.
  • Anonymous
    September 07, 2012
    Is this also possible through an Excel application-level add-in?BTW, fantastic blog post, very well written tutorial.
  • Anonymous
    September 08, 2012
    Hi Dan,I think so. Essentially, you are only programming Excel. Keep in mind that no matter what way you choose, there is no supported API to automate PowerPivot in Excel 2010. By the way, things get a lot better in Excel 2013. Check out the Excel 2013 Developer Reference at msdn.microsoft.com/.../jj238428(v=office.15), specifically those functions that start with Model, such as ModelConnection, ModelTable, and ModelRelationship. The documentation is still a work in progress, but I think once the documentation is finished you'll like the enhanced capabilities in the new version.Hope this helps.Cheers,Kay
  • Anonymous
    April 06, 2015
    Any chance you can have an updated post on how to do the same for excel 2013? It should be more straightforward, since Excel 2013 provides API access to PowerPivot.