Cómo exportar datos a un archivo Excel usando PHPExcel

He tomado el código fuente de limesurvey y he agregado la biblioteca de PHPExcel a mi código limesurvey para exportar datos a un archivo de Excel después de hacer clic en un enlace. Actualmente, el archivo de Excel se abre con algunos datos ficticios sin problemas. Necesito poder agregar datos dinámicamente desde el servidor web después de que el usuario ingrese la información de la encuesta. He investigado algunos sitios que he encontrado pero no he tenido mucha suerte. ¿Puede alguien ayudarme?

EDITAR

<?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:
" . mysql_error() . "
" . mysql_errno()); //select database $Db = @mysql_select_db($dbname, $Connect) or die("Couldn't select database:
" . mysql_error(). "
" . mysql_errno()); //execute query $result = @mysql_query($sql,$Connect) or die("Couldn't execute query:
" . mysql_error(). "
" . 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 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; $jgetActiveSheet()->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');

Si has copiado esto directamente, entonces:

 ->setCellValue('B2', Ackermann') 

debiera ser

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

En respuesta a tu pregunta:

Obtenga los datos que desee de limesurvey y use setCellValue () para almacenar esos valores de datos en las celdas donde desea almacenarlos.

El archivo de ejemplo Quadratic.php en / Tests podría ayudar como punto de partida: toma datos de un formulario de entrada y lo establece en celdas en un libro de Excel.

EDITAR

Un ejemplo extremadamente simplista:

 // 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

Usando su código existente como base

 // 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; $jgetActiveSheet()->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'); 

Pruebe el siguiente ejemplo completo para el mismo

 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__)); ?> 
 $this->load->library('excel'); $file_name = 'Demo'; $arrHeader = array('Name', 'Mobile'); $arrRows = array(0=>array('Name'=>'Jayant','Mobile'=>54545), 1=>array('Name'=>'Jayant1', 'Mobile'=>44454), 2=>array('Name'=>'Jayant2','Mobile'=>111222), 3=>array('Name'=>'Jayant3', 'Mobile'=>99999)); $this->excel->getActiveSheet()->fromArray($arrHeader,'','A1'); $this->excel->getActiveSheet()->fromArray($arrRows); header('Content-Type: application/vnd.ms-excel'); //mime type header('Content-Disposition: attachment;filename="'.$file_name.'"'); //tell browser what's the file name header('Cache-Control: max-age=0'); //no cache $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5'); $objWriter->save('php://output'); 

Actualmente uso esta función en mi proyecto después de una serie de búsqueda en Google para descargar el archivo Excel de la statement sql

  // $sql = sql query eg "select * from mytablename" // $filename = name of the file to download function queryToExcel($sql, $fileName = 'name.xlsx') { // initialise excel column name // currently limited to queries with less than 27 columns $columnArray = array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"); // Execute the database query $result = mysql_query($sql) 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; // fetch result set column information $finfo = mysqli_fetch_fields($result); // initialise columnlenght counter $columnlenght = 0; foreach ($finfo as $val) { // set column header values $objPHPExcel->getActiveSheet()->SetCellValue($columnArray[$columnlenght++] . $rowCount, $val->name); } // make the column headers bold $objPHPExcel->getActiveSheet()->getStyle($columnArray[0]."1:".$columnArray[$columnlenght]."1")->getFont()->setBold(true); $rowCount++; // Iterate through each result from the SQL query in turn // We fetch each database result row into $row in turn while ($row = mysqli_fetch_array($result, MYSQL_NUM)) { for ($i = 0; $i < $columnLenght; $i++) { $objPHPExcel->getActiveSheet()->SetCellValue($columnArray[$i] . $rowCount, $row[$i]); } $rowCount++; } // set header information to force download header('Content-type: application/vnd.ms-excel'); header('Content-Disposition: attachment; filename="' . $fileName . '"'); // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file // Write the Excel file to filename some_excel_file.xlsx in the current directory $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); // Write the Excel file to filename some_excel_file.xlsx in the current directory $objWriter->save('php://output'); }