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

Hi, Perl monks

I got this code from below URL

https://metacpan.org/pod/release/JMCNAMARA/Excel-Writer-XLSX-0.94/lib/Excel/Writer/XLSX/Chart/Doughnut.pm 


#!/usr/bin/perl
 
use strict;
use warnings;
use Excel::Writer::XLSX;
 
my $workbook  = Excel::Writer::XLSX->new( 'chart_doughnut.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $bold      = $workbook->add_format( bold => 1 );
 
# Add the worksheet data that the charts will refer to.
my $headings = [ 'Category', 'Values' ];
my $data = [
    [ 'Glazed', 'Chocolate', 'Cream' ],
    [ 50,       35,          15      ],
];
 
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );
 
# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'doughnut', embedded => 1 );
 
# Configure the series. Note the use of the array ref to define ranges
+:
# [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
$chart->add_series(
    name       => 'Doughnut sales data',
    categories => [ 'Sheet1', 1, 3, 0, 0 ],
    values     => [ 'Sheet1', 1, 3, 1, 1 ],
);
 
# Add a title.
$chart->set_title( name => 'Popular Doughnut Types' );
 
# Set an Excel chart style. Colors with white outline and shadow.
$chart->set_style( 10 );
 
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'C2', $chart, 25, 10 );

[download]

When I run perl filename.cgi, it creates the chart_doughnut.xlsx file. I copied it to web root /var/ww/html/chart_doughnut.xlsx. while accessing http://ipaddress/chart_doughnut.xlsx, Excel file will be downloaded to my Pc. Then, I can view it.

What I need is TO display it's image doughnut chart via my web browser.. I think I expalined correctly..

How can I achive it? Pls help me.. It may be a MINOR task for perl monks..

I found below Urls. I tried them. But, I am stil going without Succuess.

https://www.perlmonks.org/?node_id=1025384

https://fastapi.metacpan.org/source/JMCNAMARA/Excel-Writer-XLSX-0.98/examples/cgi.pl

below code from this URl

https://metacpan.org/pod/Excel::Writer::XLSX#EXAMPLES 

binmode( STDOUT );
my $workbook = Excel::Writer::XLSX->new( \*STDOUT );

[download]

expect your INPUTS

Replies are listed 'Best First'.
Re: How to display the Excel::Writer::XLSX chart image to the web browser with CGI?
by marto (Cardinal) on Aug 03, 2018 at 11:18 UTC

    If you are you trying to open the Excel document via a browser https://fastapi.metacpan.org/source/JMCNAMARA/Excel-Writer-XLSX-0.98/examples/cgi.pl should work, you don't say how this failed. If you mean you have a server process that runs, creates a file (including the chart), save the file and display only the chart then you need to provide more information, you don't say which OS you are running. If Windows with Excel installed you should be able to automate it via Wi32::OLE using something like Activechart.export "d:\path\goes\here\chart.png". A quick glance at the module you're using and I don't see any methods to export a chart.

    Update: Seems along the lines of your last question, nothing there help you to make such graphs?

[reply]
[d/l]

      Thanks for your quick response.

      >>If you are you trying to open the Excel document via a browser https://fastapi.metacpan.org/source/JMCNAMARA/Excel-Writer-XLSX-0.98/examples/cgi.pl should work

      It works. It will be downloaded. I can open it.

      The code I hv given prints a NICE doughnut chart inside excel file. I ONLY need it to display via web browser. I don't need to download the whole excel file

      When I use gd:graph, it can be done with below code 

      
print "Content-type: image/png\n\n";
binmode STDOUT;
print $myimage->png;

      [download]

      How can I achieve it with Excel::Writer::XLSX ?

      >> you don't say which OS you are running.

      OS is Linux (CentOS 6.9 64 bit) without GUI. Server is Somewhere else, while I write from somewhere REMOTELY.. My Pc is Ubuntu with GUI

[reply]
[d/l]

        "How can I achieve it with Excel::Writer::XLSX ?"

        As I said, I don't think you can do this. This module writes a file, think of it as an XML file in a zip container. It's the application that actually draws the charts. You're not using the Excel application with this module.

        Update: On Linux you should be able to open the file in OpenOffice, and extract the rendered chart, in a similar fashion to the Win32::OLE route. Take a look at OpenOffice::UNO. If you have a working solution via GD couldn't you just use that method?

[reply]
Re: How to display the Excel::Writer::XLSX chart image to the web browser with CGI?
by bliako (Abbot) on Aug 03, 2018 at 12:41 UTC

    You need to render the data in the spreadsheet into an image (or HTML file with the image). The image/html can be loaded by any browser automatically.

    BUT: there will be a lot of options you need to set to create that image. I.e. size, colors, labels.

    Most important BUT: who will do the rendering? If you were in windows (and had installed office i presume) then probably you could achieve this using Win32::OLE.

    Yiaks! <joke on>Couldn't you just email a certain Mr Gates the xlsx file and kindly ask him to email you back a GIF of so-and-so table? A long shot I know but after all "xlsx" is his own proprietary format!</joke off>

    Do you really need to use a spreadsheet to store your data? If not, then there are much simpler, easier and kinder to your sanity solutions.

    First and foremost solution if your data is just a table of rows and columns. There are no relationships in there (e.g. equations and cell-references in excel), no formating requirements etc., but just this:

    Glazed 50
Chocoloate 35
Cream 15

    [download]

    Then, the good news is that you do not need M$ excel or any other spreadsheet, free or commercial, package at all. You keep the data in a DB or a text CSV/JSON file. Read it and then pass it to GD::Graph, you know this already.

    If you do insist on keeping your data in a spreadsheet format which allows you formating and equations and the like, then why don't you use the open source OpenOffice and perl module OpenOffice::OODoc? This is a module which allows you to open/modify/save/create a spreadsheet. With this, most likely you will be able to programmatically create a spreadsheet file in open-office format, do programmatically any formating you wish, like change fonts, labels, colors. Insert, again programmatically cell relationships, equations and the likes. And finally, export the table or the whole spreadsheet into a multitude of formats including PDF, PNG, HTML, HTML+CSS which any browser can understand and load automatically. HTML would be pretty cool rather than just a PNG.

[reply]
[d/l]

Back to Seekers of Perl Wisdom