Excel templating

Excel templating

Posted by: vladislavit.loginov on 27 March 2023, 5:40 pm EST

• 

  vladislavit.loginov

  • Post Options:
  • Link
    Copy

  Posted 27 March 2023, 5:40 pm EST - Updated 27 March 2023, 5:55 pm EST

  Hi, I have some issue with excel templating.
  
  I have a next json structure:

  {
    "arrayProp": [
      {
        "id": 1,
        "name": "name1"
      },
      {
        "id": 2,
        "name": "name2"
      }
    ]
  }

  My main goal is to create an excel workbook where the number of worksheets will be equal to the number of arrayProp elements and each worksheet will describe
  
  one element of the array. So, I use worksheet name = {{arrayProp.id}} in the template, but in this case I see all the data from arrayProp on each page, i.e. the data has not been filtered.

  I understand, that I can solve this task using another json structure:

  [
    {
      "id": 1,
      "name": "name1"
    },
    {
      "id": 2,
      "name": "name2"
    }
  ]

  So, two problems that I would like to solve:

  1. I would like to find a solution for the first json.
  2. I would like to filter the worksheet data by the “id” property, but name the page by the “name” property.
     

     1017×300 6.86 KB
• 

  nitin.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 28 March 2023, 8:24 pm EST

  Hi,

  Thanks for reaching out to us with your query.

  1. You can resolve this issue by setting TemplateOptions.PaginationMode=true as:(see below code)

  workbook.Names.Add("TemplateOptions.PaginationMode", "true");

  Now all array data will not be generated on each page. Only respective data items will generate on each sheet.

  2. You need to set {{ds.arrayProp.name}} on the sheet name to get the “name” on each sheet name. JFYI, sheet count will depend on the number of object in datasource. And each sheet name will be linked with the respectiva data-item name.

  Please refer the attached sample for the same: JsonTemplateProcessing.zip

  Best Regards,
  
  Nitin