PHPExcel

Topics: Developer Forum, Project Management Forum, User Forum
Aug 19, 2011 at 9:03 AM

I am trying to get a database Mysql export to Excel using phpExcel, here's my code:

<?php
require_once '../Classes/PHPExcel.php';
//require_once '../Classes/PHPExcel/IOFactory.php';

$sql = "Select * from $DB_TBLName";

mysql_connect("127.0.0.1","root","");
mysql_select_db("development");
$result = mysql_query("select * from zr_1657");

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
							 ->setLastModifiedBy("Maarten Balliauw")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");

$objPHPExcel->setActiveSheetIndex(0);

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');

// datadunp
$col = 0;
$row = 0;
while($row = mysql_fetch_assoc($result)) {
    foreach($row as $key=>$value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
        $col++;
    }
    $row++;
}



// Redirect output to a client�s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment;filename="omgnotworking.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

Is there something obvious that i am doing wrong? I'm quite new to PHP, so maybe there's a syntax or something.

Aug 19, 2011 at 9:16 AM
Edited Aug 19, 2011 at 9:17 AM

You are actually using the $row var twice ;) once as a row iterator, and once as an array to contain the result data from the mysql query ;)

 

So, the solution is to change the name of one of the two! Furthermore, it would probably help if you could describe your problem a little more. I'm assuming your script isn't working :)

Aug 19, 2011 at 10:32 AM

I want to export data from my table in my database, and export into a excel file, I can't find an example from phpExcel class i am using that does this and pull data and save it into a excel file. I have used phpExcel because i need to create multiple worksheets in Excel. I manage get the script going, but when it downloads the file, it pulls nothing from the database, so the file is empty.

Aug 19, 2011 at 10:40 AM

dit you read the rest of my comment? you initialiser $row as an int with value 0, then you overwrite it with the mysql result. Then you try to use the result row ($row) as a rowindex, which obviously doesn't work ;) Furthermore, you init $col to 0 from outside the while loop, this means it never gets reset during the fetching of the mysql result.

 

you probably want something like the following:

// datadunp
$rowIterator = 0;
while($row = mysql_fetch_assoc($result)) {
   $col = 1;

    foreach($row as $key=>$value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $rowIterator, $value);
        $col++;
    }
    $rowIterator++;
}

May 6, 2013 at 11:43 AM
Dear,

I want to know how export mysql database to excel 2010 or 2013. I tried more but when i open xls file it still loading....
Please help me!
Coordinator
May 6, 2013 at 6:02 PM
@ndtdnc - I appreciate that English probably isn't your first language, but can you actually explain what your problem is?