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

Replies are listed 'Best First'.
Re: Using Perl and OWC to render Excel-Style Graphs
by chunlou (Curate) on Jul 02, 2003 at 01:50 UTC
    Quite an admirable effort, knowing how messy OLE can get.

    How do you print a graph as binary directly to stdout?

    Last time I checked, a MS Office OLE could easily eat up MBs of RAM. That's why it wasn't usable for website with even moderate load. How'd OWC do in that regard?

    (When clients asked for Word or Excel doc from the Web, we normally simply produced html or some text files, since MS Office could read those formats seamlessly. The clients didn't really know the difference.)
[reply]

      I take the lazy approach of dropping the gif into a predefined file on the server, then using an <img src='xxx.gif'> tag. There is no way of doing it inline. If you're generating many of these images, it is usual to generate a name automatically using a random number combined with a date/time element, then use each run of the script to clear up images from previous runs of the same script, such as clearing any image older than ten minutes old.

      I haven't checked the memory usage, but I'm using this on an internal server, and it doesn't seem to leak memory, as the COM object should(?) drop all of its' memory at the end of the script.

[reply]
Re: Using Perl and OWC to render Excel-Style Graphs
by halley (Prior) on Jul 02, 2003 at 16:05 UTC
    I've pondered if PerlMonks should have some color syntaxing filter options available, but please don't do that by default. Your favorite options happen to be confetti-colored, but I would expect a LOT of people don't enjoy that particular choice for presentation.

    The <code> and <readmore> tags are available to mark up the posting so that it fits in with the usability goals of the community... I can change my CSS file to alter the general appearance, but personal CSS files won't undo the technicolor wonder of code presented this way.

    --
    [ e d @ h a l l e y . c c ]

[reply]

      Okay, I'll update it then - if anyone is interested, this was generated using source-highlight from the GNU site at http://www.gnu.org/directory/text/doc/sourcehighlight.html.

      The command line to do this is

      source-highlight -i d:\temp\owc.pl -sperl -fhtml > xx.htm

      The file xx.htm will then contain the HTML segment.

[reply]
Re: Using Perl and OWC to render Excel-Style Graphs
by YAFZ (Pilgrim) on Jul 02, 2003 at 10:03 UTC
    Really impressive! Thanks for the sharing. BTW the coloured syntax also looks real cool on my dark theme ;-) Keep up with good work.
[reply]

Back to Cool Uses for Perl