How to use Excel::Writer::XLSX to create a table where inserting a row copies a column's formula?

Question

So, in Excel, I can manually create create the following (I'm having it display the formula in column C):

Then, I can select A:1 through C3, choose Insert -> Table, and press OK on the Create Table dialog to make my data into a table, unchecking the Filter Button box:

Then, when I right-click on row 3 and select Insert, I get a new row 3, with column C's formula copied correctly.

My problem is I can't seem to generate an Excel file with Perl using Excel::Writer::XLSX that can create a table where inserting a row will cause the formula to be copied. It ends up being blank, like this:

Below is my code. Any insights would be much appreciated.

#!/usr/bin/env perl

use strict;
use warnings;

use Excel::Writer::XLSX;

my $filePath = shift;
my $workbook = Excel::Writer::XLSX->new( $filePath );
my $worksheet = $workbook->add_worksheet( 'Metadata' );

my @data = ( [1,2,"=SUM(A2:B2)"], [3,4,"=SUM(A3:B3)"], [5,6,"=SUM(A4:B4)"] );

my $rowCount = (scalar @data) + 1;

$worksheet->add_table( 
    "A1:C$rowCount", 
    {
        data => \@data,
        name => 'Table1',
        style => 'Table Style Medium 2',
        autofilter => 0,
        header_row => 1,
        columns => [
            { header => 'Value 1' },
            { header => 'Value 2' },
            { header => 'Sum' },
        ]
    }
);

$workbook->close();

Answer 1

Excel tables are so underused... it's good to see someone with an appreciation for what they have to offer, and kudos to the module author for adding table support.

A couple of things... if you want a table-based formula, declare it in the formula property of the table column. This will cause it to be copied to any new rows that are created.

{ header => 'Title', formula => '=1+2' }

Secondly, there may be a way to do this via a R1C1 syntax (Excel supports it, but I don't know if Excel::Writer::XLSX does), but I find when you are referencing other columns in the table, it's easiest to do it based on the table column rather than the cell reference -- this is one of big advantages of Excel tables over standard ranges.

formula => '=[@[Header A]]*[@[Header B]]'

The impacted code should look as follows:

my @data = ( [1,2], [3,4], [5,6] );

my $rowCount = (scalar @data) + 1;

$worksheet->add_table(
    "A1:C$rowCount",
    {
        data => \@data,
        name => 'Table1',
        style => 'Table Style Medium 2',
        autofilter => 0,
        header_row => 1,
        columns => [
            { header => 'Value 1' },
            { header => 'Value 2' },
            { header => 'Sum',
              formula => '=SUM([@[Value 1]]:[@[Value 2]])'
            },
        ]
    }
);

When you open the spreadsheet and add a row, I think you will get the behavior you desire.