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

Hi, I have been searching this for long. For an excel chart is there a option to set the x-axis labels at the bottom(outside of chart) not under 0 always. This looks bad when there are negative values in Line chanrt. I have checked in Spreadsheet:WriteExcel and Win32:OLE modules but i cudnt find any functions to do this. I might be missing something or dint look everywhere. Please help me.

  • Comment on How to set x-axis label at the bottom(outside of chart) in a line chart

Replies are listed 'Best First'.
Re: How to set x-axis label at the bottom(outside of chart) in a line chart
by jmcnamara (Monsignor) on Mar 21, 2012 at 17:46 UTC

    You can do this in Excel::Writer::XLSX using the label_position chart axis option set to low. Here is a small working example: 

    
    use strict;
    use warnings;
    use Excel::Writer::XLSX;

    # Create a workbook, a worksheet and a chart.
    my $workbook  = Excel::Writer::XLSX->new( 'chart_axis.xlsx' );
    my $worksheet = $workbook->add_worksheet();
    my $chart     = $workbook->add_chart( type => 'column', embedded =
+> 1 );

    # Some data to add to plot.
    my $data = [
        [  1,  2, -3, 4,  5  ],
        [ -2,  4,  6, 8,  10 ],
        [  3, -6,  9, 12, 15 ],

    ];

    # Write the data to the worksheet.
    $worksheet->write( 'A1', $data );

    # Add three data series to the chart.
    $chart->add_series( values => '=Sheet1!$A$1:$A$5' );
    $chart->add_series( values => '=Sheet1!$B$1:$B$5' );
    $chart->add_series( values => '=Sheet1!$C$1:$C$5' );

    $chart->set_x_axis( label_position => 'low' );

    # Insert the embedded chart.
    $worksheet->insert_chart( 'E9', $chart );


    __END__

    [download]

    See the Excel::Writer::XLSX::Chart docs for details.

    --
    John.

[reply]
[d/l]

      Hi John, thanks a lot,it did the work.Its a new planet out there which i wasnt awre of.Thanks again.

[reply]

      HI John, one small thing...can we rotate the x-axis labels like 90% so that they show up verical? Do you have any code for that?Sorry for bugging you again...

[reply]

        There isn't currently any way to manipulate the fonts on charts. This is a planned feature but I can't say when it will be available.

        --
        John.

[reply]
Re: How to set x-axis label at the bottom(outside of chart) in a line chart
by blakew (Monk) on Mar 21, 2012 at 17:05 UTC
    Using Win32::OLE you have access to the MS object models.

    These are the steps I took to try to answer your question:

    1. Open Excel
    2. Open Visual Basic in the Developer tab
    3. Hit F1
    4. Search "Chart"
    5. Click "Chart Object Members"
    6. Click "Axes"
    7. Search "Axis"
    8. Click "Axis Object Members"
    9. Click "MinimumScale"

    Now you know how to get the chart object, get the x-axis, set the minimum value to 0.

    Update: it looks like you want to set the Position property of the DisplayUnitLabel where you can specify a custom position. However you use the word "labels" but I'm not sure if you mean Title. I misread your post initially, it's pretty confusing.

[reply]

      I was trying to get perl codes to do this. I have the perl script for generating line charts fine but i cant get the x-axis labels set at the bottom(outside of chart) of the line chart.I dont want the labels to be fixed under the 0 axis. I want it outside of the chart so it will look good when there is negative values.Please let me know if you have it.

[reply]
        What did you try. Did you try manipulating the label?
[reply]

Back to Seekers of Perl Wisdom