Numeric in sheet names and define_name

[Marvin Budd]

Here is my program:

#!/usr/bin/perl
use Excel::Writer::XLSX;
use Excel::Writer::XLSX::Utility;

my $wb  = new Excel::Writer::XLSX('mywb.xlsx');
my $sheetname = '110910';
my $ws  = $wb->add_worksheet("$sheetname");
my $wsrow = 0;
$ws->write( $wsrow, 3, [ 'Some Factor' ] );
$ws->write( $wsrow, 4, [ 1.03 ] );
$wb->define_name('Some_Factor', "=$sheetname!" . AbsCell( $wsrow++, 4 ));
$ws->write( $wsrow++,0, ['Fund', 'ORGN', 'Budget' ] );
$ws->write( $wsrow++,0,[ '11', '4100', '=300*Some_Factor'  ] );
$ws->write( $wsrow++,0,[ '11', '4105', '=1000*Some_Factor' ] );

$wb->close;

# Change from relative reference (BI31) to absolute ($BI$31)
sub AbsCell {
    my ( $row, $col ) = @_;
    my $rowcol = xl_rowcol_to_cell( $row, $col );
    my $numcol = 1;
    if ($rowcol =~ /[[:alpha:]]*(\d*)/) {
       $numcol = index($rowcol, $1);
    }
    return '$' . substr($rowcol,0,$numcol) . '$' . substr($rowcol,$numcol);
}

If the sheetname is something alpha, like 'ops' it works. However, as shown above the define_name is not working, and references to it get an error. Also, when opening the workbook I have to CTRL_SHIFT_F9 to get it calculated. Am I coding this wrong? Is there a way to quote the sheet name so it is not considered a number?

Marvin Budd

[jmcnamara]

On Wednesday, 2 March 2016 13:03:02 UTC, Marvin Budd wrote:

Here is my program:
...

$wb->define_name('Some_Factor', "=$sheetname!" . AbsCell( $wsrow++, 4 ));

...

 Hi,

The program works okay for me with the latest version of the Excel::Writer::XLSX, 0.88, and Excel 13, so something may have been fixed in one of those versions.

Anyway, the issue is that certain worksheet names in Excel formulas need to be single quoted, for example worksheet names with spaces. If you examine the defined name from your program in Excel (after a CTRL SHIFT F9 refresh) you will see that 110910 is also single quoted. So something like this should fix the issue:

    $wb->define_name('Some_Factor', "='$sheetname'!" . AbsCell( $wsrow++, 4 ));

John.
--

[Marvin Budd]

Thanks! Eventually got that working.

[jmcnamara]

On Wednesday, 2 March 2016 20:18:45 UTC, Marvin Budd wrote:

Thanks! Eventually got that working.

Cool.

One minor thing that I forgot to mention, the AbsCell() function you are using looks like xl_rowcol_to_cell() (which it uses) when you use it with the additional "absolute" parameters:

    $str = xl_rowcol_to_cell(0, 0);       # A1
    $str = xl_rowcol_to_cell(0, 0, 1, 1); # $A$1

http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX-0.47/lib/Excel/Writer/XLSX/Utility.pm#xl_rowcol_to_cell%28$row,_$col,_$row_absolute,_$col_absolute%29

John

[Marvin Budd]

Great! I was hoping I had missed that somewhere.