Sort Excel spreadsheet with Ruby?

Discussion in 'Ruby' started by Moran, Craig M (BAH), Apr 14, 2004.

  1. Has anyone successfully used Ruby to sort an Excel spreadsheet? It has been
    said that VBScript usually translates directly to Ruby if you're using
    WIN32OLE. For the most part, I have found this to be true. However, this
    example translated from VBScript fails on the Sort line:

    #/usr/bin env ruby
    require 'win32ole'
    excel = WIN32OLE.new("excel.application")
    excel['Visible'] = TRUE
    spreadsheet =
    excel.Workbooks.Open("C:\\Path\\to\\your\\spreadsheet\\Spreadsheet.xls")
    sheet = spreadsheet.Worksheets(1)
    sheet.Cells.Select
    sheet.Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo,
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    How would one translate this to Ruby? If you comment out the Sort line,
    Ruby will succeed in running to the Cells.Select point.
    v/r-
    Craig
     
    Moran, Craig M (BAH), Apr 14, 2004
    #1
    1. Advertising

  2. Moran, Craig M (BAH)

    Kent Dahl Guest

    Moran, Craig M (BAH) wrote:
    > Has anyone successfully used Ruby to sort an Excel spreadsheet? It has been
    > said that VBScript usually translates directly to Ruby if you're using
    > WIN32OLE. For the most part, I have found this to be true. However, this
    > example translated from VBScript fails on the Sort line:

    [...]
    > sheet.Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo,
    > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


    Is Sort expecting a Hash argument?

    In that case, you would probably be more successful using => instead of
    := for all the arguments in that line.

    Never used WIN32OLE, so YMMV and HTH :)

    --
    (\[ Kent Dahl ]/)_ _~_ _____[ http://www.pvv.org/~kentda/ ]_____/~
    ))\_student_/(( \__d L b__/ Master of Science in Technology )
    ( \__\_õ|õ_/__/ ) _) Industrial economics and technology management (
    \____/_ö_\____/ (____engineering.discipline_=_Computer::Technology___)
     
    Kent Dahl, Apr 14, 2004
    #2
    1. Advertising

  3. Moran, Craig M (BAH)

    Alan Chen Guest

    "Moran, Craig M (BAH)" <> wrote in message news:<>...
    > require 'win32ole'
    > excel = WIN32OLE.new("excel.application")
    > excel['Visible'] = TRUE
    > spreadsheet =
    > excel.Workbooks.Open("C:\\Path\\to\\your\\spreadsheet\\Spreadsheet.xls")
    > sheet = spreadsheet.Worksheets(1)

    ....

    I was able to perform a simple sort by replacing your code:
    > sheet.Cells.Select
    > sheet.Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo,
    > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


    for this:
    rng = sheet.Cells.Range("A:B") # or some other range
    sheet.Cells.Sort(rng)

    I haven't tried all the optional arguments yet, but at least this
    gives you a minimally working code snippet.

    Cheers,
    - alan
     
    Alan Chen, Apr 14, 2004
    #3
  4. Moran, Craig M (BAH)

    James Toomey Guest

    There are a few problems with the code. For one, these values:
    xlAscending, xlNo, xlTopToBottom
    are constants, but Ruby doesn't know the value of these constants
    because they're stored in the Excel library. Therefore, you need to
    substitute actual numbers for these. Here are the numeric values of
    those constants:
    xlAscending = 1, xlNo = 2, xlTopToBottom = 1
    The other problem is that VBA uses named arguments, but I don't think
    Ruby uses named arguments; that means you can't say "Header:=xlNo".
    The third problem is that "Selection" is a property of the
    Application, not of the Sheet, so even in VBA your code failed for me.
    This line will work for your code:
    excel.Selection.Sort(sheet.Range("A1"))
    However, that uses all the default values. I'm still trying to figure
    out how to incorporate all the arguments of the Sort routine, but I
    suspect that you need to pass blanks for the unneeded arguments, and
    you definitely need to pass actual numbers in places of the Excel
    constants. Here's the Sort routine:
    ..Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
    OrderCustom, MatchCase, Orientation, SortMethod,
    IgnoreControlCharacters, IgnoreDiacritics,
    IgnoreKashida)
    The working code will probably look something like this:
    excel.Selection.Sort(sheet.Range("A1"), 1, '','','','','',2, 1, False,
    1)
     
    James Toomey, Apr 15, 2004
    #4
  5. "Moran, Craig M (BAH)" <> wrote in message
    > Has anyone successfully used Ruby to sort an Excel spreadsheet?


    One ugly work around would be to define a Macro (say SortMacro) in the
    spread-sheet which does the sort as required and then do:

    #---------------------------------------------------------------------------
    -
    require 'win32ole'
    excel = WIN32OLE.new("excel.application")
    excel['Visible'] = TRUE
    spreadsheet =

    excel.Workbooks.Open("C:\\Path\\to\\your\\spreadsheet\\Spreadsheet.xls")
    sheet = spreadsheet.Worksheets(1)
    sheet.Cells.Select

    excel.Application.Run "SortMacro"
    #---------------------------------------------------------------------------
    -

    This will work.

    You can even define a macro on the fly if required.
    See http://support.microsoft.com/default.aspx?scid=kb;EN-US;194611

    HTH,
    -- shanko
     
    Shashank Date, Apr 17, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. FSD
    Replies:
    0
    Views:
    1,191
  2. Tim Marsden

    Displaying an Excel spreadsheet

    Tim Marsden, Feb 20, 2004, in forum: ASP .Net
    Replies:
    5
    Views:
    2,656
    S. Justin Gengo
    Feb 20, 2004
  3. Kanna Akella via JavaKB.com

    sort problem with spreadsheet - poi

    Kanna Akella via JavaKB.com, Feb 21, 2005, in forum: Java
    Replies:
    1
    Views:
    645
  4. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,527
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  5. Navin
    Replies:
    1
    Views:
    705
    Ken Schaefer
    Sep 9, 2003
Loading...

Share This Page