Tables

The Encodian population engine enables JSON data to be easily added to tables within your Excel spreadsheet.

This article provides detailed examples for creating the following types of tables:

Table - Simple

Table - Single Column

Table - Total

Table - Span Rows

Table - Merge Cells

Table - Dynamic Background Colors

Table - Simple

Example Spreadsheet: Encodian - Template Syntax - Table Simple.xlsx

Example JSON Data:

{
  "contracts": [
  {
    "customerName": "Microsoft",
    "value": 100000,
    "expires": "13/06/2021"
  },
  {
    "customerName": "Encodian",
    "value": 6872500,
    "expires": "18/02/2029"
  },
  {
    "customerName": "Heritage",
    "value": 13549,
    "expires": "6/12/2025"
  }
 ]
}

Configure the table within your Excel spreadsheet and then populate it as follows to add the relevant JSON properties to each column:

Column A/B: <<foreach [contract in contracts]>><<[contract.customerName]>>
Column C/D: <<[contract.expires]:"yyyy.MM.dd">>
Column E/F: <<[contract.value]>><</foreach>>

This generates the following output:

Table - Single Column

Example Spreadsheet: Encodian - Template Syntax - Table Single Column.xlsx

Example JSON Data:

{
  "contracts": [
  {
    "customerName": "Microsoft",
    "value": 100000,
    "expires": "13/06/2021"
  },
  {
    "customerName": "Encodian",
    "value": 6872500,
    "expires": "18/02/2029"
  },
  {
    "customerName": "Heritage",
    "value": 13549,
    "expires": "6/12/2025"
  }
 ]
}

Configure the table within your Excel spreadsheet and then populate it as follows to add the relevant JSON properties to each column:

Column 1: <<foreach [contract in contracts]>>Customer: <<[contract.customerName]>>, Contract Date:<<[contract.expires]:"yyyy.MM.dd">>, Value: £<<[contract.value]>><</foreach -greedy>>

This generates the following output:

NOTE: the -greedy switch instructs the population engine to treat the property as a single row, if this is not used the output would be as follows:

Table - Total

Example Spreadsheet: Encodian - Template Syntax - Table Totals.xlsx

Example JSON Data:

{
  "contracts": [
  {
    "customerName": "Microsoft",
    "value": 100000,
    "expires": "13/06/2021"
  },
  {
    "customerName": "Encodian",
    "value": 6872500,
    "expires": "18/02/2029"
  },
  {
    "customerName": "Heritage",
    "value": 13549,
    "expires": "6/12/2025"
  }
 ]
}

Configure the table within your Excel spreadsheet and then populate it as follows to add the relevant JSON properties to each column:

Row 3 > Column A/B: <<foreach [contract in contracts]>><<[contract.customerName]>>
Row 3 > Column C/D: <<[contract.expires]:"yyyy.MM.dd">>
Row 3 > Column E/F: <<[contract.value]>><</foreach>>
Row 4 - Column E/F: <<[contracts.Sum(c =>c.value)]>>

This generates the following output:

Table - Span Rows

Example Spreadsheet: Encodian - Template Syntax - Table Span Rows.xlsx

Example JSON Data:

{
  "contracts": [
  {
    "customerName": "Microsoft",
    "value": 100000,
    "expires": "13/06/2021"
  },
  {
    "customerName": "Encodian",
    "value": 6872500,
    "expires": "18/02/2029"
  },
  {
    "customerName": "Heritage",
    "value": 13549,
    "expires": "6/12/2025"
  }
 ]
}

Configure the table within your Excel spreadsheet and then populate as follows to add the relevant JSON properties to each column:

Row 3 > Column A/B: <<foreach [contract in contracts]>><<[contract.customerName]>>
Row 3 > Column C/D: <<[contract.expires]:"yyyy.MM.dd">>
Row 4 > Column A/B: Value: £<<[contract.value]>>
Row 4 > Column C/D: <</foreach -greedy>>

NOTE: the -greedy switch instructs the population engine to treat the property as a single row

This generates the following output:

Table - Merge Cells

Merge table cells with equal text content using the <<cellMerge>> tag. 

In populate excel only a vertical merge is possible as the -horz tag is not supported. 

Example Spreadsheet: Encodian - Template Syntax - Table Merge.xlsx

Example JSON Data:

{
  "Movies": [
  {
    "Genre": "Sci-Fi",
    "Titles": [
    {
      "Title": "Star Wars: The Phantom Menace",
      "Rating": "8",
      "Platform": "Disney",
      "Type": "Movie"
    },
    {
      "Title": "Star Trek: The Motion Picture",
      "Rating": "8",
      "Platform": "Amazon",
      "Type": "Movie"
    },
    {
      "Title": "Star Wars: The Mandalorian",
      "Rating": "10",
      "Platform": "Disney",
      "Type": "TV Series"
    }
   ]
  },
  {
    "Genre": "Fantasy",
    "Titles": [
    {
      "Title": "Lord of the Rings: The Rings of Power",
      "Rating": "8",
      "Platform": "Amazon",
      "Type": "Movie"
    },
    {
      "Title": "The Magicians",
      "Rating": "6",
      "Platform": "Amazon",
      "Type": "TV Series"
    }
   ]
  }
 ]
}

Configure the table within your Excel spreadsheet and then populate as follows to add the relevant JSON properties to each column:

Column A: <<foreach [movie in Movies]>><<foreach [title in movie.Titles]>><<[movie.Genre]>><<cellMerge>>
Column B/C: <<[title.Title]>>
Column D/E: <<[title.Rating]>>
Column F/G: <<[title.Platform]>>
Column H/I: <<[title.Type]>><</foreach>><</foreach>>

This generates the following output: