Export MySql data into Excel or CSV format
To generate reports or to print data or for some other purpose we often need data to be export in excel or csv format. It is quiet easy to fetch records from mysql and export the result set as .csv file or .xls file and let user to force download it. Here is the code:
Here, first we will extract the data which we want to export.
Then, will make make headers for exported file and then append the exported data
then will forcefully download the file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
<?php /* * Export Mysql Data in excel or CSV format using PHP * Downloaded from http://DevZone.co.in */ // Connect to database server and select $con=mysqli_connect('localhost','root','','adventureworks'); if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } // retrive data which you want to export $query = "SELECT * FROM vendor limit 60"; $header = ''; $data =''; $export = mysqli_query($con,$query ) or die(mysqli_error($con)); // extract the field names for header while ($fieldinfo=mysqli_fetch_field($export)) { $header .= $fieldinfo->name."\t"; } // export data while( $row = mysqli_fetch_row( $export ) ) { $line = ''; foreach( $row as $value ) { if ( ( !isset( $value ) ) || ( $value == "" ) ) { $value = "\t"; } else { $value = str_replace( '"' , '""' , $value ); $value = '"' . $value . '"' . "\t"; } $line .= $value; } $data .= trim( $line ) . "\n"; } $data = str_replace( "\r" , "" , $data ); if ( $data == "" ) { $data = "\nNo Record(s) Found!\n"; } // allow exported file to download forcefully header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=devzone_co_in_export.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data"; ?> |
–Export MySql data into Excel or CSV format–
