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 ...

Why have you altered the string when you use it in Perl? What happens when you use the original version? — Dave Cross, May 17 '19 at 13:58
`$E` inside double quotes in Perl interpolates into the value of the `$E` variable. Use `q()` instead of double quotes to prevent interpolation. — choroba, May 17 '19 at 14:05
If I use the same code like in Excel I get the same error. I am changing the string because i found an example on the internet. — Thomas Kühnert, May 17 '19 at 14:13
I've tested q(), the error is the same on both strings: $validate_source = q(=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)); and $validate_source = q(=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)); — Thomas Kühnert, May 17 '19 at 15:06
Is there a possibility that it doesn't like the German Excel formula? — simbabque, May 17 '19 at 15:39

score 2 · Answer 1 · answered Mar 01 '20 at 14:26

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)

score 1 · Answer 2 · answered May 20 '19 at 10:09

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__

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

2 Answers2