PHP and PEAR spreadsheet_excel_writer

[ale1981]

Hi guys, need some help and where else would I look?!

 

Ok, I am trying to extract data from a MS SQL database and put it into a spreadsheet using the PEAR Spreadsheet_Excel_Writer.

 

So far I have this;

 

<?php
require_once 'Spreadsheet/Excel/Writer.php';

include('db_conn.php');

$sql = 'SELECT TOP 1 VJL.dbo.IV00101.ITEMNMBR AS [item Number], RTRIM(VJL.dbo.IV00101.ITEMDESC) AS [item Description], VJL.dbo.IV00101.CURRCOST AS [Current Cost], VJL.dbo.IV00102.QTYONHND AS Qty
	FROM VJL.dbo.IV00101 INNER JOIN VJL.dbo.IV00102 ON VJL.dbo.IV00101.ITEMNMBR = VJL.dbo.IV00102.ITEMNMBR
	WHERE (VJL.dbo.IV00101.ITEMNMBR IN 
	(SELECT     TOP 100 PERCENT ITEMNMBR AS ITEMNUMBER
FROM         VJL.dbo.IV00102
WHERE     (LOCNCODE = "HO") AND (LSRCPTDT < DATEADD(Year, - 1, GETDATE())) AND (LSRCPTDT <> CONVERT(DATETIME, 
                      "1900-01-01 00:00:00", 102))
ORDER BY ITEMNMBR)) AND (VJL.dbo.IV00101.ITEMNMBR IN (SELECT TOP 100 PERCENT ItemNumber FROM         dbo.tblKPIData
GROUP BY ItemNumber
HAVING      (ItemNumber <> "NON STOCK") AND (MAX(DISTINCT DOCDATE) < DATEADD(Year, - 1, GETDATE()))
ORDER BY ItemNumber)) 
	AND (VJL.dbo.IV00102.LOCNCODE = "ho") AND (VJL.dbo.IV00101.ITEMTYPE <> 2) AND (VJL.dbo.IV00102.QTYONHND > 0)';

$result = mssql_query($sql) or die();
$fields = mssql_num_fields($result);

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

//$format_bold =& $workbook->addFormat();
//$format_bold->setBold();

$format_title =& $workbook->addFormat();
$format_title->setBold();
//$format_title->setColor('yellow');
//$format_title->setPattern(1);
$format_title->setFgColor('yellow');

for ($i = 0; $i < $fields; $i++) { 
    $worksheet->write(0,$i, mssql_field_name($result, $i), $format_title); 
} 

$workbook->send('test.xls');
$workbook->close();
?>

 

Ok now that writers the headers to the spreadsheet file fine, but how can I add the actual data to the spreadsheet under the correct headers? As you can see the $worksheet->write() function writes the data to the file, but Im not sure how to loop through the data and output it like the headers?

 

 

Thanks for your help.

[ale1981]

BUMP