#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0

    How to create a chart with data from database


    Hi,

    I need to create some charts using Spreadsheet::WriteExcel with data driven from database using DBI, I tried googling but could not find any help, here is a sample script that fetches data from a sample database and I want to create a bar chart using the name, surfacearea and population columns can someone please help me with the routines to get it done

    Code:
    my $sth = $dbh->prepare("SELECT a.name,b.name as capital, a.continent,a.region, a.lifeexpectancy,a.surfacearea,a.population FROM country a, city b WHERE a.capital=b.id ORDER BY 1");
    $dbh->do("SET search_path to world") or die;
    $sth->execute();
    
    my $row=1;
    while ($a = $sth->fetchrow_hashref()) {
    $worksheet->write($row,0, $a->{name}, $bold);
    $worksheet->write($row,1, $a->{capital});
    $worksheet->write($row,2, $a->{continent});
    $worksheet->write($row,3, $a->{region});
    $worksheet->write($row,4, $a->{lifeexpectancy});
    $worksheet->write($row,5, $a->{surfacearea});
    $worksheet->write($row,6, $a->{population});
    $row++;
    }
    As always, your help would be much appreciated.
    Thanks
    Terry
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    All I need is how to map the data retrieved from database and written to excel sheet1 to the chart data source, the columns which the data is written to in sheet1 is in cell A, F and G, I need to do it using perl as detailed in my initial msg for this thread.

    Thank you for your help.
    Rgds.
    Terry
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    OK, after studying the examples I have figured out how to map the data to the chart and it works, here is the code for my case:

    Code:
    $worksheet = $workbook->add_worksheet("Population Chart");
    $worksheet->add_write_handler(qr[\w], \&store_string_widths);
    my $chart1 = $workbook->add_chart( type => 'bar', embedded => 1 );
    
    # Configure the series.
    $chart1->add_series(
          categories => '=Summary!$A$2:$A$row',
          values     => '=Summary!$G$2:$G$row',
          name       => 'World Population',
          );
    
    # Add another series.
    $chart1->add_series(
         categories => '=Summary!$A$2:$A$row',
         values     => '=Summary!$F$2:$F$row',
         name       => 'World Surface Area',
         );
    
    # Add some labels.
    $chart1->set_title( name => 'Results of Population analysis' );
    $chart1->set_x_axis( name => 'Countries' );
    $chart1->set_y_axis( name => 'Population' );
    
    # Insert the chart into the main worksheet.
    $worksheet->insert_chart( 'A2', $chart1 );
    autofit_columns($worksheet);
    Thank you for your help.
    Rgds.
    Terry

IMN logo majestic logo threadwatch logo seochat tools logo