Perl Excel::Writer::XLSX correct syntax for the Excel function Move Area.function

Question

I use the Perl module Excel::Writer::XLSX. If I enter the function directly in excel under data check it works. The modified form for the Perl script does not work.

I have some sheets in my Excel file. Under 'data validation' -> 'list', I use this as 'source':

=BEREICH.VERSCHIEBEN(Boden_Subtyp!$E:$G;1;VERGLEICH(Profil!$G$2;Boden_Subtyp!$E$1:$G$1;0)-1;ANZAHL2(INDEX(Boden_Subtyp!$E:$G;;VERGLEICH(Profil!$G$2;Boden_Subtyp!$E$1:$G$1;0)));1)

It works.

If I use this in a modified form in my Perl script to create an Excel file with this function so I can't open the Excel file. The modified form:

=BEREICH.VERSCHIEBEN('Boden_Subtyp'!$E:$G;1;VERGLEICH('Profil'!$G$2;'Boden_Subtyp'!$E$1:$G$1;0)-1;ANZAHL2(INDEX('Boden_Subtyp'!$E:$G;;VERGLEICH('Profil'!$G$2;'Boden_Subtyp'!$E$1:$G$1;0)));1)

Code snipped:

$validate_source = "=BEREICH.VERSCHIEBEN('Boden_Subtyp'!$E:$G;1;VERGLEICH('Profil'!$G$2;'Boden_Subtyp'!$E$1:$G$1;0)-1;ANZAHL2(INDEX('Boden_Subtyp'!$E:$G;;VERGLEICH('Profil'!$G$2;'Boden_Subtyp'!$E$1:$G$1;0)));1)";
$profil_zugriffshash -> data_validation(
    $iii,$spaltenzaehler,
    {
        validate => 'list',
        source   => "$validate_source",
    }
);

If the Excel file is created I'll open it with Excel and get the error: excel's unreadable content was found ...

Answer 1

That's not the solution to the problem. I simplify the problem. The script creates a file that Excel can read without errors. If the commented source entry is used, Excel reports an error. However, the commented source entry can be directly exchanged for the uncommented source entry in Excel.

#!/usr/bin/perl -w
use diagnostics;
use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( '/home/nutzer/test_bereich_verschieben.xlsx' );
my $home_hash = $workbook->add_worksheet('Home');
$home_hash -> write(0, 0, 'range_val');
$home_hash -> data_validation(
    1,0,
    {
        validate => 'list',
        source   => 'Horizont!$C$5:$C$6',       # list: 2 and 3
#       source   => '=BEREICH.VERSCHIEBEN(Horizont!$C:$E;3;0;3;1)', # list: 1 and 2 and 3
    }
);

my $horizont_hash = $workbook->add_worksheet('Horizont');
$horizont_hash -> write(3, 2, '1');
$horizont_hash -> write(4, 2, '2');
$horizont_hash -> write(5, 2, '3');

$workbook->close;
exit;   
__END__

Answer 2

From the Excel::Writer::XLSX docs but repeated here for clarity:

Non US Excel functions and syntax

Excel stores formulas in the format of the US English version, regardless of the language or locale of the end-user's version of Excel. Therefore all formula function names written using Excel::Writer::XLSX must be in English:

    worksheet->write_formula('A1', '=SUM(1, 2, 3)');   # OK
    worksheet->write_formula('A2', '=SOMME(1, 2, 3)'); # French. Error on load.

Also, formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:

    worksheet->write_formula('A1', '=SUM(1, 2, 3)'); # OK
    worksheet->write_formula('A2', '=SUM(1; 2; 3)'); # Semi-colon. Error on load.

If you have a non-English version of Excel you can use the following multi-lingual Formula Translator (http://en.excel-translator.de/language/) to help you convert the formula. It can also replace semi-colons with commas.

Using the translator listed above the formula should be:

OFFSET('Boden_Subtyp'!$E:$G,1,MATCH('Profil'!$G$2,'Boden_Subtyp'!$E$1:$G$1,0)-1,COUNTA(INDEX('Boden_Subtyp'!$E:$G,,MATCH('Profil'!$G$2,'Boden_Subtyp'!$E$1:$G$1,0))),1)