Exporting Mysql Data

[arpit_gadle]

Dear All,

        I am a newbie to PHP. I want to know how to export data returned by a sql query to a Excel file.

 

Regards,

Arpit

[cags]

The simplest method would be to create a csv file and simply import that with Excel. Otherwise you will have to find an extension/set of classes for working with Excel in PHP such as PHPExcel. If you have a PHP version newer that 5.1.0 you can use...

 

$file = fopen('path.csv', 'w');

while($row = mysql_fetch_assoc($result)) {
   fputcsv($file, $row);
}

fclose($file);

[arpit_gadle]

Dear Cags,

  Thanks for the solution. After creating CSV of the data returned by query what i have to is this?

Psuedo Code

 

1. String filename="test.xls";

2. open test.xls using fopen;

3. read data from CSV just created

4. write data to test.xls using some fwrite function;

 

 

Am I right;

 

 

Regards,

 

Arpit

[cags]

No. The point of creating a CSV file is that you don't have to mess around finding a decent library for working with the xls format, you completely sidestep it by using Excel manually. You would simply take the csv file, open Excel and click 'Import from CSV', then click save to give you an xls file. If you have to create the xls file with PHP then you will be looking at an awful lot more code than I posted in my last post.

[arpit_gadle]

But the psuedo which i posted makes sense as i have to generate excel in web-app when user clicks on a button...  and give him a option to either open the excel or save it on disk

[cags]

No the pseudo code doesn't make sense. The whole point of using the csv was to avoid the complexities of writing to the xls format. If you are going to write to xls using PHP there is no point in creating a csv file first, you might aswell simply take the data direct from the database. You will need to use a set of classes designed to manipulate xls files though, such as PHPExcel as I mentioned in my very first post.

[arpit_gadle]

but you agree with the logic given in the psuedo code? I agree what you are saying but processing time taken by psuedo code logic is less than the generating and excel file using PHP Excel package and formatting is not my requirement only data is to be inserted to excel file from database and the rest of the things will be handle by data entry department viz formattingm filtering of excel etc....

[cags]

Ok, sure. Feel free to go ahead and use fwrite to write out your xls file. Just to make your life a little easier here is the specification for the xls format.

 

http://download.microsoft.com/download/0/B/E/0BE8BDD7-E5E8-422A-ABFD-4342ED7AD886/Excel97-2007BinaryFileFormat(xls)Specification.pdf

 

Good luck.

[arpit_gadle]

thanks pal...

        but why would i have to refer Microsoft office specification as my requirements are very basic..

 

 

Thanks once again

 

 

Arpit Gadle

[cags]

Because the specification shows the REQUIRED format of an xls file. How complex you think your problem is doesn't matter, a valid xls file must follow a set of specifications. Hence the fact I recommended using csv files which Excel can read and has a very simple file format.

[arpit_gadle]

okie dokieee

[otuatail]

of you use phpmyadmin there is an export there.

 

Desmond