PEAR Forum :: PHP Extension and Application Repository

Babe Ruth · Joined: 18 Apr 2008 Posts: 6

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

mark · Joined: 07 Jan 2007 Posts: 1053

How about adding a

Babe Ruth · Joined: 18 Apr 2008 Posts: 6

Thanks for your reply. I did not expect a reply this early. Now when I run with the additional code ---var_dump($row); --- it is concluded from my script that for each row, the first column always takes the values of my last column.

I know there is a problem with my script or how do I conceptualize it.

Can anyone teach me a better code to put the sql results into the spreadsheet, but in a manner that I have the flexibility to put the results to whatever starting spreadsheet-cell that I want. For example, I would like the query results to be located not from cells 'A1 to J25' but from cells 'B2 to K26'

BTW, i have noted the Structures_DataGrid

mark · Joined: 07 Jan 2007 Posts: 1053

PEAR Forum :: PHP Extension and Application Repository

'Actiemonitor' online projectmanagement software