Is anyone aware of a method to export specific fields from a database with custom column titles into excel from MSQL using PHP?
Where are there resources on this?
5
-
2stackoverflow.com/search?q=generate+excel+file+%5Bphp%5Ddeceze– deceze ♦2011-02-04 06:48:21 +00:00Commented Feb 4, 2011 at 6:48
-
Must be excel format, or is CSV good enough?The Scrum Meister– The Scrum Meister2011-02-04 06:48:55 +00:00Commented Feb 4, 2011 at 6:48
-
must be excel unfortunetly...Mike– Mike2011-02-04 07:04:09 +00:00Commented Feb 4, 2011 at 7:04
-
a good csv would suffice for the time thoughMike– Mike2011-02-04 07:04:32 +00:00Commented Feb 4, 2011 at 7:04
-
Unfortunately, Excel 2010 in Win 7 will no longer open files with the .xls extension if they are formatted as TAB-delimited text or CSV. The same files that download fine on my XP or Vista computers don't open at all on my Win7 computer.Steve– Steve2012-07-10 22:17:05 +00:00Commented Jul 10, 2012 at 22:17
Add a comment
|
3 Answers
Using the PHPExcel library:
// connection with the database
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "database";
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
// require the PHPExcel file
require 'Classes/PHPExcel.php';
// simple query
$query = "SELECT username,emailAdress,locationCity FROM users ORDER by id DESC";
$headings = array('User Name', 'EMail Address','City');
if ($result = mysql_query($query) or die(mysql_error())) {
// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('List of Users');
$rowNumber = 1;
$col = 'A';
foreach($headings as $heading) {
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading);
$col++;
}
// Loop through the result set
$rowNumber = 2;
while ($row = mysql_fetch_row($result)) {
$col = 'A';
foreach($row as $cell) {
$objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
$col++;
}
$rowNumber++;
}
// Freeze pane so that the heading line will not scroll
$objPHPExcel->getActiveSheet()->freezePane('A2');
// Save as an Excel BIFF (xls) file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="userList.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
}
echo 'a problem has occurred... no data retrieved from the database';
Using PHPExcel, you can also add formatting, or create workbooks with multiple worksheets, etc
2 Comments
wimvds
+1 For PHPExcel, but keep in mind that it uses lots of resources (well, lots of memory).
Mark Baker
@wimvds - I'm fully aware of all the limitations of PHPExcel... I am the developer - and there there are tricks and techniques that can be used to reduce memory and improve speed as well.
Excel will happily read a CSV file as a spreadsheet. CSV is just a text format, so create some text and echo it with the appropriate content-type:
$query = "SELECT col1, col2, col3 FROM table";
$result = mysql_query($query);
$csv = '"First Column Title","Second Column Title","Third Column Title"' . "\n";
while ($row = mysql_fetch_assoc($result)) {
$csv .= '"' . str_replace('"', '""', $row['col1']) . '",';
$csv .= '"' . str_replace('"', '""', $row['col2']) . '",';
$csv .= '"' . str_replace('"', '""', $row['col3']) . '"' . "\n";
}
header("Content-type: application/vnd.ms-excel");
echo $csv;
6 Comments
Mike
$csv .= '"' . str_replace('"', '""', $row['col1']) . '",'; What goes in the '' marks?
Trevor
Mike, I believe he is double-quoting any quotes that are in the fields because of the way .csv files use quotation marks.
Dan Grossman
That's right. Since values are enclosed in double quotes, any double quotes appearing within the value should be escaped. CSV format escapes double quotes by doubling them, i.e. turning
" into "", which is what the code there does.Mike
is $result = mysql_query($result); supposed to be $result = mysql_query($query); ??
Mike
This now downloads as a .php file with the results inside... what could cause that?
|
Just do a select on the fields you want, and output the results with fields separated by commas, and each row on its own line. Ideally, should should surround each value with quotes, and addslashes() to escape any quotes in the data.
Output the result and save to a file with a name ending in .csv, and open it in Excel.
