mercredi 25 février 2015

Not able to create multiple worksheet in PHP Excel

Hi I am creating multiple worksheet in PHP Excel but it is not working. I am facing one issue "You tried to set a sheet active by the out of bounds index: 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 all 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);




Aucun commentaire:

Enregistrer un commentaire