PHPExcel脚本需要很长时间才能使用大型数据库

Question

This is the PHP code, I don't know much about PHPExcel to make it run faster, ideally I don't want to limit to 10000 rows (still takes at least 5 minutes before it sends the excel file)

Any ideas?

The script basically selects all data from the sqlite database then it loops the keys of the first row to add as titles for the columns.
Then it loops all rows and sets each cell value.
After this it adds the formula columns.
Then sends the excel data to the user.

The script is run at: http://example-site.org/getStatsExcel.php - so each time a user goes to that page, it runs this script - I think I should store the database per day and if it's already stored for that day, then just return the file, else generate the excel again...

<?php

date_default_timezone_set('Europe/Zurich');

require_once 'phpexcel/Classes/PHPExcel.php';

ini_set('max_execution_time', 900);

$dbname = 'admin';
$fullPath = sprintf('/var/www/fullpathtosqlite/%s.sqlite', $dbname);

$dbh = new PDO('sqlite:' . $fullPath);

$phpExcel = new PHPExcel();
$phpExcel->getProperties()->setTitle('Export : Statistics');
$phpExcel->getProperties()->setCreator('PHPExcel Stats Script');

$sheet = $phpExcel->getActiveSheet();
$sheet->setTitle('stats');

$phpExcel->setActiveSheetIndex(0);

$sql = 'SELECT * FROM (SELECT * FROM statistics ORDER BY timestamp DESC LIMIT 10000) ORDER BY timestamp ASC';

if(!$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL))) {
    die(var_export($dbh->errorinfo(), TRUE));
}

$stmt->execute();

// Fetch the first row
$row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT);

$results = array();
// Iterate over the results and print each one in a line
while ($row != false) {
    $results[] = $row;
    // Fetch the next line
    $row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT);
}

$row = 1;
$col = 0;
foreach ($results[0] as $key => $value) {
    $sheet->setCellValueByColumnAndRow($col, $row, $key);
    $col++;
}

// date
$sheet->setCellValueByColumnAndRow($col, $row, 'date');
$col++;
// time
$sheet->setCellValueByColumnAndRow($col, $row, 'time');
$col++;
// full_date
$sheet->setCellValueByColumnAndRow($col, $row, 'full_date');

$row = 2;
foreach ($results as $result) {
    $col = 0;
    foreach ($result as $key => $value) {
        $sheet->setCellValueByColumnAndRow($col, $row, $value);
        $col++;
    }

    // date
    $sheet->setCellValueByColumnAndRow($col, $row, '=DATE(LEFT(A' . $row . ',4),MID(A' . $row . ',5,2),MID(A' . $row . ',7,2))');
    $col++;
    // time
    $sheet->setCellValueByColumnAndRow($col, $row, '=TIME(MID(A' . $row . ',9,2),MID(A' . $row . ',11,2),MID(A' . $row . ',13,2))');
    $col++;
    // full_date
    $sheet->setCellValueByColumnAndRow($col, $row, '=F' . $row . '+G' . $row);

    $row++;
}

$sheet->getStyle('F2:F' . $row)
      ->getNumberFormat()
      ->setFormatCode('dd/mm/yyyy');
$sheet->getStyle('G2:G' . $row)
      ->getNumberFormat()
      ->setFormatCode('h:mm AM/PM');
$sheet->getStyle('H2:H' . $row)
      ->getNumberFormat()
      ->setFormatCode('dd/mm/yyyy hh:mm');

foreach(range('A','H') as $columnID) {
    $sheet->getColumnDimension($columnID)->setAutoSize(true);
}

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"statistics.xls\"");
header("Cache-Control: max-age=0");

$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, "Excel5");
$objWriter->save("php://output");
exit;

Answer 1

The more data you're working with, the longer it will take. That's why we recommend that generating large spreadsheets should be farmed off to a back-end process so that it doesn't leave the user waiting while it builds the spreadsheet.

If you can build these large data spreadsheets "offline", then do so; if you can cache them, then do so.