Problem with charts on multiple worksheets

Topics: Developer Forum, User Forum
Mar 25, 2013 at 11:53 AM
Edited Mar 26, 2013 at 9:36 AM
Hi all,

at the moment my workbook shows the same charts on all worksheets, as long as one worksheet remains named "Worksheet". The problem is, all charts refer to this one worksheet called "Worksheet", and I'd like to change that behavior.

I tried different notations for replacing "Worksheet" by the name of my sheets, but without success. $name is used for the name and $tab_count for index.

Here ist the code:
//  Set the Labels for each data series we want to plot
//      Datatype
//      Cell reference for data
//      Format Code
//      Number of datapoints in series
//      Data values
//      Data Marker
$dataseriesLabels2 = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$1', null, 1),
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$1', null, 1),
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$1', null, 1),
);

//  Set the X-Axis Labels
//      Datatype
//      Cell reference for data
//      Format Code
//      Number of datapoints in series
//      Data values
//      Data Marker
$xAxisTickValues2 = array(
    new PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$2:$A$13', null, 12),);
//  Set the Data values for each data series we want to plot
//      Datatype
//      Cell reference for data
//      Format Code
//      Number of datapoints in series
//      Data values
//      Data Marker
$dataSeriesValues2 = array(
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$2:$B$13', null, 12),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$2:$C$13', null, 12),
    new PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$2:$D$13', null, 12)
);
Can anyone help me finding the correct syntax?

Thanks in advance!

Regards,
Christian
Mar 27, 2013 at 12:03 PM
No $name or $tab_count in the example...

A simple substitution should suffice unless it has spaces in the name.
new PHPExcel_Chart_DataSeriesValues('String', 'MySheet!$B$1', null, 1),

new PHPExcel_Chart_DataSeriesValues('String', '\'A Sheet\'!$B$1', null, 1),
Mar 27, 2013 at 12:32 PM
Hi LWol,

thanks for your advice.
No $name or $tab_count in the example...
Right, I was looking for the right syntax to change "Worksheet" by $name. Sorry for the confusion.

In the meantime, I found a working solution:
I inserted 
$s_n1="'".$t_User_Name."'!B$1";   # notice the missing "$" between "!" and the cell identifier.
$s_n2="'".$t_User_Name."'!C$1";
$s_n3="'".$t_User_Name."'!D$1";
$s_n4="'".$t_User_Name."'!A$2:A$13";
$s_n5="'".$t_User_Name."'!B$2:B$13";
$s_n6="'".$t_User_Name."'!C$2:C$13";
$s_n7="'".$t_User_Name."'!D$2:D$13";
and changed
$dataseriesLabels2 = array(
    new PHPExcel_Chart_DataSeriesValues('String', $s_n1, null, 1),
    new PHPExcel_Chart_DataSeriesValues('String', $s_n2, null, 1),
    new PHPExcel_Chart_DataSeriesValues('String', $s_n3, null, 1),
);

$xAxisTickValues2 = array(
    new PHPExcel_Chart_DataSeriesValues('String', $s_n4, null, 12),);

$dataSeriesValues2 = array(
    new PHPExcel_Chart_DataSeriesValues('Number', $s_n5, null, 12),
    new PHPExcel_Chart_DataSeriesValues('Number', $s_n6, null, 12),
    new PHPExcel_Chart_DataSeriesValues('Number', $s_n7, null, 12)
);
Now each chart has it`s own Worksheet and no unnecessary "Worksheet" in the workbook.

Regards,
Christian