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

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:

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

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

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 >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 >';

        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>

CodePudding user response：

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.