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

Writing into an Existing Excel file

P: 68
Hi,


I had problems writing in an existing Excel File.

There is an excel file with a worksheet. I would like to open this existing Excel file and add another worksheet and write data into it. But I dont know how to open an existing excel file.


can anyone give me some hint or help?



Thanks


Ravi
Jan 28 '08 #1
Share this Question
Share on Google+
3 Replies


eWish
Expert 100+
P: 971
Ravi,

Have a look at the Spreadsheet::WriteExcel Module. Also, I believe that there are others as well.

--Kevin
Jan 29 '08 #2

P: 58
Here's a sample.

Expand|Select|Wrap|Line Numbers
  1. use warnings;
  2. use strict;
  3. use Cwd;
  4. use OLE;
  5. use Win32::OLE::Const "Microsoft Excel";
  6.  
  7. &init;
  8.  
  9. sub init
  10. {
  11.    my $directory = &getcwd;
  12.  
  13.    &edit_excel ( "$directory/Temp.xls" );
  14. }
  15.  
  16. sub edit_excel
  17. {
  18.    my ( $xl_report ) = @_;
  19.    my ( $excel, $workbook, $sheet, $row );
  20.  
  21.    $excel = CreateObject OLE 'Excel.Application';
  22.    $excel -> {Visible} = 1;
  23.  
  24.  
  25.    $workbook = $excel    -> Workbooks  -> Open ( "$xl_report" ) or &terminate ( "open $xl_report" );
  26.    $sheet    = $workbook -> Worksheets -> Add ( { After => $workbook -> Worksheets ( $workbook -> Worksheets -> {Count} ) } );
  27.  
  28.    $sheet -> Activate;
  29.    $sheet -> Range ( "A:D" ) -> Font -> {Size}        = 8;
  30.    $sheet -> Range ( "A:D" ) -> Font -> {Bold}        = "True";
  31.    $sheet -> Range ( "A:D" ) -> {HorizontalAlignment} = xlHAlignLeft;
  32.    $sheet -> Range ( "A:A" ) -> {NumberFormat}        = "\@";
  33.    $sheet -> Range ( "B:D" ) -> {NumberFormat}        = "#,##0_);[Red](#,##0)";
  34.  
  35.    $sheet -> Range ( "A1"  ) -> {Value} = "Column 1";
  36.    $sheet -> Range ( "B1"  ) -> {Value} = "Column 2";
  37.    $sheet -> Range ( "C1"  ) -> {Value} = "Column 3";
  38.    $sheet -> Range ( "D1"  ) -> {Value} = "Column 4";
  39.  
  40.    $row   = 2;
  41.  
  42.    $sheet -> Range ( "A" . $row ) -> {Value} = "1";
  43.    $sheet -> Range ( "B" . $row ) -> {Value} = "2";
  44.    $sheet -> Range ( "C" . $row ) -> {Value} = "3";
  45.    $sheet -> Range ( "D" . $row ) -> {Value} = "4";
  46.  
  47.    $sheet -> Range ( "A:D" ) -> {Columns} -> Autofit;
  48.    $sheet -> Range ( "A2"  ) -> Activate;
  49.    $excel -> ActiveWindow    -> {FreezePanes} = "True";
  50.    $sheet -> {Name} = "New Sheet Name" . $workbook -> Worksheets -> {Count};
  51.  
  52.    $excel    -> {DisplayAlerts} = 0;
  53.  
  54.    # $workbook -> SaveAs ( "$xl_report" );
  55.  
  56.    $workbook -> Save;
  57.    $workbook -> Close;
  58.    $excel    -> Quit;
  59. }
  60.  
  61. sub terminate
  62. {
  63.    print "\a\a\nCould not @_ for the following reason:\n\n$!\n";
  64.    print "This program will now terminate.  Press <ENTER> to exit.";
  65.    <STDIN>;
  66.    exit;
  67. }
Jan 31 '08 #3

P: 68
Thanks for your replies, I got what I wanted.


Ravi
Feb 5 '08 #4

Post your reply

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