Aug
30

Export from MySQL to Excel using PHP

PHP,SQL          Trackback

Did you ever wanted to export data from your MySQL database to some format nongeek people can work with? Here is simple PHP script you can use to export your data form MySQL table to Excel file. (Actually not real Excel XLS file but CSV file which Excel can read)
Download


".mysql_error().'
'.mysql_errno()); header('Content-Type: application/vnd.ms-excel'); //define header info for browser header('Content-Disposition: attachment; filename='.$dbTable.'-'.date('Ymd').'.xls'); header('Pragma: no-cache'); header('Expires: 0'); for ($i = 0; $i < mysql_num_fields($result); $i++) // show column names as names of MySQL fields echo mysql_field_name($result, $i)."\t"; print("\n"); while($row = mysql_fetch_row($result)) { //set_time_limit(60); // you can enable this if you have lot of data $output = ''; for($j=0; $j < mysql_num_fields($result); $j++) { if(!isset($row[$j])) $output .= "NULL\t"; else $output .= "$row[$j]\t"; } $output = preg_replace("/\r\n|\n\r|\n|\r/", ' ', $output); print(trim($output))."\t\n"; } ?>


20 Comments

Make A Comment
  • girish Said:

    its not working in IE.

  • Vlatko Zdrale Said:

    How do you mean its not working in IE? This is server side script so it should work in every browser. Did you change setting for your database?

  • Batty13 Said:

    Sweet, Thanks. Worked extremely well. Except I ran into an excel warning error when tested over excel 08! When file is accessed via save or open option you get warning saying something about data is not actually excel data. So I’m wondering if there is a way to input excel data code into this?
    Example, When you take a microsoft word document and copy paste it into a wusiwyg html program you can see all the ms word code added into code view for the ms word markup.

    Perhaps doing the same with excel would work, copy paste data into a wysiwyg editor, view code and then use some of the excel data??

  • batty Said:

    I think he means not working in IE over https. I had it working with IE6, IE7, Firefox. However, when I have the code inside the same directory that I have the user in with other files and set to HTTPS it does not work in either IE6 or IE7 but firefox is okay with it. Odd, Trying to figure it out myself.

  • lokin Said:

    broken is your form

  • lokin Said:

    I think he means it doesnt work in IE over https. I had it working in http with IE, but now that I am in a ssl directory trying to do the same thing I get an error where ie says it cant be found

  • lokin Said:

    I think he means it doesnt work over https. I had it working in http with IE, but now that I am in a ssl directory trying to do the same thing I get an error where ie says it cant be found

  • Vlatko Zdrale Said:

    I didn’t test it over https. But I will try…

  • batty Said:

    Vlatko!

    I Found a solution using the following headers in addition and in place of.

    It worked like a charm.

    header(“Content-type: application/x-msdownload”);

    header( ‘Cache-Control: must-revalidate, post-check=0, pre-check=0’ );

    header( ‘Content-Transfer-Encoding: binary’ );

    solution was found at the following site (scroll to bottom of post)

    http://forums.thedailywtf.com/forums/p/7211/193676.aspx#193676

  • Vlatko Zdrale Said:

    Thanks Batty, I will update code soon.

  • batty Said:

    No problem bro,

    Thanks for the awesome script!
    It worked so well and was very easy to understand. 😀

    By the way, do you know if there is a way to have excel format the data that is dumped?
    Thinks like row height, or background color? This is the next thing I need to figure out.

  • Vlatko Zdrale Said:

    Yes there is a simple way to do that. You just have to push same headers as in this example and then format html table and push it after it. Excel recognizes html table and imports it. I was playing with it little bit and it work ok. You can try, and let me know if you have any questions about it.

  • batty Said:

    Do you have a short example I can see?

    Ive tried a few things and different results but nothing correct where it formats the spreadsheet like a bgcolor

    Im guessing it needs to be inside the loops. Maybe a small sample of how to do for looop where you show column names of mysql fields?

    thanks in advance!

  • Zemoon Blog by Vlatko Zdrale » Blog Archive » Export MySQL To Excel better way Said:

    […] MS Excel file, but that wasn’t real excel files we were creating. Actually you were getting CSV and HTML files which excel can read. These were textual files, and now I am gonna show you how to […]

  • Vlatko Zdrale Said:

    Yes, I already wrote about that, and you can find that post here: Export MySQL to Excel with style using PHP & HTML tables . Let me know if you have any problem implementing it.
    But I think best way of exporting to excel is this one.

  • batty Said:

    Found a great way to add formatting! If you copy and paste text from ms word to design view of web editor, you get ms word code behind the scenes. Using this principle, I created an excel formatted sheet, then copy and pasted that styled sheet into a web editor design view, switched over to code view, and viola:
    excel formatted code.

    I then used that excel formatting code and added that into the correct fields or parts of the php code you’ve written in the great tutorial / example.

    hope you dont mind I add here for others that find yours!
    Will just put the major part, the while/for loop.

    NOTE: for “\t” I used a var instead
    $sep = “\t”;

    //my while/for loop
    while($row = mysql_fetch_row($result))
    {
    //set_time_limit(60); //
    $output = ”;
    echo “”;
    for($j=0; $j < mysql_num_fields($result); $j++)
    {
    if(!isset($row[$j]))
    $output .= “NULL”.$sep;
    else
    //$output .= “$row[$j]”.$sep;
    echo “”.$row[$j].””;
    }
    echo “”;
    $output = preg_replace(“/\r\n|\n\r|\n|\r/”, ‘ ‘, $output);
    print(trim($output)).”\t\n”;
    }
    echo ”;

  • batty Said:

    oops… my post doesnt show the html fields inside of echo, sorry about that..

    vlatko, could you please delete that post then? it’s wrong without the html : (

  • batty Said:

    kk, here is how i coded the html into the while loop for mysql data.
    Replace the word quote with actual quotation marks.
    Add note … is just shorthand for lazy

    while (…)
    {
    $output ….
    echo quotequote;
    for (…)
    {
    if(…)
    $output ….
    else
    echo quote quote.$row[$j].quotequote;
    }
    echo quotequote;
    $output =preg……
    print(….)
    }
    echo quotequote;

  • batty Said:

    sorry ya’ll.. my html keeps getting removed and or hidden.

    For the first echo, between the quotes add html tablerow

    second is dual quotes inside else statement. Set html for tabledata open and close so that row is nested inside them.

    Third quote is end tablerow

    Fourth quote outside while loop is end table.

    Sorry for the spam posts, didnt know my html would be hidden and wanted to share what I had figured out with others that find this post as it ranked high in google for the search term used “export mysql to excel using php”.

    Cheers,

    Batty13

  • Vlatko Zdrale Said:

    Thanks Batty for your contribution. I’ll compile all these methods for exporting mysql into excel in one big post when I find some time to work on it.

Comments RSS Feed    TrackBack URL

Leave a comment

top