Generating Excel Sheet using PHP

obrienkev

How do I generate an excel (.xsl) sheet usinh PHP??

Thanks.

Kevin.

rachel2004

I believe there is a class file (if you are into OOP) for this at phpclasses.org. I've never used it, but I've seen others ask about it or mention it. You may want to do a search of this forum's archives because I recall a question about this sometime last week or this week.

Another option is to import/export as csv (comma separated values).

AstroTeg

I've had pretty good luck just generating an XLS file by generating a stripped down HTML file and slapping a .XLS on the file name and passing the right headers back to the browser. The HTML should just contain an HTML table with the rows and columns being your Excel data you want to present.

CSV works great too. The only problem is Excel does not handle text and numbers properly and treats everything it can find as numeric is numeric - including zip codes. With zips, Excel will drop the leading zeros (even if you wrap quotes around the zip which in all other CSV import operations means the field is to be treated as text). There's a trick around this too. Let me know if you need it...

rachel2004

I'm interested to know what you have as the workaround to prevent dropping leading zeros in CSV. I know you can prevent that from within Excel's native format, but I haven't tried it via CSV. Thanks.

obrienkev

Any examples on how to generate excel doc(.xsl) using php please??

Cheers,

Kevin.

Napster

Instead of a 0, cant you just replace them with the ACSII code?

artoo

There's a PEAR library for this. I think the link is pear.php.net and its called ExcelWriter.

AstroTeg

For the Excel CSV work around, its like this:

[... CSV data ...],"more data",+"00123","data"

Insert a plus before the quoted field but after the comma. Excel will then properly handle the leading 0's. Its retarded but it works. The problem with this is the plus is not a part of the CSV format and I would expect it to break just about every other application that can import CSVs. So I used to have programs that generated a report in HTML, XLS (really a CSV with plusses added), and a proper CSV file. Although now days, I've dropped the CSV with plusses for just a plain HTML table.

rachel2004

Thanks for the reply, AstroTeg. I'll have to try it one of these times when asked to work with Excel.

Thomas_Liao

Originally posted by AstroTeg
I've had pretty good luck just generating an XLS file by generating a stripped down HTML file and slapping a .XLS on the file name and passing the right headers back to the browser. The HTML should just contain an HTML table with the rows and columns being your Excel data you want to present.

Dear AstroTeg

Would you please give me an example that show me how to do this in php code.

AstroTeg

Yes and no. The XLS code is actually combined with various functions which allows me to output XLS, CSV, and HTML without tweaking the query code itself. Its big and complex...

But, I do recommend experimenting strictly with HTML first. Then start writing the PHP. How? First start out by creating an HTML table. Just use a naked standalone table. Excel doesn't really like having html or body tags before or after the table. If you want cell borders in Excel, then specify border="1" on the table. Then just use tr and td tags to generate some rows and columns of data. Save the file as xls. Double click on the file and it should load up in Excel. Check the formatting to make sure you're happy with it. If everything looks ok, then take a step back and look at the HTML and what you had to do to generate it. Then write PHP code do the generating. Its really no different then having PHP write a regular HTML table out to the browser. Also remember you'll need to send the proper headers out to trigger Excel to load, if that's what you're looking to do.

Thomas_Liao

Thanks a lot.

I think I can generate a .xls file by php. Thank you for showing me the detail steps.

Is there anyway to read data from .xls file? COM will slow down the performance of the server. So I don't want to use it. Do you have any idea that can import data from an Excel file easily?

AstroTeg

In the PEAR library, there's an Excel reader available. I tried it and had very mixed results with it (enough so that I decided CSV was still the way to go). Your mileage may vary...

Thomas_Liao

Your mileage may very...

Sorry, just like my signature below. I don't know what you mean. Do you mean that there is no way to read from Excel easily?

Thank you reply me anyway. And you have gave me much helpness. Thanks.

Weedpacket

Originally posted by Thomas Liao
Do you mean that there is no way to read from Excel easily?

Correct. The Excel document format was developed by Microsoft. They haven't published the details of the format to allow other people to write programs that read Excel documents, because Microsoft want to force people to use their product and not anyone else's.

This has not stopped people from examining Excel documents in an attempt to work out exactly what the format is, and write programs that can read them, but it is not easy; it's made even more difficult because Microsoft keeps changing the format.