Get and present Excel data appropriately

This article describes the importance of data format in extracting and visualizing Excel data using public APIs in the context of apps for Excel.

Apps for Excel, in particular, content apps are often used to visualize data. If we want to develop an app for Excel to visualize or present our data, we need pay attention to the data format.

We first give an Excel data format example. We enter a number “3e10” to an Excel cell.

Figure 1 . Entering "3e10" in an Excel cell

Excel automatically formats it.

Figure 2 . Excel cell formats "3e10" in E notation

Choose this cell, it shows the unformatted data:

Figure 3 . Choosing the Excel cell shows number in decimal notation

The importance of data format

In this section, we give two examples to show the importance of correct data format and then describe how to format an Excel cell/a set of excel cells to present data inside.

Correct presentation

When we visualize data, data format is important and sometimes a wrong cell format returns a misleading number. For example, when we enter an Excel cell a date Friday, November 08, 2013 and then we call the document method getSelectedDataAsync or the binding method getDataAsyncto return the unformatted date within a binding, we get an integer 41586 instead of 11/08/2013--the number 41586 is the days from January 1^st, 1900 to November 8^th, 2013. This is because Excel stores and presents data differently. Excel stores date in its underlying sequential serial number form.

ValueFormat enumeration (apps for Office) lists the two formats "formatted" and "unformatted"used by the binding method getDataAsync.

Nice visualization

The appropriate data format also renders a nice visualization. For example, as shown in figure 4, a number 30,000,000,000 can be presented awkwardly. However, using an abbreviated form of scientific notation as shown on the right side of figure 4 matches the bubble perfectly.

Figure 4. Present a number

In general, use formatted data for display, and use “unformatted” stored data for calculation.

How to format Excel cells

To manually format a cell/a set of cells:

1. As shown in figure 5, choose a cell/a set of cells, choose “Format Cells…” Or, as shown in figure 6, on the Home tab, choose the Dialog box launcher next to Number.

Figure 5. Format cells

Figure 6. Dialog box launcher

2. Choose the Number tab, and then choose a category and a type.

Figure 7. Format a number

Create a custom number format describes guidelines for customizing a number format in general, and how to create/delete a custom number format in particular.

How to get data correctly

In this section, we describe two approaches to get the data. The first approach uses method getSelectedDataAsync. The second approach first establishes a binding with a cell or a collection of cells in a worksheet, then calls the method getDataAsync to return the data within the binding.

Use method getSelectedDataAsync to get data

We can call the document method getSelectedDataAsync to read the data contained in the current spreadsheet. Here we use table binding as an example. The first time we call getSelectedDataAsync to get unformatted data, and then we call the method again to get the formatted date ready for visualization.

 function getData(callback) {
  ...
  // Get unformatted data by calling getSelectedDataAsync the first time
  Office.context.document.getSelectedDataAsync(
      Office.CoercionType.Matrix,
      { valueFormat: Office.ValueFormat.Unformatted},
      function (result) {
        if (result.status === Office.AsyncResultStatus.Succeeded) {
          finalData.unformatted = result.value;
          // Get formatted data by calling 
          //   getSelectedDataAsync the second time
          Office.context.document.getSelectedDataAsync(
              Office.CoercionType.Matrix,
              { valueFormat: Office.ValueFormat.Formatted},
              function (result) {
                if(result.status === Office.AsyncResultStatus.Succeeded) {
                  finalData.formatted = result.value;
                  callback(finalData);
                } else {
                  callback(null);
                }
              });
        } else {
          callback(null);
        }
      });
}

The above code works for Office.CoercionType.Table as well. How to: Create your first content app for Excel by using "Napa" Office 365 Development Tools also contains the complete code to get the data using getSelectedDataAsync.

Use method getDataAsync to get data

In the remaining subsection, we describe another approach. We first bind to an Excel worksheet region, select the bindings, and then call the method getDataAsync to return the data within the binding.

Add a binding to user data

To visualize data, first establish a binding with a cell or a collection of cells in a worksheet. We often use one of the following two methods:

• Office.context.document.bindings.addFromPromptAsync
• Office.context.document.bindings.addFromSelectionAsync

Binding to regions in a document or spreadsheet describes how to create bindings to regions of spreadsheets, and then read and write data to those bindings. It also describes how to create and remove event handlers for changes to data or the user's selection in a binding.

Get the selection

After we add user data to a binding, we call the Office.select method to create a promise to return a binding based on the bindingName passed in.

var selection = Office.select(bindingName, callback); // Get a selection first

Get the data

With the selection, we get the data by invoking getDataAsync twice.

Here we use table binding as an example. The first time we call getDataAsync to get unformatted data, and then we call the method again to get the formatted date ready for visualization.

 function getData(callback) {
  ...
  // Get unformatted data by calling getDataAsync the first time
  selection.getDataAsync(
      {coercionType: Office.CoercionType.Table,
        valueFormat: Office.ValueFormat.Unformatted,
      },
      function (result) {
        if (result.status === Office.AsyncResultStatus.Succeeded) {
          finalData.unformatted = result.value;
          // Get formatted data by calling getDataAsync the second time
          selection.getDataAsync(
              {coercionType: Office.CoercionType.Table,
                valueFormat: Office.ValueFormat.Formatted,
              },
              function (result) {
                if(result.status === Office.AsyncResultStatus.Succeeded) {
                  finalData.formatted = result.value;
                  callback(finalData);
                } else {
                  callback(null);
                }
              });
        } else {
          callback(null);
        }
      });
}

How to: Create your first content app for Excel by using "Napa" Office 365 Development Tools describes how to create a content app for Excel.

References

• ValueFormat enumeration (apps for Office)
• getDataAsync
• Create a custom number format
• Binding to regions in a document or spreadsheet
• Document.getSelectedDataAsync method (apps for Office)
• Office.context.document.bindings.addFromPromptAsync
• Office.context.document.bindings.addFromSelectionAsync
• Office.select method (apps for Office)
• How to: Create your first content app for Excel by using "Napa" Office 365 Development Tools

Attribute

This post was written by ecoSystem team SDET Kun Liu, program manager Zhongzhong Li, and content developer Tony Liu. Program manager Juan Balmori Labra provided valuable feedback.

Comments

• Anonymous
  November 30, 2013
  Good overview, relevant for anyone building dataviz apps for Excel. one question-is there or will there be a better way of detecting date values, besides having to get formatted & unformatted values and do some guessing...? ex: get a native javascript datetime for excel date values. Kind regards, Rui http://rquintino.wordpress.com

• Anonymous
  December 04, 2013
  great post

• Anonymous
  January 08, 2014
  Rui, good question. As far as we know, currently there’s no such API.