How to export mysql table data to excel 2007

Question

I want to export data from mysql tables to excel sheet. I am using excel 2007. Once this code was working properly but today I am getting problem. Please guide me where I am doing wrong. I have huge data about 60,000 rows.

<?php
/* 
Export MySQL to Excel using PHP & HTML tables
Author: Vlatko Zdrale, http://blog.zemoon.com

Look but don't touch :)
*/
    include "mysql_connection.php";
    //$dbTable = 'info';            // table name
    $con=open_db_connection();


    $sql = "select info_id, name, category_list.category, company_name, company_address, company_phone, date from info, city_list, category_list where city=cid and info.category=id";
    $result = @mysql_query($sql)    or die("Couldn't execute query:<br>".mysql_error().'<br>'.mysql_errno());

    header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');   //define header info for browser
    header('Content-Disposition:attachment; filename=information-'.date('Ymd').'.xlsx');
    header('Pragma: no-cache');
    header('Expires: 0');

    echo '<table><tr>';
    for ($i = 0; $i < mysql_num_fields($result); $i++)   // show column names as names of MySQL fields
        echo '<th>'.mysql_field_name($result, $i).'</th>';
    print('</tr>');

    while($row = mysql_fetch_row($result))
    {
        //set_time_limit(60); // you can enable this if you have lot of data
        $output = '<tr >';
        for($j=0; $j<mysql_num_fields($result); $j++)
        {
            if(!isset($row[$j]))
                $output .= '<td>&nbsp;</td>';
            else
                $output .= "<td>$row[$j]</td>";
        }
        print(trim($output))."</tr>\t\n";
    }
    echo('</table>');
?>

Its very important please guide me. Thanks in advance.

@Passerby its creating .xlsx file and asking to download but when i m opening the file after downloading its giving the error "cannot open file file format or extension is not valid" — Pawan Gupta
– Pawan Gupta, Commented Sep 21, 2012 at 9:14

Mark Baker · Accepted Answer · 2012-09-21 09:39:41Z

1

You will get that message because the file isn't an OfficeOpenXML xlsx file, but a file containing HTML markup with a .xlsx extension. You're telling Excel that the file is one format by the extension when it's really another, and it let's you know that the content doesn't match the extension. As long as it can read the HTML markup cleanly, it should still load successfully, but will always issue this message.

The more recent versions of Excel are more fussy about this than earlier versions.

If you want to get rid of the message, then either you rename your .xlsx as a .html file so that the extension matches the content (you'll need to import into Excel then, or moodify file associations so that html files are opened using Excel); or give it a tab-separated value file with a csv extension (that can be opened by double click), though you can't add any formatting using this option; or give it a real OfficeOpenXML .xlsx file

answered Sep 21, 2012 at 9:39

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pawan Gupta Over a year ago

Yea you are correct. but I want an OfficeOpenXML xlsx only how can i achieve that with the simple modification of above code ??

Mark Baker Over a year ago

You can't achieve it with a simple modification of your code, you'd need to use a library such as PHPExcel that actually creates real OfficeOpenXML file

Pawan Gupta Over a year ago

Ohhk thanks but now I hv tired with code so i just connected excel sheet with mysql using odbc connecter. now there no need to export always when i data updated in mysql table again and again. :)

Fry_95 · Accepted Answer · 2012-09-21 08:01:14Z

0

First, you should put the output in a buffer variable and not echo or print all the way.

After that you should read your own comment -> //set_time_limit(60); // you can enable this if you have lot of data

answered Sep 21, 2012 at 8:01

Fry_95

2411 silver badge6 bronze badges

2 Comments

Pawan Gupta Over a year ago

I m very new in php so don't know whw to put output in buffer variable will u pls give me a simple example for that

Fry_95 Over a year ago

Just put a $output = ''; at the beginning and replace echo and print by $output .= then put a print $output; at the end.

sics · Accepted Answer · 2012-09-21 08:02:02Z

0

First of all: tell us something about the error!

I recommend you to do one of the two following steps (or both).

set_time_limit(60); 
// increase this value to 300 (means the server doesn't timeout for 5 minutes)

ini_set('memory_limit','512M');
// increases the memory limit to 512 MB

answered Sep 21, 2012 at 8:02

sics

1,3181 gold badge11 silver badges25 bronze badges

1 Comment

Pawan Gupta Over a year ago

thanks for ur advice but still i m getting same error. I hv added both of line u recommended but getting same error

Arthur Kushman · Accepted Answer · 2012-09-21 08:03:09Z

0

Try to use "\t" between columns not rows, it makes the native offset/margin in Excel. Also better use for loop with curly-braces as a better standard to not confuse Yourself.

answered Sep 21, 2012 at 8:03

Arthur Kushman

3,65910 gold badges53 silver badges64 bronze badges

Comments

Passerby · Accepted Answer · 2012-09-21 09:33:01Z

0

From your comment I'll guess something is messing up the markup.

In everything between <th></th> and <tr></tr>, try to use htmlentities:

for ($i = 0; $i < mysql_num_fields($result); $i++)   // show column names as names of MySQL fields
    echo '<th>'.htmlentities(mysql_field_name($result, $i),ENT_COMPAT,"UTF-8").'</th>';

And

if(is_null($row[$j]))
     $output .= '<td>&nbsp;</td>';
else
     $output .= "<td>".htmlentities($row[$j],ENT_COMPAT,"UTF-8")."</td>";

Note that I'm assuming your encoding to be UTF-8.

answered Sep 21, 2012 at 9:33

Passerby

10.1k2 gold badges37 silver badges53 bronze badges

Collectives™ on Stack Overflow

How to export mysql table data to excel 2007

5 Answers 5

3 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related