I have to create a excel file having near about 350 columns and 1000 rows. I have developed code for this task using PHPExcel. But it takes 42secs to create file. So I have to create excel file without PHPExcel. So I have developed a script in which data is identified by "\t" for different tab data. It takes just 2secs to create file. But problem is that when I'm going to open that file (created by using "\t"), message of corrupted file is displayed. And on repairing that file, it works fine. But I can't understand that where I'm making mistake in script. If anyone can solve this problem either by using PHPExcel (less execution time) or by solving error of corrupt file, then answer will be appreciated. Here is my code (CakePHP 3).
Input Array like
// Do not confuse with { instead of [. It's okey.
// $export_data and $data both are same (Input array).
{
"0": {
"customer_id": 1,
"name": "John Stevens",
"Date of 1 Purchase": "2014-08-05T00:00:00+0000",
"Date of 2 Purchase": "2014-09-05T00:00:00+0000",
"Date of 3 Purchase": "2014-10-05T00:00:00+0000",
...
...
... 350 Cols ...
}
"1": {
...
}
...
...
"999 Rows"
}
Using PHPExcel
$r = 1;
$filename = FILE_PATH . 'galliyan.xlsx';
$header = array_keys($export_data[0]);
$objPHPExcel = new \PHPExcel();
$col = 0;
foreach ($header as $field) {
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($filename);
chmod($filename, 0777);
$r++;
$objPHPExcel = \PHPExcel_IOFactory::load($filename);
foreach($export_data as $row) {
$col = 0;
foreach ($row as $ro) {
$objPHPExcel->getActiveSheet()->setCellvalueByColumnAndRow($col, $r, $ro);
$col++;
}
$r++;
}
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($filename);
chmod($filename, 0777);
EDIT (UPDATED CODE)
$filename = FILE_PATH . 'galliyan.xlsx';
$header = array_keys($export_data[0]);
$objPHPExcel = new \PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$col = 0;
foreach ($header as $field) {
$sheet->setCellValueByColumnAndRow($col, 1, $field);
$col++;
}
$objPHPExcel->getActiveSheet()->fromArray($export_data, null, 'A2');
$writer = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$writer->save($filename);
chmod($filename, 0777);
Using "\t"
foreach ($data as $row) {
if (!$flag) {
$header = array();
// display field/column names as first row
$header = array_keys($row);
$header = $this->setExcelHeaders($header);
$this->createExcelFile($dir_name, $filename, $header, $export_type);
$flag = TRUE;
}
array_walk($row, array($this, 'cleanData'));
array_push($values, $row);
}
$values = $this->setValues($values);
$this->writeExcelFile($dir_name, $filename, $values);
function setExcelHeaders($hdrs) {
$header = '';
foreach ($hdrs as $title_val) {
$header .= $title_val . "\t";
}
return $header;
}
function createExcelFile($dir_name, $filename, $header, $export_type = '') {
$fp = fopen($dir_name . "/" . $filename, 'w');
fwrite($fp, "$header\n");
fclose($fp);
$permission = Configure::read('Config.PERMISSION');
if ($export_type == "") {
chmod($dir_name, $permission);
}
chmod($dir_name . "/" . $filename, $permission);
}
public function cleanData(&$str) {
$str = preg_replace("/\t/", "\\t", $str);
$str = preg_replace("/\r?\n/", "\\n", $str);
if (strstr($str, '"'))
$str = '"' . str_replace('"', '""', $str) . '"';
}
private function setValues($all_vals) {
$data = '';
for ($i = 0; $i < count($all_vals); $i++) {
$line = '';
foreach ($all_vals[$i] as $value) {
if ((!isset($value)) || ( $value == "")) {
$value = "\t";
}
else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line) . "\n";
}
return $values = str_replace("\r", "", $data);
}
function writeExcelFile($dir_name, $filename, $data) {
$fp = fopen($dir_name . "/" . $filename, 'a');
fwrite($fp, "$data");
fclose($fp);
}
$sheet = $objPHPExcel->getActiveSheet(); $sheet->setCellValueByColumnAndRow($col, $ro, $field);