[NA] PHP -> Excel?

[CJones]

OK guys.. I feel like I'm chasing my own tail here.

I am using PHP to pull a large chunk of data from Oracle and mySQL. I combine the results from these queries into one large array. I use the array and PHP to write HTML to display the data on a webpage. I would like to be able to export the data from the array to Excel as well, but WITHOUT having to run the queries again (for speed reasons).

I know I can redirect to a new page and have PHP run the queries again and use header(...) to export to Excel, but that requires running the queries again. I also don't want to use the header(...) on the current page because I don't want to lose the data that is displayed on the current page and have to wait for it to refresh.

I tried using PHP to write JavaScript that creates an AciveXObject, but that only works in IE and only if security levels are set to allow ActiveX objects. Since I am in a corporate environment, the IE security is set too high and I don't want to deal with it getting reset by a logon script if someone manually adjusts it low enough for my page to work.

Any ideas on how I can make this work without having to run the queries again? I think I've thought about it for so long that I'm starting to confuse myself...

 

[JeffDG]

OK guys.. I feel like I'm chasing my own tail here.

I am using PHP to pull a large chunk of data from Oracle and mySQL. I combine the results from these queries into one large array. I use the array and PHP to write HTML to display the data on a webpage. I would like to be able to export the data from the array to Excel as well, but WITHOUT having to run the queries again (for speed reasons).

I know I can redirect to a new page and have PHP run the queries again and use header(...) to export to Excel, but that requires running the queries again. I also don't want to use the header(...) on the current page because I don't want to lose the data that is displayed on the current page and have to wait for it to refresh.

I tried using PHP to write JavaScript that creates an AciveXObject, but that only works in IE and only if security levels are set to allow ActiveX objects. Since I am in a corporate environment, the IE security is set too high and I don't want to deal with it getting reset by a logon script if someone manually adjusts it low enough for my page to work.

Any ideas on how I can make this work without having to run the queries again? I think I've thought about it for so long that I'm starting to confuse myself...

First, I've never done this, so take it with a grain of salt...

You can connect to Excel files via ODBC/ADO...essentially each worksheet becomes a "table" and you can insert rows into it like it were a database...

 

[Areeda]

The easy way to do this is to take the HTML table and export it as with the mime type of an excel spreadsheet. Opens fine in excel or open office.

If the problem is running the query, save the html table id'ed as something like the search id is used in this forum.

Joe

 

[Clark1961]

Joe's suggestion should work as long as the formatting doesn't make things wonky.

Another approach would be to just write the array to an Excel file as a parallel or subsequent process.

http://www.appservnetwork.com/modules.php?name=News&file=article&sid=8

Fair warning on anything I suggest: I'm always messing around inside Excel/VBA and this external stuff is foreign.

 

[jesse]

This is ghetto, but would work, you could "cache" the results of all your queries. If you did something like:

//this is pretty much just pseudo code that I wrote quickly without looking anything up.  I didn't try to execute it.  Good luck :)

$randomValue = rand(1000,100000);
$storagePath='/tmp';

//make sure file does not exist
while(is_file($storagePath.'/'.$randomValue)) {
   $randomValue = rand(1000,100000);
}

//store array
$yourArray = serialize($yourArray);
file_put_contents($storagePath.'/'.$randomValue, $yourArray);

Basically, shove the array onto the filesystem. In PHP you can serialize an object or an array and store it. Then you can have a URL you call with that randomValue as a parameter. If that file exists on the filesystem your code can unserialize it and build the excel file.

Write a script to cleanup old cached files after 24 hours.

Make sense? You could also just write the CSV to the filesystem and create a link to it....

This scenario isn't ideal --- the ideal solution would be to speed up the database layer. If you do use my approach, if someone stealing other people's reports is a concern, use a better random value...like a UUID.

 

[CJones]

I ended up creating a csv file on the server when the page is loaded. Then, when the user presses the 'Export to Excel' button, it fires a javascript event that opens a new window with a new php page that sends the previously created csv file via the header().

Definitely not the most efficient way to do it, but oh well.

 

[TangoWhiskey]

And if your users are using IE, they can just right click over any HTML table and select "export to excel"... (with some caveats)

 

[CJones]

And if your users are using IE, they can just right click over any HTML table and select "export to excel"... (with some caveats)

We ARE stuck with IE, but I'm not outputting in table form, so that's a wash.

 

[Cap'n Jack]

Can the user select all the data, copy it, then past (special, as text) into Excel?

 

[CJones]

Can the user select all the data, copy it, then past (special, as text) into Excel?

Nope. It is formatted and displayed more 'graphically' than a standard table.

Saving it as CSV and exporting it that should work for what I need at this point. It will be relatively low-frequency of use, and won't have to be exported every time it is used. I just wanted to build in the capabilities while I had the hood open.