467,146 Members | 1,010 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

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

Hi All,

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

In test1.xls sheet contain 3 colunm

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

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
  • viewed: 2331
3 Replies
Expert 256MB
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
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...
  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();
  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;
  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. }
Jul 29 '08 #3
Expert Mod 2GB

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.


Jul 29 '08 #4

Post your reply

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

Similar topics

2 posts views Thread by ben moretti | last post: by
3 posts views Thread by Vumani Dlamini | last post: by
13 posts views Thread by BigDaDDY | last post: by
2 posts views Thread by moller@notvalid.se | last post: by
9 posts views Thread by Wayne Smith | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.