(Example) Create a Microsoft Excel File to Use as a Data Source for an App

This example shows how to create a Microsoft Excel file to use as a data source for an app.

Background and Setup

Good to Know

Step 1: Create a Microsoft Excel File with Column Headers

First, create the Microsoft Excel file with the column headers to use as a data source.

Good to Know

  • In the Excel file, in the first row, each column must have a unique text string. AgilePoint NX uses this text as the column name.

    The supported characters are:

    • English letters
    • Numbers
    • Underscores (_)

How to Start

  1. Open Microsoft Excel.
  2. Click Blank workbook.
  3. Save an Excel file with the file name Sales Invoice Items.xlsx.

Procedure

  1. On the Sales Invoice Items.xlsx file, in the first row, enter the column names as shown in this table:
    ProductPriceQuantityTotalPrice

    Column Name

    The column headers are not required to be bold. But, in this example, the column headers are entered in bold to differentiate the column names and data.

  2. Save the Sales Invoice Items.xlsx file.

Step 2: Configure the Data Type for the Column Headers

Next, configure the column headers with a data type in the Microsoft Excel file.

Prerequisites

Good to Know

  • Each column must specify a data type, such as date, currency, or text, to match the data you enter in the Excel file.

Procedure

  1. In the Sales Invoice Items.xlsx file, select the Product column.

    Select Product Column
  2. On the Excel ribbon, in the Number Format field, select General.

    Select General
  3. Select the Price column.

    Select Price Column
  4. In the Number Format field, select Currency.

    Select Currency
  5. Select the Quantity column.

    Select Quantity Column
  6. In the Number Format field, select Number.

    Select Number
  7. In the Number submenu, click the dialog launcher.

    Open Number Submenu
  8. On the Format Cells screen, in the Decimal places field, select 0 to remove the decimals.

    Decimals
  9. Click Ok.

    The Excel file shows.

  10. In the Excel file, select the TotalPrice column.

    Select TotalPrice Column
  11. In the Number Format field, select Currency.

    Select Currency
  12. Select the row number 1.

    Select Column Row
  13. In the Number Format field, select General.

    Select General
  14. Save the Sales Invoice Items.xlsx file.

Step 3: Enter the Data into the Microsoft Excel File

Next, enter the data into the Microsoft Excel file to use in your app.

Prerequisites

Good to Know

  • In your Excel file, the data must be entered in rows and columns.

    This is the data your AgilePoint NX app will read in your Excel file.

Procedure

  1. In the Sales Invoice Items.xlsx file, enter the values for each column as shown in this table:
    ProductPriceQuantityTotalPrice
    Laptop Computer120022400
    Desktop Computer75053750
    Printer5002010000
    Portable Hard Drive100505000

    Enter Column Values
  2. Save the Sales Invoice Items.xlsx file.

Step 4: Format the Data as Excel Table

Next, format the data in the Microsoft Excel file as a table.

Good to Know

  • For AgilePoint NX to read the data in Excel, it must be defined as a table.
  • The table name functions like a database table name. This name lets AgilePoint NX locate your data in the Excel file.
  • The supported characters are:
    • English letters
    • Numbers
    • Underscores (_)

Procedure

  1. In the Sales Invoice Items.xlsx file, select a cell that has your data.

    Select Cell
  2. On the Excel ribbon, click Format as Table > select a table.

    Click Format Table

    The Format As Table screen shows the range for your data. Adjust the range if necessary.


    Range Shows
  3. Click Ok.
  4. On the Excel ribbon, in the Table Name field, and enter a name for your table.

    In this example, the table name is Invoice_Items.


    Enter Table Name
  5. Save the Sales Invoice Items.xlsx file.