Page 1 of 1

Problems in PEAR's Spreadsheet_Excel_Writer

Posted: Thu Apr 17, 2008 6:43 am
by Babe Ruth
I am trying to transform an SQL result set into an Excel spreadsheet. I want to turn all the records from the table into rows of an Excel spreadsheet. I am using PEAR's Spreadsheet_Excel_Writer so that I can make a format/template design of a spreadsheet. Take a look at the code I made:

<?php
// include class file
include 'Spreadsheet/Excel/Writer.php';

// initialize reader object
$excel = new Spreadsheet_Excel_Writer();

// send client headers
$excel->send('country.xls');

// add worksheet
$sheet =& $excel->addWorksheet('SQL_Output');

// attempt a connection
try {
$pdo = new PDO('mysql:dbname=cdms;host=localhost', 'root', 'admin');
} catch (PDOException $e) {
die("ERROR: Could not connect: " . $e->getMessage());
}

// read data from database
// convert into spreadsheet
$rowCount = 0;
$sql = "SELECT * FROM businessunits";
if ($result = $pdo->query($sql)) {
// get header row
for ($x=0; $x<$result->columnCount(); $x++) {
$meta = $result->getColumnMeta($x);
$sheet->write($rowCount, $x, $meta['name']);
}
// get data rows
$rowCount++;
while($row = $result->fetch()) {
foreach ($row as $key => $value) {
$sheet->write($rowCount, $key, $value);
}
$rowCount++;
}
} else {
echo "ERROR: Could not execute $sql. " . print_r($pdo->errorInfo());
}

// close connection
unset($pdo);

// save file to disk
if ($excel->close() === true) {
echo 'Spreadsheet successfully saved!';
} else {
echo 'ERROR: Could not save spreadsheet.';
}
?>

<html>
<head>
<title>Untitled Document</title>
</head>

<body>
</body>
</html>

The problem I am encountering is that the value of the SQL results doesn't match with what the results show in the Excel. Instead of showing the correct results for the first column, it always show what are the query results of my last column. The sample is this:

UnitID SOLID RCNumber GroupNumber
0 9999 999 0
1 1901 270 1
1 1902 271 1
1 1901 274 1
3 1904 275 3
1 3401 276 1

What lines should I change in my php code in order for the excel results match what is in my database

Re: Problems in PEAR's Spreadsheet_Excel_Writer

Posted: Fri Apr 18, 2008 5:09 pm
by jmut