Results 1 to 7 of 7

Thread: export data in the bd Mysql to the Excel file

  1. #1
    Join Date
    Nov 2005
    Posts
    625

    export data in the bd Mysql to the Excel file

    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!

  2. #2
    Join Date
    Dec 2007
    Posts
    1,599
    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.

  3. #3
    Join Date
    Dec 2007
    Posts
    1,736
    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

  4. #4
    Join Date
    Jun 2006
    Posts
    623
    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;

  5. #5
    Join Date
    Dec 2007
    Posts
    2,291
    <?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

  6. #6
    Join Date
    Mar 2009
    Posts
    1

    Re: export data in the bd Mysql to the Excel file

    Quote Originally Posted by EINSTEIN_007 View Post
    <?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
    Hi Einstein,

    Thanks for the code above.

    Is there any way which the name of the file , currently your_desired_name.xls can be modified or inputted by the user.

    Many thanks,

    Harry

  7. #7
    Join Date
    May 2008
    Posts
    115

    Re: export data in the bd Mysql to the Excel file

    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";
    }

Similar Threads

  1. How to export data from tally to excel
    By Amitesh in forum Windows Software
    Replies: 5
    Last Post: 19-02-2011, 03:35 PM
  2. Export excel data to Word 2007
    By Uzair in forum Windows Software
    Replies: 5
    Last Post: 25-02-2010, 06:31 PM
  3. Insert data from an Excel file into MySQL.
    By Antonio1 in forum Windows Software
    Replies: 3
    Last Post: 24-11-2009, 01:51 AM
  4. How to export data from acess to excel
    By Gunter in forum Windows Software
    Replies: 3
    Last Post: 13-05-2009, 12:45 PM
  5. Export Project Data to Excel
    By Asgar in forum Microsoft Project
    Replies: 3
    Last Post: 20-06-2007, 07:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,643,240,559.95783 seconds with 16 queries