没有PHPExcel的PHPExcel文件

Question

我必须创建一个与350 columns和1000 rows相近的excel文件。我已经使用PHPExcel为这个任务开发了代码。但是创建文件需要42secs。所以我必须创建excel文件without PHPExcel。因此，我已经开发了一个脚本，在这个脚本中，数据由"\t"为不同的选项卡数据标识。创建文件只需使用2secs即可。但问题是，当我要打开该文件(使用“\t”创建)时，将显示message of corrupted file。在修复那个文件的时候，它工作得很好。但我不明白我在哪里写错了剧本。如果有人可以通过使用PHPExcel (较少的执行时间)或通过解决损坏文件的错误来解决此问题，则将不胜感激。这是我的代码(CakePHP 3)。

输入阵列

// 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);

编辑(更新的代码)

$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);
}

Answer 1

正如您提到的“没有PHPExcel"，您是否尝试过寻找比PHPExcel更快的替代方案？例如，您可以使用喷口 (https://github.com/box/spout)在几秒钟内生成一个350x1000xlsx电子表格。

生成CSV文件也是一个很好的选择，如果可以的话，我建议您选择这个路径。出口过程会更快！但是不要试图重新发明轮子，已经有很多CSV编写器可以使用了(比如Spout和PHPExcel都有)。