«

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..

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