Save mysql query to excel file from php webpage

Question

Hi,

I have a table within a mysql database. What i want to do, is in a php page, run a select statement and output the result into an excel file.

I've seen quite a few tutorials out there and tried them, however these output the contents of the webpage into the excel file. I just want the mysql table data and headers.

Is there a way to do this?

Answer 1

You can use one of the available PHP libraries for that. One, two.

One thing that is pretty fast to develop but not as neat as using a LIBRARY for that is:

1. Perform your MySQL query
2. Create an HTML table with the results
3. Set content type of the response to: application/vnd.ms-excel

In most browsers, using that page will open the HTML table as a spreadsheet in Excel. You can use other HTTP headers to suggest file name for that file.

Answer 2

I've used PHPExcel for quite a while now and I must say, it's pretty easy to use if you can read the doc.

Did what you want in an afternoon (reading the doc, trying stuff and finalizing)

Answer 3

Definitely you have to use a component to generate a Excel file. I use this lib:

• Spreadsheet::WriteExcel: A library for generating Excel Spreadsheets
• Copyright (C) 2002 Xavier Noguer [email protected]

Here is a small snippet:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$filename" );
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");

$formato1 =& $workbook->add_format();
$formato1->set_border(1);
$formato1->set_bg_color('white');
$formato1->set_fg_color('grey');
$formato1->set_pattern();
$formato1->set_size(10);
$formato1->set_color('white');
$formato1->set_align('center');
$formato1->set_bold();

$formato2 =& $workbook->add_format();
$formato2->set_size(10);
$formato2->set_border(1);

$worksheet1 =& $workbook->add_worksheet('Relatório de Inscrições');
$linha = 1;

//Query dos dados das inscrições recebidas

$sql = "select 
          A.Name, A.Code
        from 
          Customers A
        order by
          A.Name";

$resultado = $conn ->_execute($sql);

$worksheet1->write_string(0, 0, 'Name', $formato1);
$worksheet1->write_string(0, 1, 'Code', $formato1);

for($a = 0; $a < count($resultado); $a++) 
{
  $row = $resultado[$a];

  $worksheet1->write_string($linha, 0, utf8_decode($row->Name),    $formato2);  
  $worksheet1->write_number($linha, 1, $row->Code,                 $formato2);

  $linha++;
}  

$workbook->close();