Solved

Creating Hyper-links of Perl variables

Posted on 2011-03-16
13
483 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:sunny82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 35151187
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
 

Author Comment

by:sunny82
ID: 35151234
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
 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 150 total points
ID: 35151712
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sunny82
ID: 35152092
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
 

Author Comment

by:sunny82
ID: 35152209
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 35152354
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
 
LVL 25

Expert Comment

by:clockwatcher
ID: 35154215
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
 

Author Comment

by:sunny82
ID: 35156973
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
 
LVL 25

Accepted Solution

by:
clockwatcher earned 350 total points
ID: 35159933
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
 
LVL 26

Expert Comment

by:wilcoxon
ID: 35160062
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
 

Author Comment

by:sunny82
ID: 35160605
the code working fine...but the color is going away...the format color $wr_format1 is not showing in the hyperlinked cell "Fail"
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 35161019
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
 

Author Comment

by:sunny82
ID: 35165765
Thanks you have been of great help. You were absolutely correct, I was not defining the color format for the new workbook created.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question