Failed to create multiple worksheets in PHP Excel

Question

Hi, I am creating several sheets in PHP Excel, but it does not work. I encountered one problem: "You tried to set the sheet with an active index outside the borders: 1. The actual number of sheets is 1."

Here is my code:

function downloadReportInExcel($excelSheetDetails, $headers, $resultSetFields, $resultSetPointer) {
    /** Error reporting */
    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('Asia/Calcutta');

    if (PHP_SAPI == 'cli')
        die('This example should only be run from a Web Browser');

    /** Include PHPExcel */
    require_once '../../lib/PHPExcel.php';


    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    //Enable cell caching and serialize it so that memory footprint is small and performance is high
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

    //Setting a default height for all rows
    $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);

    // Set document properties
    $objPHPExcel->getProperties()->setCreator($excelSheetDetails['createdBy']) //"Maarten Balliauw"
            ->setLastModifiedBy($excelSheetDetails['modifiedBy']) //"Maarten Balliauw"
            ->setTitle($excelSheetDetails['title']) //"Office 2007 XLSX Test Document"
            ->setSubject($excelSheetDetails['subject']) //"Office 2007 XLSX Test Document"
            ->setDescription($excelSheetDetails['description']) //"Test document for Office 2007 XLSX, generated using PHP classes."
            ->setKeywords("office 2007 openxml php")
            ->setCategory($excelSheetDetails['category']); //"Test result file"
    // Add data
    $i = 1;
    $j = 65;
    $length = count($headers);
    for ($count = 0; $count < $length; $count++) {
        //Add headers
        foreach ($headers[$count] AS $headerValue)
        {
            //echo $headerValue;
            $objPHPExcel->setActiveSheetIndex($count)
                    ->setCellValue('' . chr($j) . $i . '', $headerValue);
            $objPHPExcel->getActiveSheet()->getColumnDimension(chr($j))->setAutoSize(true);
            $j++;
        }
        //~ exit;
        $k = $j - 1;
        $i+= 1;
        $j = 65;

        $styleArray = array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );
        $objPHPExcel->getActiveSheet()->getStyle(chr($j) . '1:' . chr($k) . '1')->applyFromArray($styleArray);

        foreach ($resultSetPointer[$count] AS $result)
        {
            $resultKeyIndex = -1;
            //print_r($result);
            for ($counter = $j; $counter <= $k; $counter++)
            {
                $objPHPExcel->setActiveSheetIndex($count)
                        ->setCellValue('' . chr($counter) . $i . '', $result[$resultSetFields[$count][++$resultKeyIndex]]);
                //echo $result[$resultKeyIndex].'<br />';
            }
            $i++;
        }
        //exit;
        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($excelSheetDetails['worksheetTitle']); //"Simple"
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex($count);
    }
    //print_r($objPHPExcel);exit;
    // Redirect output to a client’s web browser (Excel5)
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $excelSheetDetails['fileName'] . '.xls"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.0

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

    //Break cyclic references to clear worksheet from memory
    $objPHPExcel->disconnectWorksheets();
    unset($objPHPExcel);

    exit;
}

Here I mentioned the whole parameter I passed in this function

for($count = 0; $count < $studentWiseAttendanceLen; $count++) {
    $finalArray[0][$count]['name']      = $studentWiseAttendance[$count]['student'];
    $finalArray[0][$count]['present']   = $studentWiseAttendance[$count]['presentPercentage'];
    $finalArray[0][$count]['absent']    = $studentWiseAttendance[$count]['absentPercentage'];
    $finalArray[0][$count]['late']      = $studentWiseAttendance[$count]['latePercentage'];
    $finalArray[0][$count]['overall']   = $studentWiseAttendance[$count]['overallPresent'];
}
for($count = 0; $count < $dateWiseAttendanceLen; $count++) {
    $finalArray[1][$count]['attendancedatetime']    = $dateWiseAttendance[$count]['attendanceTakenTime'];
    $finalArray[1][$count]['present']               = $dateWiseAttendance[$count]['presentPercentage'];
    $finalArray[1][$count]['absent']                = $dateWiseAttendance[$count]['absentPercentage'];
    $finalArray[1][$count]['late']                  = $dateWiseAttendance[$count]['latePercentage'];
    $finalArray[1][$count]['totalStudent']          = $dateWiseAttendance[$count]['totalStudent'];
}

//echo("<script>console.log('PHP: ". print_r($dateWiseAttendance) ."');</script>");
// Array fields
$resultSetFields[0] = array(
    'name',
    'present',
    'absent',
    'late',
    'overall'
);
$resultSetFields[1] = array(
    'attendancedatetime',
    'present',
    'absent',
    'late',
    'totalStudent'
);

// Excel details
$excelSheetDetails = array(
    'createdBy' => 'Admin',
    'modifiedBy' => 'Admin',
    'title' => $subjectName . ' Attendance Report',
    'subject' => 'Attendance Report',
    'description' => 'Attendance Report for students in percentage',
    'category' => 'Report file',
    'worksheetTitle' => 'Attendance Details',
    'fileName' => $subjectName . ' Attendance_Report'
);
// File headers
$headers[0] = array(
    'STUDENT NAME',
    'PRESENT(%)',
    'ABSENT(%)',
    'LATE(%)',
    'OVERALL PRESENT(%)'
);
$headers[1] = array(
    'CLASS TAKEN',
    'PRESENT(%)',
    'ABSENT(%)',
    'LATE(%)',
    'OVERALL STUDENT PRESENT'
);
downloadReportInExcel($excelSheetDetails, $headers, $resultSetFields, $finalArray);

Answer 1

You need

$objPHPExcel->createSheet();

before new sheets