Win32::OLE Excel Chart SeriesCollection. Problem changing from Excel 2000 to 2002

Discussion in 'Perl Misc' started by moller@notvalid.se, Jul 1, 2004.

  1. Guest

    Win32::OLE Excel Chart SeriesCollection.
    Problem when changing from Excel 2000 to 2002


    I have a data sheet with 5 columns of data called $Sheet.
    In Excel 2000 each of the columns ends up in a separate series
    when creating a chart with the 5 columns in the datarange.

    When switching from Excel 2000 to Excel 2002 the chart creation
    takes the 5 column range and sets the first 4 columns as the
    first series.

    Does anyone have any idea how to work around this or why it's happening?
    Or pointers to relevant documentation somwhere. Or any other
    suggestions



    A small snip of the relevant code for context follows.

    # My datarange
    my $Range = $Sheet->Range('B2:F38');

    # Create a new chart
    my $num_of_Sheets = $Book->Worksheets->{Count};
    my $chart = $Book->Charts->Add({After => $Book->Worksheets($num_of_Sheets)})
    || die Win32::OLE->LastError();

    # Name the chart
    $chart->{Name} = $Cdays[$dow-1];

    $chart->SetSourceData({Source => $Range,
    PlotBy => xlColumns
    });

    # Put Chart on a page of its own
    $chart->Location({Where => xlLocationAsNewSheet });

    $chart->SeriesCollection(1)->{AxisGroup} = xlPrimary;
    $chart->SeriesCollection(1)->{ChartType} = xlColumnStacked;
    $chart->SeriesCollection(1)->Fill->TwoColorGradient(1,1);
    $chart->SeriesCollection(1)->Fill->ForeColor->{SchemeColor} = 10;
    $chart->SeriesCollection(1)->Fill->BackColor->{SchemeColor} = 1;

    $chart->SeriesCollection(2)->{AxisGroup} = xlPrimary;
    $chart->SeriesCollection(2)->{ChartType} = xlColumnStacked;
    $chart->SeriesCollection(2)->Fill->TwoColorGradient(1,1);
    $chart->SeriesCollection(2)->Fill->ForeColor->{SchemeColor} = 3;
    $chart->SeriesCollection(2)->Fill->BackColor->{SchemeColor} = 1;

    # Crashes on the following line with
    # Can't use an undefined value as a HASH reference at ..... file name and linenr
    $chart->SeriesCollection(3)->{AxisGroup} = xlSecondary;
    $chart->SeriesCollection(3)->{ChartType} = xlLineMarkers;
    $chart->SeriesCollection(3)->{MarkerBackgroundColorIndex} = 7;
    $chart->SeriesCollection(3)->{MarkerForegroundColorIndex} = 7;
    $chart->SeriesCollection(3)->{MarkerStyle} = xlTriangle;
    $chart->SeriesCollection(3)->Border->{Colorindex} = 7;
    $chart->SeriesCollection(3)->Border->{Weight} = xlMedium;

    SNIP REST..
    , Jul 1, 2004
    #1
    1. Advertising

  2. Guest

    writes:

    >
    > Win32::OLE Excel Chart SeriesCollection.
    > Problem when changing from Excel 2000 to 2002
    >
    >
    > I have a data sheet with 5 columns of data called $Sheet.
    > In Excel 2000 each of the columns ends up in a separate series
    > when creating a chart with the 5 columns in the datarange.
    >
    > When switching from Excel 2000 to Excel 2002 the chart creation
    > takes the 5 column range and sets the first 4 columns as the
    > first series.
    >
    > Does anyone have any idea how to work around this or why it's happening?
    > Or pointers to relevant documentation somwhere. Or any other
    > suggestions


    I found the problem, locale... or lack of it.

    On the first computer the WIN-XP was swedish and MSOffice english [1]
    so when perl used dot as a decimal separator it worked fine.
    Om the new computer both XP and MSOffice was swedish and it broke
    on 23.45 when it expected 23,45.

    Example Data:
    08:00-08:15 | 148 | 56 | 43.275 | 80
    08:15-08:30 | 153 | 18 | 11.999 | 90

    Column data should be interped as follows

    text | number | number | number | number

    but since dot was no longer decimal separator
    excel interped it as

    text | number | number | text | number

    and then decided that the first four
    columns should be DataSeries(1). That
    gave me this data

    text | number
    08:15-08:30 153 18 11.999 | 90

    I wonder why???

    /moller

    [1]. Somthing I missed
    , Jul 5, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brian Shade

    Excel Chart SeriesCollection Error

    Brian Shade, Sep 9, 2004, in forum: Perl
    Replies:
    0
    Views:
    991
    Brian Shade
    Sep 9, 2004
  2. C Did
    Replies:
    3
    Views:
    3,929
    Chris Lithgow
    Jun 20, 2006
  3. Lance Hoffmeyer
    Replies:
    0
    Views:
    231
    Lance Hoffmeyer
    Nov 17, 2003
  4. Dirk

    MS Project 2000 and Win32:OLE

    Dirk, Jan 16, 2004, in forum: Perl Misc
    Replies:
    1
    Views:
    342
    Chris
    Jan 16, 2004
  5. axroh
    Replies:
    1
    Views:
    200
    axroh
    Jul 10, 2007
Loading...

Share This Page