Exporting result set to MS Excel

how do i do this?

[danielm28]

PART 1

I need to display a list of dealers on screen that are grouped by state and alphabetized. All states need to be shown.
In addition, each dealer needs to have a checkbox next to dealer name.

For Example, I need to show the following on screen:

Column1--> state1
Result 1-->(checkbox) Dealer1 in state1
(checkbox) Dealer2 in state1
(checkbox)DealerX in state1

Column2--> state2
Result 2--> (checkbox)Dealer1 in state2
(checkbox)Dealer2 in state2
(checkbox)DealerX in state2

Column2--> stateX
Result 2--> (checkbox)Dealer1 in stateX
(checkbox)Dealer2 in stateX
(checkbox)DealerX in stateX

this is my sql query to retrieve the data ==> "select dealer_id, name FROM `dealer` order by state,name"

QUESTIONS:

1. I would like to loop through the result set to see whether or not the "state" field changes. When it does, I want to start a new column. Then after x columns have been created on the page, i want to move 2 rows down to begin with the next column. Does anyone have a code example for this or anything similar?

PART 2

Once dealers are shown by state with a checkbox next to the dealer name, the user should be able to select X number of dealers and then click submit.
This would execute the following query ==> "select name, state,zipcode from dealers where dealer_id(which will be passed in the checkbox) in (1,2,.,n)"
The results need to open within microsoft excel with the following format:

state1 (bold)
name1 zip2 (located in column B in Excel)

state2 (bold)
name2 zip2 (located in column B in Excel)

stateX (bold)
nameX zipX (located in column B in Excel)

QUESTION

1. How can i have microsoft excel automatically open with the result set within the worksheet?
2. How can I format the sheet to display my desired format above?

Codes sample would be highly appreciated.

thanks

[coopster]

I don't have time to throw down a sample, but I can tell you that you may want to read up on certain functions such as fopen() [php.net] and fwrite() [php.net] to open a new file and write to it. When you open the file you can give it a '.xls' extension.

To format the file, just add the delimiters (comma, tab, space, semicolon, etc.) and the column value qualifiers (double-quote, single-quote, none) and insert them accordingly upon each iteration of the columns of the rows in your result set. Of course, each row might get a newline inserted after it to get you to the next row of the "spreadsheet".

Hope this gets you thinking in the right direction.

[Tangerine Dream]

1. How can i have microsoft excel automatically open with the result set within the worksheet?

Hi, you can return you data to browser either in CSV or in HTML format (it's a sort of fake) by sending appropriate MIME type like this:

-- begin PHP

header("Expires: Sat, 01 Jan 2000 00:00:00 GMT");
header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public"); 
header("Content-Description: File Transfer");
session_cache_limiter("must-revalidate");
header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename="fileToExport.xls"');

-- end PHP
This sample code found in session_cache_limiter [ru.php.net] topic

-- begin PHP

header('Cache-Control: no-cache, no-store, must-revalidate, post-check=0, pre-check=0');
header('Pragma: no-cache');
header('Content-Type: application/x-msexcel; charset=windows-1251; format=attachment;');
header('Content-Disposition: attachment; filename=some.xls');
echo '<table>';
while($res=mysql_fetch_assoc())
echo '<tr><td>'.implode('</td><td>',$res).'</td></tr>';
echo </table>

-- end PHP

[edited by: jatar_k at 3:56 pm (utc) on April 13, 2005]
[edit reason] no urls thanks [/edit]

[danielm28]

that worked. thanks guys