从PHP导出SQLite数据库到Excel

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.

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

URL: http://phpexcel.codeplex.com

I think that what you actually want to do is to create a CSV file. There is a specific function in PHP for writing this kind of file, here is the link: fputscsv function

As you can see in the function's documentation, the function handles the array that contains the different fields and the separator, I paste you the code of the example that I think is very clear:

<?php

$list = array (
    array('aaa', 'bbb', 'ccc', 'dddd'),
    array('123', '456', '789'),
    array('"aaa"', '"bbb"')
);

$fp = fopen('file.csv', 'w');

foreach ($list as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);
?>

The output of the example (on file.csv in this case):

aaa,bbb,ccc,dddd
123,456,789
"""aaa""","""bbb"""

Excel opens automatically the CSV files interpreting a specific separator (I think depending the regional configuration this can be different, ";" for example), you should check this in order to use the appropriate separator.

I hope this helps.