Can I do this? (Read/write book with lots of sheets and formulas and keep format)

Topics: Developer Forum, User Forum
May 13, 2008 at 11:06 AM
Hello! my problem is the following and I would want to know if PHPExcel can help me solve it.

Preface: I have a Java application that opens an Excel 2003 workbook with the Excel application. It is a workbook with some sheets to fill data and some cells with results taken from a lot of hidden sheets with a lot of formulas, linked between sheets.... a completely mess created by my boss for calculating mortages. Then the user saves the workbook locally and the click a button in the Java app to send that file to the server. This presents several problems. The user has to click "Save" in excel, and "Save" in Java for doing one action. The Excel mustn't go out of the office and now anyone can get a copy of it. Some of the data in the form must be entered twice in Java GUi and in Excel form... I cannot replace the Excel workbook with java code because it is a complete formula's labyrinth. It won't be productive... so I need to use that workbook.

THE PROBLEM: My purpose is to be able to implement an interface similar to the workbook (preferably in Java) and having the workbook loaded in memory in the background so the user transparently uses all the formula calculation implemented in that workbook, but seeing the results in the Java interface. Each workbook is saved in a server for each of our clients.

I have tried apache POI java API, but it has been a completely failure. Simply reading the Excel with the API and writing it again (even without doing any modification) results in an Excel document with no format at all and without any formulas... so I ruled it out.

I have seen that PHPExcel may do formula calculation better than POI and can save documents directly in the server. But my doubts are:

1. Can PHPExcel read Excel 2003 documents? If no, can I simply convert my 2003 workbook to 2007 by opening it with Excel 2007 and saving it again? Will then PHPExcel be able to read it?

2. My workbook has a lot of formulas linked one to another in a long "chain" of formulas even jumping from one sheet to another. Can PHPExcel read my workbook, write some values in the "fill in form" sheet I have, then recalculate absolutely everything (as Excel does) and save the document exactly as it was but with the new values inserted? the same way as if I open the document in Excel, edit some cells and save it again with the formulas calculated?

(if all of this is possible, to solve my problem, I will create a copy of the document in the server for each client, and with a Java GUI accept values from the user, and when the user click some kind of "Update" button it queries a php webpage to save the changes and get the resulting values back so they appear in the Java GUI) (I think it is not too smart but what the heck...Im so lost :D )

3. Is there any other way, apart from PHPExcel, for achieving this objectives:
   3.1. Preventing user from saving the Excel file to wherever he wants in his computer (maybe disabling "Save as"...)
   3.2. Not making the user having to click two buttons, "Save" in excel (locally), and "Save" in my java app, to save it to the server.
   3.3. Transferring some data from the Java GUI to the Excel workbook to help the user completing the form.

I know it has been a large post. Sorry :)

Hope any can answer my question/s. I am very desperate cause my boss is putting pressure on me to reach these objectives...

Thanks in advance
May 13, 2008 at 11:08 AM
Ups, I forgot a question. Does PHPExcel parse formulas even if they are written in Spanish version of Excel?
Coordinator
May 13, 2008 at 8:16 PM
1. Can PHPExcel read Excel 2003 documents? If no, can I simply convert my 2003 workbook to 2007 by opening it with Excel 2007 and saving it again? Will then PHPExcel be able to read it?
PHPExcel can read Excel2003 documents, but not the formulas (yet). It is however possible to open it in Excel2007 and save it as a .xlsx file. PHPExcel can read these .xlsx files.

2. My workbook has a lot of formulas linked one to another in a long "chain" of formulas even jumping from one sheet to another. Can PHPExcel read my workbook, write some values in the "fill in form" sheet I have, then recalculate absolutely everything (as Excel does) and save the document exactly as it was but with the new values inserted? the same way as if I open the document in Excel, edit some cells and save it again with the formulas calculated?
This is exactly possible! If the formulas are not to complicated and chains are not to long, PHPExcel should be fine. Check http://blog.maartenballiauw.be/post/2008/03/Reuse-Excel-business-logic-with-PHPExcel.aspx as an example. You can simply create a web form which accepts input and pass it into PHPExcel. Afterwards you can export a sheet to HTML if you want.

3. Is there any other way, apart from PHPExcel, for achieving this objectives:
   3.1. Preventing user from saving the Excel file to wherever he wants in his computer (maybe disabling "Save as"...)
   3.2. Not making the user having to click two buttons, "Save" in excel (locally), and "Save" in my java app, to save it to the server.
   3.3. Transferring some data from the Java GUI to the Excel workbook to help the user completing the form.
Check the answer on 2.

Extra: I do not have experience with it, but there is a Java-PHP bridge which may be an additional help. Check it out at http://php-java-bridge.sourceforge.net/doc/.
May 14, 2008 at 7:03 AM
Thanks a lot for you answer. I will give it a try :)