Spreadsheet::WriteExcel

Discussion in 'Perl' started by Fabio, Mar 2, 2004.

  1. Fabio

    Fabio Guest

    Hello,

    there's a module called Spreadsheet::WriteExcel. It allows me to create
    a new worksheet and edit its cells:

    my $workbook = Spreadsheet::WriteExcel->new('my.xls');
    my $worksheet = $workbook->addworksheet();
    $worksheet -> write('A2', 222);

    But how can I put some values into the cells of an existing worksheet?
    Thanks for any advice.

    ...:: fabio
     
    Fabio, Mar 2, 2004
    #1
    1. Advertising

  2. Fabio <> wrote in message >
    > But how can I put some values into the cells of an existing worksheet?
    > Thanks for any advice.


    Use either Spreadsheet::parseExcel::SaveParser for rudimentary changes
    to existing workbooks. If you need to do complex stuff, it may
    (unfortunately) be easier to use OLE to do it.

    w
     
    Will Stranathan, Mar 2, 2004
    #2
    1. Advertising

  3. Fabio

    Tom Niesytto Guest

    Fabio <> wrote in message news:<c21l9f$9c2$>...
    > Hello,
    >
    > there's a module called Spreadsheet::WriteExcel. It allows me to create
    > a new worksheet and edit its cells:
    >
    > my $workbook = Spreadsheet::WriteExcel->new('my.xls');
    > my $worksheet = $workbook->addworksheet();
    > $worksheet -> write('A2', 222);
    >
    > But how can I put some values into the cells of an existing worksheet?
    > Thanks for any advice.
    >
    > ..:: fabio


    Dunno Spreadsheet::WriteExcel.
    Here is sth I wrote recently using Win32::OLE
    -------------
    #for OLE automation
    use Win32::OLE;
    use Win32::OLE::Const "Microsoft Excel";
    use Win32::OLE 'in';
    use Win32::OLE::Variant;
    #get current directory
    use Cwd;

    #writes array of changes to existing excel spreadsheet as new excel spreadsheet
    #arguments:
    # input_file - name of existing Excel spreadsheet
    # output_file - name under which changed file is to be save under (SaveAs option)
    # change_array_ref - reference to array of changes - (row \t column \t new value)

    sub write_changes_into_excel_file
    {
    #name of existing Excel spreadsheet
    my $input_file = shift;
    #name under which changed file is to be save under (SaveAs option)
    my $output_file = shift;

    #reference to array of changes - (row, column, new value)
    my $change_array_ref = shift;

    my $dir = cwd();
    my $fullname_input_file = $dir.'/'.$input_file;
    my $fullname_output_file = $dir.'/'.$output_file;

    #check if input file exists in first place
    unless(-f $fullname_input_file)
    {
    print STDERR "Input file: $fullname_input_file does not exists.\n";
    exit;
    }

    # use existing instance if Excel is already running
    eval
    {
    $excel = Win32::OLE->GetActiveObject('Excel.Application')
    };
    die "Excel not installed" if $@;

    unless (defined $excel)
    {
    $excel = Win32::OLE->new('Excel.Application', 'Quit')
    or die "Oops, cannot start Excel";
    }
    #to avoid excessive dialogs when saving in non-Excel format
    $excel->{DisplayAlerts} = 0;

    my $wbook;
    if($wbook = Win32::OLE->GetObject($fullname_input_file)){;}
    else
    {
    #the failure og GetObject is potentially due to the fact
    #that file $fullname_input_file is already opened.
    #In such case we need to find workbook that corresponds
    #to already opened file and if we cannot we print error
    #message and exit.
    my $wbooks = $excel->Workbooks();
    my $name = Variant($fullname_input_file);
    if($wbooks->{"$name"}->Activate())
    {
    $wbook = $wbooks->{"$name"};
    }
    else
    {
    print STDERR "Could not open the file $fullname_input_file: $!\n";
    exit;
    }
    }

    # write to a particular cell
    my $wsheet = $wbook->Worksheets(1);

    my $line;
    my $cell_value;
    my $row_num;
    my $col_num;
    my $do_next;
    foreach $line (@{$change_array_ref})
    {
    (
    $row_num,
    $col_num,
    $cell_value
    )
    = split("\t",$line);

    $row_num =~ s/\s//g;
    $col_num =~ s/\s//g;

    $wsheet->Cells($row_num,$col_num)->{Value} = "$cell_value";
    $wsheet->Cells($row_num,$col_num)->Select();
    }

    $wbook->SaveAs($fullname_output_file);
    undef $wbook;
    }
     
    Tom Niesytto, Mar 4, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Erica
    Replies:
    2
    Views:
    108
    John M. Gamble
    Sep 5, 2003
  2. Sven Jungnickel
    Replies:
    3
    Views:
    201
    John McNamara
    Jul 29, 2004
  3. Zhidian Du
    Replies:
    1
    Views:
    136
    A. Sinan Unur
    Dec 11, 2003
  4. Sean Berry

    Spreadsheet::WriteExcel

    Sean Berry, May 8, 2004, in forum: Perl Misc
    Replies:
    11
    Views:
    255
    Sean Berry
    May 9, 2004
  5. A Ymous

    Query about Spreadsheet::WriteExcel

    A Ymous, Sep 10, 2004, in forum: Perl Misc
    Replies:
    1
    Views:
    118
    Carl Inglis
    Dec 2, 2004
Loading...

Share This Page