JS xlsx json_to_sheet creates unwanted empty cells

Question

I'm using this xlsx-js library to generate an Excel file from a list of json objects in a Vue3 application.

I need to have values in multiple cells, but it seems that it skips rows which have been already "touched".

Code:

  import * as XLSX from 'xlsx'; 

  var wb = XLSX.utils.book_new();

  var myws = XLSX.utils.json_to_sheet([
  {"name": "Peter"},
  {"name": "Adam"},
  {"age": "13"},
  {"age": "25"}
 ]);
 
 XLSX.utils.book_append_sheet(wb, myws, 'my_sheet1' );

Excel table output:

|name  |age |
|Peter |    |
|Adam  |    |
|      | 13 |
|      | 25 |

Desired output:

|name  |age |
|Peter | 13 |
|Adam  | 25 |

Can you help please ?

EDIT:

Solution by changing the order of json entries won't be that easy.

On input, I have array of objects like:

{
  "Name":"Peter",
  "Age": 24,
  "Hobbies": "golf;fishing;tennis;volleybal;hiking;",
  "Friends": "Adam;Steve;Stan;Lucy;Amie;Leo;Bruce;Eva;Patrick;"
}

Imagine that Peter has too many hobbies/friends, separated with semi colon.

In the Excel there is a limitation to max 32,767 characters in a single cell.

So in order to not exceed this number. Detailed sheet about Peter is created.

  var filename='exported_excel_file'+ '.xlsx';
  var wb = XLSX.utils.book_new();
  
  for (const person_obj of alldata){ // for each person in the main input array
    var person_obj_str = JSON.stringify(person_obj)
    var parsed_person_obj = JSON.parse(person_obj_str)
    var sheet_data = []

    // for each attribute of person object e.g. name, hobbies, etc.
    Object.keys(parsed_person_obj).forEach(mykey => {   
    
    // separate semi-colon attribute values into an array
      var value_array = parsed_person_obj[mykey].split(';') 

    // e.g. for each friend in person's friends
      for (var line of value_array) { 
        
        if (line != '') {
        // separate values into different entries with same key.
        // e.g. friends into {"friends": "Adam"}, {"friends": "Steve"}, etc.
        var entry =  {[mykey]: line} 
        sheet_data.push(entry)           
        }
      }          
  })
     
 var myws = XLSX.utils.json_to_sheet(sheet_data);
    var ran_num = Math.random(500)
    XLSX.utils.book_append_sheet(wb, myws, person_obj.name +'_sheet_' + ran_num );
  }

  XLSX.writeFile(wb,filename);   

},

So this code eventually has issue mentioned above.

If I would wanted to change the output to

  {"name": "Peter"},      
  {"age": "13"},
  {"Hobbies": "golf"},
  {"Hobbies": "fishing"},
  ...

  {"name": "Adam"},
  {"age": "25"},
  {"friends": "Peter"},
  {"friends": "Steve"},
  ...
  

It would has this issue anyway.. It looks like that when xlsx sees the same attribute/key name on the input, it will automatically proceed to the next row.

Is it possible to disable this automatic row setting?

Or is it possible to detect empty cells in each column starting from second row and end with first non-empty cell in that column? Then delete it so there won't be spaces?

If not, I would probably need to use many arrays and some round-robin functions.

Does anyone have a better solution for this issue?