By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,695 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,695 IT Pros & Developers. It's quick & easy.

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

P: 1
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,case3,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,verification_p t_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.
Jul 28 '08 #1
Share this Question
Share on Google+
3 Replies


nithinpes
Expert 100+
P: 410
You can make use of Spreadsheet::WriteExcel 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.
Jul 28 '08 #2

sudhamani
P: 1
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
Expand|Select|Wrap|Line Numbers
  1. use Win32::OLE qw(in with);
  2. use Win32::OLE::Const 'Microsoft Excel';
  3. use Spreadsheet::WriteExcel;
  4. $Win32::OLE::Warn = 3;                                # die on errors...
  5.  
  6. # get already active Excel application or open new
  7. my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
  8.     || Win32::OLE->new('Excel.Application', 'Quit');  
  9. use Spreadsheet::WriteExcel;
  10.         # Create a new Excel workbook called perl.xls
  11.         my $workbook = Spreadsheet::WriteExcel->new("tes1.xls");
  12.         # Add some worksheets
  13.         my $sheet1 = $workbook->add_worksheet();
  14.         #you can have color,bold attributes.
  15.         my $format = $workbook->add_format();
  16.  
  17.         $sheet1->activate();
  18. # open Excel file
  19.  my $Book = $Excel->Workbooks->Open("C:\abc.xls");
  20. my $Sheet = $Book->Worksheets(1);#opens first work sheet
  21. my $LastRow = $Sheet->UsedRange->Find({What=>"*",
  22.     SearchDirection=>xlPrevious,
  23.     SearchOrder=>xlByRows})->{Row};
  24. my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
  25.                   SearchDirection=>xlPrevious,
  26.                   SearchOrder=>xlByColumns})->{Column};
  27. print $LastRow."-".$LastCol;
  28.  
  29. $ro =  $LastRow; #11;
  30. $col = $LastCol; #3;
  31. $writeRow = 0;
  32. foreach my $row (1..$ro)
  33. {
  34.  foreach my $col (1..$col)
  35.  {
  36. $str=$Sheet->Cells($row,$col)->{'Value'}; #contains the read data
  37. $sheet1->write($row,$col,$actual, $format);#sheet1 is for writing data
  38.  }
  39. }
  40.  
Jul 29 '08 #3

numberwhun
Expert Mod 2.5K+
P: 3,503
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
Jul 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.