Ruby Excel question

[gregarican]

I am looking to create a Ruby script that will extract an inventory
list from an SQL recordset. This recordset will be used for an offsite
e-commerce application. Usually I just pipe these recodsets to a CSV
text file. The trick is this particular script needs to also include
binary image files associated with each element of the SQL recordset
(i.e. - a JPG file for each SKU). So I am trying to port things over to
Excel as the output file since it can accept inserted binary images.

Looking at the Ruby Garden and some tips posted on the c.l.r board I
have a grasp on inserting text data into an Excel spreadsheet. What
would be the way to insert a JPG file into the spreadsheet using Ruby?
There are other ways around this, as I can just create a script to
upload the JPG files through FTP to get them where they need to be. But
I was looking to have all the data in one master document. Am I going
about this wrong?

 

[Corey Lawson]

Well... what you have to do then is insert a bunch of OLE Object
containers into the worksheet, with one image per container. If you
link these to files on the HD, it won't be too bad, but if you include
the JPG files themselves into the spreadsheet, it's going to get big
pretty fast.

So your Ruby code is going to be kind of like:

require "Win32Ole"

xl =3D WIN32OLE.create("Excel.Application")

wb =3D xl.NewWorkbook

ws =3D wb.ActiveSheet

ws.Pictures.Insert("D:\docs\My Pictures\Sample.jpg").Select

(how did I get this? Well, I just recorded a macro in Excel, which will do =
this:

ActiveSheet.Pictures.Insert("D:\docs\My Pictures\Sample.jpg").Select

So I just need to remember that "ActiveSheet" can be replaced with
"Application.ActiveWorkbookk.ActiveSheet", and I already have refs to
replace )Application (xl) and ActiveBook (wb),... )

This inserts a link to your image file in the workbook, which is
generally the way to go. If you save and distribute the workbook,
Excel at least should try to keep a basic thumbnail of the image in
the spreadsheet, but the recipient won't be able to see the whole
image.

 

[gregarican]

Well... what you have to do then is insert a bunch of OLE Object
containers into the worksheet, with one image per container. If you
link these to files on the HD, it won't be too bad, but if you include
the JPG files themselves into the spreadsheet, it's going to get big
pretty fast.

Thanks for the tips. This is indeed true that the spreadsheet soon
would become ungainly. Since this file will be uploaded to an ASP for
e-commerce it won't be practical to OLE the pix. I will create a batch
upload FTP script since the filenames are standardized. Thanks again!