Dynamic sheet names.

Feb 11, 2010 at 7:02 PM

If all my worksheet names are written dynamical ( I will not know the names of them) how can I build a "Totals" worksheet at the end and then calculate and average of data rows?

I have 100 students and their grades.  Each worksheet is a separate series of exams given.  At the end I want to write totals.

I have seen where people have used $worksheet = $excel->getSheetByName('Sheet 3'); but like I said I will not know the names.  I have 6 different courses all with different exams. 

 

Here is an example.

 

Worksheet #1 is named Cardiovascular Pathology.

Worksheet #2 is named Autonomic Pharmacology

Worksheet #3 is called CNS Infections

 

Worksheet #4 is my totals I want to write for each student. 

If E is where I want to calculate my averages how do I write the equation and pull the info from each worksheet?

$objPHPExcel->getActiveSheet()->getCell('E'.$dr)->setValue(??????);

 

 

Feb 11, 2010 at 7:17 PM

I also stuck the name of each worksheet in this cell.  'E'.$datarow

 

So each worksheet has a redundant column of the exam name.  Why I did this I don't know, but it is there.

Feb 11, 2010 at 7:56 PM

If I do this

$worksheets = $objPHPExcel->getSheetNames();
            
            print_r($worksheets);
            die();

I get...

 

Array ( [0] => Cardiovascular Pathology [1] => Autonomic Pharmacology [2] => Totals )


Totals is the worksheet I'm trying to create.

hmmm. So a formula with something like...

=AVG('$worksheets[i]'!'E'.$dr...etc)

 
 I need to insert this somewhere with setValue();

Developer
Feb 18, 2010 at 11:32 PM

I'm a bit unsure whether you are asking how the formula should look like in Excel or how to insert the Excel formula using PHPExcel.