📁 Bulk Import & Export Excel Data for Joget Form Grids

Overview

Manually entering large volumes of data into a Form Grid or Spreadsheet element is a common bottleneck in enterprise workflows. This guide provides a professional-grade solution using the SheetJS (XLSX) library to allow users to upload an Excel file and instantly populate a Joget Form Grid. It also includes a "Clean Export" feature to download the grid data back into Excel.

How It Works

The integration acts as a client-side bridge between your local file and the Joget UI:

1. Parsing: When a user selects a file, the FileReader API reads the content, and SheetJS converts the binary data into a JSON object.
2. Mapping: The script scans the Excel header row to find specific columns (e.g., "Item Name", "Quantity"). This makes the import flexible—users don't have to have columns in a rigid, fixed order as long as the names match.
3. Injection: The script uses Joget’s internal JavaScript API—specifically the [FieldID]_add function—to programmatically "click" the add button and fill the fields for every row found in the Excel sheet.
4. Async Processing: To prevent the browser from freezing during a 500-row import, the script uses async/await to process rows in small batches.

Where to Use in Joget

• ✅ Form Builder: Place this logic inside a Custom HTML element positioned above your Form Grid.
• ✅ Procurement/Finance Apps: Ideal for Bids, Expense Claims, or Inventory lists where line items are usually prepared in Excel first.

Full Code

Ensure you have the SheetJS library added to your App (via Userview Settings > Custom JavaScript or a script tag in the Form).

Example Use Cases

• 💡 Tendering: Vendors uploading their entire technical proposal and pricing list in one click.
• 💡 Bulk User Updates: HR administrators uploading a list of employee adjustments to a processing form.

Customization Tips

• ⚙️ Target Field IDs: Replace Uploaded_bids in the code with the actual ID of your Form Grid.
• ⚙️ Security Note: This script processes data entirely in the user's browser. No Excel data is sent to external servers for parsing, ensuring data privacy.
• ⚙️ Template Matching: Ensure the row[x] indexes in the addItemsToBid function match the column order in your "Sample File Format" download.

Key Benefits

• ✅ Better UX: Transforms a tedious data entry task into a simple file upload.
• ✅ Data Integrity: Automates the mapping of Excel data to Joget fields, reducing transcription errors.
• ✅ Performance: Handle hundreds of rows smoothly using asynchronous row injection.

Final Thoughts

Providing an Excel-to-Form bridge is one of the most requested features in enterprise environments. By leveraging SheetJS and Joget's internal grid functions, you provide a seamless, high-performance experience that makes your application feel truly professional.

---

Source Code

<input id="file_upload" name="file_upload" type="file" size=""
  accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">

<!--<input type="button" value="Import Excel" class="import_excel">-->
<input type="button" value="Export Excel" class="export_excel">
<input type="button" value="Delete All" class="delete_all">

<div>
  <a href="/jw/web/app/E_procurement/resources/BidTemplate.xlsx">Sample File Format</a>
</div>


<script>
  $(document).ready(function () {

    $('.delete_all').on('click', function (e) {
      $('[name=Uploaded_bids] tbody tr:not(:first-child)').empty();
    });



    $('.export_excel').click(function () {
      ExportToExcel('xlsx');
    })


    function ExportToExcel(type, fn, dl) {
      var originalTable = document.querySelector(".tablesaw");

      // Clone the table
      var clonedTable = originalTable.cloneNode(true);

      // Select all the table cells in the cloned table
      var cells = clonedTable.querySelectorAll("td, th");

      // Identify the cells with class "grid-action-cell" and remove them
      for (var i = cells.length - 1; i >= 0; i--) {
        if (cells[i].classList.contains("grid-action-cell")) {
          cells[i].parentNode.removeChild(cells[i]);
        }
      }

      var wb = XLSX.utils.table_to_book(clonedTable, { sheet: "sheet1" });
      return dl
        ? XLSX.write(wb, { bookType: type, bookSST: true, type: "base64" })
        : XLSX.writeFile(wb, fn || "MySheetName." + (type || "xlsx"));
    }


    $('#file_upload').change(function (e) {
      var files = e.target.files;
      var fileReader = new FileReader();

      fileReader.onload = function () {
        var data = fileReader.result;
        var workbook = XLSX.read(data, { type: 'binary' });
        var sheetName = workbook.SheetNames[0];
        var worksheet = workbook.Sheets[sheetName];

        // Get the range of the worksheet
        var range = XLSX.utils.decode_range(worksheet['!ref']);

        // Get the index of the required columns
        var item_name_index = -1;
        var item_uom_index = -1;
        var quantity_index = -1;
        var description_index = -1;
        var technical_req_index = -1;

        for (var C = range.s.c; C <= range.e.c; ++C) {
          var cell_address = { c: C, r: range.s.r };
          var cell_ref = XLSX.utils.encode_cell(cell_address);
          var cell = worksheet[cell_ref];
          var column_name = (cell ? cell.v : '');

          switch (column_name.trim().toLowerCase()) {
            case 'item name':
              item_name_index = C;
              break;
            case 'item uom':
              item_uom_index = C;
              break;
            case 'quantity':
              quantity_index = C;
              break;
            case 'description':
              description_index = C;
              break;
            case 'technical requirement':
              technical_req_index = C;
              break;
          }
        }

        // If any of the required columns are missing, return an error message
        if (item_name_index === -1 || item_uom_index === -1 || quantity_index === -1 || description_index === -1 || technical_req_index === -1) {
          alert('excel format is not proper');
          console.error('One or more required columns are missing.');
          return;
        }

        var jsonData = XLSX.utils.sheet_to_json(worksheet, {
          header: 1,
          range: range.s.r + 1,
          blankRows: false,
          defval: '',
          raw: false,
          dateNF: 'yyyy-mm-dd',
          transform: function (value, column) {
            if (column === item_name_index || column === item_uom_index || column === quantity_index || column === description_index || column === technical_req_index) {
              return value;
            } else {
              return undefined;
            }
          }
        });

        console.log(jsonData);

        // Asynchronously add items to the bid
        addItemsToBid(jsonData);
      };

      fileReader.readAsBinaryString(files[0]);
    });
  });

  async function addItemsToBid(jsonData) {

    for (const element of jsonData) {
      await new Promise(resolve => {
        setTimeout(() => {
          var field = FormUtil.getField("Uploaded_bids");
          var functionName = window[field.attr("id") + "_add"];
          if (typeof functionName === 'function') {
            var args = new Object();
            args['result'] = JSON.stringify({
              "item_Name": element[0],
              "item_UOM": element[1],
              "quantity": element[2],
              "description": element[3],
              "technical_requirement": element[4],
            });
            functionName(args);
          }
          resolve();
        }, 0);
      });
    }
  }

</script>