Solved

Creating Hyper-links of Perl variables

Posted on 2011-03-16
13
424 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
  • 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
perl rename 2 137
Strange perl issue 6 122
.properties file to call function/method 9 55
perl: Cleaning meta tags using RegEX 12 43
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now