Solved

write_url function to display a particular sheet in an excel file

Posted on 2011-03-21
19
455 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
Comment Utility
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
Comment Utility
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
Comment Utility
Any solution to this above question? what do you think?
0
 
LVL 11

Expert Comment

by:tel2
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What is line 38 of Sanity_Tests.pl?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Expert Comment

by:clockwatcher
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Pls let me know if the problem I am facing is because of the writeExcel object as I stated above?
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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