Как экспортировать данные в файл excel с помощью PHPExcel

Я взял исходный код из limesurvey и добавил библиотеку PHPExcel в свой код limesurvey, чтобы экспортировать данные в файл excel после нажатия ссылки. В настоящее время файл excel открывается с некоторыми фиктивными данными в нем без проблем. Мне нужно иметь возможность динамически добавлять данные с веб-сервера после того, как пользователь вводит данные опроса. Я просмотрел некоторые сайты, которые я нашел, но мне не повезло. Может кто-нибудь мне помочь?

РЕДАКТИРОВАТЬ

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

Если вы скопировали это прямо, то:

 ->setCellValue('B2', Ackermann')

должно быть

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

Отвечая на ваш вопрос:

Получите данные, которые вы хотите от limesurvey, и используйте setCellValue () для хранения этих значений данных в ячейках, где вы хотите их сохранить.

Файл примера Quadratic.php в / Тесты могут помочь в качестве отправной точки: он берет данные из входной формы и устанавливает ее в ячейки в книге Excel.

РЕДАКТИРОВАТЬ

Крайне упрощенный пример:

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

Использование существующего кода в качестве основы

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

Попробуйте приведенный ниже пример для того же

 <?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__)); ?>

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

В настоящее время я использую эту функцию в своем проекте после серии поисковых запросов для загрузки файла excel из 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'); }