perl - 如何让 Perl 的 Spreadsheet::WriteExcel 使用 VLOOKUP 创建公式？

Question

我在 Spreadsheet::WriteExcel 方面遇到困难以及使用 VLOOKUP 的公式。以下测试脚本使用一些数据填充工作表并尝试创建 VLOOKUP 公式。当我打开生成的 Excel 文件时，公式结果显示为 #VALUE!。如果我手动编辑任何包含公式的单元格(按 F2，然后只需 ENTER 而不更改任何内容)，我可以让 Excel 正确计算公式。知道出了什么问题吗？

无论如何，如果我在 OpenOffice 中打开同一个文件，公式就可以正常工作。

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $wb = Spreadsheet::WriteExcel->new('foo.xls');
my $ws = $wb->add_worksheet;

for my $r (0 .. 9){
    for my $c (0 .. 4){
        $ws->write($r, $c, $r * 10 + $c);
    }
    $ws->write($r, 10, $r * 10);
    my $formula = sprintf('=VLOOKUP(K%s, A1:B10, 2, FALSE)', $r + 1);
    $ws->write( $r, 11, $formula );
    # $ws->write_formula( $r, 11, $formula ); # Does not help either.
}

版本信息:

• Excel 2007 SP2。
• Spreadsheet::WriteExcel:尝试了 2.25 和 2.37。

Answer 1

我是 Spreadsheet::WriteExcel 的作者。

这是 WriteExcel 中的公式解析器和某些公式类型的已知错误。您可以使用 store_formula() 和 repeat_formula() 来解决这个问题，如下所示:

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my $wb = Spreadsheet::WriteExcel->new('foo.xls');
my $ws = $wb->add_worksheet;

my $formula = $ws->store_formula('=VLOOKUP(K1, A1:B10, 2, FALSE)');

# Workaround for VLOOKUP bug in WriteExcel.
@$formula = map {s/_ref2d/_ref2dV/;$_} @$formula;

for my $r (0 .. 9){
    for my $c (0 .. 4){
        $ws->write($r, $c, $r * 10 + $c);
    }
    $ws->write($r, 10, $r * 10);

    $ws->repeat_formula( $r, 11, $formula, undef, qr/^K1$/, 'K' . ($r +1) );
}