📁 Bulk Import & Export Excel Data for Joget Form Grids

#automation #javascript #productivity #tutorial

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:

Parsing: When a user selects a file, the FileReader API reads the content, and SheetJS converts the binary data into a JSON object.
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.
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.
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).

<div class="excel-import-container" style="padding: 15px; border: 1px solid #ddd; border-radius: 5px;">
  <input id="file_upload" type="file" accept=".xlsx" style="display:none;">
  <button type="button" class="form-button" onclick="$('#file_upload').click();">📁 Select Excel File</button>
  <button type="button" class="export_excel form-button">📥 Export Grid</button>
  <button type="button" class="delete_all form-button-destructive">🗑️ Clear Grid</button>
  <div style="margin-top: 10px;">
    <a href="/jw/web/app/E_procurement/resources/BidTemplate.xlsx" style="font-size: 0.9em; color: #007bff;">📥 Download Required Excel Template</a>
  </div>
</div>

<script>
  $(document).ready(function () {
    // Clear Table Logic
    $('.delete_all').on('click', function () {
      if(confirm("This will remove all entries from the grid. Proceed?")) {
        $('[name=Uploaded_bids] tbody tr:not(:first-child)').empty();
      }
    });

    // Export Logic
    $('.export_excel').click(function () {
      var originalTable = document.querySelector(".tablesaw");
      var clonedTable = originalTable.cloneNode(true);

      // Remove Action/Delete columns from the export
      $(clonedTable).find(".grid-action-cell, th:last-child, td:last-child").remove();

      var wb = XLSX.utils.table_to_book(clonedTable, { sheet: "Sheet1" });
      XLSX.writeFile(wb, "Grid_Export.xlsx");
    });

    // Import Logic
    $('#file_upload').change(function (e) {
      var files = e.target.files;
      var reader = new FileReader();

      reader.onload = function (e) {
        var data = e.target.result;
        var workbook = XLSX.read(data, { type: 'binary' });
        var sheet = workbook.Sheets[workbook.SheetNames[0]];
        var range = XLSX.utils.decode_range(sheet['!ref']);

        // Convert to JSON (Header: 1 returns an array of arrays)
        var jsonData = XLSX.utils.sheet_to_json(sheet, { header: 1 });

        addItemsToBid(jsonData);
      };
      reader.readAsBinaryString(files[0]);
    });
  });

  async function addItemsToBid(jsonData) {
    // Loop through rows (index 0 is header)
    for (let i = 1; i < jsonData.length; i++) {
      let row = jsonData[i];
      if (!row || row.length === 0) continue;

      await new Promise(resolve => {
        setTimeout(() => {
          var field = FormUtil.getField("Uploaded_bids");
          var gridAddFunction = window[field.attr("id") + "_add"];

          if (typeof gridAddFunction === 'function') {
            gridAddFunction({
              'result': JSON.stringify({
                "item_Name": row[0] || "",
                "item_UOM": row[1] || "",
                "quantity": row[2] || "",
                "description": row[3] || "",
                "technical_requirement": row[4] || ""
              })
            });
          }
          resolve();
        }, 30); // 30ms stagger to keep UI responsive
      });
    }
    alert("Data import successful!");
  }
</script>

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.

DEV Community