Solved

write_url function to display a particular sheet in an excel file

Posted on 2011-03-21
19
467 Views
Last Modified: 2012-06-27
Hi,

I want to know whether we can point to a particular excel sheet $sheetname in an excel file which is composed of many such sheets through the write_url function?

Is it possible to point to say sheetname A through this?

Here is what I did, its working fine except that when I click on Fail it takes me to the first sheet of the excel file whereas I want to display the particular excel sheet.

=============================
            print "\n\nTest Failed\n\n";
            ###############Writing Passed/Failed To All Source Sheet#############################################################
            my $wr_parser  = Spreadsheet::ParseExcel::SaveParser->new();
            my $wr_workbook = $wr_parser->Parse("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls") or die "could not read the excel: $!";
            my $wr_worksheet = $wr_workbook->{Worksheet}[0];
            my $wr_format1 = $wr_workbook->AddFormat(Fill => [0,11,0]);
            my $wr_format2 = $wr_workbook->AddFormat(Fill => [1,10,0]);
            $wr_worksheet->AddCell($all_row, 4, "Fail", $wr_format2);
            $wr_worksheet->AddCell($all_row, 3, $cob_date2, $wr_format2);
            my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('red');
            $format->set_underline();
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls', "Fail", $format);
==============================================
0
Comment
Question by:sunny82
  • 12
  • 6
19 Comments
 
LVL 25

Expert Comment

by:clockwatcher
ID: 35186025
If you define a named range (http://support.microsoft.com/kb/229808) in the sheet to which you're linking (Sanity_Batch_Tests_Config.xls) and then reference it via #whatever_you_called_it in your URL, it should open to that named range.

 external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mydefinedname

0
 

Author Comment

by:sunny82
ID: 35189806
For example, if my sheet name is $sheetname1 = 'FCBalance' then will this work?

external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#$sheetname1

0
 

Author Comment

by:sunny82
ID: 35193438
Any solution to this above question? what do you think?
0
 
LVL 12

Expert Comment

by:tel2
ID: 35193961
Hi sunny82,

As you know, clockwatcher has given you a possible solution.  I have no idea whether the example you posted would work, but...

Questions:
1. Have you read the webpage at the Microsoft link he provided?
2. Have you tested it?
3. What was the result?
0
 

Author Comment

by:sunny82
ID: 35198653
The problem with that solution is I am building a framework in Perl and generating many possible excel sheets on the fly. Hence I cannot open an excel sheet and specify the ranges. I think thats what the link says as per my understanding though I may be wrong. I need to store a sheetname in $sheetname and display that with the write_url function as I stated in the code above.

Pls let me know if this is possible. At present I can create an url for the whole excel file and not its individual tabs.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 35200223
Nowhere have you specified that the file that you're linking to is being generated on the fly-- only the file that contains the link to it.  It's no big deal, it just wasn't specified anywhere.  

The name needs to be defined in the sheet to which you're linking.  You can't do anything about it in the sheet that is linking to it.  In other words, the code that creates the Sanity_Batch_Tests_Config.xls file needs to contain a line like the below:

  $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $sheetname);

The code that creates the workbook that links to it needs to then reference that bookmark.

   $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Fail", $format);
0
 

Author Comment

by:sunny82
ID: 35201088
I did this as you suggested --

============================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
$oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
==================================================

But getting this error, I dont know why this is coming because the sheet FC Balance Check is present in Sanity_Batch_Tests_Config.xls


===========================
Unknown sheet name FC Balance Check in formula
 at Sanity_Tests.pl line 38
        main::__ANON__('Unknown sheet name FC Balance Check in formula\x{a}') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Formula.pm line 791
============================
0
 

Author Comment

by:sunny82
ID: 35201120
I mean I did this
======================
============================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
=======================================


and still got the error even though the sheet FC Balance Check is present in Sanity_Batch_Tests_Config.xls

===========================
Unknown sheet name FC Balance Check in formula
 at Sanity_Tests.pl line 38
        main::__ANON__('Unknown sheet name FC Balance Check in formula\x{a}') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Formula.pm line 791
============================

0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 35204452
What is line 38 of Sanity_Tests.pl?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

Expert Comment

by:clockwatcher
ID: 35204473
Honestly... doesn't even really matter.  I think I must not be communicating very well.

You need to define the name in the workbook that's being linked to not the workbook that contains the link.  The code that you posted is creating a sheet called:

  Sanity_Check_RunBook.xls

inside this workbook, you're adding a link to another workbook (and specifically to a sheet within that workbook):

   Sanity_Batch_Tests_Config.xls

The name has to be defined in the Sanity_Batch_Tests_Config.xls file.  The code that you've posted is defining the name in the  Sanity_Check_RunBook.xls file.  

Where is the code that generates Sanity_Batch_Tests_Config.xls?  That's where the name needs to be defined.

   
0
 

Author Comment

by:sunny82
ID: 35210643
Oops, I did no see that. The problem is definitely a lack of communicatioion on my part. The workbook Sanity_Batch_Tests_Config.xls has some excel tabs FC Balance Check, FC Qty Check and FC Variance containing individual queries and their success and fail conditions. The other excel workbook Sanity_Check_RunBook.xls contains a list of those checks to be run (which are essentially the 3 tabs in Sanity_Batch_Tests_Config.xls), which tab to run marked by a Y or N. If it is Y, it will run that particular Sanity_Batch_Tests_Config.xls tab which are essentially the SQL queries in that tab and generate results, compare against their pass and fail conditions and update whether it is Pass or Fail in Sanity_Check_RunBook.xls workbook with a hyperlink beside that tab name in a status column. Clicking on this hyperlink will take me back to the particular tab of Sanity_Batch_Tests_Config.xls where the query ran.

Thats the reason why I read Sanity_Batch_Tests_Config.xls with Spreadsheet::ParseExcel module to read and run its queries while the workbook Sanity_Check_RunBook.xls is to be updated with Pass or Fail and hence I used SaveParser module for that.

In this scenario, is there a way to access the function "define_name('mybookmark', sprintf('=%s!$A$1', $input)" with a ParseExcel object, since I only used ParseExcel module to read Sanity_Batch_Tests_Config.xls and there is no WriteExcel object defined for it?? Without mentioning this tab name, it only takes me to the first tab of Sanity_Batch_Tests_Config.xls  on clicking Pass or Fail.

0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 35211437
Now I'm back to my original comment because it doesn't sound like you're dynamically creating Sanity_Batch_Tests_Config.xls.  

Are you not allowed to modify Sanity_Batch_Tests_Config.xls?  

Is there a problem with just opening up that file (Sanity_Batch_Test_Config.xls) in Excel and defining the names (bookmarks) by hand ?  Open Sanity_Batch_Tests_Config.xls in Excel, from the menu bar, select Insert, Name, Define, enter the name you want to use to refer to it, the sheet reference it refers to and hit the Add button. Save the sheet.

Once the bookmarks have been defined in the Sanity_Batch_Tests_Config.xls sheet, then the links that you're placing in Sanity_Check_RunBook.xls with write_url will work.
0
 

Author Comment

by:sunny82
ID: 35216880
If I name all the three sheets FC Balance Check , FC Variance Check and FC Qty Check as mybookmark, will the code be intelligent enough to open that particular sheet when I click its corresponding "P" or "F". Or Conversely, if I use three different bookmarknames for the three sheets can the code determine which sheet to open?
0
 

Author Comment

by:sunny82
ID: 35216892
This code --

$oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $sheetname);

is generic for all the 3 sheets, thats why I said the above.
0
 

Author Comment

by:sunny82
ID: 35217017
I put in a bookmarkname for FC Balance Check sheet as "mybookmark" manually in that sheet and saved it in Sanity_Batch_Test_Config.xls then ran this --

============================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $input));
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
=======================================


but getting the same error as earlier --
===========================
Unknown sheet name FC Balance Check in formula
 at Sanity_Tests.pl line 38
        main::__ANON__('Unknown sheet name FC Balance Check in formula\x{a}') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Formula.pm line 791
============================

Am I doing any mistake again?
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 35217376
You don't need this line:

  $oWriteExcel->define_name('mybookmark', sprintf('=%s!$A$1', $sheetname);

In any of your code.  All you need is the bookmark defined in the sheet that is being linked to and then the write_url in the sheet that contains the link.
0
 

Author Comment

by:sunny82
ID: 35219045
Ok Cool. Another problem I having is in the excel file Sanity_Check_RunBook.xls

It is like this

FC Balance Check                Y                       Pass
FC Qty Check                       Y                       Fail
FC Variance Check              Y                       Pass

where Pass and Fail are all hyperlinks. If the status is N, that check wont run. The problem is only the last hyperlink is persisting. For e.g. in the above if I run all the three (since status is Y) only the last Pass has a hyperlink whereas in the case of first two the hyperlinks completely disappear.
Is it because of the WriteExcel object which gets created from the SaveAs method of ParseExcel SaveParser everytime such that when it writes the last hyperlink, in the case of first two the hyperlinks simply disappear though all the checks are run one after another?

Here is the code --
==========================================
my $oWriteExcel = $wr_workbook->SaveAs("/v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Check_RunBook.xls");
            my $format = $oWriteExcel->add_format();
            $format->set_bg_color('lime');
            $format->set_underline();
            $oWriteExcel->sheets(0)->write_url($all_row, 4, 'external:file://///v/region/na/appl/qatools/util/data/reports/automation_framework/Sanity_Tests/Sanity_Batch_Tests_Config.xls#mybookmark', "Pass", $format);
0
 

Author Comment

by:sunny82
ID: 35219171
Just to clarify what I said above, the first two "Pass" and "Fail" are not hyperlinks anymore but the third "Pass" is a hyperlink. Is it because of the reason I stated above?
0
 

Author Comment

by:sunny82
ID: 35224536
Pls let me know if the problem I am facing is because of the writeExcel object as I stated above?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
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…
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…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 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

9 Experts available now in Live!

Get 1:1 Help Now