I have many arrays and i print it into excel using PHPExcel. Some times it need many column to write. It is over 5000 column.
The problem is sometime even the column is over from 5000 column, it can be print succeesfully but sometime it show as corrupted file.
I have read this : Why PHPExcel does not allow to write more than 5000 rows
But still im confusing.
This is my code :
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
if (PHP_SAPI == 'cli')
die('This example should only be run from a Web Browser');
/** Include PHPExcel */
require_once 'PHPExcel.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Valerian Timothy")
->setLastModifiedBy("Valerian Timothy")
->setTitle("Excel Document")
->setSubject("Data Mining")
->setDescription("Count how many words exists.")
->setKeywords("Data Mining")
->setCategory("Data Mining");
// Add some data
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Username')
->setCellValue('B1', 'Jumlah')
->setCellValue('C1', 'Hashtag')
->setCellValue('D1', 'Jumlah')
->setCellValue('E1', 'Etc')
->setCellValue('F1', 'Jumlah')
->setCellValue('G1', 'Date')
->setCellValue('H1', 'Jumlah');
// Miscellaneous glyphs, UTF-8
if($_SESSION['username'] != "")
{
$w = 2;
foreach($_SESSION['username'] as $user => $jumlah)
{
$cellUser = 'A' . $w;
$cellJumlah = 'B' . $w;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($cellUser, $user)
->setCellValue($cellJumlah, $jumlah);
$w++;
}
}
if($_SESSION['hashtag'] != "")
{
$x = 2;
foreach($_SESSION['hashtag'] as $hashtag => $jumlah)
{
$cellUser = 'C' . $x;
$cellJumlah = 'D' . $x;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($cellUser, $hashtag)
->setCellValue($cellJumlah, $jumlah);
$x++;
}
}
if($_SESSION['etc'] != "")
{
$y = 2;
foreach($_SESSION['etc'] as $etc => $jumlah)
{
$cellUser = 'E' . $y;
$cellJumlah = 'F' . $y;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($cellUser, $etc)
->setCellValue($cellJumlah, $jumlah);
$y++;
}
}
if(!empty($_SESSION['tanggal']))
{
$z = 2;
foreach($_SESSION['tanggal'] as $date => $jumlah)
{
$cellUser = 'G' . $z;
$cellJumlah = 'H' . $z;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue($cellUser, $date)
->setCellValue($cellJumlah, $jumlah);
$z++;
}
}
foreach(range('A','H') as $columnID) {
$objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
->setAutoSize(true);
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$today = strtotime(date('Y-m-d H:i:s'));
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Data-Mining'.$today.'.xlsx"');
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, 'Excel2007');
$objWriter->save('php://output');
exit;
session_destroy();
?>
Could you help me to fix it ?