Creating Hyper-links of Perl variables

I have 2 variables --

$pass = "Pass";
$fail = "Fail";

I want to create hyperlinks of these 2 variables and then output the hyperlinks to a sheet (using Spreadsheet::ParseExcel::SaveParser) like this -

$wr_worksheet1->AddCell($s_row, 9, $fail, $wr_format4);

so that when I click on the word "Fail" in this sheet, it should open the path "/var/tmp/Results/".

How can I do that?
sunny82Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wilcoxonCommented:
Do you need to use Spreadsheet::ParseExcel::SaveParser or can you use Spreadsheet::WriteExcel?  The latter has more functionality in some areas (not sure about specifically what you want to do).
0
sunny82Author Commented:
No I need to use Spreadsheet::ParseExcel::SaveParser as I need to update an existing sheet.

I just need to create hyperlinked values of these two variables $pass and $fail pointing to the link "/var/tmp/Results".

If you can tell me how I can make the hyperlinks in perl, then I can easily put the variables inside $worksheet->AddCell() method to put those into a sheet.

I just do not know how to create hyper-links for those two variables pointing to the location specified.

Is there a way to do it?
0
wilcoxonCommented:
You want a hyperlink to open a file?  Will the spreadsheet be opened only on the machine where that file exists?  If not, then it won't work.  If you want to be able to access the file /var/tmp/Results from other machines, it must be a URL or network share of some sort (otherwise the file will not exist when the link is clicked).

If you were using Spreadsheet::WriteExcel, it would just be:

$wr_worksheet1->write_url($s_row, 9, 'external:\var\tmp\Results', $fail, $wr_format4);

According to the Spreadsheet::ParseExcel docs, HyperLink support is still on the todo list.

I've always found the docs for Spreadsheet::ParseExcel::SaveParser to be lacking and it takes pieces from both ParseExcel and WriteExcel so I'm not sure if SaveParser has support or not.
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

sunny82Author Commented:
Here is how the excel sheet should look like. Once I click on Pass it should take me to the exact shared network drive where the results are stored as specified by the hyperlink and open the corresponding results sheet.




TestQuery.xls
0
sunny82Author Commented:
Can we add maybe VBMacro code inside "here" documents of this perl code to make all of the links hyperlink since SaveParser does not support it?

I have found an example in Win32::OLE about how to do it, but I dont know how to do it with Spreadsheet::ParseExcel::SaveParser. Any ideas on how to do this? Here is what I found --

===============================================
use warnings;
use strict;
use Win32::OLE;

my $excel = Win32::OLE->GetActiveObject('Excel.Application')||Win32::OLE->new('Excel.Application', 'Quit');

# The Excel VBA code, as a string.
my $code = <<'END_CODE';
Sub Macro1()
    Sheet1.Hyperlinks.Add Anchor:=Sheet1.Range("D5"), Address:="/var/tmp/Results/", TextToDisplay:="link"
End Sub
END_CODE

my $workbook = $excel->Workbooks->Add;
# Add a module to the workbook.
my $component = $workbook->VBProject->VBComponents->Add(1);
# importing the constants.
$component->{Name} = 'my_macros';
# Add our subroutine to the module.
my $codemodule = $component->Codemodule; $codemodule->AddFromString($code);
# Run the subroutine.
$excel->Run('Macro1');
=======================================
0
wilcoxonCommented:
I rarely code for Windows so I'm not familiar with Win32::OLE.  Based on your example code, it looks like you should be able to use Win32::OLE to add the VBA code after you save the Excel file in SaveParser (Spreadsheet::ParseExcel does not support macros so I'm assuming SaveParser doesn't either).
0
clockwatcherCommented:
Glancing at the source for the modules involved in Spreadsheet::ParseExcel::SaveParser, it appears that the Spreadsheet::WriteExcel object is returned from the Spreadsheet::ParseExcel::SaveParser::Workbook's SaveAs method (http://cpansearch.perl.org/src/JMCNAMARA/Spreadsheet-ParseExcel-0.58/lib/Spreadsheet/ParseExcel/SaveParser/Workbook.pm)... which in english means you should be able to use it with the code that wilcoxon provided to get your url to work.

 
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('template.xls');
my $worksheet = $template->worksheet(0);
 
# do whatever standard stuff you're doing with SaveParser
$worksheet->AddCell(0, 0, 'New string' );

# use the SaveParser::Workbook SaveAs to get your reference to the Spreadsheet::WriteExcel object
my $oWriteExcel = $template->SaveAs('newfile.xls');

# now use the code wilcoxon's provided to create your link
$oWriteExcel->write_url($s_row, 9, 'external:\var\tmp\Results', $fail, $wr_format4);

Open in new window


I'm too lazy to test it-- a bit past my bedtime and in a moment of surprising zeal I accepted an 8:30 meeting tomorrow morning (I need to remind myself to block out anything before 10:00 or so).  But, I'm pretty sure it will work.

If you're on windows and Win32::OLE and excel automation is actually an option, then you can definitely replace anything you're doing with SaveParser and get your link to work without problem.  But based on the fact you went down the SaveParser road, I'm guessing that automating Excel with Win32::OLE probably isn't an option.  If it is, let us know.
0
sunny82Author Commented:
Using these 2 lines --

###########################################
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            $oWriteExcel->write_url($all_row, 4, 'external:\\v\region\na\appl\qatools\util\data\reports\automation_framework\Sanity_Tests\Sanity_Check_RunBook.xls', "Pass", $wr_format1);
#########################################

I get the error -

Can't locate object method "write_url" via package "Spreadsheet::WriteExcel"
main::__ANON__('Can\'t locate object method "write_url" via package "Spreadsh...')

From the docs, I find that write_url can only be called with a worksheet object not a SaveAs object

Also pls note the path is a local unix share which can be opened in windows.
0
clockwatcherCommented:
There is no such thing as a SaveAs object.  The object that SaveAs returns is a Spreadsheet::WriteExcel (which you can tell from the error that you're getting).  A Spreadsheet::WriteExcel has a method called sheets that gives you the Worksheet object.  The following works fine for me.
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('template.xls');
my $worksheet = $template->worksheet(0);
 
# do whatever standard stuff you're doing with SaveParser
$worksheet->AddCell(0, 0, 'New string' );

# use the SaveParser::Workbook SaveAs to get your reference to the Spreadsheet::WriteExcel object
my $oWriteExcel = $template->SaveAs('newfile.xls');

# now use the code wilcoxon's provided to create your link
$oWriteExcel->sheets(0)->write_url(0, 1, 'external:file:///s:/event.log', 'Fail');

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wilcoxonCommented:
I hadn't looked at SaveParser in enough detail to know it returned a WriteExcel object.

I'm very interested to know if this sort of thing works as it would be really nice to be able to use normal WriteExcel methods when updating an Excel file.
0
sunny82Author Commented:
the code working fine...but the color is going away...the format color $wr_format1 is not showing in the hyperlinked cell "Fail"
0
clockwatcherCommented:
You aren't showing me anywhere where you're defining $wr_format1.  My guess is that you're not defining it within the context of the new workbook that you're creating.  You need to make sure that you're adding it as a format to the workbook that you're actually working with... In other words,


use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('template.xls');
my $worksheet = $template->worksheet(0);
 
# do whatever standard stuff you're doing with SaveParser
$worksheet->AddCell(0, 0, 'New string' );

# use the SaveParser::Workbook SaveAs to get your reference to the Spreadsheet::WriteExcel object
my $oWriteExcel = $template->SaveAs('newfile.xls');

my $format = $oWriteExcel->add_format(
                                        color => 33, # index for pink
                                        font  => 'Times New Roman',
                                        size  => 20,
                                        bold  => 1,
                                     );

# now use the code wilcoxon's provided to create your link
$oWriteExcel->sheets(0)->write_url(0, 1, 'external:file:///s:/event.log', 'Fail', $format);

Open in new window

0
sunny82Author Commented:
Thanks you have been of great help. You were absolutely correct, I was not defining the color format for the new workbook created.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.