Export mysql database to excel

Question

I have been trying to export the content of mysql database to excel using the script below to no avail. I must be doing somethign wrong:

$getExcel = "SELECT name, age, course, city FROM person";
$res = mysql_query($getExcel);


/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

/** PHPExcel */
require_once 'Classes/PHPExcel.php';


// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();

if(!$res){
    die("Error");
}
$col = 0; 
$row = 0; 
while($row = mysql_fetch_assoc($res)) { 
    foreach($row as $key=>$value) { 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value); 
        $col++; 
    } 
    $row++; 
} 

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('record.xlsx');

What I'm I doing wrong please?

Aside from the fact that $row should start at 1 rather than 0, what's the actual problem? Is the file being generated? Are you getting any error messages? — Mark Baker
– Mark Baker, Commented Mar 15, 2012 at 16:11
Keep it simple, just create an html file in your code with a table in it, and save it as .xls, then open it with excel. it's a dirty dirty hack but it works if all you need to do is export some data to excel. It won't an entire whole system... — Yaniro
– Yaniro, Commented Mar 15, 2012 at 16:11
@Yaniro - it's a dirty trick that brings up a warning message in most new versions of MS EXcel - not the greatest user experience in the world... I'd consider it pretty unprofessional if I was the user opening that file — Mark Baker
– Mark Baker, Commented Mar 15, 2012 at 16:16
@Mark Baker, The file is not being generated and i am not getting any errors either. I can connect to the db and the query does return data - just doesn't generate the excel file. I have tried changing $row to 1 but no joy. I also tried changing $col to 1... No success. — echez
– echez, Commented Mar 15, 2012 at 16:19
The file should be generated in the directory on the server where the script is running... double check user permissions for that folder... or try saving to php://output with appropriate headers (and without any echoes in the code) to download it directly from your browser — Mark Baker
– Mark Baker, Commented Mar 15, 2012 at 16:21

Mark Baker · Accepted Answer · 2012-03-15 17:03:17Z

4

Your issue is, likely, that you're using the variable $row in two different contexts ... try this:

$row = 1; 
while($mrow = mysql_fetch_assoc($res)) { 
    $col = 0; 
    foreach($mrow as $key=>$value) { 
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value); 
        $col++; 
    } 
    $row++; 
}

edited Mar 15, 2012 at 17:03

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

answered Mar 15, 2012 at 16:28

keithhatfield

3,2831 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

echez Over a year ago

Alright, this generates the excel but the columns are not aligned. E.g. if the first record starts at A1, the second start at C2, then G3 instead of all records to start at column A. Any ideas guys?

Mark Baker Over a year ago

$col = 0; should be inside the while loop, immediately before the foreach() loop

echez Over a year ago

You twuo guys are amazing. I'm eternally grateful!!

Collectives™ on Stack Overflow

Export mysql database to excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related