Skip to main content
shrutik0007
shrutik0007
February 17, 2022
Question

Customize cell keys and cell values for export CDT to excel

  • February 17, 2022
  • 3 replies
  • 0 views

I have a requirement where I have to export form data to excel. I am using excel template to populate data in specified cells using "cell keys" and "cell values" but data is not coming properly.

Expected Output should look like-

column C,D,E are coming up additionally. Could anyone help me how to correct this. Below is the current configuration:

CDT- pv!cdt,

cell values- {  "B2",  "B3",  "B4",  "B5"}

cell values-{pv!cdt.f1,pv!cdt.f2,pv!cdt.f3,pv!cdt.f4}

Document_name_to_create- "Form Fields"

Document_save_directory-cons!Folder

Excel_base_template- cons!excelTemplate

Include_header_row-false

sheet_number-0

Starting_cell-"B1"

    3 replies

    mikes0011
    mikes0011
    Brainy
    February 17, 2022

    This node is doing two separate things.  First, it is writing the CDT (pv!cdt) to the excel template starting at "Starting Cell" (B1).  This will write the entire value of the CDT to the Excel template in tabular format with the top-left-most cell being B1.

    Second, it is writing the manually-defined "cell keys / cell values", with your 4 manually-defined cells of B2 -> B5 and your 4 manually-defined data inputs.

    Since you're doing both, they're colliding with each other.  The "extra" values we're seeing in C2, D2 and E2 are actually expected results.

    This is not what the node is meant to do.  In other words, the "cell keys / values" are not meant to duplicate / replace the "insert CDT" functionality.  Usually these are left blank unless you would like to insert some extra values, manually, elsewhere in the Excel sheet.  In the past I've used these spots to insert text indicating user-entered filters that affected the data set on the generated Excel sheet, for example.

    The Expression Guru
      shrutik0007
      shrutik0007Author
      February 20, 2022

      Thanks for the explanation. Could you please provide any example or explain like what configuration I should be doing in order to achieve the manually defined cell keys and cell values without having any kind of repetition (  i.e. eliminating column C, D and E)

      mikes0011
      mikes0011
      Brainy
      February 20, 2022

      Well to be quite honest that's not really what the CDT to Excel node is for, if you don't want to actually use the "CDT" part of it.  But you could always try defining a 1-column CDT and passing in a blank value for it, just to satisfy the required inputs of the node, then let your manually-defined columns do the rest. 

      TBH if you're going to stick with this approach, you might have an easier time using the "SQL to Excel" node instead, and just passing in a dummy SQL statement like "SELECT NULL".  IIRC it has the exact same manual defined cell/key functionality as the CDT to excel node.

      The Expression Guru
      Terms & ConditionsAccessibility statement