Fog Creek Software
Discussion Board




Getting Excel to work with the web

OK. This is what comes of starting a programming project without any of the relevent skills.

[background - distinctly skippable]
My father has a (surprisingly popular) blog about British politics. It runs on a usermode linux server I admin; the CMS software used is WordPress.

One thing his site doesn't have is a "predictor"; enter your assumed shares of the vote for the UK general election, and it tells you how many seats each party gets.

So, about 48 hourse ago I sat down and wrote one in Excel. You enter your share of vote assumptions, and it spits out the results. (It's pretty simple, based on uniform swings, but that's not important right now.)

Now, I assumed I could just upload this spreadsheet to his web-site. People could download it and play with the assumptions. Unfortunately, it came to an astonishing 14mb; which I considered a little large for casual downloading.

[/background]

So, I have an Excel spreadsheet (sitting on my home PC), which does some fairly compex maths. And I would like people to enter some numbers in a web-page, and to get back the results of the spreadsheet's calculations.

Now, I thought of various ways I might do this (I can program - very - basic PHP). The easiest way (given my limited skillset) seemed to be this:

Web page containing Form (enter shares of vote).
PHP page to throw this out as a text file of some sort (i.e. LD 30; C 34; L 28).
I then knock up a VBA program to constantly (every sec?) check for the existence of this file. When it finds it, it loads it and then deletes it.
The Excel sheet then does its work.
Another VBA program (or even the first one), detects that the Excel sheet has finished, and writes another text file.
Meanwhile, back at the web server, the PHP script sees the existence of the new text file; loads it up, spits out HTML, and then deletes the text file.

I can attach the session IDs to text files to allow more than one person to access the web page simultaneously. *But* this doesn't seem a very elegant solution. In fact, it looks like an almighty kludge because I don't know how to do it any better.

Does someone have a better idea of how I might write this (given limited programming technique)?

Many thanks,

Robert

Robert Smithson
Tuesday, July 27, 2004

14MB is pretty big!

was it full of graphics?

I can tell you offhand that your idea of watching files is neither robust nor efficient, but a long explanation of how you might go about doing it is only going to introduce databases and nasty stuff that has quite a learning curve.

Tackle the 14MB problem first?

i like i
Tuesday, July 27, 2004

Is the math too complicated for PHP? If not, then get rid of the Excel part of the equation, use a form to let users input data, compute, and spit out stuff. Besides the obvious CSV format (you can print out anything you want from a script, not just HTML), I wouldn't be surprised if someone wrote a PHP module to generate data in Excel format.

Fred
Tuesday, July 27, 2004

It's a good question; 14mb is pretty big.

There are no graphics in the sheet. (I'm just wondering if we have the wandering 65,500 row problem...) What there are are five sheets, one with historic data for every parliamentary seat in the country, one which works out who has won each seat, one which takes assumptions for the share of the vote, and then "forward" calculations for the first two sheets.

I know the file passing solution is a foolish one.  But it will (probably) work. I just know there is a better way, I just wish I knew enough to make it work.

Now, regarding using a database, I have a basic yunderstanding of MySQL, and have written (very) simple stuff like a random quotes generator for my web-page.

Are you suggesting I put the form contents into a database (I can do that), and then have Excel query the database (ODBC?), and then place results back into the database where my web-page can see them again?

Robert Smithson
Tuesday, July 27, 2004

Fred,

Actually that's not a bad idea. Although I am using a reasonably big dataset. I need to calculate the result of every parliamentary seat in the UK. (659 at last count.)

So - what I would do, I would guess, is output the dataset ('01 election results) to a CSV file,

Then, each time the web-page is "run", it loads the CSV file and runs the assumptions on it. That's 3,000 - 5,000 calculations per time - so long as I don't get Slashdotted, that should be fine.

The only issue I have with this, is that it'll require changing the PHP if I chang my assumptions. But otherwise I like it.

Thanks,

Robert

Robert Smithson
Tuesday, July 27, 2004

>> Although I am using a reasonably big dataset. I need to calculate the result of every parliamentary seat in the UK. (659 at last count.)

First try to use flat files, to check if performance is really too bad. If it's just too slow, take a look at SQLite instead of MySQL. It offers very good performance with just a single DLL (no server to install).

>> So - what I would do, I would guess, is output the dataset ('01 election results) to a CSV file. Then, each time the web-page is "run", it loads the CSV file and runs the assumptions on it

... or you could regenerate the output every time a user uses the form. You can spit out CVS (ie. item<TAB>item<TAB><CRLF>) instead of HTML, and tel the user to choose File| Save to save this into a txt file on their PC, ready to be opened in Excel if they wish.

Fred
Tuesday, July 27, 2004

ok, maybe i don't understand the problem completely, but, can't you do everything online?

ie. let users input stuff online, save data into mysql, output caclulations onto a webpage.  have the webpage generate an excel form if the user so desires one.

Kenny
Tuesday, July 27, 2004

How does a dll or odbc for that matter help the op

"It runs on a usermode linux"

Formerly someone else
Tuesday, July 27, 2004

>>How does a dll or odbc for that matter help the op

In case performance is not good enough when working with flat files. I suggested SQLite because it's open-source, cross-platform, is the default DB with PHP5, and doesn't require a server.

Fred
Tuesday, July 27, 2004

You are looking for something like this:

http://www.sitepoint.com/article/php-apps-excel-worksheet-server

I am not sure if it's going to work for you but may be you can have a look.

Regards,
JD

JD
Tuesday, July 27, 2004

Okay, everyone has some good objections, but assuming we are pushing forward on this, OWA (the Office Web Access) component can help if you use IIS and Windows web serving. ASP has access to all sorts of the components, including the OWA libraries which makes it possible for you to programmatically control Excel and Word installed on the web server. If you point the library to your excel file, it could hit it with queries and grab back cells (containing the answers). It could even call up charts and get back GIF rendering of charts. It is not the fastest thing in the world so you might want to cache the answer instead of it allowing your users to hit it every second. However earlier posts clearly points out the performance hit you have to live with... so cache cache cache answers to common queries so you don't repeat unnecessary queries. However most computers now days are P4s with a few qooglebits of ram anyways, so youshould expect your Excel to survive a bit of hits. Read up on the license of the OWA though, Microsoft would like to charge you for every users who access that spreadsheet--if you can believe it. Time to read up about Open Office *wink*.

Li-fan Chen
Tuesday, July 27, 2004

Li-Fan,

I like it. It solves almost all my problems, while simultaneously forcing me to learn some new stuff. (Which is a good thing.)

Thank you,

Robert

Robert Smithson
Tuesday, July 27, 2004

[plug]
BadBlue is designed to do exactly this (share Excel over the web) without converting anything.  It uses Excel natively and provides audit logging/tracking, cell-level security, etc.

http://badblue.com/helpxls.htm - Excel/web sharing help
[/plug]

The only drawback (for you, perhaps) is that there really isn't much configuration required and really no programming is necessary.

dir at badblue com
Tuesday, July 27, 2004

Put this at the top of your ASP File

<!--METADATA NAME="Microsoft ActiveX Data Objects 2.6 Library" TYPE="TypeLib" UUID="{00000205-0000-0010-8000-00AA006D2EA4}"-->
<!--METADATA NAME="Microsoft Excel 9.0 Object Library" TYPE="TypeLib" UUID="{00020813-0000-0000-C000-000000000046}"-->

(pulls in all kinds of constants for you)

Then start with this code:

Dim xlapp
    Set xlapp = CreateObject("Excel.Application")
With xlapp
    .interactive = false
    .displayalerts = false
End With

Dim xlwb
    Set xlwb = xlapp.Workbooks.Open( "C:\path\to\your\xlsfile.xls" , 0, True)

From there you can call any Excel Macros in your wb like so I believe

xlwb.MacroNameHere(macroparameters)

You can save charts as GIFs using

iChartObject.Export "C:/imagename.gif"

Michael H. Pryor
Fog Creek Software
Tuesday, July 27, 2004

Do you need to output fancy graphs? if not it would definately be easier to do it all server-side. Just have the form submit to a PHP script which does the calculations and outputs the results. It could even output bar charts as these are pretty easy to do in HTML. Even if you need pie charts you could look at using the GD graphics library functions in PHP (and I'll bet there's some PHP classes out there already for graphing with GD that'll do the hard work for you).

You say the calculations need to run against a large data set - the obvious thing to do would be to put this into a database table on the server (MySQL would be a common choice). You'd probably find your calculations could be done with a single SQL query using aggregate functions, if you model the data right.

But that's a bit of a steep learning curve if you've not done any database work before, so like you say just working with the data set in a CSV file on the server side would probably do, provided you don't get billions of hits to the thing, in which case a proper database would be advisable. Or pre-calculating results into a lookup table for a suitable range of inputs, if that's viable.

Matt
Tuesday, July 27, 2004

"Put this at the top of your ASP File"

Again.  Did I miss something here?  The OP said he is using Linux, all your fancy ass ODBC and .asp isn't going to do a damn thing for him. 

Formerly someone else
Tuesday, July 27, 2004

How about content type app/excel (or whatever the syntax is).  I know that will launch Excel in IE.  Dont know if it helps with the 14Mb though.

Anon
Tuesday, July 27, 2004

Could you calulcate most of the outcomes and spit them to static html ?

dood For speed
Tuesday, July 27, 2004

I saw, recently, a program that would take a static worksheet & post it on the web, but allow the user to do all sorts of dynamic things to it. Maybe something like this is what you're looking for?

www.MarkTAW.com
Tuesday, July 27, 2004

Copy all the data and paste it into another workbook. You will quite likely find the size reduces dramatically. Post back with the result.

Stephen Jones
Wednesday, July 28, 2004

I'd say use Javascript rather than PHP, save's having to work on the server.

I've emailed a request for the spreadsheet.  I'll have a go at converting it for you.

Ged Byrne
Wednesday, July 28, 2004

What is wrong with a simple lookup table implementation?  If there are really only three parties, and people specify accuracy to (say) 1% on each, then you need a 2d array of 10,000 elements (the third party gets 1-other two parties, so is not necessary to calculate).  Running a one time script to calculate these values, and a simple python script to spit out answers from the list should be simple enough.

danielsn
Thursday, July 29, 2004

s/python/php/ 

danielsn
Thursday, July 29, 2004

*  Recent Topics

*  Fog Creek Home