Jump to content

PHP and PEAR spreadsheet_excel_writer


ale1981

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/54594-php-and-pear-spreadsheet_excel_writer/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.