从Excel工作表与Perl Win32 :: OLE获取超链接列表 [英] Getting list of hyperlinks from an Excel worksheet with Perl Win32::OLE

问题描述

我想在Excel电子表格中更改一堆超链接的路径。搜索Google后，我发现了添加解决方案 >超链接到电子表格，但不能更改他们。微软展示了如何使用VBA 此处关闭的内容。

由于我想编辑文档中的每个超链接，我不知道如何解决的关键步骤是：

1. 获取Perl中超链接对象的列表




2. 将其地址提取1乘以1， / p>




3. 运行正则表达式以使路径更改




4. 将更新的路径存储在超链接 - >对象并重复

我是新来使用OLE，并且（1）。这是我迄今为止所尝试的：

 ＃！perl 
 use strict; 
使用警告; 
使用5.014; 
使用OLE; 
使用Win32 :: OLE :: ConstMicrosoft Excel; 
 
我的$ file_name ='C：\path\to\spreadsheet.xlsx'; 
我的$ excel = Win32 :: OLE-> new（'Excel.Application'，sub {$ _ [0]  - > Quit;}）; 
 $ excel-> {Visible} = 1; 
 my $ workbook = $ excel-> Workbooks-> Open（$ file_name）; 
我的$ sheet = $ workbook->工作表（'Sheet 1'）; 
 foreach我的$链接（$ sheet->超链接）{
说$ link->地址; 
} 
  

但这会给代码错误：

Win32 :: OLE（0.1709）：GetOleEnumObject（）不是Win32 :: OLE :: Enum对象在C：/ Dwimperl / perl / vendor / lib / Win32 / OLE / Lite.pm行167.
无法在script.pl第14行调用方法超链接，没有包或对象引用。

它正在选择正确的工作表，所以我不知道为什么它抱怨对象引用。我尝试了几种变体（在超链接上添加{}，删除'in'，尝试将其存储为列表，作为散列，以及作为哈希的引用）任何人都可以给我一些指针？谢谢！

解决方案

首先，你应该设置 $ Win32 :: OLE :: Warn = 3 所以你的脚本会在发生错误的时刻呱呱呱呱。其次，我知道您不能在旧版本的Excel中按名称选择工作表，尽管我不知道最新版本中的内容是什么样的。最后，我想你会发现使用 Win32 :: OLE： ：枚举。

这是一个例子：

 ＃/ usr / bin / env perl 
 
使用5.014; 
使用警告;使用严格
 
使用鲤鱼qw（croak）; 
使用Path :: Class; 
使用Try :: Tiny; 
使用Win32 :: OLE; 
使用Win32 :: OLE :: Const'Microsoft Excel'; 
使用Win32 :: OLE :: Enum; 
 $ Win32 :: OLE :: Warn = 3; 
 
我的$ book_file =文件（$ ENV {TEMP}，'test.xls'）; 
说$ book_file; 
 
我的$ excel = Win32 :: OLE-> new（'Excel.Application'，sub {$ _ [0]  - > Quit;}）; 
 $ excel-> {Visible} = 1; 
 
我的$ book = $ excel->工作簿 - >打开（$ book_file）; 
我的$ sheet = get_sheet（$ book，超链接表）; 
我的$ links = $ sheet->超链接; 
我的$ it = Win32 :: OLE :: Enum-> new（$ links）; 
 
 while（defined（my $ link = $ it-> Next））{
 my $ address = $ link-> {Address}; 
说$地址; 
 if（$ address =〜s / example / not.example /）{
 $ link-> {Address} = $ address; 
 $ link-> {TextToDisplay} =更改为$ address; 
} 
} 
 
 $ book->保存; 
 $ book->关闭; 
 $ excel->退出; 
 
 sub get_sheet {
我的（$ book，$ wanted_sheet）= @_; 
 
我的$ sheets = $ book->工作表; 
 my $ it = Win32 :: OLE :: Enum-> new（$ sheets）; 
 
 while（defined（my $ sheet = $ it-> Next））{
 my $ name = $ sheet-> {Name}; 
说$ name; 
 if（$ name eq $ wanted_sheet）{
 return $ sheet; 
} 
} 
 
 croak找不到'$ wanted_sheet'; 
} 
  

该工作簿包含一个名称为的工作表超链接表。该表格中的单元格 A1 包含 http://example.com 和 A2 包含 http://stackoverflow.com 。

I want to change the path for a bunch of hyperlinks in an Excel spreadsheet. After searching Google, I came across a solutions to the problem of adding hyperlinks to spreadsheets, but not changing them. Microsoft showed how to something close with VBA here.

Since I want to edit every single hyperlink in my document, the key steps that I don't know how to solve are:

1. Get a list of hyperlink objects in Perl

2. Extract their addresses 1 by 1 and

3. Run a regular expression to make the path change

4. Store the updated path in the Hyperlink->object and repeat

I am new to using the OLE and am getting tripped up on (1). Here is what I have tried so far:

#!perl
use strict;
use warnings;
use 5.014;
use OLE;
use Win32::OLE::Const "Microsoft Excel";

my $file_name = 'C:\path\to\spreadsheet.xlsx';
my $excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;});
$excel->{Visible} = 1;
my $workbook = $excel->Workbooks->Open($file_name);
my $sheet = $workbook->Worksheets('Sheet 1');
foreach my $link (in $sheet->Hyperlinks ) {
say $link->Address;
}

But this gives code the error:

Win32::OLE(0.1709): GetOleEnumObject() Not a Win32::OLE::Enum object at C:/Dwimperl/perl/vendor/lib/Win32/OLE/Lite.pm line 167. Can't call method "Hyperlinks" without a package or object reference at at script.pl line 14.

It's selecting the right worksheet, so I am not sure why it complains about an object reference. I tried several variations (Adding {} around Hyperlinks, removing the 'in', trying to store it as a list, as a hash, and as a reference to a hash) Can anyone give me some pointers? Thanks!

解决方案

First, you should set $Win32::OLE::Warn=3 so your script will croak the moment something goes wrong. Second, I know you can't select sheets by name in older versions of Excel, although I do not know what things are like in the newest versions. Finally, I think you'll find it easier to use Win32::OLE::Enum.

Here is an example:

#!/usr/bin/env perl

use 5.014;
use warnings; use strict;

use Carp qw( croak );
use Path::Class;
use Try::Tiny;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Enum;
$Win32::OLE::Warn = 3;

my $book_file = file($ENV{TEMP}, 'test.xls');
say $book_file;

my $excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;});
$excel->{Visible} = 1;

my $book = $excel->Workbooks->Open("$book_file");
my $sheet = get_sheet($book, 'Sheet with Hyperlinks');
my $links = $sheet->Hyperlinks;
my $it = Win32::OLE::Enum->new($links);

while (defined(my $link = $it->Next)) {
    my $address = $link->{Address};
    say $address;
    if ($address =~ s/example/not.example/) {
        $link->{Address} = $address;
        $link->{TextToDisplay} = "Changed to $address";
    }
}

$book->Save;
$book->Close;
$excel->Quit;

sub get_sheet {
    my ($book, $wanted_sheet) = @_;

    my $sheets = $book->Worksheets;
    my $it = Win32::OLE::Enum->new($sheets);

    while (defined(my $sheet = $it->Next)) {
        my $name = $sheet->{Name};
        say $name;
        if ($name eq $wanted_sheet) {
            return $sheet;
        }
    }

    croak "Could not find '$wanted_sheet'";
}

The workbook did contain a sheet with the name "Sheet with Hyperlinks". Cell A1 in that sheet contained http://example.com and A2 contained http://stackoverflow.com.

这篇关于从Excel工作表与Perl Win32 :: OLE获取超链接列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！