Download database into excel using php

Beginners - Learning PHP
#1

Hi, I want to download an excel file with data from sql server database. I tried but failed. So I attached my code for you to help me. Thank you

<?php
include (__DIR__ .'\Classes\PHPExcel-1.8\Classes\PHPExcel\IOFactory.php');
//require_once('vendor/autoload.php');

include "const.php"; [quote="nabilabolo, post:1, topic:31038, full:true"]
Hi, I want to download an excel file with data from sql server database. I tried but failed. So I attached my code for you to help me. Thank you

<?php
include (__DIR__ .'\Classes\PHPExcel-1.8\Classes\PHPExcel\IOFactory.php');
//require_once('vendor/autoload.php');

include "const.php"; 
$conn = odbc_connect(DSN," "," ");

$objPHPExcel = new PHPExcel();




	for($X=0;$X<2;$X++){
		$AreaID = $X + 1;

		if($X != 0) $objPHPExcel->createSheet();
		
		$objPHPExcel->setActiveSheetIndex($X);
		
		// Add column headers
		 $objWorksheet= $objPHPExcel->getActiveSheet()
 					->setCellValue('A1', 'Station ID')
					->setCellValue('B1', 'Tester ID')
				    ->setCellValue('C1', 'Log Date')
					->setCellValue('D1', 'Test Type')
					->setCellValue('E1', 'Barcode')
					->setCellValue('F1', 'Test No')
					->setCellValue('G1', 'Test Mode')
					->setCellValue('H1', 'Result')
					->setCellValue('I1', 'Status')
					->setCellValue('J1', 'Operator ID')
					->setCellValue('K1', 'Remark');
				

		$sql = "SELECT TOP 20 * FROM tblTestResultLog WHERE StationID = 'Server1'";
		
		$numRow = 1;
		$result =odbc_exec($conn,$sql);
		
		while(odbc_fetch_row($result)) {
			$numRow = $numRow + 1;
			
			$date = date('Y-m-d',strtotime(odbc_result($result,"ResultDateTime"))); //2018-11-15
			$time = date('H:i:s',strtotime(odbc_result($result,"ResultDateTime"))); //09:00:00
			
			$objPHPExcel->getActiveSheet()->setCellValue("A".$numRow, odbc_result($result,"StationID")));
			$objPHPExcel->getActiveSheet()->setCellValue("B".$numRow, odbc_result($result,"TesterID")));
			$objPHPExcel->getActiveSheet()->setCellValue("C".$numRow, odbc_result($result,"LogDate"));
			$objPHPExcel->getActiveSheet()->setCellValue("D".$numRow, odbc_result($result,"TestType"));
			$objPHPExcel->getActiveSheet()->setCellValue("E".$numRow,  odbc_result($result,"Barcode"));
			$objPHPExcel->getActiveSheet()->setCellValue("F".$numRow,  odbc_result($result,"TestNo"));
			$objPHPExcel->getActiveSheet()->setCellValue("G".$numRow,  odbc_result($result,"TestMode"));
			$objPHPExcel->getActiveSheet()->setCellValue("H".$numRow,  odbc_result($result,"Result"));
			$objPHPExcel->getActiveSheet()->setCellValue("I".$numRow,  odbc_result($result,"Status"));
			$objPHPExcel->getActiveSheet()->setCellValue("J".$numRow,  odbc_result($result,"OperatorID"));
			$objPHPExcel->getActiveSheet()->setCellValue("K".$numRow,  odbc_result($result,"Remark"));	
		}
	}		
	$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
	$objWriter->setIncludeCharts(TRUE);	// Write the Excel file to filename some_excel_file.xlsx in the current directory
	echo "Reports/".$filename."</br>";
	$objWriter->save('Reports/'.$filename); 
	//$objWriter->save($filename); 
	echo "d</br>";
	//$objWriter->save('php://output');	

  
?>



$conn = odbc_connect(DSN," "," ");

$objPHPExcel = new PHPExcel();




	for($X=0;$X<2;$X++){
		$AreaID = $X + 1;

		if($X != 0) $objPHPExcel->createSheet();
		
		$objPHPExcel->setActiveSheetIndex($X);
		
		// Add column headers
		 $objWorksheet= $objPHPExcel->getActiveSheet()
 					->setCellValue('A1', 'Station ID')
					->setCellValue('B1', 'Tester ID')
				    ->setCellValue('C1', 'Log Date')
					->setCellValue('D1', 'Test Type')
					->setCellValue('E1', 'Barcode')
					->setCellValue('F1', 'Test No')
					->setCellValue('G1', 'Test Mode')
					->setCellValue('H1', 'Result')
					->setCellValue('I1', 'Status')
					->setCellValue('J1', 'Operator ID')
					->setCellValue('K1', 'Remark');
				

		$sql = "SELECT TOP 20 * FROM tblTestResultLog WHERE StationID = 'Server1'";
		
		$numRow = 1;
		$result =odbc_exec($conn,$sql);
		
		while(odbc_fetch_row($result)) {
			$numRow = $numRow + 1;
			
			$date = date('Y-m-d',strtotime(odbc_result($result,"ResultDateTime"))); //2018-11-15
			$time = date('H:i:s',strtotime(odbc_result($result,"ResultDateTime"))); //09:00:00
			
			$objPHPExcel->getActiveSheet()->setCellValue("A".$numRow, odbc_result($result,"StationID")));
			$objPHPExcel->getActiveSheet()->setCellValue("B".$numRow, odbc_result($result,"TesterID")));
			$objPHPExcel->getActiveSheet()->setCellValue("C".$numRow, odbc_result($result,"LogDate"));
			$objPHPExcel->getActiveSheet()->setCellValue("D".$numRow, odbc_result($result,"TestType"));
			$objPHPExcel->getActiveSheet()->setCellValue("E".$numRow,  odbc_result($result,"Barcode"));
			$objPHPExcel->getActiveSheet()->setCellValue("F".$numRow,  odbc_result($result,"TestNo"));
			$objPHPExcel->getActiveSheet()->setCellValue("G".$numRow,  odbc_result($result,"TestMode"));
			$objPHPExcel->getActiveSheet()->setCellValue("H".$numRow,  odbc_result($result,"Result"));
			$objPHPExcel->getActiveSheet()->setCellValue("I".$numRow,  odbc_result($result,"Status"));
			$objPHPExcel->getActiveSheet()->setCellValue("J".$numRow,  odbc_result($result,"OperatorID"));
			$objPHPExcel->getActiveSheet()->setCellValue("K".$numRow,  odbc_result($result,"Remark"));	
		}
	}		
	$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
	$objWriter->setIncludeCharts(TRUE);	// Write the Excel file to filename some_excel_file.xlsx in the current directory
	echo "Reports/".$filename."</br>";
	$objWriter->save('Reports/'.$filename); 
	//$objWriter->save($filename); 
	echo "d</br>";
	//$objWriter->save('php://output');	

  
?>
#2

And what now? What did you do to find the source of your problem? And what is your problem?

#3

The problem is the data is not displayed in the excel. I want to retrieve database and download it in excel

#4

Do you even get anything back from odbc_fetch_row($result)?

#5

No, mine look like this

3
3717×303 7.97 KB

#6

As @chorn says, you should start by looking at the contents of $result .

Are you using an IDE? XDebug?

Sponsor our Newsletter | Privacy Policy | Terms of Service