On Sat, 2001-11-24 at 06:17, Emiliano wrote:
> Does anyone around here have experience generating Excel files
> (peferably with multiple worksheets, but multiple files with only one
> worksheet will do that can't be done) from PHP? I can't find anything
> out there. My other option is to do it with perl (which has a great
> module for excel files) but that would mean I'm back to CGI.
>
Not sure if it will help ... but I found this article on Linux.com back in September
.. hopefully it will give you a starter ..
Let me know if it does work, as I haven't tried it ...
Cheers
Mike
Article of Linux.com
------------------
by Frank Hilliard
Thursday September 6th, 2001
-----------------------------
One problem you're bound to run into sooner or later is a client who
wants to use customer data in a mailing list. Nine times out of ten,
she's got a Windows system and wants to make Avery labels with the label
wizard in MS Word. Your data is in a Linux database. What to do?
I'm assuming your Linux database is on a server running either PHP or
Cold Fusion Linux. I'm going to show how it's done with Cold Fusion and
I believe the same general procedure will work with PHP.
I'm indebted to Bob Keleher of the Toronto CFUG for pointing me in the
right direction. His solution is very slick indeed.
The key tag in Cold Fusion Linux is CFCONTENT. I had been using it to
generate a comma separated list, but the downside of this is that
clients often put commas in their address information and each one
pushes content into the next column. The result is that when the file is
opened in Excel, there are a lot of errors that have to be fixed
manually before you can import the Excel file into MS Word. The right
way to do it is to generate a tab-delimited Excel file directly from the
database using a variable for the tabs inside the CFOUTPUT QUERY. Here's
the code for the tab variable:
<cfset tab=chr(9)>
Now, here's the code for the output. Of course you have to have your
query running ahead of this. In my case the query that's getting the
data is named "list."
<CFHEADER NAME="Content-Disposition" VALUE="inline;
filename=Yourfilename.xls">
<CFCONTENT TYPE="application/msexcel" >
Title FirstName Lastname employmentposition
Department
Institution Address Addresstwo City ProvState
Country Code
<CFOUTPUT QUERY="list">#Title##tab#
#FirstName##tab#
#Lastname##tab#
#employmentposition##tab#
#Department##tab#
#Institution##tab#
#Address##tab#
#Addresstwo##tab#
#City##tab#
#ProvState##tab#
#Country##tab#
#Code#
</CFOUTPUT>
There are several things to keep in mind here.
* The spaces between the header names actually are tabs, not
spaces.
* There are no carrage returns except after the last header and last
variable.
* The tab variable has to be set before CFCONTENT.
* CFCONTENT has to be enabled in the security settings of the CF
administrator, or it won't run.
A link to this page opens Excel automatically and inserts all the data
without any further action on your part. From there it's a simple matter
to save the Excel file and open it with the label wizard in MS Word.
Just remember to remove any blank rows in the Excel file above the row
with the column names.
Even though the addresses are riddled with commas, they'll all come up
looking perfect!
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
