excel - 如何使用perl将Excel单元格值在公式栏中显示为 "1/1/1901"并在相应单元格中显示与 "1901-01-01"相同

Question

我们需要生成 Excel 文件，单元格值应在公式栏中显示为 mm/dd/yyyy 格式 (1/1/1901)，并显示与 yyyy-mm-dd 格式相同 (1901- 01-01) 在相应的单元格中。有人可以帮助一下如何使用 Perl 来实现这一点吗？请引用下面的屏幕截图，它将显示我们如何需要。

我们正在使用模块 Excel::Writer::XLSX 并尝试了以下代码，但它在公式栏和单元格中显示相同的格式。

        if ($cell =~ qr[^(\d{4})/(\d{1,2})/(\d{1,2})$])
        {
        my $format1 = $workbook->add_format(num_format => 'yyyy-mm-dd');
        my $date = sprintf "%02d/%02d/%04d", $2, $3, $1;
        $worksheet->write_date_time( $iR, $iC, ${date}, $format1);
        }

屏幕截图

Answer 1

来自文档:

The most direct way is to convert your dates to the ISO8601 yyyy-mm-ddThh:mm:ss.sss date format and use the write_date_time() worksheet method:

演示:

use strict;
use warnings;

use Excel::Writer::XLSX;

my $wb = Excel::Writer::XLSX->new( 'perl.xlsx' );
my $ws = $wb->add_worksheet();

$ws->set_column('A:B', 25);

my $date_format = $wb->add_format(num_format => 'yyyy-mm-dd');

my($d,$m,$y) = (20,1,1901);

#your current
my $current = sprintf "%02d/%02d/%04d", $d,$m,$y;
$ws->write('A1', 'using: %02d/%02d/%04d');
$ws->write_date_time( 'B1', $current, $date_format );

#you want
my $wanted = sprintf "%04d-%02d-%02dT", $y,$m,$d;
$ws->write('A2', 'using: %04d-%02d-%02dT');
$ws->write_date_time( 'B2', $wanted, $date_format );

在 Excel 中生成