Office Web Components is a COM object that ships with Office 2000 Professional, that offers the ability to render Excel style graphs as GIFs (amongst many other things) on the web server.

The articles at http://www.csharphelp.com/archives2/archive457.html and http://www.4guysfromrolla.com/webtech/022101-1.2.shtml give more detail from an ASP and C# .NET perspective.

Microsoft has some pretty strict licensing issues on using Office Web Components in the Internet-world (as well as on an intranet). Before you begin using Office Web Components on your Web site be sure to read Microsoft's Licensing Agreement for OWCs.

I have seen other articles that suggest that it is impossible to use this component with Perl, but a combination of:

  1. Studying the OLE module documentation, using the excellent OLE browser (Jan Dubois) in C:\Perl\html\OLE-Browser\Browser.htm in an ActivePerl distro.
  2. Reading the documentation in "C:\Program Files\Microsoft Office\Office\1033\msowcvba.chm".
  3. Using the Object Browser in the VBA Editors in MS-Office tools.

It becomes possible to make the library work with Perl. The example below shows how to do it, and assumes you have a web server installed with the OWC COM object registered.

It only requires a slight modification to render the HTML page at the same time and put the image inline.

# Uses code from http://www.csharphelp.com/archives2/archive457.html
# and http://www.4guysfromrolla.com/webtech/022101-1.2.shtml

# ----------------------------------------------------------
# chartType can be any of the following
# ----------------------------------------------------------
# chChartTypeCombo -1 
# chChartTypeColumnClustered 0 
# chChartTypeColumnStacked 1 
# chChartTypeColumnStacked100 2 
# chChartTypeBarClustered 3 
# chChartTypeBarStacked 4 
# chChartTypeBarStacked100 5 
# chChartTypeLine 6 
# chChartTypeLineMarkers 7 
# chChartTypeLineStacked 8 
# chChartTypeLineStackedMarkers 9 
# chChartTypeLineStacked100 10 
# chChartTypeLineStacked100Markers 11 
# chChartTypeSmoothLine 12 
# chChartTypeSmoothLineMarkers 13 
# chChartTypeSmoothLineStacked 14 
# chChartTypeSmoothLineStackedMarkers 15 
# chChartTypeSmoothLineStacked100 16 
# chChartTypeSmoothLineStacked100Markers 17 
# chChartTypePie 18 
# chChartTypePieExploded 19 
# chChartTypePieStacked 20 
# chChartTypeScatterMarkers 21 
# chChartTypeScatterSmoothLineMarkers 22 
# chChartTypeScatterSmoothLine 23 
# chChartTypeScatterLineMarkers 24 
# chChartTypeScatterLine 25 
# chChartTypeScatterLineFilled 26 
# chChartTypeBubble 27 
# chChartTypeBubbleLine 28 
# chChartTypeArea 29 
# chChartTypeAreaStacked 30 
# chChartTypeAreaStacked100 31 
# chChartTypeDoughnut 32 
# chChartTypeDoughnutExploded 33 
# chChartTypeRadarLine 34 
# chChartTypeRadarLineMarkers 35 
# chChartTypeRadarLineFilled 36 
# chChartTypeRadarSmoothLine 37 
# chChartTypeRadarSmoothLineMarkers 38 
# chChartTypeStockHLC 39 
# chChartTypeStockOHLC 40 
# chChartTypePolarMarkers 41 
# chChartTypePolarLine 42 
# chChartTypePolarLineMarkers 43 
# chChartTypePolarSmoothLine 44 
# chChartTypePolarSmoothLineMarkers 45 
# ----------------------------------------------------------


use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Office Web Components';
$Win32::OLE::Warn = 3; 

my $yAxisCaption = "Date";
my $xAxisCaption = "Number of Items";

my $objCSpace = Win32::OLE->new('OWC.Chart');

my $objChart = $objCSpace->Charts->Add(0);


$objChart->{Type} = 9;    # see above

#Give title to graph
$objChart->{HasTitle} = 1;
$objChart->{HasLegend} = 1;
$objChart->Title->{Caption} = "Title Of The Chart";
$objChart->PlotArea->Interior->{Color}="#f5f5f5";

my $objFont = $objChart->Title->{Font};
setFont($objChart->Title, 12);
setFont($objChart->Legend, 10);

$objChart->Axes(0)->{HasTitle} = 1;
$objChart->Axes(0)->Title->{Caption} = $yAxisCaption;
setFont($objChart->Axes(0), 8);
setFont($objChart->Axes(0)->Title, 8);

$objChart->Axes(1)->{HasTitle} = 1;
$objChart->Axes(1)->Title->{Caption} = $xAxisCaption;
setFont($objChart->Axes(1), 8);
setFont($objChart->Axes(1)->Title, 8);

my $series1 = $objChart->SeriesCollection->Add(0);


# Set series names up
$series1->Line->{Color} = "red";
$series1->Interior->{Color} = "red";
$series1->SetData ( 0, -1,  "Series 1");
$series1->SetData ( 1, -1, "Jan\tFeb\t\Mar\tApr\tMay" );
$series1->SetData ( 2, -1, "1\t2\t3\t5\t3\t4" );

my $series2 = $objChart->SeriesCollection->Add(0);

$series2->Line->{Color} = "orange";
$series2->Interior->{Color} = "orange";
$series2->SetData ( 0, -1,  "Series 2");
$series2->SetData ( 1, -1, "Jan\tFeb\t\Mar\tApr\tMay" );
$series2->SetData ( 2, -1, "1\t2\t3\t5\t3\t4" );

$objCSpace->ExportPicture (
    {     FileName=>"c:\\temp\\xx.gif", 
        FilterName=>"GIF", 
        Width=>600, 
        Height=>500 
    });

# ----------------------------------------------------------

sub setFont()
{
    my ($text, $size) = @_;

    my $font = $text->{Font};
    $font->{Name} = "Tahoma";
    $font->{Size} = $size;
    $font->{Bold} = 1;
}

# ----------------------------------------------------------

[download]

update (broquaint): added a <readmore> tag

In reply to Using Perl and OWC to render Excel-Style Graphs by emarsee

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.