creating excel sheets/charts using php3

Anon

Hi,

I'm kinda hoping that someone can help me out here. I have following problem:

I'm developing reports that have to run on demand on an apache web server (unix box). I'm using PHP3 for the report creation and presentation on the web.
I also need to create an excel file on-the-fly for the user to download. We
have implemented a first version by generating a text file (fields separated by tabs, file extension renamed to .xls). This works fine, but now we need to add some additional functionality: charts

I was thinking about creating a sylk (symbolic link file) which has an imbedded chart or which has some VBA code to generate a chart once it gets opened in Microsoft Excel.

My problem is that I barely found information how this can be done.

How do you put chart information in a sylk file?
How do you put VBA code in a sylk file?

Is there another solution to this? Report generation script needs to run on a unix machine. The report download is intended for client pc's running microsoft excel 97 in an intranet network.

Already tried an excel forum, but no luck there...

Thanks.
Filip

Anon

May this will help you:

to find out, wether the sylk format supports embedded vba scripts or charts, just create a demo file in excel containing the required features and try to save it as a sylk file. if it works you can parse the sylk file to analyse its structure. If it don't you may try some workarounds, ex. generating an image of the chart with PHP and sending it besides the data file.

I recently used sylk as export format for online store data. compared to text format it has two advantages:

excel opens it by doubleclicking its icon
excel maintains field types like text and numeric

markus

Anon

Hello Markus,

Thank you for your quick response. I had already tried what you mentioned, but unfortunately with mixed results. The problem is (I think) that Microsoft Excel doesn't allow you to export VBA macro code & charts in a sylk file. However it should be possible to import a sylk file with VBA/macro code & chart into excel.

(At the microsoft support site I found a security patch for excel to solve a potential virus threat in sylk files).

Recently I found a book describing part of the sylk format. Unfortunately no detailed information how to imbed charts or VBA code (but it does get mentioned).

We don't have the gd library compiled in our environment, and because of company reasons generating images is out of the question for the near future, but is a good solution for the long term.

I like the 2 advantages you mentioned (specially the one about keeping field type). Giving it a try.

Thanks.
Filip

ame12

Did you check out msdn.microsoft.com for details on OLE automation for MS Office? Specifically, there's info (Office Developer's Kit) on automating various aspects of Excel (this would entail some client side code). This would allow you to grab data off the Unix box (spreadsheet AND charting info) and then build a real XLS file that could then be opened by the workstation user.

Also, if you're interested in also getting live spreadsheet data out of Excel over HTTP from desktops/laptops), check out this screen shot:

http://badblue.com/helpxls.htm

Small footprint mobile web server that has good support for PHP as well.

Anon

Randy,

I did not have a chance to look at msdn.microsoft.com yet.

If I would use OLE automation for MS Office, I guess it also means that I would need an extra Windows (preferable NT) box to build the spreadsheet. That is something that I need to avoid if I want to keep things simple, reliable and fast.

Imagine PHP functions/classes that allow you to generate office files (xls/doc/ppt) on the fly (running on a unix box). Haven't seen something like that yet.

Would be cool...

Thanks,
Filip

ame12

Well, the OLE automation stuff I was referring to is probably really better suited for a client side solution. Based upon what I've read about Excel, trying to do it server-side is a major, major pain. And if you don't do it using OLE automation it's probably ten times the effort minimum, IMO.

===========================================
http://badblue.com
Small footprint P2P web server for Windows,

File-sharing, PHP, wireless apps & more

Anon

Hi Randy,

I agree on that. Just don't like the idea
about having an extra Windows machine in the picture.

Have to find out about OLE automation.

Filip

Anon

Hi, I'm doing just the same and I get SYLK format error when I try to open the txt file with MS Excel.

I appreciate any help you could give me regarding this issue...

Thanks in advance,

cavas

Anon

We use a product called sdoffice. for information see:
synergetic-data.com

Filip wrote:

Hi Randy,

I agree on that. Just don't like the idea
about having an extra Windows machine in the picture.

Have to find out about OLE automation.

Filip

Anon

Hi,

I'm trying to write a program in Pick (a multi level operating system) that will create a SYLK file automatically. The data is deferent every time. I need to find out what all those confusing codes are. Dose that book you mentioned have that kind of stuff in it? And where can I get a copy of it?

Thanks,
Ben

benmeyer

Hi,

Thanks,
Ben

Ps. my other message was to the wrong email address, sorry.

Anon

Is it really possible to show the results form MySQL database to a excel sheet.

Anon

You can check this address :
http://www.cnovak.com/
This guy as written a generic class ta create Excel compatible files

Anon

Can anyone tell me how i can insert an image in to an excel sheet using php?