How to access data from one xls file to another xls file using perl and in run time.

[rabtree]

Hi All,

I have to two xls file named test1.xls and test2.xls

In test1.xls sheet contain 3 colunm

1.Sr.No
2.Case Name
3.Validation case

In Sr.No contain serail number like 1,2,3,4,5,..... . so on.

In Case Name contain testcase name like case1,case2,cas e3,case4,case5. ...........so on.

In Validation case contain validate case like 3,5,2,4,4,3,4,1 ,3,4...so on.

In test2.xls sheet contain column
1.Sr.No
2.verification_ pt_1
3.verification_ pt_2
4.verification_ pt_3

In Sr.No contain serail number like 3,5,2,4,4,3,4,1 ,3,4...so on.

In verification_pt _1,verification _pt_2,verificat ion_pt_3 contain some validation data.

My validation here is when I read column "Validation case" from the test1.xls sheet it should move to another sheet named test2.xls sheet and point to Sr.No of test2.xls sheet.


Can anyone let me know how I can do this using perl sripting language?

Waiting for early reply.

[nithinpes]

You can make use of Spreadsheet::Wr iteExcel or Win32::OLE to write/read data to/from excel files.
All you need to do is read from these files and store the required cell values in some arrays and use it later for validation.

[sudhamani]

For your requirement the logic is little complicated...
But you can do it. you have to get few modules from cpan like Excel Reader and Writer modules and then u can make use of code similar to the following.
I have given code for both reading and writing into excell sheet

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Spreadsheet::WriteExcel;
$Win32::OLE::Warn = 3;                                # die on errors...

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  
use Spreadsheet::WriteExcel;
        # Create a new Excel workbook called perl.xls
        my $workbook = Spreadsheet::WriteExcel->new("tes1.xls");
        # Add some worksheets
        my $sheet1 = $workbook->add_worksheet();
        #you can have color,bold attributes.
        my $format = $workbook->add_format();

        $sheet1->activate();
# open Excel file
 my $Book = $Excel->Workbooks->Open("C:\abc.xls");
my $Sheet = $Book->Worksheets(1);#opens first work sheet
my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};
my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};
print $LastRow."-".$LastCol;

$ro =  $LastRow; #11;
$col = $LastCol; #3;
$writeRow = 0;
foreach my $row (1..$ro)
{
 foreach my $col (1..$col)
 {
$str=$Sheet->Cells($row,$col)->{'Value'}; #contains the read data
$sheet1->write($row,$col,$actual, $format);#sheet1 is for writing data
 }
}

[numberwhun]

sudhamani,

First, please remember to use code tags when supplying code in the forums.

Second, these are learning forums. As you can see, one of our experts was guiding the OP in the right direction, but NOT supplying them code. The reason for this? So the OP can learn how to code. If you we were to just supply code to any user who simply asked for it, we would be called a script shop or a coding service, but that is not the case. If the user supplies code and has actually tried to code, then supplying them code that fixes their issue is different.

In the future, please do not just supply whole solutions to OPs who request it. You may be doing their homework, which is against this site's policy.

Regards,

Moderator