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

How to append excel sheet using win32::OLE

100+
P: 170
hi all,

i've got the following program that needs yr help:
Expand|Select|Wrap|Line Numbers
  1. use Win32::OLE;
  2.  
  3.         # use existing instance if Excel is already running
  4.         eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
  5.         die "Excel not installed" if $@;
  6.         unless (defined $ex) {
  7.             $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
  8.                     or die "Oops, cannot start Excel";
  9.         }
  10.  
  11.  
  12.         # get a new workbook
  13.         $book = $ex->Workbooks->Add;
  14.     $sheet = $book->Worksheets(1);
  15.  
  16.         # write a 2 rows by 3 columns range
  17.         $sheet->Range("A1:J2")->{Value} = [['Date','Total (IN)','Succ (IN)','Pk (IN)/Hrs','Pk (OUT)/Hrs','Peak Hour','Total (OUT)','Succ (OUT)','MO(IN)','MO(OUT)'],
  18.                                            [$date, $total_in, $succ_in,$pk_in,$pk_out,"$pk_hour - $pk_hour_dur hr",$total_out,$succ_out ]];
  19.  
  20.     $sheet->Range("K1:L2")->{Value} = [['Pk Msg/sec','Max Pk Msg/sec'],
  21.                       [$max_pk_msg,$pk_msg]];
  22.  
  23.  
  24.     foreach(@parameters)
  25.     {
  26.     $sheet->Cells(2,9)->{Value} = [$parameter_in_array{$_}];
  27.     $sheet->Cells(2,10)->{Value} = [$parameter_out_array{$_}];
  28.     }
  29.  
  30.         # print "XyzzyPerl"
  31.         $array = $sheet->Range("A2:I1")->{Value};
  32.         for (@$array) {
  33.             for (@$_) {
  34.                 print defined($_) ? "$_|" : "<undef>|";
  35.             }
  36.             print "\n";
  37.         }
  38.  
  39.         # save and exit
  40.  
  41.         $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl\\$save_file_name.xls") ;
  42.         undef $book;
  43.         undef $ex;
  44.  
this code outputs everything to an excel sheet
however i need to append the data
how will i go about trying to determine the last row and insert it?
i considered using a counter but then i still need to determine the new cells of that the new data will go in
tough problem here, anyone pls advise?
thanks
Jan 25 '08 #1
Share this Question
Share on Google+
6 Replies


100+
P: 170
hm...anyone who can help? thanks alot
Jan 28 '08 #2

KevinADC
Expert 2.5K+
P: 4,059
Sorry, I can't help. I never do anything with Excel and I have no experience with any Excel modules.
Jan 28 '08 #3

100+
P: 170
hm...thanks anyway
anyone else who might know by chance?
:)
Jan 28 '08 #4

nithinpes
Expert 100+
P: 410
I didn't understand your problem quite well. But if you are just checking if data exists in a given cell and appending to next cell, a counter variable and test loop should work. For ex:
Expand|Select|Wrap|Line Numbers
  1.     $j=1;
  2.     foreach(@parameters)
  3.     {
  4.     while(defined $sheet->Cells($j,9)->{Value}) { $j++;}
  5.     $sheet->Cells($j,9)->{Value} = [$parameter_in_array{$_}];
  6.    #####
  7.    } 
  8.  
Here, I'm using fixed column value, since you may want a particular data into particular column. You can use a variable too if required.
Not sure if I understood your requirement properly :(
Jan 28 '08 #5

P: 58
Try

Expand|Select|Wrap|Line Numbers
  1. my $last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows}) -> {Row};
Jan 28 '08 #6

100+
P: 170
hm...basically it's like say for my 1st row my data is:

Car A SHE3442F

then when i wanna insert data of car B, i just append it like below:

Car A SHE3442F
Car B SHA2324A

hm....i try if the last row code given by windblowsME work..
another problem i see is i need to open up my saved file to add in the infomation..would it be workable still if say my first entry of data i save it in "test.xls", then at a later date if i wanna append data at "test.xls", i open the file "test.xls", does the code still works? i'm gonna try it anyway but if u got any suggestions would appreciate it!
thanks dude for the help
Jan 29 '08 #7

Post your reply

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