0

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.

Hannes Z
  • 1
  • 2
  • That's quite a bold statement. `for` loops certainly work in node. You are probably just not using them correctly. And "doesn't work" means what? Ie what is the current behaviour, what is the expected behaviour. And what is the `product_list` you are using in your second loop? – derpirscher Nov 11 '22 at 14:01

1 Answers1

0

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

  }
Kaneki21
  • 1,323
  • 2
  • 4
  • 22
  • your code work a bit better but not in all and i don´t know why. my product is this: ``` 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] }); } ``` – Hannes Z Nov 14 '22 at 13:53
  • can you edit your question and add the above code – Kaneki21 Nov 14 '22 at 13:55
  • 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 an this Error: Sheet names cannot exceed 31 chars did you know the solution? I despair on that problem xD – Hannes Z Nov 14 '22 at 13:57
  • sure wait a second :D – Hannes Z Nov 14 '22 at 13:57
  • what do you mean by code not work in all ? where are you facing issue? – Kaneki21 Nov 14 '22 at 14:00
  • i have my source code updated :D – Hannes Z Nov 14 '22 at 14:00
  • i want a excel and for each product a sheet. i scrape my data from an website and this is the code in an axios. The axios work and i´ve tested in a Filesystem, but my excel don´t work – Hannes Z Nov 14 '22 at 14:02
  • in the Filesystem it create for each product an product_name.txt and output the data. I want exactly the same for this but as an xlsx data. Do you know what i mean? Sorry for my english, i´m trying my best. I am a german^^ – Hannes Z Nov 14 '22 at 14:05
  • I updated the `sheetname` part, if you are using this [package](https://www.npmjs.com/package/xlsx) then things should work. I think your datastructures are not correctly set, as I said in this comment `const workSheet = xlsx.utils.json_to_sheet(product); //replace with the correct json for product_list item, you want to write to the sheet` – Kaneki21 Nov 14 '22 at 14:05
  • i am using this package. I implementated on the top of the axios. but it doesn´t work the xlsx system i created (i want to create). Would it help you if i´m sending you the whole code for the overview? – Hannes Z Nov 14 '22 at 14:18
  • i´m updating my code again this is the whole code – Hannes Z Nov 14 '22 at 14:24
  • if i run this the filesystem will work but not the xlsx-system. are you from germany? can i write in german or should i write still in english? – Hannes Z Nov 14 '22 at 14:27
  • is it supposed to be `const workSheet = xlsx.utils.json_to_sheet(product[i])` ? – Kaneki21 Nov 14 '22 at 14:36
  • yes it was a edditing by stackoverflow i think – Hannes Z Nov 14 '22 at 14:41
  • or i type this by mistake, but in the sourcecode it is product[i] – Hannes Z Nov 14 '22 at 14:42
  • ah i missunderstand you sorry, – Hannes Z Nov 14 '22 at 14:44
  • I would suggest to debug all the datastructures properly, or for starter try creating excel with static jsons(some sample data), then use the products array you can refer [this](https://www.geeksforgeeks.org/how-to-read-and-write-excel-file-in-node-js/) – Kaneki21 Nov 14 '22 at 14:48
  • thats a great solution for the name of the sheet. Thanks a lot! – Hannes Z Nov 14 '22 at 14:55