[XLSX-Renderer] Nested FOR loops

[unimatrix27]

I have data that is grouped by a category so I want to fill a sheet using blocks of data (one block for each category) which would be like nested FOR loop, where the first FOR loop iterates through all cagegories and then within each of those, the inner for loop iterates through all the array entries. Is that possible?

I also want sum rows for each category

What would be even nicer if I could use the excel data grouping function to create those +/- buttons where i can collapse the details and just see the sum of each block.

[Siemienik]

Hi @unimatrix27

Yes, it is possible in on of two variants:

1. Nested loops in one worksheet:

#! FOR_EACH group groups
#! FOR_EACH item group.items
#! END_LOOP item
#! SUM item
#! END_LOOP group

#! SUM item insert sum formula for the column, limited by rows range when the loop of item was rendered

2. Nested loops through worksheets (one group per one worksheet)

#! FOR_EACH group groups
#! FOR_EACH item group.items
#! END_LOOP item
#! SUM item
#! WS_NAME group.name
#! FINISH group.__last

In that case, using #! WS_NAME group.name is required because it set a unique name for the worksheet.

• Did you try to use that?
• Maybe, want you to upload your template, data, and expected result to this discussion?
• Many examples about using XLSX-renderer is available in: https://github.com/Siemienik/XToolset/tree/master/packages/xlsx-renderer/tests/integration/data

---

What would be even nicer if I could use the excel data grouping function to create those +/- buttons where I can collapse the details and just see the sum of each block.

Could you please provide an example (xlsx file with that)?

[unimatrix27]

thank you so much for the extensive feedback. I managed to get the nested looping to run just fine. (I had to use the DELETE statement otherwise it would not render anything but the first group)

I attached in a zip file the template, the data and the result - and for demonstration I added this grouping to the result manually so you see what you mean. Its nice to have but not super important. I dont think I can achieve this automatically at the moment

regarding WS i found everything in example 12 ForEach Special

Thanks for this great tool!

example.zip

[Siemienik]

Thank you for your warm words:smile: I'm glad that you find a solution 🥇

Yes, It is possible to add grouping (outlines), I will describe it detaily soon. Shortly, here you have to possibilities:

1. Using postprocessing, I described it here: How to format as table? exceljs/exceljs#1672 (reply in thread)
2. Create and add custom cell type, e.g. OutlineCell #! OUTLINE item and updates the output. The PR will be welcomed here. Or if your company wants, I'm able to make that order ;) (consulting@siemienik.com)

XLSX Renderer using ExcelJS, here you have API for outlining: https://github.com/exceljs/exceljs#outline-levels

Additionally, I invite you to our community chat: https://gitter.im/Siemienik/community 😄