SPUG: Getting / Putting data to a MS Excell Spreadsheet

[aaron salo]

North, Walter wrote:
I would like to create/update and get data from a spreadsheet,

>in particular MS Excel.  The data resides in a mysql database
>and in particular I would like to create a spreadsheet from
>it.  And allow users, in particular management, to update the
>database via the spreadsheet as I don't really have time to 
>set up a spreadsheet looking web interface at this time.
>  
>

Hi Walter,
Although Spreadsheet::WriteExcel and variants are useful, they are 
probably overkill for what you want to do here. For giving data to users 
in a form that opens in MSExcel I usually just throw an 
application/vnd.ms-excel HTTP header and stream the data to the user as 
a TSV file, it works great, it opens in Excel natively for your Uncle 
Bill users. This very quickly and effectively solves your "get a 
spreadsheet" problem.

The rub here is your wish list item about having the users update the 
data by editing those spreadsheets.

Say Janet, Ken, and Robin all download the same version of data at 8am. 
Each of them makes changes on their local copy. Then you're thinking 
(you coach them how to) upload the changed "spreadsheets" to the web 
server using HTTP file upload, and you write a parser to update the 
mySQL database. Now you have a problem coalescing those changes.

Each of those three uploads updates the db, which means that subsequent 
uploads will overwrite previously committed changes, resulting in data 
loss. You will have unhappy campers. The phone will ring, your app is 
broken, I changed New Hampshire Widgets to 234 this morning and it's 
back to 197 this afternoon. You can count on it. It will happen.

If you have multiple users with update privs on the same data set, you 
really do have to build a web interface to natively update the mySQL 
database or you're going to wish you had. You can still put a link to 
easily get a dump of the data in Excel using the previously mentioned 
technique so your users can get a copy in Excel and pivot table 
themselves silly (save you a lot of time writing reports), but as far as 
insert, update, and delete you need to have these people hitting the db 
in realtime. Just my .02.

Best of luck,
Aaron