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.
3 2588
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.
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 3,509
Recognized Expert Moderator Specialist
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 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: ben moretti |
last post by:
hi
i'm learning python, and one area i'd use it for is data management in
scientific computing. in the case i've tried i want to reformat a data
file from a normalised list to a matrix with some sorted columns. to
do this at the moment i am using perl, which is very easy to do, and i
want to see if python is as easy.
so, the data i am...
|
by: Vumani Dlamini |
last post by:
I have data which is in this format:
### data ###
area=1101
home=003
mzer=00020
mzec=101
pmpr=00000
pmpc=102
bnsr=00000
bnsc=103
|
by: BigDaDDY |
last post by:
Um yeah....In case you haven't figured it out, Microsoft sucks. I'm going
to be kicked back in my chair eating popcorn and watching football 10 years
from now, while all you clowns are scrambling to rewrite all your code
because Microsoft upgraded all their crap and nothing you wrote 10 years
earlier works. It doesn't take a rocket scientist...
|
by: moller |
last post by:
Im looking in to the possibility of moving from mySQL to
an access database.
My reasons are:
(1) Database is single user.
(2) Database local on users PC.
(3) Database has only 8 tables where 4 are filled at database creation
with aprox 20 rows each and are never added to after that.
(4) Database grows with ca 6000 rows/week.
(5) No data...
|
by: Cheryl Langdon |
last post by:
Hello everyone,
This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.
I suddenly find myself in urgent need of instruction on how to
communicate with a MySQL database table on a web server, from inside
of my company's Access-VBA application. I know VBA pretty well but
have never...
| |
by: Wayne Smith |
last post by:
I've come up against a major headache that I can't seem to find a solution
for but I'm sure there must be a workaround and I would really be grateful
of any help.
I'm currently building a web site for a small club I belong to and one of
the features I would like to include is the ability to allow users to upload
image files.
...
|
by: KevinADC |
last post by:
Introduction
In part one we discussed the default sort function. In part two we will discuss more advanced techniques you can use to sort data. Some of the techniques might introduce unfamiliar methods or syntax to a less experienced perl coder. I will post links to online resources you can read if necessary. Experienced perl coders might find...
|
by: KevinADC |
last post by:
If you are entirely unfamiliar with using Perl to sort data, read the "Sorting Data with Perl - Part One and Two" articles before reading this article. Beginning Perl coders may find this article uses unfamiliar terms and syntax. Intermediate and advanced Perl coders should find this article useful. The object of the article is to inform the...
|
by: Max58kl |
last post by:
Trying to access data and print it to the screen using Perl Builders I/O Window
--------------------------------------------------------------------------------
Hi
I am using a program called Perl Builder which is
an Integrated Development Environment for Perl scripting.
I am trying to access the data in a txt file and then print
the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |