Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re: XLSX::Chart legend question

by swl (Vicar)
on Mar 08, 2022 at 08:40 UTC ( #11141906=note: print w/replies, xml ) Need Help??


in reply to XLSX::Chart legend question

Can you reduce the problem to an SSCCE? That will increase the chances of people being able to help if they have not tried this exact process before.

Replies are listed 'Best First'.
Re^2: XLSX::Chart legend question
by smh (Acolyte) on Mar 09, 2022 at 02:55 UTC

    Below is the section of eth code that writes the worksheet and adds the chart.

    my $workbook = Excel::Writer::XLSX->new( 'chart.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 = [ @H ] #my $data = [ @D ]; my $h = <CSV>; chomp( $h); my @H = split( ',', $h ); $worksheet->write( 0,0, \@H, $bold ); # write_row() my $i = 1; while( <CSV> ) { chomp; my @B = split( ',', $_ ); $worksheet->write( $i++, 0, \@B ); # write_row() } close( CSV ); # Create a new chart object. In this case an embedded chart. my $chart = $workbook->add_chart( type => 'scatter', subtype => 'stacked', embedded => 1 ); $chart->set_size( width => 1020, height => 720 ); $chart->set_title ( name => 'NEXT: Near End Crosstalk', name_font => { name => 'Calibri', #'Arial', size => 22, bold => 1, #italic => 1, }, ); $chart->set_x_axis( name => 'Frequncy (Hz)', num_format => '#,##0', min => 10, max => 80000, crossing => 0, # position_axis => 'on_tick', log_base => 10, label_position => 'next_to', major_tick_mark => 'outside', minor_tick_mark => 'outside', major_gridlines => { visible => 1, line => { color => '#E0E0E0', width => 0.75, dash_type => 'solid' } }, minor_gridlines => { visible => 1, line => { color => '#E0E0E0', width => 0.75, dash_type => 'solid' } }, name_font => { name => 'Calibri', #'Arial', size => 14, bold => 1, #italic => 1, } ); $chart->set_y_axis( name => 'NEXT (DB)', num_format => '0E+00', #'###0', min => 0, max => 2e-4, # minor_unit => 0.4, # major_unit => 2, # interval_tick => 4, major_tick_mark => 'outside', minor_tick_mark => 'outside', major_gridlines => { visible => 1, line => { color => '#E0E0E0', width => 0.75, dash_type => 'solid' } }, minor_gridlines => { visible => 1, line => { color => '#E0E0E0', width => 0.75, dash_type => 'solid' } }, name_font => { name => 'Calibri', #'Arial', size => 14, bold => 1, } ); $chart->set_legend( position => 'overlay_top_right', #'top_right', fill => 'solidfill', ); #$chart->set_legend({ 'font'=> { 'bold' => 1, 'italic' => 1}}); <<== t +aken from CPAN example, it generates error # Configure the first series. $chart->add_series( name => '=Sheet1!$B$1', categories => '=Sheet1!$A$2:$A$6701', values => '=Sheet1!$B$2:$B$7701', line => { #color => 'red', width => 2.0 }, ); # Configure the first series. $chart->add_series( name => '=Sheet1!$C$1', categories => '=Sheet1!$A$2:$A$6701', values => '=Sheet1!$C$2:$C$7701', line => { #color => 'blue', width => 2.0 }, ); # Configure the first series. $chart->add_series( name => '=Sheet1!$D$1', categories => '=Sheet1!$A$2:$A$6701', values => '=Sheet1!$D$2:$D$7701', line => { #color => 'magenta', width => 2.0 }, ); # Configure the first series. $chart->add_series( name => '=Sheet1!$E$1', categories => '=Sheet1!$A$2:$A$6701', values => '=Sheet1!$E$2:$E$7701', line => { #color => 'cyan', width => 2.0 }, ); # Set an Excel chart style. Blue colors with white outline and shadow. $chart->set_style( 23 ); # Insert the chart into the worksheet (with an offset). $worksheet->insert_chart( 'G2', $chart , 0, 0 ); ## last twp #s are X +_offset, Y_offset $workbook->close();
      Your SSCCE was neither self-contained nor correct/compilable.

      To be able to help you, I added the use-modules at the top, plus I aliased the CSV bareword-filehandle to the built-in DATA bareword-filehandle, so that reading from <CSV> will actually read from the __DATA__ section at the end of the file:

      #!perl use 5.012; # strict, // use warnings; use Excel::Writer::XLSX; *CSV = *DATA; # makes CSV an alias to the __DATA__ section, so I can + add CSV data to the end of the script, rather than creating a separa +te file; you will not want this line in your application
      ... to the top. And then I created the __DATA__ section at the end of the script, with some CSV-style data:
      __DATA__ freq,b xtalk 10,0.000001 20,0.000002 50,0.000005 100,1.00E-05 200,2.00E-05 500,5.00E-05 1000,1.00E-04

      (I only did columns A:B; data in C:E wasn't necessary to prove that the legend and chart will work.)

      For your program, you won't need the *CSV = *DATA, nor the __DATA__ section at the end, because presumably you already opened CSV in some line of code not shown to us. (Read perldoc -f open, and learn to use lexical filehandles rather than the old-fashioned bareword filehandles. And don't forget to check for success on your open: it's easiest to do that automatically by using use autodie;)

      With the solidfill legend code in there, it gives me the warning

      Can't use string ("solidfill") as a HASH ref while "strict refs" in us +e at c:/usr/local/apps/berrybrew/perls/system/perl/site/lib/Excel/Wri +ter/XLSX/Chart.pm line 1158.
      and doesn't create the chart at all. If I comment that out, so none of the set_legend calls are active, I get a chart that I think matches your expectation (though the legend is on the center-right and isn't formatted).

      I don't know where you got fill=>'solidfill', as I don't see "solidfill" anywhere in the chart formatting section. I tried

      $chart->set_legend( position => 'overlay_top_right', fill => { color => 'red' }, );
      ... and it put it over the chart in the top right with a red background, which is what I'd expect.

      You claimed

      #$chart->set_legend({ 'font'=> { 'bold' => 1, 'italic' => 1}}); <<== t +aken from CPAN example, it generates error
      ... but the documentation actually says in the set_legend() section,
      $chart->set_legend( font => { bold => 1, italic => 1 } );
      ... Notice, it only has one pair of braces, not two like in your code. Using exactly the line that the POD shows, I get a legend on the far right whose labels are bold and italic.

      Combining it all together

      $chart->set_legend( position => 'overlay_top_right', fill => { color => 'red' }, font => { 'bold' => 1, 'italic' => 1}, );

      I get upper right, red background, bold and italic, exactly as I would expect.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11141906]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2023-02-02 15:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I prefer not to run the latest version of Perl because:







    Results (19 votes). Check out past polls.

    Notices?