Excel file to Grid Columns in Processmaker 3.8.3 - Stack Overflow

I need some help in creating a process in processmaker 3.8.3 regarding how to create a dynaform with option to upload a file, a grid (Name, Address, City, Contact Num) and a submit button. As the file is uploaded it should automatically populate the grid columns with the values of the excel sheet.

I've tried it many times and I'm stuck in the process.

I created a dynaform with the required items in it.

I created these triggers:

require_once 'vendor/autoload.php'; // Load PhpSpreadsheet library

use PhpOffice\PhpSpreadsheet\IOFactory;

// Define the path to the shared directory
define('PATH_TO_PM_SHARES', 'C:\\xampp\\processmaker\\shared');

// Get the file path from the uploaded file
$fileId = @@excel_file;
$filePath = PATH_TO_PM_SHARES . '\\sites\\workflow\\files\\' . $fileId;

// Load the Excel file
$spreadsheet = IOFactory::load($filePath);
$worksheet = $spreadsheet->getActiveSheet();

// Initialize an empty array for grid data
$gridData = [];

// Loop through the rows and extract data
foreach ($worksheet->getRowIterator() as $row) {
    $cellData = [];
    foreach ($row->getCellIterator() as $cell) {
        $cellData[] = $cell->getValue();
    }
    $gridData[] = $cellData; // Add the row data to gridData
}

// Assign the data to the grid
@@excel_grid = $gridData;

And in the steps I've assigned it after the dynaform (i.e. after the user uploads the above code runs). But it is not working, the grid columns are not being populated with the data from the uploaded file.

I need some help in creating a process in processmaker 3.8.3 regarding how to create a dynaform with option to upload a file, a grid (Name, Address, City, Contact Num) and a submit button. As the file is uploaded it should automatically populate the grid columns with the values of the excel sheet.

I've tried it many times and I'm stuck in the process.

I created a dynaform with the required items in it.

I created these triggers:

require_once 'vendor/autoload.php'; // Load PhpSpreadsheet library

use PhpOffice\PhpSpreadsheet\IOFactory;

// Define the path to the shared directory
define('PATH_TO_PM_SHARES', 'C:\\xampp\\processmaker\\shared');

// Get the file path from the uploaded file
$fileId = @@excel_file;
$filePath = PATH_TO_PM_SHARES . '\\sites\\workflow\\files\\' . $fileId;

// Load the Excel file
$spreadsheet = IOFactory::load($filePath);
$worksheet = $spreadsheet->getActiveSheet();

// Initialize an empty array for grid data
$gridData = [];

// Loop through the rows and extract data
foreach ($worksheet->getRowIterator() as $row) {
    $cellData = [];
    foreach ($row->getCellIterator() as $cell) {
        $cellData[] = $cell->getValue();
    }
    $gridData[] = $cellData; // Add the row data to gridData
}

// Assign the data to the grid
@@excel_grid = $gridData;

And in the steps I've assigned it after the dynaform (i.e. after the user uploads the above code runs). But it is not working, the grid columns are not being populated with the data from the uploaded file.

• processmaker

Share Improve this question Follow edited Feb 6 at 8:40 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 2 at 14:52 Ashesh PradhanAshesh Pradhan 344 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

you don't need to use php or trigger to upload data from excel to grid. example explanation: I had a grid with three fields ID, Name and Gender. For this reason I also had an Excel file with the same three columns which had the same header as the grids. I used the JavaScript library to read data from Excel. After receiving an array of data. First I placed the first row of data to first line of grid then I created a row in the grid by looping through the array for each row of data with the information received from the Excel file I hope I explained it correctly. form template in processmaker i use this code :

$.getScript("https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js")
.done(function() {
    console.log("XLSX library loaded successfully.");
})
.fail(function() {
    alert("Failed to load XLSX library.");
});
function importExcelData(file) {
var reader = new FileReader();
reader.onload = function () {
    try {
        var data = new Uint8Array(reader.result);
        var workbook = XLSX.read(data, { type: 'array' });
        var sheet = workbook.Sheets[workbook.SheetNames[0]];
        var jsonData = XLSX.utils.sheet_to_json(sheet, { header: 1 });

         if (!jsonData || jsonData.length === 0) {
            alert('فایل اکسل خالی است.');
            return;
        }
            // انتخاب گرید
        var $grid = $("#grid_1");

        if ($grid.length === 0) {
            alert("گرید پیدا نشد.");
            return;
        }

        // افزودن داده‌ها به گرید
        
     $("#grid_1").setText(jsonData[1][0], 1, 1) ;
      $("#grid_1").setText(jsonData[1][1], 1, 2) ;
      $("#grid_1").setText(jsonData[1][2], 1, 3) ;
      //console.log (jsonData);
    
        jsonData.forEach(function (row, index) {
            if (index >1) { // رد کردن هدر
                // اضافه کردن یک سطر جدید
              //var n= jsonData[2][0];
             //console.log (n);
             // console.log (row);
              //console.log (index);
               var aData = [
                    {value: jsonData[index][0]}, 
                    {value: jsonData[index][1]}, 
                    {value: jsonData[index][2] }
                           ];
               $("#grid_1").addRow(aData);

            }
        });
        
     
            alert("داده‌ها به گرید اضافه شدند.");
    } catch (error) {
        alert("خطا در پردازش فایل اکسل: " + error.message);
    }
};

reader.readAsArrayBuffer(file);
}

$('#button_1').on('click', function () {
var fileInput = document.createElement('input');
fileInput.type = 'file';
fileInput.accept = '.xlsx';
fileInput.click();

fileInput.addEventListener('change', function (event) {
    var file = event.target.files[0];
    if (file) {
        importExcelData(file);
    } else {
        alert("لطفاً یک فایل انتخاب کنید.");
    }
});
});