Page 1 of 1
Export MySql Data to Excel
Posted: Sat Sep 05, 2009 2:38 am
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
Re: Export MySql Data to Excel
Posted: Sat Sep 05, 2009 3:48 am
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.
Re: Export MySql Data to Excel
Posted: Sat Sep 05, 2009 6:01 am
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);
?>
Re: Export MySql Data to Excel
Posted: Sat Sep 05, 2009 11:06 am
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.
Re: Export MySql Data to Excel
Posted: Wed Sep 30, 2009 11:13 pm
by saranyas
Thanks to both of U!!!!!

Re: Export MySql Data to Excel
Posted: Wed Sep 30, 2009 11:39 pm
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!!!!!
Re: Export MySql Data to Excel
Posted: Wed Sep 30, 2009 11:58 pm
by mrvijayakumar
I will work it out and come here with good solution. Thanks.
Re: Export MySql Data to Excel
Posted: Thu Oct 01, 2009 1:45 am
by saranyas
Thanks!!!!!
r u aware of Ruby on rails?
Re: Export MySql Data to Excel
Posted: Thu Oct 01, 2009 2:36 am
by mrvijayakumar
Really sorry..
Re: Export MySql Data to Excel
Posted: Thu Oct 01, 2009 5:15 am
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).
Re: Export MySql Data to Excel
Posted: Tue Oct 06, 2009 5:09 am
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!!!!!