I'm looking for a code in php to export data in the bd Mysql to the Excel file that is to say I do list the data in a table and report it in the file excel.
If someone has an idea I thank you in advance
Thank you very much
Good day!
I'm looking for a code in php to export data in the bd Mysql to the Excel file that is to say I do list the data in a table and report it in the file excel.
If someone has an idea I thank you in advance
Thank you very much
Good day!
Assuming you wish to download the csv file as you run the script, I just use a typical mysql query with the following header:
header( "Content-Type: application/save-as" );
header ('Content-Disposition: attachment; filename="myfile.csv"');
Otherwise a csv file is just a comma delimited file, output each record with commas between the fields and ensure each record is on it's own individual line.
Hope this helps.
the steps involved woould be
1 connect to db
2 select desired records
3 create or open the destination file
4 step through the returned records writing them line by line into your destination file
5 close your destination file
mysql_connect
mysql_select_db
mysql_query
(you may want to check for 1 or more rows returned here)
fopen
mysql_fetch_array
- fwrite
fclose
try this
mysql -u username -ppassword --batch -e "select * from mytable" mydatabase > mydata.txt
or
SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;
<?php
// DB Connection here
$select = "SELECT * FROM table_name";
$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
$fields = mysql_num_fields ( $export );
for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . "\t";
}
while( $row = mysql_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 = "\n(0) Records Found!\n";
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>
Source: phpfreaks.com
There is a nice tool for reading Microsoft Excel files called PHPExcelReader. It works with .xls files up to Excel version 2003, which are based on the BIFF format (later versions use OOXML). It is written in native PHP and does not require any third-party libraries or the MS Office package.
Looping through all cells in a sheet with PHPExcelReader is as simple as this:
Code:require_once 'Excel/reader.php'; $reader = new Spreadsheet_Excel_Reader(); $reader->setOutputEncoding("UTF-8"); $reader->read("test.xls"); for ($i = 1; $i <= $reader->sheets[0]["numRows"]; $i++) { for ($j = 1; $j <= $reader->sheets[0]["numCols"]; $j++) { print "\"".$reader->sheets[0]["cells"][$i][$j]."\","; } echo "\n"; }
Bookmarks