perl - 在 Perl 的 Spreadsheet::WriteExcel 模块中使用 Repeat_formula 时，为什么我的 2D 范围没有被 3D 范围替换？

Question

我需要通过 Perl 脚本创建动态 Excel 公式。

我存储的公式包含虚假范围

my $stored_formula = $worksheet->store_formula('=MEDIAN(A1:A2)');

在脚本中，我计算新的范围

$formula_range = $mm_arr[$mmindx-1] . "!" . 
                 num2col(2+$form_off) . ((($serv-1)*5)+5) . ":" .
                 num2col((2+31+$form_off)) . ((($serv-1)*5)+5);

其中@mm_arr是一个数组("Jan", "Feb", ...)num2col 是我用来将列号转换为字母的函数。

下面的行是重复公式

$worksheet->repeat_formula(
     (($serv-1)*5)+4, 
     1+$mmindx, 
     $stored_formula, 
     undef, 
     'A1:A2', $formula_range
);

我期望得到:

=median(Feb!K3:AH3)

但我得到:

=median(K3:AH3)

所以查找/替换在某种程度上起作用，但我无法真正确定如何起作用!

我做错了什么？

Answer 1

首先，一些建议:

表达式中的括号太多，再加上我们看不到其定义的变量和函数，使您的代码难以直观地解析。您也没有发布一个小的、独立的脚本来显示问题，这意味着任何试图帮助您的人都必须设置一个测试脚本。让别人轻松一点。

例如，定义 $formula_range 的行可以重写如下:

my $formula_range = sprintf('%s!%s%d:%s%d',
    $mm_arr[$mmindx - 1],
    num2col(2 + $form_off),
    5 + 5 * ($serv - 1),
    num2col(2 + 31 + $form_off),
    5 + 5 * ($serv - 1),
);

请记住，您可以使用 Spreadsheet::WriteExcel::Utility 提供的功能在单元格符号和行/列索引之间进行转换。

问题似乎是，如果存储的公式不是采用 SheetName!Range 形式，则替换会在替换中删除工作表名称，只放入范围。

其原因似乎与 Spreadsheet::WriteExcel::Formula 中的解析器设置有关。普通范围被解析为 range2d 标记，而带有工作表引用的范围则被解析为 range3d 标记。随后，在 repeat_formula 中，替换是在标记上完成的。据我所知，range2d token 看起来像 '_ref2dA1:A10'。因此，'_ref2dA1:A2' 在重复公式中转换为 '_ref2dFeb!A1:10'。但是，当它传递给 Spreadsheet::WriteExcel::Formula::parse_tokens 时，代码仍然根据前缀将其分类为 range2d 标记。我收集最终调用 $parse_str .= $self->_convert_ref2d($token, $class); 然后将其返回为 '_ref2dA1:A10。我不确定这是否可以归类为错误，但从用户的角度来看，这绝对是出乎意料的。

因此，解决方案是输入保证存在的工作表的名称。

这是一个测试脚本:

#!/usr/bin/env perl

use strict; use warnings;
use Spreadsheet::WriteExcel;
use Const::Fast;

const my @MONTHS => qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
const my $WORKBOOK => 'test.xls';

my $book = Spreadsheet::WriteExcel->new($WORKBOOK)
    or die "Cannot open '$WORKBOOK': $!";

my %sheets = map {$_ => $book->add_worksheet($_)} Summary => @MONTHS;

for my $m (@MONTHS) {
    for my $n (1 .. 10) {
        $sheets{ $m }->write_number($n - 1, 0, $n);
    }
}

my $formula = $sheets{Summary}->store_formula('=MEDIAN(Summary!A1:A2)');

for my $n (0 .. 1) {
    my $replacement = sprintf(q{'%s'!A1:A10}, $MONTHS[$n]);

    $sheets{Summary}->repeat_formula($n, 0, $formula, undef,
        'Summary!A1:A2' => $replacement
    );
}

$book->close
    or die "Error closing '$WORKBOOK': $!";

这是一个屏幕截图: