<div class="container">
  <h1>Upload, filter and download an Excel using SheetJS</h1>
  <p>This example shows to create a filtered Excel using another one.</p>

  <h2>Create a local Excel file</h2>
  <p>Copy & Paste the following information into an Excel file on your local PC:</p>
  <table class="table">
    <tbody>
      <tr>
        <td scope="col">First name</td>
        <td scope="col">Last name</td>
        <td scope="col">Age</td>
      </tr>
      <tr>
        <td>Mark</td>
        <td>Otto</td>
        <td>18</td>
      </tr>
      <tr>
        <td>Jacob</td>
        <td>Thornton</td>
        <td>12</td>
      </tr>
      <tr>
        <td>Larry</td>
        <td>the Bird</td>
        <td>22</td>
      </tr>
      <tr>
        <td>Jane</td>
        <td>Watson</td>
        <td>44</td>
      </tr>
      <tr>
        <td>John</td>
        <td>Doe</td>
        <td>16</td>
      </tr>
      <tr>
        <td>Luca</td>
        <td>Martin</td>
        <td>60</td>
      </tr>
    </tbody>
  </table>

  <h2>Upload</h2>
  <p>Choose the file you just created and choose the filtering options.<p>
  <form id="formImportExcel" method="post" enctype="multipart/form-data">
    <div class="mb-3">
      <label for="importFile" class="form-label">File</label>
      <input id="importFile" type="file" class="form-control" name="importFile" required>
    </div>

    <div class="mb-3">
      <label for="fullName" class="form-label">Name format</label>
      <div class="form-check">
        <input id="fullName" class="form-check-input" type="checkbox" name="fullName">
        <label class="form-check-label" for="fullName">
          Join first and last name in one column
        </label>
      </div>
    </div>

    <div class="mb-3">
      <label for="age" class="form-label">Minimum Age</label>
      <input id="age" type="number" class="form-control" name="age">
    </div>

    <button type="submit" class="btn btn-primary">Convert</button>
    
    <p>Once you click on CONVERT, the download will start with a new filtered Excel file.</p>
  </form>
</div>

// Capture "submit" event from form
const formImportExcel = document.getElementById("formImportExcel");
formImportExcel.addEventListener("submit", async (event) => {
  event.preventDefault()

  /**
   * 1) Get information from form
   */
  const formData = new FormData(formImportExcel)
  // Get FILE Object
  const formFile = formData.get("importFile")
  // Get "on" or null from checkbox
  const formFullNameCheck = formData.get("fullName")
  // Get "Age" input number
  const formAge = formData.get("age") || 0
  
  /**
   * 2) Read the Excel file and process it
   */
  // Read the excel file posted from form
  const file = await formFile.arrayBuffer()
  const workbook = XLSX.readFile(file, {type: "binary"})

  // Choose first Sheet from workbook
  const firstSheet = workbook.Sheets[workbook.SheetNames[0]]
  // Convert sheet to JSON
  const sheetToJson = XLSX.utils.sheet_to_json(firstSheet, {defval: ""})

  // Create a new array from sheetToJson Object
  var newData = sheetToJson.map((row) => {
    // Join "First name" and "Last name" into one "Full name" column
    if (formFullNameCheck === "on") {
      // Create a new row format
      row = {
        "Full name": row["First name"] + " " + row["Last name"],
        "Age": row["Age"]
      }
    }

    return row;
  })
  
  // Now filter the new array (newData) by "Age" column
  newData = newData.filter(row => row["Age"] > formAge)
  
  /**
   * 3) Create a new Excel file and download it
   */
  // Create a new book
  const newWorkBook = XLSX.utils.book_new()
  // Convert the newData JSON format to Sheet format
  const jsonToSheet = XLSX.utils.json_to_sheet(newData)
  // Append Sheet to WorkBook
  XLSX.utils.book_append_sheet(newWorkBook, jsonToSheet, "Sheet1")
  // Write file and download it
  XLSX.writeFile(newWorkBook, "filtered.xlsx")
} )