CODEWITHSUNDEEP
javascripthtml
WEI ZHUANG GOH
WEI ZHUANG GOH

Reputation: 333

How to read csv and xlsx file to Html table form with Javascript

In my script, user can upload their CSV file and view the content with the HTML form. They are also able to add a new column at the first row of the HTML table. For example, when a user uploaded an excel file that contains 2 records, the script will automatically add a new row named Serial infront of the existing record. For now when user upload a csv file, it will show a weird empty column in the HTML table but it worked fine with xlsx format. Does anyone know what the cause of this issue ?

My Excel file:

enter image description here

Example (When a user uploads xlsx. format file, it will display correctly with no issue): enter image description here

But if the user uploads a .csv file, there is a weird empty column will appear： enter image description here

So how can I make the null column to dissapear? to work on both CSV and xlsx format? Now it seems like it only works for xlsx files.

Full Code

<!DOCTYPE HTML>
<html>

<head>


  <script type="text/javascript" src="https://unpkg.com/[email protected]/dist/xlsx.full.min.js"></script>
</head>

<body>
  <div class="container">
    <h2 class="text-center mt-4 mb-4">Convert Excel to HTML Table using JavaScript</h2>
    <div class="card">
      <div class="card-header"><b>Select Excel File</b></div>
      <div class="card-body">

        <input type="file" id="excel_file" />

      </div>
    </div>
    <div id="excel_data" class="mt-5"></div>
  </div>
</body>

</html>

<script>
  const excel_file = document.getElementById('excel_file');

  excel_file.addEventListener('change', (event) => {

    if (!['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'].includes(event.target.files[0].type)) {
      document.getElementById('excel_data').innerHTML = '<div class="alert alert-danger">Only .xlsx or .xls file format are allowed</div>';

      excel_file.value = '';

      return false;
    }

    var reader = new FileReader();

    reader.readAsArrayBuffer(event.target.files[0]);

    reader.onload = function(event) {

      var data = new Uint8Array(reader.result);

      var work_book = XLSX.read(data, {
        type: 'array'
      });

      var sheet_name = work_book.SheetNames;

      var sheet_data = XLSX.utils.sheet_to_json(work_book.Sheets[sheet_name[0]], {
        header: 1
      });

      if (sheet_data.length > 0) {
        var table_output = '<table class="table table-striped table-bordered">';

        for (var row = 0; row < sheet_data.length; row++) {

          table_output += '<tr>';
          if (row == 0) {
            table_output += '<th>' + ['Serial'] + '</th>'
          } else {
            table_output += '<td>' + row + '</td>'
          }

          for (var cell = 0; cell < sheet_data[row].length; cell++) {

            if (row == 0) {

              table_output += '<th>' + sheet_data[row][cell] + '</th>';

            } else {

              table_output += '<td>' + sheet_data[row][cell] + '</td>';

            }

          }

          table_output += '</tr>';

        }

        table_output += '</table>';

        document.getElementById('excel_data').innerHTML = table_output;
      }

      excel_file.value = '';

    }

  });
</script>

Upvotes: 1

Views: 2801

Answers (1)

ProDec
ProDec

Reputation: 5410

Replace your JS library with latest version

https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js

Have a try.

I think your running version's default behavior does not treat the file with single column as CSV. Try multiple columns with comma separator, it works.

Upvotes: 1

Related Questions