for-loop in node.js (xlsx sheet create) doesn´t work

Question

i need support! My loop doensn´t work and i don´t know why :/

I want only one xlsx (ProductList.xlsx), but for each product a sheet. I tried it with for-loop but it doesn´t work.

  const product = [];

  //fill out product
  for (let i = 0; i < product_seller.length; i++){
    //use this if everything is needed
    product.push({
                  Seller: product_seller[i], 
                  ItemsRemain: items_remain[i], 
                  ProductPrice: product_price[i+1],
                  ProductCountry: product_country[i],
                  SellerCountry: seller_country[i],
                  ProductSales: product_sales[i]
                 });
  }
  //console.log(product);

  const convertJsonToExcel=()=>{
    const workBook = xlsx.utils.book_new();
    
    for(let i = 0; i < product_list.length; i++){
      const workSheet = xlsx.utils.json_to_sheet(product);
      xlsx.utils.book_append_sheet(workBook, workSheet, product_list[i]);
      //xlsx.write(workBook, {bookType:'xlsx', type:'buffer'});
      //xlsx.write(workBook, {bookType:'xlsx', type:'binary'});
    }

    xlsx.writeFile(workBook, 'ProductList.xlsx');
  }
  convertJsonToExcel();
  
 

this is in an axios and i´ve got a json like this:

[
  {
    Seller: 'SpielFilmMusik',
    ItemsRemain: '1',
    ProductPrice: '159,95 €',
    ProductCountry: 'Englisch',
    SellerCountry: 'Artikelstandort: Deutschland',
    ProductSales: '6K'
  },
  {
    Seller: 'SpielFilmMusik',
    ItemsRemain: '2',
    ProductPrice: '159,95 €',
    ProductCountry: 'Deutsch',
    SellerCountry: 'Artikelstandort: Deutschland',
    ProductSales: '6K'
  },
...
]

and i´ve got an Error: Sheet names cannot exceed 31 chars

product_list is a list with all products and for each product the axios scrape the seller, price, items remain etc.

Answer 1

In your code, it is using same json to create worksheet for each element in product_list

const convertJsonToExcel=()=>{
    const workBook = xlsx.utils.book_new();
    
    for(let i = 0; i < product_list.length; i++)
    {
        const workSheet = xlsx.utils.json_to_sheet(product); //replace with the correct json for product_list item, you want to write to the sheet
        xlsx.utils.book_append_sheet(workBook, workSheet, `product_no_${i}`);
        // xlsx.write(workBook, {bookType:'xlsx', type:'buffer'});  
        // xlsx.write(workBook, {bookType:'xlsx', type:'binary'}); 
    }


    xlsx.writeFile(workBook, 'ProductList.xlsx');

  }

Answer 2

I also encountered such a problem.
After testing, I found that it was the XLSX.utils.book_append_sheet code that caused the for loop to not work, but I couldn't understand why this happened.