Can we attach documents to excel columns using Ruby?

Ruby
1

Suppose I do have some folders in a directory. Now say directory name
“Download”.i have some folders in that directory say -
“document77444”,“document58745”,“document12457” so on. Now I want those
docs to be upload in to an excel column using Ruby. Excel sheet has
columns as below:

Requestnumber Doc1 Doc2
============= ======== ========

77444 a.pdf c.jpeg
58745 b.csv d.docx

so on.

Is there any good gem with ruby to meet such requirement?

I am using Ruby

Thanks,
Arup

2

On Thu, Jan 24, 2013 at 1:34 PM, Arup R. [email protected]
wrote:

77444 a.pdf c.jpeg
58745 b.csv d.docx

so on.

Is there any good gem with ruby to meet such requirement?

roo or spreadsheet gem. The rest is just a standard file upload if this
is
going over a network.

Andrew McElroy

3

Andrew Mcelroy wrote in post #1093607:

On Thu, Jan 24, 2013 at 1:34 PM, Arup R. [email protected]
wrote:

77444 a.pdf c.jpeg
58745 b.csv d.docx

so on.

Is there any good gem with ruby to meet such requirement?

I want to upload the files into excel column, from the “local drive”,
not to the internet.

Thanks

4

Damián M. González wrote in post #1093640:

Arup R. wrote in post #1093606:
Is there any good gem with ruby to meet such requirement?

Yes, win32ole, it’s packed with Ruby. Take a look at this website:
Ruby on Windows: excel

Kind regards. Damián.

OMG!! Excellent reference you have provided. +1 to you. Thank you very
much. I will sure give it a try,hope what I am looking for ward,surely
would get from here.

Thanks

5

Arup R. wrote in post #1093606:
Is there any good gem with ruby to meet such requirement?

Yes, win32ole, it’s packed with Ruby. Take a look at this website:

Kind regards. Damián.

6

OMG!! Excellent reference you have provided. +1 to you. Thank you very
much. I will sure give it a try,hope what I am looking for ward,surely
would get from here.

Thanks

You can be sure that that will work. You can also add colors, format
the entire worksheet, also you can print the worksheet without show it
to the user, so you can use Office hided and for magic art print the
worksheet, the user of the app will say OMG! but is Excel, but he will
never know, lol. You will see that win32ole use the same methods than
Visual Basic, so you can learn about how to do things programaticaly by
recording a macro doing the steps you want then taking a loop at the
auto-generated script, methods for Visual Basic are almost the sames for
win32ole, Ruby. Have fun :).

7

Damián M. González wrote in post #1093726:

OMG!! Excellent reference you have provided. +1 to you. Thank you very
much. I will sure give it a try,hope what I am looking for ward,surely
would get from here.

Thanks

You can be sure that that will work. You can also add colors, format

Can any one help me for the below code, which I draft to meet my above
requirement?

require ‘win32ole’

#Excel Application will be started from here.
#--------------------------------------------

excel = WIN32OLE.new(‘Excel.Application’)
excel.visible = true
wb=excel.workbooks.open(“E:\WIPData\Ruby\Scripts\GSL_File_DownLoad1.xlsx”)
wbs= wb.Worksheets(1)
rows=2
column=2

until wbs.cells(rows,1).value == nil do

Dir.entries("E:\\WIPData\\Ruby").each do |f|

  if f == wbs.cells(rows,1).value then

    column=2
    Dir.foreach(f) do |x|

      full_path=Dir.pwd.concat("/" + f)
    wbs.cells(rows,column).values =

wbs.OLEObjects.Add(,full_path,False,True,f,)
column = column + 1

    end

  end

end

end

But getting error as :

E:\WIPData\Ruby\Scripts>test.rb
E:/WIPData/Ruby/Scripts/test.rb:25: syntax error, unexpected ‘,’,
expecting ‘)’
…).values = wbs.OLEObjects.Add(,full_path,False,True,f,)
… ^
E:/WIPData/Ruby/Scripts/test.rb:25: syntax error, unexpected ‘,’,
expecting ‘=’
…cts.Add(,full_path,False,True,f,)
… ^

E:\WIPData\Ruby\Scripts>

8

Continued (couldn’t edit, out of time)

Try passing nil to make things clearer.

Try using a hash as the argument to pass the parameters by name,
possible example here (untested as I don’t have excel with me at the
moment):
http://www.ruby-forum.com/topic/145406

9

Joel P. wrote in post #1094051:

Continued (couldn’t edit, out of time)

I tried below:

Dir.entries(“E:\WIPData\Ruby”).each do |f|

  if f == wbs.cells(rows,1).value then

    files_dir = File.expand_path("..", Dir.pwd)
    column=2
    Dir.foreach(files_dir.concat("/" + f)) do |x|

      full_path=files_dir.concat("/" + x)
    wbs.oleobjects.add({
                            'ClassType'     => nil,
                'Filename'      => full_path,
                'Link'          => false,
                'DisplayAsIcon' => false,
                'IconIndex'     => nil,
                'IconLabel'     => nil,
                'IconFileName'  => nil,
                'Left'          => nil,
                'Top'           => nil,
                'Width'         => nil,
                'Height'        => nil
              })
    column = column + 1

    end

     break
  end

end

Getting error as :

E:\WIPData\Ruby\Scripts>test.rb
E:/WIPData/Ruby/Scripts/test.rb:26:in method_missing': (in OLE methodadd’: )
(WIN32OLERuntimeError)
OLE error code:800A03EC in Microsoft Excel
Cannot insert object.
HRESULT error code:0x80020009
Exception occurred.
from E:/WIPData/Ruby/Scripts/test.rb:26:in `block (2 levels) in

' from E:/WIPData/Ruby/Scripts/test.rb:23:in `foreach' from E:/WIPData/Ruby/Scripts/test.rb:23:in `block in ' from E:/WIPData/Ruby/Scripts/test.rb:17:in `each' from E:/WIPData/Ruby/Scripts/test.rb:17:in `'

E:\WIPData\Ruby\Scripts>

10

wbs.OLEObjects.Add(,full_path,False,True,f,)

Check that you have the right number of arguments.

11

It appears that you are passing in ‘.’ and ‘…’ into your Dir.entries
block. That would cause the error you are getting.

Try skipping those two entries at the beginning of your block or use
Dir#glob which does not capture ‘.’ and ‘…’

12

The whole point of using a Hash as an argument is so that you don’t have
to enter all the nil values. You might need to use the “Execute” method
for that to work, I’m not sure.

Anyway, the best way to test what works is to attempt that command
manually using IRB. Put together a test sequence with different ways of
using OLEObjects, and then find out which ones work. Once you’ve played
around with it a bit you’ll be able to better understand the way it
works.