perl excel :: writer :: xlsx正确的excel函数移动区域的语法

Question

我使用perl模块excel :: writer :: xlsx。如果我在数据检查下直接输入Excel中的功能，则可以正常运行。Perl脚本的修改表格不起作用。

我的excel文件中有一些床单。在"数据验证" ->"列表"下，我将其用作"源"：

=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)

它有效。

如果我在perl脚本中以修改形式使用此功能来创建一个功能的Excel文件，以便我无法打开Excel文件。修改形式：

=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)

代码剪切：

$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",
    }
);

如果创建了Excel文件，我将使用Excel打开并获取错误：找到Excel的不可读内容...

Answer 1

来自excel :: writer :: xlsx docs，但在此处重复以清楚：

non US Excel函数和语法

Excel以美国英语版本的格式存储公式，而不论最终用户版本的Excel的语言或语言环境如何。因此，使用Excel :: Writer编写的所有公式函数名称:: XLSX必须用英语：

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

此外，公式必须与美国样式的分隔符/范围操作员一起编写，即逗号(而不是半彩色(。因此，具有多个值的公式应如下写：

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

如果您有非英语版本的Excel版本，则可以使用以下多语言公式转换器(http://en.excel-translator.de/language/(来帮助您转换公式。它还可以用逗号替换半彩。

使用上面列出的翻译器应为：

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)

Answer 2

这不是解决问题的解决方案。我简化了问题。该脚本创建一个可以在没有错误的情况下读取的文件。如果使用了评论的源条目，Excel报告了错误。但是，可以将评论的源条目直接交换为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__