I can build it, I just need to know what kind of bricks.

August 3, 2010 1:18 PM

[web programming filter] I need to build a web page that displays spreadsheet data. The data in the spreadsheet changes depending on which user is logged in to the site. Some of the data is preset and some is entered by the user (and saved between sessions). What might be the most efficient programming solution and the easiest to maintain?

The 'spreadsheet' data will actually be coming from a database. Some of the row/column information will be from data that is static (the developers enter the data into the database) and other rows and columns will be from data that individual users enter. Each user will be able to see only the boilerplate data and their own data.

[Non-disclosure agreement prevents me from divulging the exact nature of the data. The following is a representative scenario.]
The rows are instances of manufacturers and the columns are criteria for using the manufacturer. The cells will be the ranking of the criteria against the manufacturer.
Example: rows could be the names of adhesive tape producers; columns would be things like price, availability, shipping cost, etc. In this example a user might be given names of national manufacturers and might want to enter names of local manufacturers or an overseas manufacturers.

The data in the cells will be a ranking from 'Poor' to 'Excellent' in five or seven steps and should be color coded for easy visibility.

I want to be able to change the display according to how the user wants to visualize the data. A selection would allow the user to sort according to a certain column. Rearrange the rows and columns. Edit the cell data for user specified rows and columns.

I started with the idea of building the table using CSS. The problem I had with that is how to display the names for the criteria (columns). I wanted the text to be vertical or at least at an angle so more columns would be visible. The only way I saw of doing that was to use something like ImageMagick and dynamically draw the column headings into a graphic whenever the user needed to view the data.
This compounded the problem because the host server does not have ImageMagick configured to be called from PHP code (at least I had trouble). After trying a number of possible solutions, I thought that another method might be easier.
Then I started looking into using Google spreadsheets. I thought I might be able to dynamically generate the data into a tab delimited file and use the Google API to display the data.

Now I am starting to second guess myself again.

I cast my ideas and thoughts unto the waters of the hive...
What is a likely and viable solution to displaying spreadsheet information on a web page where each user sees different information according to the user's data and settings?

posted by Drasher to computers & internet (8 answers total)

PHP? It sounds like you already have a web host that supports it. Its trivial to display tabular data with PHP, a SQL query and HTML tables. The rest shouldn't be hard.
posted by wongcorgi at 1:45 PM on August 3, 2010

From a general point of view you've got to figure out:

How to build an HTML table (usually with some scripting language, like PHP)
Make the table sortable, customizable, etc. (javascripty stuff, usually)
Send changes back to your server and update things

I've done some research on this sort of thing myself... generally, just displaying data from different sources. I wouldn't build the table in CSS, I would build it using an html table and style the thing using CSS. You can use some sort of javascript framework to help you do stuff like sorting columns, filtering data, etc. I've been messing about a bit with a datatables plug-in for jquery. Basically, once you've got the table built in html, you use the datatables plug in to apply some nice sorting/filtering features. There are various javascript grid sorts of things out there, and they all have different features.

I haven't done much with editing something in an html table and saving that. Basically, you're going to have to look into POSTing data to some server side script and having that make the appropriate updates in your database. I imagine you're going to have to look into some sort of forms processing to figure that out.
posted by Mister Cheese at 1:53 PM on August 3, 2010

im kinda of a javascript nut, so my approach would be to pull raw data from the back end and do all the juggling on the front end. the upshot is that development and testing goes twice as fast since all your crunching is done on the client side.


i dunno if this helps, but i wrote a ridiculously quick and dirty app (20 lines?) to spit out table HTML given CSV data.

http://gregtaff.com/misc/make.html
posted by nihlton at 2:19 PM on August 3, 2010

You almost certainly don't want to use CSS to build a tabular display unless you absolutely have to, which you might if you need to have a form stretch across columns. An HTML table will be the best way to go in every situation.

Trying to angle or change the orientation of the column headers will probably be more trouble than it's worth. If it doesn't fit, folks will have to scroll. On the other hand, if there's a finite set of column headers, you could just pre-build all the header graphics and swap them in based on column name rather than try to build them dynamically at run-time. I've had good luck with TableKit for sorting, resizing, editing-in-place in the past.

On the server side, PHP can handle this fine. If you were interested in messing with it, this kind of project seems like it's in Ruby On Rails' wheelhouse. Obviously most of the popular scripting languages, e.g. Perl, Python, can handle this as well.
posted by ob1quixote at 3:04 PM on August 3, 2010

CSS has mixed support for rotating text. Depending on which browsers you need to support, that may be of use to you.
posted by misterbrandt at 3:11 PM on August 3, 2010

Can't you just fix the column widths? You could put a space between every letter and make the column wide enough to fit one letter, which would probably be more readable than sideways text, though semantically speaking it's kind of fraught.

If you are committed to making images, keep in mind that PHP includes the GD graphics library by default since version 4.3, and it can do that sort of text manipulation very easily.

Lastly, I agree with everyone else who says to use HTML tables and style accordingly with CSS. Tables have gotten a bad rap because they were abused for layout purposes in the late 90s to early 00s, but if you are displaying tabular data an HTML table is actually the most semantic and meaningful way of organizing that data, much more so than for instance using a bunch of divs or ordered lists or something.
posted by invitapriore at 3:20 PM on August 3, 2010

Also, for updating your cells, if JavaScript support isn't an issue I would go with an AJAX solution of some kind. You could send/receive the data as a simple JSON object (which most server-side scripting languages have the ability to parse natively, and is preferable to XML in this circumstance because it's easier and often faster), something like { "row": 1, "column": 5, "content": "out of stock"} or what have you.
posted by invitapriore at 3:27 PM on August 3, 2010

That's great help so far.

I am ready to code the whole thing as HTML table elements using PHP. The actual graphic layout is the problem.

nihlton, the example code is very helpful.
Because the cell data is compact (one or two letter data), I was trying to keep the width of the columns to a minimum. That is where the vertical text problem comes in.

Thanks for the suggestion of the GD library invitapriore, I will look at using that for putting an angle on the text.

I am much more familiar with PHP, so the Ruby on Rails, Python, and Perl ideas will have to be on the back burner for now unless they have a distinct advantage over PHP.

I will also take a close look at the TableKit idea suggested by ob1quixote.
posted by Drasher at 6:10 AM on August 4, 2010