php - How to export data to an excel file using PHPExcel

Question

I have taken the source code from limesurvey and have added the PHPExcel library to my limesurvey code to export data to an excel file after you click a link. Currently the excel file opens with some dummy data in it with no problems. I need to be able to add data dynamically from the web server after a user types in survey information. I have looked into some sites I have found but I havent had much luck. Can anyone help me out?

EDIT

<?php 
$dbhost= "mysql"; //your MySQL Server 
$dbuser = "survey"; //your MySQL User Name 
$dbpass = "password"; //your MySQL Password 
$dbname = "database"; 
//your MySQL Database Name of which database to use this 
$tablename = "questions"; //your MySQL Table Name which one you have to create excel file 
// your mysql query here , we can edit this for your requirement 
$sql = "Select * from $table "; 
//create  code for connecting to mysql 
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass) 
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
//select database 
$Db = @mysql_select_db($dbname, $Connect) 
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 
$result = @mysql_query($sql,$Connect) 
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 

error_reporting(E_ALL);

 require_once '../Classes/PHPExcel.php';
 $objPHPExcel = new PHPExcel();

 // Set the active Excel worksheet to sheet 0 

$objPHPExcel->setActiveSheetIndex(0);  

// Initialise the Excel row number 

$rowCount = 1;  


//start of printing column names as names of MySQL fields  

 $column = 'A';

for ($i = 1; $i < mysql_num_fields($result); $i++)  

{
    $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
    $column++;
}

//end of adding column names  
//start while loop to get data  

$rowCount = 2;  

while($row = mysql_fetch_row($result))  

{  
    $column = 'A';

   for($j=1; $j<mysql_num_fields($result);$j++)  
    {  
        if(!isset($row[$j]))  

            $value = NULL;  

        elseif ($row[$j] != "")  

            $value = strip_tags($row[$j]);  

        else  

            $value = "";  


        $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
        $column++;
    }  

    $rowCount++;
} 

// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="results.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');

Answer 1

If you've copied this directly, then:

->setCellValue('B2', Ackermann') 

should be

->setCellValue('B2', 'Ackermann') 

In answer to your question:

Get the data that you want from limesurvey, and use setCellValue() to store those data values in the cells where you want to store it.

The Quadratic.php example file in /Tests might help as a starting point: it takes data from an input form and sets it to cells in an Excel workbook.

EDIT

An extremely simplistic example:

// Create your database query
$query = "SELECT * FROM myDataTable";  

// Execute the database query
$result = mysql_query($query) or die(mysql_error());

// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel(); 
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0); 
// Initialise the Excel row number
$rowCount = 1; 
// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($row = mysql_fetch_array($result)){ 
    // Set cell An to the "name" column from the database (assuming you have a column called name)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['name']); 
    // Set cell Bn to the "age" column from the database (assuming you have a column called age)
    //    where n is the Excel row number (ie cell A1 in the first row)
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['age']); 
    // Increment the Excel row counter
    $rowCount++; 
} 

// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
// Write the Excel file to filename some_excel_file.xlsx in the current directory
$objWriter->save('some_excel_file.xlsx'); 

EDIT #2

Using your existing code as the basis

// Instantiate a new PHPExcel object 
$objPHPExcel = new PHPExcel();  
// Set the active Excel worksheet to sheet 0 
$objPHPExcel->setActiveSheetIndex(0);  
// Initialise the Excel row number 
$rowCount = 1;  

//start of printing column names as names of MySQL fields  
$column = 'A';
for ($i = 1; $i < mysql_num_fields($result); $i++)  
{
    $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
    $column++;
}
//end of adding column names  

//start while loop to get data  
$rowCount = 2;  
while($row = mysql_fetch_row($result))  
{  
    $column = 'A';
    for($j=1; $j<mysql_num_fields($result);$j++)  
    {  
        if(!isset($row[$j]))  
            $value = NULL;  
        elseif ($row[$j] != "")  
            $value = strip_tags($row[$j]);  
        else  
            $value = "";  

        $objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
        $column++;
    }  
    $rowCount++;
} 


// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="Limesurvey_Results.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');