Perl and Excel

[roberthpike]

How can I check to see if a spreadsheet/workbook is already open or created? Thanks.

[mlseim]

I've never used Perl with Excel, but I'm thinking there must be
some modules available to make things easier...

http://www.google.com/search?hl=en&q=perl+excel+modules&btnG=Google+Search

[hyperbole]

I assume you are trying to do this on a Windows machine. The following module only works on Windows.

I understand there is a module that runs on Linux that will help you create an Excel workbook, but I don't think that is what you're asking for. It creates the Workbook without running Excel, whereas the Win32::OLE modules use OLE to access Excel (or another program with an OLE interface) to create the Excel Workbook.



use Win32::OLE;
use Win32::OLE::Variant;


# 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', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}


# create a new workbook after the above statements succeed.
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);



HTH



.

[roberthpike]

I'm having some trouble exporting a chart I created. How do you write the perl code to access a chart (chart1) on sheet1 and export it? Here's my weak attempt at doing just that:

$swChartObj = $swWorkBook->Sheets->Sheet1->ChartObjects->Chart1;
$swChartObj->Chart->Export({FileName=>$savename, FilterName=>$filter, Interactive=>0});

Also heres another line I'm getting an error on:
$swChart->ApplyDataLabels({Type => xlDataLabelsShowPercent, LegendKey => True, HasLeaderLines => True});

...and here's the error
Win32::OLE(0.1502) error 0x800a01a8
in METHOD/PROPERTYGET "ApplyDataLabels"


Really appreciate the help. Thanks.

[roberthpike]

I'm down to solving the first part of the previous posting. I'm having trouble doing the Export portion of the module. I would really appreciate any help on this.

[hyperbole]

I'm a little confused about what you're trying to do. From the command you've given I assume you are trying to get Excel to write the sheet you created in a different format.

Since you are using Win32::OLE, you have direct access to all the data in the spread sheet from your perl script. Why don't you just use perl to write the information to a new file?

Maybe if I knew what file rformat you're trying to create I would understand your problem better.



.

[roberthpike]

Sorry about that. Here's exactly what I'm doing:
I want to have a module that will accept the name of a tab-delimited file I have so it can convert that data into an Excel Chart. $filter would have been set to JPG. I have a Perl reporting facility that writes the data to a tab-delimited file. I want this module to create the chart so as to display the JPG in the browser directly after the conversion. The main perl script writes HTML code to the browser and then accepts parameters from the HTML page which it then uses to create the specified chart. Once the module has been called (i.e. after the specifics collected and the form submitted) the entry form will re-display, clear the fields, and show the chart JPG. Hope this makes it a little clearer.

[hyperbole]

OK, That makes it clear. Unfortunately, I have only used perl to create spreadsheets; not for creating charts.

I understand that you are trying to use Excel as a tool for creating a chart which you could then access with perl to display on a web page. I just don't think I can help with that particular problem since I haven't played with it.

When I was learning how to do this, I found a complete lack of documentation on the subject. I had to learn how to access Excel through the OLE module by using the perl debugger. I assume you are familiar with the debugger. If not I can help you get started with it.


.