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'); 

Answer 2

Try the below complete example for the same

<?php   $objPHPExcel = new PHPExcel();   $query1 = "SELECT * FROM employee";   $exec1 = mysql_query($query1) or die ("Error in Query1".mysql_error());   $serialnumber=0;   //Set header with temp array   $tmparray =array("Sr.Number","Employee Login","Employee Name");   //take new main array and set header array in it.   $sheet =array($tmparray);    while ($res1 = mysql_fetch_array($exec1))   {     $tmparray =array();     $serialnumber = $serialnumber + 1;     array_push($tmparray,$serialnumber);     $employeelogin = $res1['employeelogin'];     array_push($tmparray,$employeelogin);     $employeename = $res1['employeename'];     array_push($tmparray,$employeename);        array_push($sheet,$tmparray);   }    header('Content-type: application/vnd.ms-excel');    header('Content-Disposition: attachment; filename="name.xlsx"');   $worksheet = $objPHPExcel->getActiveSheet();   foreach($sheet as $row => $columns) {     foreach($columns as $column => $data) {         $worksheet->setCellValueByColumnAndRow($column, $row + 1, $data);     }   }    //make first row bold   $objPHPExcel->getActiveSheet()->getStyle("A1:I1")->getFont()->setBold(true);   $objPHPExcel->setActiveSheetIndex(0);   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');   $objWriter->save(str_replace('.php', '.xlsx', __FILE__)); ?>