如何将制表符分隔的键值对文本内容写入Excel表格？

输入文件格式 #

每行是制表符分隔的键值对，示例：

result_1:test_a	result_2:test_b	result_1_res:pass	result_2_res:fail	result_1_str=	result_2_str=err	result_1_t:	result_2_t:100	string: wrong

目标Excel格式 #

每行提取对应值，示例：

test_a test_b pass fail  err  100 wrong

现有代码 #

use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('out.xls');
my $worksheet = $workbook->add_worksheet();

$worksheet->write("A1","Table");
$worksheet->write("A2","result_1");
$worksheet->write("B2","result_2");
$worksheet->write("C2","result_1_res");
$worksheet->write("D2","result_2_res");
$worksheet->write("E2","result_1_str");
$worksheet->write("F2","result_2_str");
$worksheet->write("G2","result_1_t");
$worksheet->write("H2","result_2_t");
$worksheet->write("I2","Comment");

open my $c, "<input.txt" or die "Cannot open:$!";

while (my $c_r = <$c>) {
    chomp;
    my @arr = split ("	", $c_r);
    chomp(my $test_name1 = $arr[0]);
    chomp(my $test_name2 = $arr[1]);
    chomp(my $res_name1 = $arr[2]);
    chomp(my $res_name2 = $arr[3]);
    chomp(my $sign_name1 = $arr[4]);
    chomp(my $sign_name2 = $arr[5]);
    chomp(my $time_name1 = $arr[6]);
    chomp(my $time_name2 = $arr[7]);
    chomp(my $comment = $arr[8]);
    ($l0,$l1) = split(":","test_name1");
    ($l2,$l3) = split(":","test_name2");
    ($l4,$l5) = split(":","res_name1");
    ($l6,$l7) = split(":","res_name2");
    ($l8,$l9) = split("=","sign_name1");
    ($l10,$l11) = split("=","sign_name2");
    ($l12,$l13) = split(":","time_name1");
    ($l14,$l15) = split(":","time_name2");
    ($l16,$l17) = split(":","comment");


        }


$workbook->close;

完善后的代码 #

use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new('out.xls');
my $worksheet = $workbook->add_worksheet();

# 写入表头
$worksheet->write("A1", "Table");
$worksheet->write("A2", "result_1");
$worksheet->write("B2", "result_2");
$worksheet->write("C2", "result_1_res");
$worksheet->write("D2", "result_2_res");
$worksheet->write("E2", "result_1_str");
$worksheet->write("F2", "result_2_str");
$worksheet->write("G2", "result_1_t");
$worksheet->write("H2", "result_2_t");
$worksheet->write("I2", "Comment");

open my $c, "<input.txt" or die "Cannot open:$!";

my $row = 3; # 从第3行开始写入数据

while (my $c_r = <$c>) {
    chomp $c_r; # 移除行尾换行符
    my @arr = split(/\t/, $c_r); # 按制表符分割每行内容

    # 提取各字段的值，限制分割次数避免值内包含分隔符
    my (undef, $result1) = split(/:/, $arr[0], 2);
    my (undef, $result2) = split(/:/, $arr[1], 2);
    my (undef, $res1) = split(/:/, $arr[2], 2);
    my (undef, $res2) = split(/:/, $arr[3], 2);
    my (undef, $str1) = split(/=/, $arr[4], 2);
    my (undef, $str2) = split(/=/, $arr[5], 2);
    my (undef, $t1) = split(/:/, $arr[6], 2);
    my (undef, $t2) = split(/:/, $arr[7], 2);
    my (undef, $comment) = split(/:/, $arr[8], 2);

    # 将提取的值写入对应列
    $worksheet->write($row, 0, $result1);
    $worksheet->write($row, 1, $result2);
    $worksheet->write($row, 2, $res1);
    $worksheet->write($row, 3, $res2);
    $worksheet->write($row, 4, $str1);
    $worksheet->write($row, 5, $str2);
    $worksheet->write($row, 6, $t1);
    $worksheet->write($row, 7, $t2);
    $worksheet->write($row, 8, $comment);

    $row++; # 行号自增处理下一行
}

close $c; # 关闭文件句柄
$workbook->close;

关键修改点 #

• 修复split语句错误：原代码将变量名作为字符串传入，改为使用实际变量（如$arr[0]而非"test_name1"）。
• 修正chomp用法：明确指定操作变量$c_r，避免依赖默认变量$_导致的错误。
• 添加行号跟踪：用$row变量控制数据写入的Excel行位，确保每行数据对应正确位置。
• 限制分割次数：split第三个参数设为2，防止值中包含冒号/等号时提取失败。
• 规范资源释放：手动关闭文件句柄，养成良好编程习惯。

内容的提问来源于stack exchange，提问作者kcv9712