如何向已有的Excel Macro-Enabled工作簿模板导出值并保留宏与直方图

你使用的Excel::Writer::XLSX模块默认生成的是无宏的.xlsx格式文件，你强行指定后缀为.xlsm时，文件内部并没有宏相关的结构，所以Excel会提示格式/扩展名无效。且该模块本身不支持直接编辑已有的Excel文件，你原来的逻辑是新建了一个空白文件写入数值，而不是在你预设了直方图和宏的模板基础上修改，自然会丢失原有模板的功能。

方案1：使用Win32::OLE调用Excel本地接口操作现有模板（仅Windows环境，推荐，不会丢失原有模板的任何配置） #

这个方案直接操作你已有的.xlsm模板文件，写入数值后直接保存，宏和直方图配置都会完整保留，示例代码如下：

use strict;
use warnings;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

# 配置路径
my $template_fn = '你的模板文件路径.xlsm';
my $output_fn = 'result.xlsm';
my $input_fn = 'input.txt.gz';

# 启动Excel进程
my $excel = Win32::OLE->new('Excel.Application', 'Quit') or die "无法启动Excel: $!";
$excel->{DisplayAlerts} = 0; # 关闭弹窗提示

# 打开模板文件
my $workbook = $excel->Workbooks->Open($template_fn) or die "无法打开模板: $!";
# 选择要写入数值的工作表，这里用第一个工作表，也可以按名称选比如Worksheets('数据页')
my $worksheet = $workbook->Worksheets(1);

# 读取解压后的txt数据
my $zip = IO::Uncompress::Gunzip->new( $input_fn )
  or die "gunzip failed: $GunzipError\n";

my $col = 0;
my $row = 1;
while (!$zip->eof()) {
    my $line = $zip->getline();
    chomp($line);
    next if $line !~ /\S/;  # 跳过空行
    # 写入单元格，注意Win32::OLE的行号列号从1开始，如果你原来的逻辑是从第二行开始写这里对应调整
    $worksheet->Cells($row+1, $col+1)->{Value} = $line;
    $row++;
}

# 另存为新的xlsm文件，参数52对应xlOpenXMLWorkbookMacroEnabled格式
$workbook->SaveAs($output_fn, 52);
$workbook->Close();
$excel->Quit();

方案2：提取模板的VBA项目嵌入到新生成的文件中（跨平台可用） #

Excel::Writer::XLSX支持嵌入提前导出的VBA项目文件，你可以先从你的.xlsm模板里把VBA项目导出为vbaProject.bin文件，然后用如下方式生成合法的xlsm文件：

1. 打开你的xlsm模板，按Alt+F11打开VBA编辑器，右键点击你的VBA项目，选择导出文件，保存为vbaProject.bin
2. 修改你的代码如下：

use strict;
use warnings;
use Excel::Writer::XLSX;
use IO::Uncompress::Gunzip qw(gunzip $GunzipError);

{
    my $output_fn = 'result.xlsm';
    my $input_fn = 'input.txt.gz';
    my $workbook = Excel::Writer::XLSX->new( $output_fn );
    # 嵌入VBA项目，这一步后生成的就是合法的xlsm格式
    $workbook->add_vba_project( 'vbaProject.bin' );
    # 这里要注意：你需要自己重建模板里的工作表结构、直方图配置，因为这个方案还是新建文件，只是加了宏
    my $worksheet = $workbook->add_worksheet();
    my $zip = IO::Uncompress::Gunzip->new( $input_fn )
      or die "gunzip failed: $GunzipError\n";
 
    my $col = 0;
    my $row = 1;
    while (!$zip->eof()) {
        my $line = $zip->getline();
        chomp($line);
        next if $line !~ /\S/;  # skip empty lines
        my $value = $line;
        $worksheet->write( $row, $col, $value );
        $row++;
    }
    $workbook->close();
}

注意：方案2需要你自行在代码中重建模板里的直方图、单元格格式等配置，不会自动继承原有模板的内容，只适合宏逻辑简单、模板结构不复杂的场景。

• 如果使用方案1，需要确保你的Windows系统已经安装了Excel，且宏安全级别设置允许运行脚本调用的宏
• 不要直接修改文件扩展名来变更Excel格式，必须通过对应接口生成符合格式规范的文件

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