Creating last worksheet to compute totals

Topics: User Forum
Jan 22, 2010 at 4:54 PM

 

Been working on this for awhile.

I am writing grades to a spreadsheet and have all my worksheets created.  Now I want to create the last worksheet and create my formula to calculate totals and final grade.

 

Here is what it looks like so far.

 

$result = $db->sql_query("SELECT a.Session_ID, b.SOMS_KEY, b.Name_Last, b.Name_First, b.UID, g.Group_Name, a.IRAT_Raw, a.IRAT_Grade, a.GRAT_Raw, a.GRAT_Grade, a.AppEx_Raw, a.AppEx_Grade, s.Session_Detail, s.Session_Name FROM ".$prefix."_tl_session_grades a
JOIN ".$prefix."_tl_session s ON (a.Session_ID = s.Session_ID)                          
JOIN ".$prefix."_tl_students b ON (a.SOMS_KEY = b.SOMS_KEY)
JOIN ".$prefix."_tl_group_students c ON (a.SOMS_KEY = c.SOMS_KEY)
JOIN ".$prefix."_tl_groups g ON (c.Group_ID = g.Group_ID)
WHERE s.Course_Number = '$Course_Number'
AND a.Academic_Year = '$query_Year'
GROUP BY a.Session_ID, a.SOMS_KEY  
ORDER BY a.Session_ID, b.Name_Last ASC");
if (!$result) {
    echo("<p>Error performing query: " . mysql_error() . "</p>");
    exit();   
}    
$session = 0;  
$sheet = 0;
while ($row = $db->sql_fetchrow($result)) {
    $SID = (int)$row['Session_ID'];
    if ($SID != $session) {
        
        $session = $SID;
        $datarow=2;
        if($sheet) $objPHPExcel->createSheet();
        
    // Set default font
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(12);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        $objPHPExcel->setActiveSheetIndex($sheet)
        ->setCellValue('A1', 'Last Name')
        ->setCellValue('B1', 'First Name')
        ->setCellValue('C1', 'UID')
        ->setCellValue('D1', 'Group')
        ->setCellValue('E1', 'Session')
        ->setCellValue('F1', 'Session Name')
        ->setCellValue('G1', 'Irat')
        ->setCellValue('H1', 'Irat Grade')
        ->setCellValue('I1', 'Grat')
        ->setCellValue('J1', 'Grat Grade')
        ->setCellValue('K1', 'Appex')
        ->setCellValue('L1', 'Appex Grade');
        $objPHPExcel->getActiveSheet()->setTitle($row['Session_Detail']);
        $sheet++;
    }
    
    // Set default font
//$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
//$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);

//$activeSheet->getColumnDimension($column++)->setAutoSize(true);
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

    extract($row);
    $objPHPExcel->getActiveSheet()->getCell('A'.$datarow)->setValue($Name_Last);
    $objPHPExcel->getActiveSheet()->getCell('B'.$datarow)->setValue($Name_First);
    $objPHPExcel->getActiveSheet()->getCell('C'.$datarow)->setValue($UID);
    $objPHPExcel->getActiveSheet()->getCell('D'.$datarow)->setValue($Group_Name);
    $objPHPExcel->getActiveSheet()->getCell('E'.$datarow)->setValue($Session_Detail);
    $objPHPExcel->getActiveSheet()->getCell('F'.$datarow)->setValue($Session_Name);
    $objPHPExcel->getActiveSheet()->getCell('G'.$datarow)->setValue($IRAT_Raw);
    $objPHPExcel->getActiveSheet()->getCell('H'.$datarow)->setValue($IRAT_Grade / 100);
    $objPHPExcel->getActiveSheet()->getStyle('H'.$datarow)->getNumberFormat()->setFormatCode('0.00%');    
    $objPHPExcel->getActiveSheet()->getCell('I'.$datarow)->setValue($GRAT_Raw);
    $objPHPExcel->getActiveSheet()->getCell('J'.$datarow)->setValue($GRAT_Grade / 100);
    $objPHPExcel->getActiveSheet()->getStyle('J'.$datarow)->getNumberFormat()->setFormatCode('0.00%');    
    $objPHPExcel->getActiveSheet()->getCell('K'.$datarow)->setValue($AppEx_Raw);
    $objPHPExcel->getActiveSheet()->getCell('L'.$datarow)->setValue($AppEx_Grade / 100);
    $objPHPExcel->getActiveSheet()->getStyle('L'.$datarow)->getNumberFormat()->setFormatCode('0.00%');        
    
    //Auto size the columns
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
    
    //Align the columns centered.
    $objPHPExcel->getActiveSheet()->getStyle('G'.$datarow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('H'.$datarow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('I'.$datarow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('J'.$datarow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('K'.$datarow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle('L'.$datarow)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $datarow++;
 }

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);    
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>

 

I want to now create the last worksheet and write totals with a bunch of formulas.   I tried to stick this outside my loop and got an error.

 

Fatal error: Uncaught exception 'Exception' with message 'Active sheet index is out of bounds.'

 

//Write totals
 $objPHPExcel->setActiveSheetIndex($sheet)
        ->setCellValue('A1', 'Last Name')
        ->setCellValue('B1', 'First Name')
        ->setCellValue('C1', 'UID')
        ->setCellValue('D1', 'Group')
        ->setCellValue('E1', 'IRAT Avg')
        ->setCellValue('F1', 'GRAT Avg')
        ->setCellValue('G1', 'Appex Avg')
        ->setCellValue('H1', 'GRAT/Appex Avg')
        ->setCellValue('I1', 'Multiplier')
        ->setCellValue('J1', 'Group / m')
        ->setCellValue('K1', 'Final TL Grade');       
        $objPHPExcel->getActiveSheet()->setTitle('Totals');
        
    $objPHPExcel->getActiveSheet()->getCell('A'.$datarow)->setValue($Name_Last);
    $objPHPExcel->getActiveSheet()->getCell('B'.$datarow)->setValue($Name_First);
    $objPHPExcel->getActiveSheet()->getCell('C'.$datarow)->setValue($UID);
    $objPHPExcel->getActiveSheet()->getCell('D'.$datarow)->setValue($Group_Name);

 

 

 

Developer
Jan 22, 2010 at 5:22 PM

So it is this line that is causing the fatal error: 'Active sheet index is out of bounds.'

$objPHPExcel->setActiveSheetIndex($sheet);


That can only mean that $sheet is too high. For example, if you have 5 sheets in the workbook, you can only set the active index to 0, 1, 2, 3, or 4.

Do a check by echoing some numbers immediately before the above line:

var_dump(count($this->_workSheetCollection));
var_dump($sheet);
$objPHPExcel->setActiveSheetIndex($sheet);

The first number is the number of sheets you have so far.
The second number is the sheet index (0-based) you are attempting to set as the active one.

What do you get?

Jan 22, 2010 at 5:31 PM

Fatal error: Using $this when not in object context

Developer
Jan 22, 2010 at 5:40 PM

Sorry, I meant:

var_dump(count($objPHPExcel->getAllSheets()));
var_dump($sheet);
$objPHPExcel->setActiveSheetIndex($sheet);

Jan 22, 2010 at 6:57 PM

It tells me int(2) int(2)

I have two worksheets I'm trying to write, and a third I'm trying to create at the end to then write totals and compute final grades.  Some of these courses will have many more sheets.. one will have 12 that I know of.

 

Jan 22, 2010 at 7:21 PM

I wasn't creating the sheet.

 

$num_ws = count($objPHPExcel->getAllSheets());
if($num_ws) $objPHPExcel->createSheet();

 

 

DOH!!!