Problem with data from MSSQL

Topics: Developer Forum
Nov 1, 2010 at 8:17 AM

I have this php file

<?php$connectionInfo_hq = array('Database'=>'xxx', 'ReturnDatesAsStrings'=> true,'UID'=>'xxx', 'PWD'=>'xxx', 'CharacterSet'=>'UTF-8');$conn = sqlsrv_connect('xxx , 1433', $connectionInfo_hq);
// SQL$tsql = 'select * from customer order by first_name';
$stmt = sqlsrv_query( $conn, $tsql);
/** Error reporting */error_reporting(E_ALL);
date_default_timezone_set('Europe/London');
/** PHPExcel */require_once '../Classes/PHPExcel.php';

// Create new PHPExcel object$objPHPExcel = new PHPExcel();
// Set properties$objPHPExcel->getProperties()->setCreator("Maarten Balliauw") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file");

// Add some data$objPHPExcel->setActiveSheetIndex(0)            ->setCellValue('A1', 'customernr')            ->setCellValue('B1', 'first_name')            ->setCellValue('C1', 'Last_name')
$i = 2;
$objPHPExcel->setActiveSheetIndex(0)
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
 ->setCellValue('A' . $i, "$row[customernr]")->setCellValue('B' . $i, "$row[first_name]")->setCellValue('C' . $i, "$[last_name]")
$i = $i + 1;
}
// Rename sheet$objPHPExcel->getActiveSheet()->setTitle('Simple');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition: attachment;filename="01simple.xlsx"');header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');$objWriter->save('php://output');exit;
?>

When I download the file can not be opened in Excel 2007 and 2010. But if I delete "while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {"
the file can be downloaded just fine but no data. Someone who can help with this error?

Coordinator
Nov 1, 2010 at 10:14 PM
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
   ->setCellValue('A' . $i, "$row[customernr]")->setCellValue('B' . $i, "$row[first_name]")->setCellValue('C' . $i, "$[last_name]")
   $i = $i + 1;
}

should give a  PHP parse error because it isn't valid PHP.

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
   $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $row["customernr"])->setCellValue('B' . $i, $row["first_name"])->setCellValue('C' . $i, $["last_name"]);
   $i = $i + 1; 
}

might resolve your issue

Nov 2, 2010 at 7:08 AM

Hey Mark,

No, it gives the same error when I open the file in Excel.

My code :

// Add some data

$objPHPExcel->setActiveSheetIndex(0)

            ->setCellValue('A1', 'customernr')

            ->setCellValue('B1', 'first_name')

            ->setCellValue('C1', 'Last_name');
$i = 2;

$objPHPExcel->setActiveSheetIndex(0);

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
$objPHPExcel->getActiveSheet()
->setCellValue('A' . $i, $row["customernr"])

->setCellValue('B' . $i, $row["first_name"])

->setCellValue('C' . $i, $row["last_name"]);
   $i = $i + 1; 

}

Hope you can help again ?