Export to excel file by Camunda Service Task

Hello everyone,
I want to export some variables to excel file (screenshot has been attached), by using the camudna service task (screenshot has been attached), how can define the JS file into BPMN/ Service Task.
In addtion, how can added new rows of values for every variable with out detele the current values, which mean, every iteration I want to add a new row values without delete/ doing changing in values that has been added in the previous iterations.
Thank You,

var Files = Java.type(‘java.nio.file.Files’)

var Paths = Java.type(‘java.nio.file.Paths’)

var CSVPrinter = Java.type(‘org.apache.commons.csv.CSVPrinter’)

var CSVFormat = Java.type(‘org.apache.commons.csv.CSVFormat’)

var StandardOpenOption = Java.type(‘java.nio.file.StandardOpenOption’)

var Customer_Name = execution.getVariable(‘CustomerName’)

var Customer_Credit = execution.getVariable(‘CustomerCredit’)

var Customer_Type = execution.getVariable(‘CustomerType’)

var Maximum_Shipping_Weight = execution.getVariable(‘MaximumShippingWeight’)

var csvPath = “C:/Users/M.Sc Project.DESKTOP-2QLSO86/OneDrive/Shipping Order Camunda Run Final Verson/Output/output.csv”;

var writer = Files.newBufferedWriter(Paths.get(csvPath), StandardOpenOption.APPEND);

// Example of adding a header row.

// Make sure to add logic for detecting header row and not adding the header row on subsequent appends

// Simple workaround is to just make sure the first row is always included in your CSV file.

var csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader(“Customer_Name”, “Customer_Credit”, “Customer_Type”, “Maximum_Shipping_Weight”));

//var csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT);

// If you have multiple records to add to the csv,

// you can loop through this line:

csvPrinter.printRecord(Customer_Name, Customer_Credit, Customer_Type, Maximum_Shipping_Weight);

csvPrinter.flush();

,

Hi @YazanAsaad,

I would use the external task client for JavaScript: GitHub - camunda/camunda-external-task-client-js: Implement your BPMN Service Task in NodeJS. and move your javascript code into a handler.

Implement the service task as an external service task: External Tasks | docs.camunda.org.

Hope this helps, Ingo

Hello @Ingo_Richtsmeier
const { Client, logger } = require(“camunda-external-task-client-js”);

const { Variables } = require(“camunda-external-task-client-js”);

// configuration for the Client:

// - ‘baseUrl’: url to the Process Engine

// - ‘logger’: utility to automatically log important events

const config = { baseUrl: “http://localhost:8080/engine-rest”, use: logger };

// create a Client instance with custom configuration

const client = new Client(config);

// susbscribe to the topic: ‘ExportExcelFile’

client.subscribe(“ExportExcelFile”, async function({ task, taskService }) {

// Put your business logic

const Order_Number = task.variables.get(“RequestNumber”);

const Customer_Name = task.variables.get(“CustomerName”);

const Product_Name = task.variables.get(“ProdutName”);

const Product_Quantity = task.variables.get(“ProductQuantity”);

const Unit_Price = task.variables.get(“UnitPrice”);

var Excel = require(‘xlsx’);

// A new Excel Work Book

const ExportExcelFile = new Excel.Workbook();

var sheet = workbook.addWorksheet(‘Sheet1’);

 // A table header

 sheet.columns = [

    { header: 'Request_Number', key: 'Request_Number' },

    { header: 'Variable', key: 'Variable' },

    { header: 'Value', key: 'Value' }

]

// Add rows in the above header

sheet.addRow({Request_Number: Order_Number, Variable: 'Unit_Price', Value: Unit_Price });

sheet.addRow({Request_Number: Order_Number, Variable: 'Cusromer Name', Value: Customer_Name});

sheet.addRow({Request_Number: Order_Number, Variable: 'Product Name', Value: Product_Name});

sheet.addRow({Request_Number: Order_Number, Variable: 'Product Quantity', Value: Product_Quantity});

  // Save Excel on Hard Disk

  workbook.xlsx.writeFile("My First Excel.xlsx")

  .then(function() {

      // Success Message

      alert("File Saved");

  });

// complete the task

await taskService.complete(task, ExportExcelFile);

});

Unfortunately, it does not work!

Check InternalService methods table.AddRows, table.Read, excel.Create, telegram.File, email - It is production ready service to add data to temp-tables, read temp-table, create excel file with many sheets + send report to telegram or by email.

1 Like

Hi @YazanAsaad,

what exactly didn’t work?

const { Client, logger } = require(“camunda-external-task-client-js”);

const { Variables } = require(“camunda-external-task-client-js”);

// configuration for the Client:

// - ‘baseUrl’: url to the Process Engine

// - ‘logger’: utility to automatically log important events

const config = { baseUrl: “http://localhost:8080/engine-rest”, use: logger };

‘use strict’;

const Excel = require(‘exceljs’);

const tmp = require(‘tmp’);

const fs = require(‘fs’);

async function excelCreate (service, sheets, data)

{

var tmpfile = tmp.fileSync({ mode: 0o644, postfix: ‘.xlsx’ });

const options = {

filename: tmpfile.name,

useStyles: true,

useSharedStrings: true

};

const workbook = new Excel.stream.xlsx.WorkbookWriter(options);

for (var i=0; i<sheets.length; i++) {

var worksheet = workbook.addWorksheet(sheets[i]);

worksheet.state = 'visible';

var sheetData = data[i];

// Get sheet data from redis cache

if (typeof sheetData == 'string' && sheetData.startsWith('redis')) {

  var key = sheetData.substring(6);

  var redisData = await service.redis.getAsync (key);

  sheetData = JSON.parse (redisData);

}

var sheetColumns = [];

for (var j=0; j<sheetData.length; j++) {

  for (let [key] of Object.entries(sheetData[j])) {

    var found = false;

    for (var k = 0; k < sheetColumns.length; k++) {

      if (sheetColumns[k].header == key) {

        found = true;

      }

    }

    if (! found) {

      sheetColumns.push ({header: key, key: key, width: 20});

    }

  }

}

worksheet.columns = sheetColumns;

for (j=0; j<sheetData.length; j++) {

  worksheet.addRow(sheetData[j]).commit();

}

worksheet.commit();

}

await workbook.commit();

var exceldata = fs.readFileSync(options.filename);

fs.unlinkSync(options.filename);

return Buffer.from(exceldata).toString(‘base64’);

}

module.exports = { excelCreate };

Hello @Ingo_Richtsmeier
I would export my data to excel file, I use several codes with connecting to Canumda-external-task-client-js but I did not successed to do that, can you help me plaese

Hello @Ingo_Richtsmeier Variables_Export_To_Excel

I would export these varibales to excel file.
How can I do that?