Export MySql Data to Excel
Moderator: General Moderators
Export MySql Data to Excel
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
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
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.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......
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.
- mrvijayakumar
- Forum Commoner
- Posts: 58
- Joined: Tue Aug 18, 2009 12:39 am
- Location: Chennai city, India
- Contact:
Re: Export MySql Data to Excel
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.
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
I have to disagree. It doesn't split the records into different excel sheets.... it simply creates several csv files.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.
Re: Export MySql Data to Excel
Thanks to both of U!!!!! 
Re: Export MySql Data to Excel
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!!!!!
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!!!!!
- mrvijayakumar
- Forum Commoner
- Posts: 58
- Joined: Tue Aug 18, 2009 12:39 am
- Location: Chennai city, India
- Contact:
Re: Export MySql Data to Excel
I will work it out and come here with good solution. Thanks.
Re: Export MySql Data to Excel
Thanks!!!!!
r u aware of Ruby on rails?
r u aware of Ruby on rails?
- mrvijayakumar
- Forum Commoner
- Posts: 58
- Joined: Tue Aug 18, 2009 12:39 am
- Location: Chennai city, India
- Contact:
Re: Export MySql Data to Excel
Really sorry..
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: Export MySql Data to Excel
Look at my suggestion to split your rows across several worksheets, and export to a genuine Excel file rather than a CSV file.saranyas wrote:it exports onto different csv files......s ter any other way to export into different sheets in a single excel 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
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!!!!!
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!!!!!