Chart SeriesCollection. Problem changing from Excel 2000 to 2002

[moller]

I'm using perl and Win32::OLE but I'm wondering if there
are any known issues before I delve more into a perl solution.

The problem is as follows:

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. So I only get 2 series :-(

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


A small snip of the relevant code for context follows.
(Anyone used to VB probably understand it)

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

 

[Jon Peltier]

What's in the first row of the data? If you want all five columns to be
plotted as separate series, make sure you have no column headers. The
behavior you describe is expected if the top cells of the first 4
columns are blank and the top cell of the fifth is not. Is the top cell
of the 5th column used as the legend entry for that series?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

I'm using perl and Win32::OLE but I'm wondering if there
are any known issues before I delve more into a perl solution.

The problem is as follows:

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. So I only get 2 series :-(

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


A small snip of the relevant code for context follows.
(Anyone used to VB probably understand it)

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

 

[moller]

What's in the first row of the data? If you want all five columns to
be plotted as separate series, make sure you have no column
headers. The behavior you describe is expected if the top cells of the
first 4 columns are blank and the top cell of the fifth is not. Is the
top cell of the 5th column used as the legend entry for that series?

All five columns have the top cell filled with a header text
written in bold. In Excel 2000 on XP-Pro I get the first column
on the x-axis. and the other four as data series on the y-axis.
Ans,Missed,AqT,Man ends up as labels in the legend.

In excel 2002 I get XP-Pro same version of perl same code I get
the data for Time,Ans,Missed,AqT on the x-axis like this
"08:00-08:15 148 56 43.275" and the Man datapoints on y-axis.


Example data in the range:
Time Ans Missed AqT Man
08:00-08:15 148 56 43.275 80
08:15-08:30 153 18 11.9996 90

 

[moller]

All five columns have the top cell filled with a header text
written in bold. In Excel 2000 on XP-Pro I get the first column
on the x-axis. and the other four as data series on the y-axis.
Ans,Missed,AqT,Man ends up as labels in the legend.

In excel 2002 I get XP-Pro same version of perl same code I get
the data for Time,Ans,Missed,AqT on the x-axis like this
"08:00-08:15 148 56 43.275" and the Man datapoints on y-axis.


Example data in the range:
Time Ans Missed AqT Man
08:00-08:15 148 56 43.275 80
08:15-08:30 153 18 11.9996 90
.
.
.
16:45-17:00 12 1 2.7692 50

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

 

[Jon Peltier]

Excel tries its best to figure out what we want when we don't tell it
enough. To ensure that Excel uses the columns you want for categories
and the rows you want for series names, make sure the cell(s) at the
intersection of these rows and columns are blank (completely clear, not
just "" as a formula might return). So your example data would look like:

Ans Missed AqT Man
08:00-08:15 148 56 43.275 80
08:15-08:30 153 18 11.9996 90

 

[moller]

Excel tries its best to figure out what we want when we don't tell it
enough. To ensure that Excel uses the columns you want for categories
and the rows you want for series names, make sure the cell(s) at the
intersection of these rows and columns are blank (completely clear,
not just "" as a formula might return). So your example data would
look like:

Ans Missed AqT Man
08:00-08:15 148 56 43.275 80
08:15-08:30 153 18 11.9996 90
.
.
.
16:45-17:00 12 1 2.7692 50

When the fourth column data was interpreted by Excel as text, it
decided since it couldn't plot the text, that it would use it
anyway. The only way it could figure to do this was to give you four
columns of category labels, and only one series. Change the column of
incorrectly identified text into decimal fractions (here's the
language setting problem), and you get your other series back, with
the one column of category labels.

Yes when I got that part right everything else worked as expected.

This might seem like a strange treatment, but it can be handy. I have
used multi-column category labels, which are separated by carriage
returns along the bottom axis, to give extra information about the
plotted points.

I'm going to use this. Thanks for the tip and help.

/moller - Off to Scottland for a week.