Re: [PLUG] Text file to Excel, automated

[Tim Allen <flipper@peregrinesalon.com>]

This is a quite bit of a hack, but one I've used for years with success when
exporting SQL queries to Excel from a web site, under Firefox, Opera, and
IE, under PHP (but should work under any scripting language):

If your URL is:

http://www.foo.com/bar.php?this.php?var1=this&var2=that

Set the headers as follows (in PHP), change for your language of choice:

  // SET THE CONTENT TYPE
  header("Content-Type: application/vnd.ms-excel");
  // HACKS FOR IE TO GET AROUND VARIOUS REFRESH ISSUES
  header("Expires: 0");
  header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

Change the URL target in the HTTP request to "trick" IE (the hack):

http://www.foo.com/bar.php?this.php?var1=this&var2=that&/test.xls

Pass it basic formed HTML with tables, and tables will be translated into
Excel. It handles colspans and rowspans, and well as basic font and bgcolor
declarations. Strip things like new lines and escape quotes to avoid
problems.

This was a hack from back in the PHP4 days, before Excel libraries existed,
so there are more elegant solutions available now... but this is a quick and
dirty hack for getting data out. You can also do a dump of a SQL query as
either basic HTML or tab delimited with CRs, and open the URL directly in
Excel under File --> Open.

If you're looking for a permanent solution, go with one of the libraries
(PEAR or otherwise), but if you're looking for a quick a dirty solution for
dumping data to Excel... I'd recommend the above.

Not the prettiest, but fast and effective - and if your data is mostly
clean, you'll be in good shape.

Regards,

-Tim

-----Original Message-----
From: plug-bounces@lists.phillylinux.org
[mailto:plug-bounces@lists.phillylinux.org] On Behalf Of Roman Stanchak
Sent: Thursday, July 03, 2008 11:47 AM
To: julienfmills@yahoo.com; Philadelphia Linux User's Group Discussion List
Subject: Re: [PLUG] Text file to Excel, automated

This is probably overkill, but OO also has python bindings.  google
"open office python"

On Thu, Jul 3, 2008 at 9:50 AM, Julien Mills <julienfmills@yahoo.com> wrote:
> I'm looking for a way to put the text output of some
> sql queries into an excel document which I can email
> to others in the company.
___________________________________________________________________________
Philadelphia Linux Users Group         --        http://www.phillylinux.org
Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce
General Discussion  --   http://lists.phillylinux.org/mailman/listinfo/plug

___________________________________________________________________________
Philadelphia Linux Users Group         --        http://www.phillylinux.org
Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce
General Discussion  --   http://lists.phillylinux.org/mailman/listinfo/plug