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