PHP Language - excel exporter

Author

excel exporter

toffee

2006-10-06, 9:57 pm

Hi all,

i have several php files which each run a different SELECT query and
displays the data in a table. I would like to create an option to export the
table to excel and one route i would like to go down is creating a single
export.php file with an excel header; and basically it would read the sql
query stored in a session; and just create a spreadsheet from the results.
the problem is each query yields a different number of columns, so what php
code would tell it to select all fields for every row rather than specifying
each field one by one?

I hope this makes sense to you.

Kind regards

T

Noodle

2006-10-07, 7:58 am

Colin Fine

2006-10-07, 7:00 pm

toffee wrote:
> Hi all,
>
> i have several php files which each run a different SELECT query and
> displays the data in a table. I would like to create an option to export the
> table to excel and one route i would like to go down is creating a single
> export.php file with an excel header; and basically it would read the sql
> query stored in a session; and just create a spreadsheet from the results.
> the problem is each query yields a different number of columns, so what php
> code would tell it to select all fields for every row rather than specifying
> each field one by one?
>
> I hope this makes sense to you.
>
> Kind regards
>
> T
>
>

Assuming you're using MySQL,
mysql_fetch_row returns a row as an enumerated array
mysql_fetch_array returns an enumerated or associative array
mysql_fetch_object returns an object.

In each case, you do not need to know in advance how many fields will be
returned. And if you use the associative array or the object you can get
at the names of the fields as well.

Does this answer your question?

Colin

Gleep

2006-10-07, 7:00 pm

On Sat, 7 Oct 2006 03:40:38 +0100, "toffee" <toffee@toffee.com> wrote:

>Hi all,
>
>i have several php files which each run a different SELECT query and
>displays the data in a table. I would like to create an option to export the
>table to excel and one route i would like to go down is creating a single
>export.php file with an excel header; and basically it would read the sql
>query stored in a session; and just create a spreadsheet from the results.
>the problem is each query yields a different number of columns, so what php
>code would tell it to select all fields for every row rather than specifying
>each field one by one?
>
>I hope this makes sense to you.
>
>Kind regards
>
>T
>

here is the code i use to generate an excel spread sheet - you will have to adapat it to your needs
but this will get you started.

look into
Here is a simple script get names of columns in an array:
//Get names of columns in table
$field_names = array();
$res = mysql_query("SHOW COLUMNS FROM `my_table`");
for($i=0;$i<mysql_num_rows($res);$i++){
array_push($field_names,mysql_result($re
s, $i));
}

if($_POST['Submit']=="Create Excel Report"){
$qry = mysql_query("SELECT first_name, last_name, email, homephone, officephone, cellphone, address
FROM users WHERE username!='' ORDER BY last_name, first_name") or die(mysql_error() );
while($rs = mysql_fetch_array($qry)){
// THIS BUILDS UP THE RESULT ARRAY - THIS NEEDS TO BE SORTED FOR OUTPUT
// THIS SCRIPT STRIPS EACH ARRAY, FORMATS IT FOR EXCEL AND CREATES A LONG STRING WITH TABS AND
RETURNS FOR EXCEL FORMAT
// THIS ARRAY HAS TO MATCH CONFIGURATION OF THE HEADER ROW (BELOW)
$row = array($rs['first_name'], $rs['last_name'], $rs['email'], $rs['homephone'],
$rs['officephone'], $rs['cellphone'], $rs['address']);
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
} // END OF FOR EACH LOOP
$data .= trim($line)."\n";
$data = str_replace("\r","",$data);
} // END OF WHILE LOOP ABOVE
$header = "First Name\tLast Name\tEmail\tHome Phone\tOffice Phone\tCell Phone\tAddress";
// THIS CREATES THE EXCEL FILE
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=UserList.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
exit;
} // END OF SUBMIT

this may look confusing at first but it's not really.
you have to understand that the excel data must be formated with tab between each field - then at
the end of the row a line return for the next row. That is why I pull out the data into an array for
each row and do the string replace.

note that there needs to be a header row, it contains the titles of the fields and it MUST be in the
same order you extract the field data.

in your situation is a bit more complex because you said you will have different field names
depending on the report - so here you would need to get the field list first and turn that into a
string that would be formatted correctly and be placed in the SELECT query
ie $qry = mysql_query("SELECT field1, field2, field3 .....
then do that same on $header = "field1\field2\field3.....
I'll let you figure that out

the neat thing about this script is that it generates excel sheet on the fly and posts a
download/save interface

Sponsored Links