Perl Guru Forums: Perl Programming Help: Beginner: Advice on charting data from an existing excel file.

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Beginner:
Advice on charting data from an existing excel file.

rnovelo79
Novice

Apr 14, 2013, 10:02 AM

Post #1 of 6 (536 views)

Advice on charting data from an existing excel file.	Can't Post

Hello,
I kindly request some advice/input on how to approach an issue I'm currently having. I have a perl script already written that parses data and writes max values per day into an excel file. What I would like to do now is be able to chart the data from that file into another worksheet.

Is there a script that can chart the data from scratch into another worksheet?
I already have an excel template with old data and the charts, is there a script that can update the template with the new data?

Attached is the excel file with the data I'd like to chart or update into an existing template.

Please ... any advice would be highly appreciated.

Thanks

rnovelo79
Novice

Apr 14, 2013, 1:26 PM

Post #2 of 6 (524 views)

Re: [rnovelo79] Advice on charting data from an existing excel file. [In reply to]	Can't Post

Hi guys,
I found this script which works as an example but I'd like to modify it. I intend to use it in a combination of 'if' and 'for' loops but I can't seem to name the worksheets, whenever I try to it corrupts the file and i can't open it. For instance, I'd like to name the first worksheet where the data is as 'ach', then I'd like to have other worksheets by region as 'north', 'south' and 'west' where I will sort through the data my other script generates and start inserting the charts corresponding to my loops. Can someone please help me modify this script?

For this particular script, two worksheets are generated, 'sheet1' and 'chart1'. I'd like to have control of the naming of the worksheets and be able to have control over inserting the chart in a particular worksheet.

I'd like to make mention that this script will be added to my other script.

Code

#!/usr/bin/perl 
 
    use strict; 
    use warnings; 
    use Excel::Writer::XLSX; 
 
    my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' ); 
    my $worksheet = $workbook->add_worksheet(); 
 
    my $chart     = $workbook->add_chart( type => 'line' ); 
    $chart->set_legend( position => 'bottom' ); 
 
    # Configure the chart. 
    $chart->add_series( 
        categories => '=Sheet1!$A$2:$A$7', 
        values     => '=Sheet1!$B$2:$B$7', 
    ); 
 
    # Add the worksheet data the chart refers to. 
    my $data = [ 
        [ 'Category', 2, 3, 4, 5, 6, 7 ], 
        [ 'Value',    1, 4, 5, 2, 1, 5 ], 
    ]; 
 
    $worksheet->write( 'A1', $data );

(This post was edited by rnovelo79 on Apr 14, 2013, 1:37 PM)

rnovelo79
Novice

Apr 17, 2013, 12:18 PM

Post #3 of 6 (500 views)

Re: [rnovelo79] Advice on charting data from an existing excel file. [In reply to]	Can't Post

Can anyone help me with this issue? I don't know if it's so simple that you won't bother waste your time with me, but I would sincerely appreciate any advice how to approach/resolve this issue. I'm trying to use part the above script but I'd like to have control on naming the worksheets and assigning the graph to a particular worksheet.

Please help.

BillKSmith
Veteran

Apr 17, 2013, 1:00 PM

Post #4 of 6 (496 views)

Re: [rnovelo79] Advice on charting data from an existing excel file. [In reply to]	Can't Post

I do not think that you are being ignored. I have never heard of your module and know little about excel itself. You made the situation worse by failing to attach the code that you promised.

I do notice that your new code calls the workbook method "add_worksheet" without any arguments. The documentation on CPAN shows that it expects a sheetname. It looks like that document is all you have. Study it very carefully!
Good Luck,
Bill

rnovelo79
Novice

Apr 17, 2013, 1:25 PM

Post #5 of 6 (494 views)

Re: [BillKSmith] Advice on charting data from an existing excel file. [In reply to]	Can't Post

Hi Bill,
I apologize, I'm new to the forum and I'm still learning how to work the available tools in the site. I thought I had uploaded the attachment. Crazy

I have now attached it properly. In the attachment you will notice multiple columns, that's why I wanted to keep the sample script as simple as possible because my intention is to sort the data in the excel file and chart them by region... eg: 'North', 'South' etc ... I have been reading as you have pointed out, and yes I did notice the script did not have a worksheet name. I had mentioned previously that on that particular sample script, whenever I tried adding a worksheet name, it would corrupt the file and would not allow me to open it.

On another note, I've been trying to dig up something more simple, and I found this particular script in some ask for help site. As you see, I managed to name the worksheet, and now I get one worksheet as 'ach' and another as 'chart1'. I am trying to figure out how to rename 'chart1' worksheet and add more worksheets and find a way to reference the chart and to chart it into a particular worksheet. I'm still researching, but this is what I have so far.

Code

 
#!/usr/bin/perl 
use warnings; 
use 5.014; 
use Excel::Writer::XLSX; 
 
my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' ) or die $!; 
my $worksheet = $workbook->add_worksheet( 'ach' ); 
my $ref = [ 
[ 'Max.', 'Min.' ], 
[ 7, 5 ], 
[ 11, 5 ], 
[ 9, 2 ], 
[ 8, 5 ], ]; 
$worksheet->write_col( 0, 0, $ref ); 
 
my $chart = $workbook->add_chart( type => 'line' ); 
 
$chart->add_series( 
    values => [ 'ach', 1, $#$ref, 0, 0 ], 
    name => '=ach!$A$1', 
); 
 
$chart->add_series( 
    values => [ 'ach', 1, $#$ref, 1, 1 ], 
    name => '=ach!$B$1', 
);

Attachments:

ach-20130408.xlsx (26.3 KB)

rnovelo79
Novice

Apr 17, 2013, 2:12 PM

Post #6 of 6 (492 views)

Re: [rnovelo79] Advice on charting data from an existing excel file. [In reply to]	Can't Post

Well Bill,
I think your words jump started a part of my brain, because I think I finally found what I was looking for. :D Here's the simple code with some more code I added to make what I needed work.

Code

#!/usr/bin/perl 
use warnings; 
use 5.014; 
use Excel::Writer::XLSX; 
 
my $workbook = Excel::Writer::XLSX->new( 'chart.xlsx' ) or die $!; 
my $worksheet1 = $workbook->add_worksheet( 'ach' ); 
my $worksheet2 = $workbook->add_worksheet( 'north' ); 
my $worksheet3 = $workbook->add_worksheet( 'south' ); 
my $worksheet4 = $workbook->add_worksheet( 'west' ); 
 
my $ref = [ 
[ 'Max.', 'Min.' ], 
[ 7, 5 ], 
[ 11, 5 ], 
[ 9, 2 ], 
[ 8, 5 ], ]; 
$worksheet1->write_col( 0, 0, $ref ); 
 
my $chart = $workbook->add_chart( type => 'line', , embedded => 1 ); 
 
$chart->add_series( 
    values => [ 'ach', 1, $#$ref, 0, 0 ], 
    name => '=ach!$A$1', 
); 
 
$chart->add_series( 
    values => [ 'ach', 1, $#$ref, 1, 1 ], 
    name => '=ach!$B$1', 
); 
 
 
 
$worksheet3->insert_chart( '$A$2', $chart );

Now ... all I need is to write the foreach/if loops to sort through the data in the excel file and insert the charts according to their zones. :D

Thanks a lot Bill, I appreciate it very much! :)

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives