1

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);
}
6
  • You can reduce the time for the PHPExcel script by writing the file only once rather than twice Commented Feb 12, 2016 at 10:33
  • 1
    Alternatively, if you want to fix your non-PHPExcel version, and create a CSV file as an alternative to an OfficeOpenXML-format xlsx file, use PHP's built-in fputcsv() function rather than a bad homebrew code Commented Feb 12, 2016 at 10:35
  • @Mark. your solution is beneficial as it reduced time by 2-3 sec. I have also tried this solution as you suggested in another question. i.e, $sheet = $objPHPExcel->getActiveSheet(); $sheet->setCellValueByColumnAndRow($col, $ro, $field); Commented Feb 12, 2016 at 10:51
  • @MarkBaker. As per your second suggestion, I have to create excel file, not csv. So I think I cant use fputcsv() to write tabular data in file. Commented Feb 12, 2016 at 10:55
  • The non-PHPExcel code that you've posted is generating a csv file, not a native format Excel file...... it wasn't my suggestion, it was a comment on your code; you're creating a csv file with a tab-separator using a badly-written homebrew Commented Feb 12, 2016 at 11:12

1 Answer 1

1

As you mentioned "without PHPExcel", have you tried looking at alternatives that can be faster than PHPExcel? For instance, you can generate a 350x1000 XLSX spreadsheet with Spout (https://github.com/box/spout) in just a few seconds.

Generating a CSV file is also a good alternative and I'd recommend you going this route if you can. The export process will be way faster! But don't try to reinvent the wheel, there a already a lot of CSV writer out there, ready to use (Spout and PHPExcel both have one for instance).

Sign up to request clarification or add additional context in comments.

2 Comments

If you want to go only for .xls (large file as per mentioned in question) files, Spout is best in terms of response time.
My response time is decreased from 42 Secs(PHPExcel) to 5 Secs(Spout). But I will not use Spout for smaller file. I will go for PHPExcel if file is 100rows * 50cols (near about)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.