从PHP导出SQLite数据库到Excel

Question

How can I export a SQLite file as Excel from a PHP script?

I currently export the tables and data as just plain HTML tables, with the following code:

$dbh = new PDO('sqlite:' . $path . "/" . $lang . "/" . $dbname . ".sqlite");

$sql = "SELECT tbl_name FROM sqlite_master WHERE type='table';";

$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL));
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) {
  $sql = "SELECT * FROM " . $row['tbl_name'];
  $sub_stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL));

  $sub_stmt->execute();

  echo "<br><br><table border=1><thead>". $row['tbl_name'] ."</thead><tbody>";
  while ($sub_row = $sub_stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) {
    foreach ($sub_row as $key => $value) {
      $titles .= "<td>" . $key . "</td>";
      $sub_row_table[] = "<td>" . $sub_row[$key] . "</td>";
    }

    if(!$titlesPrinted) {
      echo "<tr>" . $titles . "</tr>";
      $titlesPrinted = true;
    }

    echo "<tr>";
    foreach($sub_row_table as $value) {
      echo $value;
    }

    echo "</tr>";
  }
  echo "</tbody></table>";
}

$dbh = null;

However that doesn't work great if the sqlite table has a lot of data, the page takes a while to load everything. Ideally I would prefer to create links to download the Excel sheets (or possible to export all into one Excel workbook file with all the sheets as each table?), or at least links to pop-up/display the table rather than using up lots of resources to echo to the users' screen.

EDIT:
Forgot to mention that my sqlite data contains some columns that use HTML and a lot of data uses accents like: è, é, ê, à, ä etc.

Answer 1

PHPExcel is a plugin that allows you to dynamically handle Excel objects from PHP for read and write actions.

URL: http://phpexcel.codeplex.com