Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Excel->workbooks->open

by dbasds (Initiate)
on Feb 24, 2004 at 17:34 UTC ( [id://331448]=perlquestion: print w/replies, xml ) Need Help??

dbasds has asked for the wisdom of the Perl Monks concerning the following question:

I am looking for a way in Solaris to open an existing Excel spreadsheet so I can add data to sheet 1. This is a spreadsheet that I FTP'd from Windows. Sheets 2-4 already have macros and graphing and stuff. I simply need to refresh the data on sheet one via an database query. The only way I have been able to open up an Excel file for wiring is if a create a whole new file. How can I open up an existing Excel file for writing?

Replies are listed 'Best First'.
Re: Excel->workbooks->open
by rchiav (Deacon) on Feb 24, 2004 at 19:10 UTC
    If you really really have to do this on Solaris, you could probably use Spreadsheet::ParseExcel to parse the spreadsheet and then use Spreadsheet::WriteExcel to write it back out again.

    I agree with the other posters that you're probably going to be better off doing this on a windows machine.

Re: Excel->workbooks->open
by Anonymous Monk on Feb 24, 2004 at 21:45 UTC
    Am not sure if this will do it, but here is a CPAN entry for Excel files:

    http://search.cpan.org/~scesano/Spreadsheet-TieExcel-0.73/TieExcel.pod

Re: Excel->workbooks->open
by bear0053 (Hermit) on Feb 24, 2004 at 17:55 UTC
    this will open an existing excel file and allow you to make changes to it by using win32::ole object
    use Win32::OLE; # name file my $xlfile = "c:/book1.xls"; # open application my $excel = new Win32::OLE('Excel.Application'); #my $excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32 +::OLE->new('Excel.Application', 'Quit') or die Win32::OLE->LastError +(); #$excel->{'Visible'} = 0; ## 0 = Not Visible, 1 = Visible # open workbook my $workbook = $excel->Workbooks->Open($xlfile); # add a new workbook #$workbook = $excel->Workbooks->Add; # add new worksheet #$workbook->Worksheets->Add(); # setup active worksheet my $worksheet = $workbook->Worksheets('Sheet1'); # write values #$worksheet->Cells(1,1)->{Value} = "fee"; #$workbook->Worksheets('Sheet1')->Range('A1')->{Value} = 'perl writing + to Excel'; $worksheet->Range("A11")->{Value} = 'testing write'; #print "ERROR:" . Win32::OLE->LastError(); # save, close and quit $workbook->Save(); $excel->ActiveWorkbook->Close(0); $excel->Quit();
    hope this helps. keep in mind you will need to alter this to do what you want.

      Win32::OLE won't work on Solaris I believe.Spreadsheet::WriteExcel will write the files but it isn't able to write to an existing Excel file.

      Perhaps re-think your design on this one. If all you need from the Solaris server is the results of a db query. Then connect to it via DBI and put the results into the Excel sheet via Win32::OLE on the Windows machine. This all assumes you can connect via DBI to your database.


      Grygonos
      The OP requested a Solaris solution, Win32:: tree is not available on Solaris.


      -Waswas

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://331448]
Approved by Grygonos
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (3)
As of 2025-02-14 21:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found