Topic: Reading Excel spreadsheets in Rails

I have a forthcoming application where users will want to load up columns of data in Excel (rather than XML or CSV) and I'll need to be able to read the column headings and push the data into the database.  I don't need to manipulate the data via Excel - all I need is to get it out.

The best idea I can come up so far is to use something to extract the data out of the Excel file into CSV and then process the CSV.  Gnumeric could read the Excel file but I know nothing of Gnumeric.  Google Spreadsheets could load up the Excel file and then allow me to grab it as CSV but it doesn't (yet) have an API.  EditGrid looks like Google Spreadsheets and may even be better but the licence remain to be explored and the API doesn't seem to include a file upload.  There is also Spreadsheet-ParseExcel but it only goes up to Excel 2000.  The ScriptingExcel page at rubygarden assumes you have Excel running on the server, which I won't!

I can't be the first person to try to do something really simple with Excel in Ruby (I'll use PHP if I have to!) so my googling skills clearly aren't good enough.  Could someone point me in the right direction?

Many thanks

allen

Last edited by allen (2006-07-11 09:45:37)

Re: Reading Excel spreadsheets in Rails

I looked for a long time for a ruby solution and never found one.  I ended up using Excel's XML format, and it worked quite well.

If I had to use the xls format I'd probably use Java and POI, then create some kind of ruby wrapper for it.  Not sure if that's an option though.

Re: Reading Excel spreadsheets in Rails

Thanks thabenksta.  At least now I know it's not my googling technique that's the problem.  I've just taken a look at the Save As XML Spreadsheet option and it should be quite straightforward.  Do you use XSLT do convert it into something simpler first?

Re: Reading Excel spreadsheets in Rails

No, I use an XML parser.  I can't remember which one, I'll have to look it up later.

Re: Reading Excel spreadsheets in Rails

I'm playing with REXML and it seems to work very well.  The MSEXCEL XML structure is fairly straightforward, at least in terms of being able to get into Worksheets (/Workbook/Worksheet/) and then into the rows and columns of the tables on those sheets (/Workbook/Worksheet/Table/Row/Cell/Data).  If anyone's interested, I'll post up some code here as I go along.

Re: Reading Excel spreadsheets in Rails

with every new version of Excel, do I need to change my code to support it?