Hey there! Great job getting those dynamic defined names (piece-0, piece-1, etc.) set up correctly. Adding the SUM formula to your row is super straightforward once you know the trick.

Here's how you can do it:

1. Collect your defined names dynamically
   First, generate a list of all your piece-* names based on the index range you used to create them. For example, if you created names from piece-0 to piece-3, you can build this list with:

   # Adjust the range (0..3) to match your actual number of pieces
   piece_names = (0..3).map { |i| "piece-#{i}" }
   

2. Build the SUM formula
   Use Excel's SUM function and pass in all your defined names separated by commas. Caxlsx handles formulas natively as long as they start with =:

   sum_formula = "=SUM(#{piece_names.join(', ')})"
   

3. Insert the formula into your row
   Replace your placeholder with the generated formula in the add_row call:

   she.add_row ["Total","",""] + [sum_formula]
   

Example with dynamic index count #

If you're using each_with_index on an array (say my_items), you can tie the name list directly to that array's length to avoid hardcoding the range:

my_items = ["item1", "item2", "item3", "item4"] # Your original array
piece_names = my_items.each_with_index.map { |_, i| "piece-#{i}" }
sum_formula = "=SUM(#{piece_names.join(', ')})"

she.add_row ["Total","",""] + [sum_formula]

This will generate a formula like =SUM(piece-0, piece-1, piece-2, piece-3) which Excel will resolve using your pre-defined names correctly. Just double-check that your defined names are pointing to the right cells (you mentioned they're already correct, so this should be smooth sailing!).

内容的提问来源于stack exchange，提问作者Marcos R. Guevara