• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

How paste spreadsheet data into web page

I want a way to securely submit standard spreadsheet data (student grades).

One way would be if we could have a password protected web page, and have a form on it that a user could cut/paste into from their spreadsheet.

But, I am not familiar with a way to paste structured spreadsheet data into a HTML form.

The web page woud then iterate over the rows and make entries into a database.

The main context is that grades originate in a spreadsheet, and we want them to end up in a central database, and have some authentication over submission.
0
guthrie
Asked:
guthrie
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Jason C. LevineNo oneCommented:
Hi guthrie,

You would be better off allowing users to securely upload the spreadsheet and processing it from there.  Cutting and pasting won't work that well.
0
 
sypderCommented:
You can upload a tab delimited file and process this with PHP and then put it into a MySQL database. This is explained at http://www.experts-exchange.com/Web/Web_Languages/PHP/Q_21821898.html

Does that server your needs?
0
 
guthrieAuthor Commented:
Thanks for the replies.

Jason;
Thanks, that is what I figured about C&P, but was not sure. I already have a secure upload site, and can then use that. The issue is that it then takes more manual processing on the server side, to take the SS and process iinto database. I suppose that the upload servlet could also initiate (or do) the SS reading and database update, but the Java for that decoding is more complez & I was hoping to avoid it. (use Java POI I suppose...)

Syper;
to do that I would have to have the user take multiple steps, save as Tab-seperated, then read that and upload. But then the servlet could process it. In fact, could C&P the Tabbed data into a text field, and then read/process that - but overall it exposes more messyness to teh [naive] user.
(I was assuming servlets, but PHP could be used - we're mostly Java based!).

Thanks to both.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jason C. LevineNo oneCommented:
Guthrie,

Sorry to burst your bubble there.  The easiest thing of all would be to train the users to enter their grades via the site and allow them export a spreadsheet on demand.  That frees you up from having to worry about server side processing of CSV or TSV or Excel or whatever and still allows your users to export to a spreadsheet for their own calculations or what not.
0
 
guthrieAuthor Commented:
yes, I see your idea, but generally each person has their own private spreadsheets, some multipls sheets, which calculate grades in many and varied ways, the only commonality is that they all end up with a single set of columns on IDs and grades.

The grades are teh outcome of many updates and partial calsulations throught a course, and this is all local on a user machine. They only need (want) to connect to a simpler (restricrted) model for final reporting..

I'll bet that an AJAX page chould do it nicely, but I'm not yet proficient there..
0
 
smidgie82Commented:
If you're committed to using a regular ol' HTML form, then I agree that file uploading is the way to go.  The files can be processed post-upload to accomplish the same end result as just uploading the pertinent data would, it just involves a greater requirement for server-side processing, and will result in slightly more bandwidth usage.

On the other hand, if you're willing to go the ASP route, you could include a control on the webpage that could gracefully handle the spreadsheet data.  Also, there are libraries for using COM objects with Java, so you could likely create an applet that accepts OLE data that represents the spreadsheet data and handle it that way.  Doing so would, of course, make your website no longer platform-independent, but I got the impression most of your users are likely to be Windows users anyway, so... Maybe that's a solution that would work for you.
0
 
guthrieAuthor Commented:
Thanks.

No, I am not HTML restricted, in act presume a JSP page - but the user will have that as the interface.

I'm purely Java centric, so am not sure that I got all of your comments... but woudl presume that I could do the same thing wiht JSP. I guess what I would be missing is an active control to handel the spreadsheet data, but as you note could make an applet to do that; is this the basic idea?

So it would be .. an applet which makes a form, which accepts a paste of SS structured data? I'm not sur what this woudl involve... [No, I can't assume all windows users... :-( ]
0
 
smidgie82Commented:
Hmm...  In Windows, when you copy something, what's actually being done is an OLE COM object is generated that contains the right information to describe to a target program the contents of the material that was copied, and on a "paste" operation, the object is delivered to the target app.  The same is true for drag-and-drop between programs (and depending on the app, frequently used for internal drag-and-drop as well).  That sort of behavior, though, is specific to Windows.  I don't know how X-Windows would handle it, much less any other windowing system.  So I don't think you'd be able to use my suggestion, because the applet would have to be built in a platform-dependent way in order to properly handle the copy-and-paste actions on a given platform.  So, if you can't assume all users use a particular platform, I don't think my suggestion would work unless you have a way for detecting the client OS and loading the right applet accordingly.  Even then, you'd probably have to write custom versions of the applet for Excel, Microsoft Works, Lotus, Access (not a spreadsheet, I know, but conceivable that your users might use databases regardless), the OpenOffice spreadsheet program, the KOffice spreadsheet program, the Gnome spreadsheet program, etc., as I doubt they all generate the same format of data when a copy / paste is done.

Sorry!  But if you do go that way, please let me know, I'd be fascinated to see how you handle it.

But, there's another possibility:

I just created an Excel spreadsheet with two columns, as you describe.  The left column has the numbers 1-5, and the right has the letters A-E.  When I copy and paste them, I get:

1      A
2      B
3      C
4      D
5      E

It appears that Excel, at least with this data, does tab-delineation when pasting to a text-only format.  I don't know about other progs, but if you can depend on that behavior, it should be a cinch to allow users to paste their data into a <textarea> element and parse it on the server side.
0
 
Jason C. LevineNo oneCommented:
Smidgie82,

If the spreadsheet is pretty simple, you will get a simple tsv paste.  The problems start to occur with cell-spanning and other formatting issues
0
 
guthrieAuthor Commented:
Smidgie82, thanks for the detailed explaination,a nd example. I think that if the TSV works, that will be fine, I'll cook up a simpel JPS page to test it.

Otherwise, possibilities abound...
Encrypted Email with attachment, SSH encrypted ftp of .xls, etc...

All inovolve various issues, but might be options.

Thanks.
Gregory
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now