0

I am creating an xlsx-file, and I want to add a column chart, displaying 2 values for a calenderweek.

However, I don't know how many charts (depending on the items available) and how many calenderweeks I'll have to display (depending on the time-frame selected by the user).

Creating the weeks and the data-table is not that problem, that is working fine, iterating through my data with two nested foreach-loops, using setCellValueByColumnAndRow().

My problem is, I have a dynamic number of columns and a dynamic number of rows. So I need a hint, how to transfer

$value = new PHPExcel_Chart_DataSeriesValues('Number', 
   'Worksheet!$C$9:$H$9', 
   NULL, 
   $numberOfWeeks);

array_push($values, $value);

into something like this:

$value = new PHPExcel_Chart_DataSeriesValues('Number', 
'Worksheet!$' . $START_COLUMN . '$' . $CURRENT_ROW . ':$' . $END_COLUMN . '$' . $CURRENT_ROW,
NULL,
$numberOfWeeks); 

array_push($values, $value);

How do I create the with a chart and a dynamic number of columns?

EDIT:

Within the snippet, there was a missing "!" (Thanks to Mark Barker). Fixed this in my first post. Because I was using Integer-Values to iterate through the colums, I had to convert the integer back to a letter. A solution for this is posted here. In the meantime, Mark poked my nose to the function, build in of course ;-) , PHPExcel_Cell::stringFromColumnIndex()

Community
  • 1
  • 1
Achim S
  • 1
  • 1

2 Answers2

0

Missing ! after Worksheet is the obvious problem.... MS Excel cares about such things.... and missing $ for the absolute references:

$value = new PHPExcel_Chart_DataSeriesValues('Number',
    'Worksheet!$' . $START_COLUMN . '$' . $CURRENT_ROW . ':$' . $END_COLUMN . '$' . $CURRENT_ROW,
    NULL,
    $numberOfWeeks);

But this is really a simple matter of PHP string concatenation 101

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 1
    Thank you! there was indeed something missing, but the main problem was, that i have the numeric represenation of the column, not the alpabetical value. – Achim S Sep 23 '14 at 07:02
0

While i am writing this answer the PHPExcel is dead already use PhpSpreadsheet instead.

Use Coordinate class method stringFromColumnIndex() which will return String of Excel Column from getting integer index number, (you can also use array's count if you have array).

$column = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(2);
// $column = 'B'

Here it will count from 1 so 1 = 'A', 2 = 'B', 3 = 'C', etc.

Be cautious; Dont use 0, it will results in 'Z'.

Reference: https://phpspreadsheet.readthedocs.io/en/latest/

Haritsinh Gohil
  • 5,818
  • 48
  • 50
  • Do you have an idea on how to change the data range of an existing chart? My chart is in a template with two rows, headings `=RawData!$A$1:$H$1` and data `=RawData$A$2 : $H$2`. The chart exists on Sheet `ChartData`. As I read in data to `RawData`, the data could expand (or shrink) out to column Q or Z (or ???), so accordingly I need recast the chart to match the new size of the data set. Example: headings `=RawData!$A$1:$Q$1` and data `=RawData!$A$2 : $Q$2`. But I can't figure out how to do it. – rolinger Jul 30 '22 at 14:30
  • It is quite not easy to understand it fully you can ask it as new question stackoverflow community will definitely help you, but as I understood you want to do it dynamically then do it like get the length of data using `data.length` in php and then convert it to cell variable using `stringFromColumnIndex` and then you will get the dynamic cell name. – Haritsinh Gohil Aug 01 '22 at 08:36
  • Hey, I clarified question yesterday with this post: https://stackoverflow.com/questions/73179026/phpspreadsheet-how-to-change-the-data-set-assigned-to-a-chart-in-a-template-file – rolinger Aug 01 '22 at 12:41