Solved

How do I copy one row at atime from 1 excel to another

Posted on 2010-08-27
13
512 Views
Last Modified: 2012-06-21
How can I copy only 1 row from one excel file to another? Excel 1 may have 10 rows, but only 1 row at a time writes to Excel 2. So finally Excel 2 is formed with only the last row of Excel1 and all others overwritten. here is my code, this copies the whole excel 1 into 2--

Where is it wrong? Pls help me..

use warnings FATAL => qw(all);

use Spreadsheet::ParseExcel;

use Spreadsheet::ParseExcel::SaveParser;

use Spreadsheet::WriteExcel::Big;



use strict;

use IO::Scalar;

use Data::Dumper;

use DBI;

use DBD::MSDB2;

use File::Copy qw(cp);

use Carp;







$SIG{__DIE__} = sub { confess @_ };

$SIG{__WARN__} = sub { confess @_ };





my $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res3.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");





my ( $row_min, $row_max ) = $sql_worksheet->row_range();

my ( $col_min, $col_max ) = $sql_worksheet->col_range();







my $workbook  = Spreadsheet::WriteExcel::Big->new("Temp_Res3_Int.xls");

my $worksheet = $workbook->addworksheet();







for my $row ( $row_min .. $row_max ) {

for my $col ($col_min .. $col_max) {



my $cell = $sql_worksheet->get_cell($row,$col);





$worksheet->write($row,$col,$cell->value);



#print $cell->value;





}

}

Open in new window

0
Comment
Question by:sunny82
  • 6
  • 4
  • 3
13 Comments
 
LVL 10

Expert Comment

by:jeromee
ID: 33546607
If all you care about is to copy the last row of excel 1 into the 1st row of Excel 2 try this:
for my $col ($col_min .. $col_max) {
   my $cell = $sql_worksheet->get_cell($row_max, $col);       # Read last row
   $worksheet->write(1, $col, $cell->value);               # write in to the 1st row

   #print $cell->value;
}
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33546629
Line 41 needs to be $worksheet->AddCell rather than write.  You need to use the functions from Spreadsheet::ParseExcel::SaveParser if you want to edit an existing Excel.

Also, you need to do $workbook->SaveAs at the end of your code in order to save the changes.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33546667
Never mind, I was assuming you were using SaveParser for $workbook since you had it in your use section and you talk about copying from one file to another (rather than creating a second one based on the first).

To do what you want, I would change 35-47 to:

for my $col ($col_min..$col_max) {
    my $cell = $sql_worksheet->get_cell($row_max, $col);
    next unless defined $cell;
    $worksheet->write(1, $col, $cell->value);
}
0
 

Author Comment

by:sunny82
ID: 33546692
No,
what I wanted was -- The first row of Excel 1 will populate first row of Excel 2 and create the Excel 2 sheet, then second row of Excel 1 will again create Excel 2 sheet by becoming first row of Excel 2 and overwriting the previous sheet created and so on.

So after all the rows of Excel 1 are processed, we will be left with Excel 2 sheet only containing the last row of Excel 1. i do not want the last row specifically but all the rows created in Excel Sheet 2 like this, so that I can modify the Excel 2 sheet by calling some other script from this.

Thus as I explained above, Excel sheet 2 will only have 1 row at a time.

Is it possible? What do you think?
0
 

Author Comment

by:sunny82
ID: 33546742

use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel::Big;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;



$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };


my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Temp_Res3.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");


my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();



my $workbook  = Spreadsheet::WriteExcel::Big->new("Temp_Res3_Int.xls");
my $worksheet = $workbook->addworksheet();


for my $col ($col_min..$col_max) {
    my $cell = $sql_worksheet->get_cell($row_max, $col);
    next unless defined $cell;
    $worksheet->write(1, $col, $cell->value);
}
0
 
LVL 10

Expert Comment

by:jeromee
ID: 33546763
I don't understand why you care about copying all those rows if they will eventually be overwritten by the last row?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:sunny82
ID: 33546779
The above code only showed me the last row in sheet 2. But as I said earlier, I need to be see only 1 row at a time in sheet 2. Thus if I write a statement

system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Next_Code.pl" inside


for my $col ($col_min..$col_max) {
    my $cell = $sql_worksheet->get_cell($row_max, $col);
    next unless defined $cell;
    $worksheet->write(1, $col, $cell->value);
}

I should be able to see sheet 2 with row 1 of sheet 1 in it. Again when it scans  the next row
of sheet 1, my calling script can access sheet 2 with row 2 of sheet1 in it. In this way, it has to be done.

Is it possible?

0
 
LVL 10

Expert Comment

by:jeromee
ID: 33546822
Are you saying that you want to create excel 2 as many times as there rows in excel1 for the purpose of having excel 2 verified by another script?

If so, it's possible but you need to save excel 2 each time you create one row...
BTW, it seems highly inefficient.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33546875
I agree with jeromee.  What you are trying to do is *very* inefficient.

Is there some reason you can't loop over the rows in sheet 1 and then call the scripts using the values in the cells as arguments (or better just write them as another subroutine in the perl script)?

What you are trying to do is effectively create an entirely new Excel sheet with one row in it each time which has huge amounts of overhead (see discussion in SaveParser on why/how it's not possible to actually edit an xls file).
0
 

Author Comment

by:sunny82
ID: 33547486
Actually, yes. I know thats a very inefficient way. But thats what I am trying to do by using the excel sheet having only 1 row at a time and then calling my validation script

system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Val_Code.pl" . Here are the reasons why -

I tried to use only 1 excel for data validation purposes by calling the validation script from the block above, but it was validating with all the rows for a particular column value rather than the column value for that particular row.. Pls see the link below for details --

http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26435242.html

So, now basically what I need is, If Excel 1 has 10 rows say, I want to cut and paste the 1st row from Excel 1 to an intermediate excel sheet, then do my data validation and then write it to a final excel, say Final_Results.xls. Then I will cut and paste the second row of Excel 1, put it into an intermediate excel, do data validations and then append it to that Final_Results.xls. Even the cut is not a mandatory thing, it can be ignored. The data validation is working fine, except that as I said above it needs to verify a particular column value in that same row instead of validating it with each and every row. Thats the reason why, I want to do the validation in an intermediate script which will have just 1 row and then append it to a Results sheet.

Pls let me know how I do it here in the code above by creating an entirely new excel sheet i.e. an intermediate excel sheet, for every row of excel 1 so that I can do the validations there and then append it to a final results sheet after the validations are done.

Or any better solution to the problem at http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26435242.html

Anything will do..Pls help me as I am stuck with this for 3-4 days now..

0
 
LVL 10

Accepted Solution

by:
jeromee earned 500 total points
ID: 33547814
Maybe you should take a look at you validating script (/ms/user/s/sayantag/Val_Code.pl) and see what it does in order to incorporate its validation logic to the script that you are trying to right.
Otherwise you have to do the creation, saving and validation of Excel2 each time you loop thru yourr outter (row) loop.
Something like this... (I didn't test it)

my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
   my $workbook  = Spreadsheet::WriteExcel->new("Temp_Res3_Int.xls");
   my $worksheet = $workbook->addworksheet();

   for my $col ($col_min .. $col_max) {
      my $cell = $sql_worksheet->get_cell($row,$col);
      $worksheet->write($row,$col,$cell->value);
   }

    $workbook->close()
    system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Val_Code.pl" . Here are the reasons why -
}

Good luck!
0
 

Author Comment

by:sunny82
ID: 33548175
I will let you know on Monday if that worked and if I need more ..Many thanks.
0
 

Author Comment

by:sunny82
ID: 33567971
This worked fine..Thx so much...I am closing this question...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel - Scroll Speed 3 24
excel file 5 47
formattig excel from access 3 17
onOpen 14 38
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

17 Experts available now in Live!

Get 1:1 Help Now