Write data to Excel and export

The writeDataToExcelAndExport extension creates an Excel workbook from up to five JSON array inputs and exports it as an .xlsx file. Each non-empty JSON input is converted into a worksheet, and each worksheet can use either a provided name or a generated fallback name.

This is useful when a journey needs to collect structured runtime data, split it across multiple sheets, and export the result as a workbook for review, reporting, reconciliation, or audit evidence.

Parameters:

• jsonInputData1 optional, a JSON string representing an array of objects to write into the first worksheet. If empty, the first worksheet is not created;
• jsonInputData2 optional, a JSON string representing an array of objects to write into the second worksheet. If empty, the second worksheet is not created;
• jsonInputData3 optional, a JSON string representing an array of objects to write into the third worksheet. If empty, the third worksheet is not created;
• jsonInputData4 optional, a JSON string representing an array of objects to write into the fourth worksheet. If empty, the fourth worksheet is not created;
• jsonInputData5 optional, a JSON string representing an array of objects to write into the fifth worksheet. If empty, the fifth worksheet is not created;
• workbookNameInput optional, a string used as the exported workbook file name. If not provided, the extension generates a random workbook name;
• worksheet1 optional, a string used as the first worksheet name. If not provided and data exists, the extension generates a random sheet name;
• worksheet2 optional, a string used as the second worksheet name. If not provided and data exists, the extension generates a random sheet name;
• worksheet3 optional, a string used as the third worksheet name. If not provided and data exists, the extension generates a random sheet name;
• worksheet4 optional, a string used as the fourth worksheet name. If not provided and data exists, the extension generates a random sheet name;
• worksheet5 optional, a string used as the fifth worksheet name. If not provided and data exists, the extension generates a random sheet name;

NLP usage

Use the extension in a journey by calling writeDataToExcelAndExport with the execute command. Pass each value to the matching extension input using as inputName.

execute "writeDataToExcelAndExport" using '[{"Name":"Alice","Status":"Passed"},{"Name":"Bob","Status":"Failed"}]' as jsonInputData1 and '[{"CaseId":"TC-001","Result":"Passed"}]' as jsonInputData2 and "" as jsonInputData3 and "" as jsonInputData4 and "" as jsonInputData5 and "QA_Report" as workbookNameInput and "Summary" as worksheet1 and "Cases" as worksheet2 and "" as worksheet3 and "" as worksheet4 and "" as worksheet5 returning $response

execute "writeDataToExcelAndExport" using '[{"Region":"CAN","Code":"AB"}]' as jsonInputData1 and "" as jsonInputData2 and "" as jsonInputData3 and "" as jsonInputData4 and "" as jsonInputData5 and "" as workbookNameInput and "Regions" as worksheet1 and "" as worksheet2 and "" as worksheet3 and "" as worksheet4 and "" as worksheet5 returning $response

Variables can also be used when the JSON arrays, workbook name, or worksheet names are generated earlier in the journey.

execute "writeDataToExcelAndExport" using "$sheetOneData" as jsonInputData1 and "$sheetTwoData" as jsonInputData2 and "" as jsonInputData3 and "" as jsonInputData4 and "" as jsonInputData5 and "$workbookName" as workbookNameInput and "$sheetOneName" as worksheet1 and "$sheetTwoName" as worksheet2 and "" as worksheet3 and "" as worksheet4 and "" as worksheet5 returning $response

store value '[{"Name":"Alice","Status":"Passed"},{"Name":"Bob","Status":"Failed"}]' in $sheetOneData
store value '[{"CaseId":"TC-001","Result":"Passed"}]' in $sheetTwoData
store value "QA_Report" in $workbookName
store value "Summary" in $sheetOneName
store value "Cases" in $sheetTwoName
execute "writeDataToExcelAndExport" using "$sheetOneData" as jsonInputData1 and "$sheetTwoData" as jsonInputData2 and "" as jsonInputData3 and "" as jsonInputData4 and "" as jsonInputData5 and "$workbookName" as workbookNameInput and "$sheetOneName" as worksheet1 and "$sheetTwoName" as worksheet2 and "" as worksheet3 and "" as worksheet4 and "" as worksheet5 returning $response

Example output when the workbook is created successfully:

Workbook created successfully.

This extension requires the following resources:

• https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js 

The extension should be configured as:

• Run asynchronously: No
• Scope: Global

Add the extension to your Virtuoso instance

Select the domain that matches your Virtuoso account.

const data1 = jsonInputData1 ? JSON.parse(jsonInputData1) : [];
const data2 = jsonInputData2 ? JSON.parse(jsonInputData2) : [];
const data3 = jsonInputData3 ? JSON.parse(jsonInputData3) : [];
const data4 = jsonInputData4 ? JSON.parse(jsonInputData4) : [];
const data5 = jsonInputData5 ? JSON.parse(jsonInputData5) : [];

const generateRandomSheetName = () => {
  const timestamp = new Date().getTime();
  const randomNumber = Math.floor(Math.random() * 1000);
  return `Sheet_${timestamp}_${randomNumber}`;
};

const generateRandomWorkbookName = () => {
  const timestamp = new Date().getTime();
  const randomNumber = Math.floor(Math.random() * 1000);
  return `Workbook_${timestamp}_${randomNumber}`;
};

const wbName = workbookNameInput; 
const sheet1Name = worksheet1;
const sheet2Name = worksheet2; 
const sheet3Name = worksheet3;
const sheet4Name = worksheet4;
const sheet5Name = worksheet5;

try {
  const randomNumber = Math.floor(Math.random() * 100000);
  const wb = XLSX.utils.book_new({
    SheetNames: [],
    SheetTitles: []
  });

  if (data1 && data1.length > 0) {
    const ws1 = XLSX.utils.json_to_sheet(data1);
    const sheet1 = sheet1Name ? sheet1Name : generateRandomSheetName();
    XLSX.utils.book_append_sheet(wb, ws1, sheet1);
  }
if (data2 && data2.length > 0) {
  const ws2 = XLSX.utils.json_to_sheet(data2);
  const sheet2 = sheet2Name ? sheet2Name : generateRandomSheetName();
  XLSX.utils.book_append_sheet(wb, ws2, sheet2);
}

if (data3 && data3.length > 0) {
  const ws3 = XLSX.utils.json_to_sheet(data3);
  const sheet3 = sheet3Name ? sheet3Name : generateRandomSheetName();
  XLSX.utils.book_append_sheet(wb, ws3, sheet3);
}

if (data4 && data4.length > 0) {
  const ws4 = XLSX.utils.json_to_sheet(data4);
  const sheet4 = sheet4Name ? sheet4Name : generateRandomSheetName();
  XLSX.utils.book_append_sheet(wb, ws4, sheet4);
}

if (data5 && data5.length > 0) {
  const ws5 = XLSX.utils.json_to_sheet(data5);
  const sheet5 = sheet5Name ? sheet5Name : generateRandomSheetName();
  XLSX.utils.book_append_sheet(wb, ws5, sheet5);
}

  const workbookName = wbName || generateRandomWorkbookName();

  XLSX.writeFile(wb, `${workbookName}.xlsx`);

  console.log('Workbook created successfully.');
  return ('Workbook created successfully.');
} catch (error) {
  console.error(`An error occurred while creating the workbook: ${error.message}\n${error.stack}`);
  throw new Error(`An error occurred while creating the workbook: ${error.message}\n${error.stack}`);
}