Forum: Ruby Can we attach documents to excel columns using Ruby?

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

On Thu, Jan 24, 2013 at 1:34 PM, Arup Rakshit <lists@ruby-forum.com> 
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

Andrew Mcelroy wrote in post #1093607:
> On Thu, Jan 24, 2013 at 1:34 PM, Arup Rakshit <lists@ruby-forum.com>
> 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

> Arup Rakshit 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: 
http://rubyonwindows.blogspot.com.ar/search/label/excel

 Kind regards. Damián.

Damián M. González wrote in post #1093640:
>> Arup Rakshit 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:
> http://rubyonwindows.blogspot.com.ar/search/label/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

> 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 :).

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>

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

Check that you have the right number of arguments.

http://msdn.microsoft.com/en-us/library/microsoft....

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

Joel Pearson 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 method
`add': )
(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
<main>'
        from E:/WIPData/Ruby/Scripts/test.rb:23:in `foreach'
        from E:/WIPData/Ruby/Scripts/test.rb:23:in `block in <main>'
        from E:/WIPData/Ruby/Scripts/test.rb:17:in `each'
        from E:/WIPData/Ruby/Scripts/test.rb:17:in `<main>'

E:\WIPData\Ruby\Scripts>

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.

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 '..'