Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

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?
0
sunny82
Asked:
sunny82
  • 6
  • 4
  • 3
2 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now