Export MySql Data to Excel

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
saranyas
Forum Newbie
Posts: 5
Joined: Sat Sep 05, 2009 2:34 am

Export MySql Data to Excel

Post by saranyas »

Hi..I am a beginner in PHP.........i have generated a report for my proj wherein it holds more than lakh records & i need it to export it to excel....as excel allows only 65000 records, & it shows "SHTML Wrapper - 500 Server Error" & also many records are missing......
It would be more helpful if u figure out where am wrong n wat should be done......go threwed many forums but couldnt find way to do it.....I need it urgently......
Thanks in advance
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Export MySql Data to Excel

Post by Mark Baker »

saranyas wrote:Hi..I am a beginner in PHP.........i have generated a report for my proj wherein it holds more than lakh records & i need it to export it to excel....as excel allows only 65000 records, & it shows "SHTML Wrapper - 500 Server Error" & also many records are missing......
You don't say how you're exporting to Excel, which could make a difference. If you're simply exporting as a comma or tab separated value file, then trying to open it with Excel, then this is a real problem.

If you're exporting to a real Excel file, then there's a few options.

Excel 2007 (xlsx) supports more than 64k rows in a worksheet, so that might be an option for you; though if you try to open in Excel 2003 (even with the compatibility pack), it will error.

Alternatively, the 64k limit for Excel (xls) files is 54k rows per worksheet. You could split your rows across several worksheets, so (for example) a workbook with 4 worksheets would allow you to export 256k rows.
User avatar
mrvijayakumar
Forum Commoner
Posts: 58
Joined: Tue Aug 18, 2009 12:39 am
Location: Chennai city, India
Contact:

Re: Export MySql Data to Excel

Post by mrvijayakumar »

Hi,
Try below code, it will be more useful for generating excel files, if we have thousands of records. It will split records into different excel sheets depends upon number of records.

Edit '$rows_per_page' to have number of records in each excel file. Write me for more.

Code: Select all

<?php
 
    //Connection
    $server = "localhost";  //Host name
    $username = "root"; //DB username
    $password = ""; //DB Password
    $dbname = "database"; //DB name
    $tablename = "table"; //table name
    $rows_per_page =5; //Total number of records to present in each page
    
    //DB connection
    $conn =  mysql_connect($server,$username,$password);
    mysql_select_db($dbname,$conn);
    
    //Queries
    $querycount = "SELECT count(*) FROM $tablename";
    $query = "SELECT *  FROM $tablename";
    
    //For page count
    $result = mysql_query($querycount) or trigger_error("SQL", E_USER_ERROR);
    $query_data = mysql_fetch_row($result);
    $numrows = $query_data[0];
    $lastpage      = ceil($numrows/$rows_per_page); //Calculate total number of files to be generated
    $pageno = 1; //intialize page as 1
    
    //Generating files in CSV format
    for($i=1; $i<=$lastpage; $i++) {
        
        //Fixing limit
        $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
        $filename = "master_data_" . $i . ".csv";
        $fp = fopen($filename, "w");
        
        $res = mysql_query("$query $limit");
        
        // fetch a row and write the column names out to the file
        $row = mysql_fetch_assoc($res);
        $line = "";
        $comma = "";
        foreach($row as $name => $value) {
        $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
        $comma = ",";
        }
        $line .= "\n";
        fputs($fp, $line);
        
        // remove the result pointer back to the start
        mysql_data_seek($res, 0);
        
        // and loop through the actual data
        while($row = mysql_fetch_assoc($res)) {
        
        $line = "";
        $comma = "";
        foreach($row as $value) {
        $line .= $comma . '"' . str_replace('"', '""', $value) . '"';
        $comma = ",";
        }
        $line .= "\n";
        fputs($fp, $line);
        }
        $pageno = $pageno+1; //count page number for next file generation
        
    }
    fclose($fp);
 
?>
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Export MySql Data to Excel

Post by Mark Baker »

mrvijayakumar wrote:Try below code, it will be more useful for generating excel files, if we have thousands of records. It will split records into different excel sheets depends upon number of records.
I have to disagree. It doesn't split the records into different excel sheets.... it simply creates several csv files.
saranyas
Forum Newbie
Posts: 5
Joined: Sat Sep 05, 2009 2:34 am

Re: Export MySql Data to Excel

Post by saranyas »

Thanks to both of U!!!!! :)
saranyas
Forum Newbie
Posts: 5
Joined: Sat Sep 05, 2009 2:34 am

Re: Export MySql Data to Excel

Post by saranyas »

Hiiiiii Vijay
Ur code worked out!!!Thanks!!!!but it exports onto different csv files......s ter any other way to export into different sheets in a single excel file.??????

Also do u know RoR?

Thanks in advance!!!!!
User avatar
mrvijayakumar
Forum Commoner
Posts: 58
Joined: Tue Aug 18, 2009 12:39 am
Location: Chennai city, India
Contact:

Re: Export MySql Data to Excel

Post by mrvijayakumar »

I will work it out and come here with good solution. Thanks.
saranyas
Forum Newbie
Posts: 5
Joined: Sat Sep 05, 2009 2:34 am

Re: Export MySql Data to Excel

Post by saranyas »

Thanks!!!!!
r u aware of Ruby on rails?
User avatar
mrvijayakumar
Forum Commoner
Posts: 58
Joined: Tue Aug 18, 2009 12:39 am
Location: Chennai city, India
Contact:

Re: Export MySql Data to Excel

Post by mrvijayakumar »

Really sorry..
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Export MySql Data to Excel

Post by Mark Baker »

saranyas wrote:it exports onto different csv files......s ter any other way to export into different sheets in a single excel file.??????
Look at my suggestion to split your rows across several worksheets, and export to a genuine Excel file rather than a CSV file.
To do this, you'd need a PHP library capable of writing Excel files, such as PHPExcel, or to use COM objects (if you're on a Windows server with Excel installed).
saranyas
Forum Newbie
Posts: 5
Joined: Sat Sep 05, 2009 2:34 am

Re: Export MySql Data to Excel

Post by saranyas »

hi i need an urgent help again.....
i need to compress de exported csv files as .rar or .zip & mail wit attachment.......
how s it poss in php?
wen i searched,they r askin to import de class "zip"
also wen i tried it wit .gz,it said file cannot be opened...call to undefine dmethod so on.. s ter any soln for it..
thanks in advance!!!!!
Post Reply