Databases
StackOverflow2013
Postsdbo
POHow to export data to an excel file using PHPExcel
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to export data to an excel file using PHPExcel
    text
    copied!<p>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? </p> <p><strong>EDIT</strong></p> <pre><code>&lt;?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:&lt;br&gt;" . mysql_error() . "&lt;br&gt;" . mysql_errno()); //select database $Db = @mysql_select_db($dbname, $Connect) or die("Couldn't select database:&lt;br&gt;" . mysql_error(). "&lt;br&gt;" . mysql_errno()); //execute query $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:&lt;br&gt;" . mysql_error(). "&lt;br&gt;" . mysql_errno()); error_reporting(E_ALL); require_once '../Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); // Set the active Excel worksheet to sheet 0 $objPHPExcel-&gt;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 &lt; mysql_num_fields($result); $i++) { $objPHPExcel-&gt;getActiveSheet()-&gt;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&lt;mysql_num_fields($result);$j++) { if(!isset($row[$j])) $value = NULL; elseif ($row[$j] != "") $value = strip_tags($row[$j]); else $value = ""; $objPHPExcel-&gt;getActiveSheet()-&gt;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-&gt;save('php://output'); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload