MySQL -> PHP -> MS Excel?

Posted by sheldonlg on November 26, 2008, 7:31 am

Please log in for more thread options

sheldonlg wrote:
show/hide quoted text

Here it is:

<?php
class ExportToExcel
{
var $body = '';
function setHeader($excel_file_name) {
header("Content-type: application/octet-stream");
header('Content-Disposition: attachment; filename="' .
$excel_file_name . '"');
header("Pragma: no-cache");
header("Expires: 0");
}

function export2DArray($arr, $excel_file_name, $title) {
show/hide quoted text
foreach ($arr as $row) {
show/hide quoted text
foreach ($row as $column) {
show/hide quoted text
}
show/hide quoted text
}
show/hide quoted text
}

function addArray($arr) {
foreach ($arr as $row) {
show/hide quoted text
foreach ($row as $column) {
show/hide quoted text
}
show/hide quoted text
}
}

function addTitle($title) {
show/hide quoted text
}

function addColumnHeading($arr) {
show/hide quoted text
foreach ($arr as $column) {
show/hide quoted text
}
show/hide quoted text
}

function closeTable() {
show/hide quoted text
}

function sendArray($excel_file_name) {
show/hide quoted text
}

function addLines($num) {
show/hide quoted text
for ($i=0; $i<$num; $i++) {
show/hide quoted text
}
show/hide quoted text
}
}
show/hide quoted text

I use it as follows:
$list = 2-d array that I want to write;
$excel = new ExportToExcel();
show/hide quoted text
$columns = array(comma separated list of headings);
show/hide quoted text

Posted by Jerry Stuckle on November 26, 2008, 2:21 pm

Please log in for more thread options

sheldonlg wrote:
show/hide quoted text

Sheldon,

Unfortunately, that doesn't actually create an Excel file - it creates
an HTML file and calls it an Excel file.

There isn't anything wrong with that if the client wants to export it -
but the op indicated his client specifically wants a native Excel format
program.

I suspect it could be done with a COM object, but I've never done it
myself - just always wrote a csv file.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Posted by sheldonlg on November 26, 2008, 3:35 pm

Please log in for more thread options

Jerry Stuckle wrote:
show/hide quoted text

While technically true, I suspect what the OP **really** wanted was for
the end user to click a button and have it open up as a file that can be
used in an Excel program and be saved to disk. The actual coding inside
would be immaterial -- just so long as it appears to the end user as an
Excel file and could be opened from his hard drive using Excel after he
saved it there. After all, here is what he wrote:

"I need to get some data from a MySQL database into an Excel
Spreadsheet, potentially using PHP."

What I gave him does exactly that, albeit not in native Excel coding.

Posted by Jerry Stuckle on November 26, 2008, 4:03 pm

Please log in for more thread options

sheldonlg wrote:
show/hide quoted text

(in response to Luuk's suggestion to use a csv file)

"That was my preferred choice too, but the customer wants it directly
in MS-Excel format - they don't want to have to do an import of any
sort."

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Posted by sheldonlg on November 26, 2008, 7:47 pm

Please log in for more thread options

Jerry Stuckle wrote:
show/hide quoted text

....and as **I** said, the file opens directly as an Excel file. There
is no import with my method. To all intents and purposes, it **is** an
MS-Excel file. It is just that behind the scenes it is not native Excel
coding. It still is a **real** Excel file (as far as the user is
concerned). All he has to do if he is using Internet Explorer is to
make sure his setting is NOT to open an Excel file, ANY Excel file
native coding or not, as an html file (which is their default setting).