Spreadsheets to online?

An easy way?

[cyclic]

I have just been tasked with putting a spreadsheet onto a site. The only way I can think of doing this is by form inputs into a database and then pulling records from the db to be handled by output scripts. Is this the logical way or am I missing a more obvious approach?

The spreadsheet has simple formula but a large amount of potential variables.

[frodo]

I may be missing the obvious, but you can upload an Excel spreadsheat onto your server, point a link to it and the Excel spreadsheet should display nicely in an IE browser!

Alternatively, there is an Excel file parser availabe from [hotscripts.com...] (havn't used it)This parser program can import data from an Excel file, versions from Excel 5.0 to Excel 2000 and XP....

If I'm off the beaten track explain a bit further please.

[jatar_k]

That is definitely a possibility. Is it an excel spreadsheet?

If so, can't excel be embedded? I am not totally sure but thought it was possible.

[cyclic]

Thanks. I was actually thinking of a functioning spreadsheet as opposed to just a flat database file. I have been given a spreadsheet which they want to use online ie. input and output data. The oly way I can think of is to translate the excel formulae into php and use mysql to store the data.

I was just curious if I was going about this right way as it involves a considerable amount of work.

[jatar_k]

I see what you are getting at, I would look into it to make sure that excel doesn't have that capability. The time it will take you to check it out will be much less than the creation of it in php.

If not then go for it, I have made a few things like that for our intranet. They are a lot of work but are very satisfying because they are usually complex.

[hiker_jjw]

Save the spreadsheet as a CSV file (Comma Seperated Values). Then you can use a number of library and modules to split the rows of values.

Module Text::CSV_XS for example.
Other PERL Libraries are also available.

[dingman]

Last I checked, which I admit was a while ago, the problem with CSV was that you didn't get formulas exported, just their calculated value.

[cyclic]

Jatar, I think that this will be a big project so standby for plenty of postings!

Hiker, I am fairly mediocre with php so you have lost me a bit! Is this perl you are referring to?

[jatar_k]

yes that is perl.

>>standby for plenty of postings

no prob, that's what we're here for.

[Annii]

I know you guys hate Front Page, but I have used Frontpage to embed a working interactive excel spreadsheet in a page. It took about 20 seconds to do :)
It only works if the user has MS Office installed on their PC, but I was quite pleased with it...
Sticky me if you want the URL as an example...
By the way you don't need to be running Frontpage extensions on your server for this to work. I have a unix apache with no FP extensions and I use FTP to upload...

Anni

[jatar_k]

Very good suggestion Annii,

The only problem is cross platform compatibility. It would work well for an intranet or some predefined user group though.

[Annii]

Yes you're right about the compatibility, but for those of us who can't write code and when it's not absolutely essential to the page, it's the lazy mans option ;)

Anni

[cyclic]

Thanks Annii, it is a good suggestion but I cannot rely on everyone having Office and I have an aversion to FP! I think the only way is to translate the whole spreadsheet into PHP.

I have already started with some success but my first obstacle is user registration with mySql. I need the user to come back and access the data they inputted last time. I have done some mySql work but never for more than one user so I have just used htaccess on the directory. I need the individuals to gain access to only their own records. Can this be done securely on one database? I will issue user names and passwords myself.

I would be grateful for your advice.

[dingman]

I don't know exactly how users and permissions work in MySQL, but they are supported. A good book on the matter will certainly have a section on those details, and once you have the database set up propperly for the task, you should be able to construct a database connection string for each user based on their login info. I'd consider using a different authentication scheme than .htaccess, simply so that you don't have to make your users keep track of two different passwords that they will probably only change in one place or the other, etc. O'Reilly has a couple books I found useful last time I worked on a MySQL project.

Another way around the problem would be to write your code to carefully restrict your selects to the data that the current user has access to. It's probably not as efficient as using different database users, but as long as the control is very loose you might find it enough easier to implement that it's worth it.

I'm thinking of perhaps switching from a single-database-user to multiple-detabase-users with my pet project, though I probably won't get to it for a few months because there are other features I want to implement first. If you're interested, I can sticky you or write a post about it when I get there. (Its a PostgreSQL based project, not a MySQL based one, but at least some of it will probably transfer.)