Export mysqli query

Aug 12, 2010 at 12:47 AM
Hi, I am very new to this. Your help would be greatly appreciated. I would like to know how to export data from a MySql table into an excel spreadsheet. I have the knowledge to get the information from the database to the spreadsheet but how do I have the code repeat until all data is echoed onto the page? Here is what I have already. This produces only a single row of results from the database...just need to know how to get it all onto the spreadsheet! <?php /** Error reporting */ error_reporting(E_ALL); /** PHPExcel */ require_once '../Classes/PHPExcel.php'; // Create new PHPExcel object $objPHPExcel = new PHPExcel(); require_once('../includes/connection.php'); $result = mysqli_query($connection,"SELECT * FROM students ORDER BY school ASC") or die(mysqli_error($connection)); while($row = mysqli_fetch_array($result)) { //get all rows you want from the tutors table $school = $row['school']; } // Set properties $objPHPExcel->getProperties()->setCreator("Inschool") ->setLastModifiedBy("Somebody") ->setTitle($school."Student List") ->setSubject($school."Student List") ->setDescription("Student List at ".$school) ->setKeywords("Student Listr") ->setCategory("Student List"); // Add some data $result = mysqli_query($connection,"SELECT * FROM students ORDER BY school ASC") or die(mysqli_error($connection)); while($row = mysqli_fetch_array($result)) { //get all rows you want from the tutors table $id = $row['id']; $full_name = $row['full_name']; $school = $row['school']; $year = $row['school_year']; $class = $row['class']; $contact = $row['contact']; $phone_h = $row['phone_h']; $phone_m = $row['phone_m']; $tutor_1 = $row['tutor_1']; $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', $full_name) ->setCellValue('B1', $contact) ->setCellValue('C1', $year.$class) ->setCellValue('D1', $tutor_1); } // Rename sheet $objPHPExcel->getActiveSheet()->setTitle('Simple'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet $objPHPExcel->setActiveSheetIndex(0); // Redirect output to a client’s web browser (Excel5) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="01simple.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; ?>
Aug 17, 2010 at 9:19 AM
hello all you need is to make it like this.. $counter=1; // add a counter for the row loop{ $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A'.$counter, $full_name) ->setCellValue('B'.$counter, $contact) ->setCellValue('C'.$counter, $year.$class) ->setCellValue('D'.$counter, $tutor_1); $counter++; } let me know if this works.. cheers!