asked on

Spreadsheet/Excel/Writer.php and MySQL

I'm trying to write code that will output to excel based on an SQL query using the pear excel/writer function.
The following code only returns a blank excel file:
I'd also like to allow for an incoming array such as:
for id in @array, output to spreadsheet
Basically, I have a table listing ID, IP, HOST, DOMAIN and need a way to export to excel.
For now, I'm just dumping all (via select *), but if you know how, I'd like to do a select * from where id=[array of id's]

Thanks!

if ($export == 1) {
require_once "Spreadsheet/Excel/Writer.php";
mysql_connect($db_host,$mysql_username,$mysql_password) or die (mysql_error());
mysql_select_db($mysql_db) or die(mysql_error());
$select = "SELECT ip,hostname,domainname FROM $mysql_table";
$export = mysql_query($select) or die (mysql_error());
$numfields = mysql_num_fields($export) or die (mysql_error());

$xls =& new Spreadsheet_Excel_Writer();

// Send HTTP headers to tell the browser what's coming
$xls->send("$mysql_table.xls");

// Add a worksheet to the file, returning an object to add data to
$sheet =& $xls->addWorksheet("$mysql_table");

// Write some numbers
for ($i = 0; $i < $numfields; $i++) {
// Write data
$sheet->write($i,1,$export[$i]);
}

// Finish the spreadsheet, dumping it to the browser
$xls->close();

} else {

ASKER CERTIFIED SOLUTION

Cornelia Yoder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cdukes

ASKER

Gracias :-)

Here's what I ended up with in case anyone else ever needs it:

[code]
if ($export == 1) {
require_once "Spreadsheet/Excel/Writer.php";
// $idarr = "1,2";
mysql_connect($db_host,$mysql_username,$mysql_password) or die (mysql_error());
mysql_select_db($mysql_db) or die(mysql_error());
if ( $idarr ) {
$select = "SELECT * from $mysql_table where FIND_IN_SET(id, '$idarr')>0;";
} else {
$select = "SELECT * from $mysql_table";
}
$export = mysql_query($select) or die (mysql_error());
$numfields = mysql_num_fields($export) or die (mysql_error());

$xls =& new Spreadsheet_Excel_Writer();

// Send HTTP headers to tell the browser what's coming
$xls->send("$mysql_table.xls");

// Add a worksheet to the file, returning an object to add data to
$sheet =& $xls->addWorksheet("$mysql_table");

// Write data
// $sheet->write($rownum,$colnum,$export[$i]);
// Top Column Names
$rownum = 0;
$colnum = 0;
$sheet->write($rownum,$colnum,$field[1][2]);
$colnum++;
$sheet->write($rownum,$colnum,$field[1][3]);
$colnum++;
$sheet->write($rownum,$colnum,$field[1][4]);
$rownum = 1;
// Row Data
while ($row = mysql_fetch_assoc($export)) {
$colnum = 0;
$sheet->write($rownum,$colnum,$row[$field[2][2]]);
$colnum++;
$sheet->write($rownum,$colnum,$row[$field[2][3]]);
$colnum++;
$sheet->write($rownum,$colnum,$row[$field[2][4]]);
$rownum++;
}
// Finish the spreadsheet, dumping it to the browser
$xls->close();

} else {
[/code]
...rest of the page code