On Sat, 02 Mar 2002 07:33:39 GMT, Masaki Suketa
<masaki.suketa / nifty.ne.jp> wrote:

>In message "Re: Using Win32OLE with Excel leaves Excel process hanging"
>    on 02/03/01, "Robert C. Martin" <u.n.c.l.e.b.o.b. / .o.b.j.e.c.t.m.e.n.t.o.r.d.o.t.c.o.m> <unparsable-address> writes:
>
>> >A "ruby.exe" process?
>> 
>> No, an Excel process, or some Excel resource.
>> 
>> Can you start up an Excel program, then exit the ruby program leaving
>> Excel running?  That's what seems to hang me up.
>
>What the following script output on your environment?
>
>  require 'win32ole'
>  puts "Win32OLE: #{WIN32OLE::VERSION}"
>  puts "Ruby: #{RUBY_VERSION}(#{RUBY_RELEASE_DATE})"

irb(main):002:0*   require 'win32ole'
true
irb(main):003:0>   puts "Win32OLE: #{WIN32OLE::VERSION}"
Win32OLE: 0.2.6
nil
irb(main):004:0>   puts "Ruby: #{RUBY_VERSION}(#{RUBY_RELEASE_DATE})"
Ruby: 1.6.4(2001-06-04)
>
>And what version of Excel do you use?

Excel 2000 9.0.2720
>
>I'm not sure, but I am afraid this is Win32OLE bug.
>So, I want to try your script to fix the bug if this is Win32OLE bug, 
>but I can't find AmazonAnalyzer.rb. How can I get AmazonAnalyzer.rb?
>Or, could you show me the script without AmazonAnalyzer.rb which 
>hang you up?

AmazonAnalyzer.rb----
require 'ranking'
require 'date'
require 'pstore'

class DataRange
  def initialize(rangeArray)
    @rangeArray = rangeArray
  end
  def to_s
    "(%d:%d:%d)[%d]"%[min,mean,max,n]
  end

  def min
    @rangeArray.min
  end

  def max
    @rangeArray.max
  end

  def mean
    sum = 0
    @rangeArray.each {|x| sum += x}
    Float(sum)/(@rangeArray.length)
  end
  
  def n
    @rangeArray.size
  end
end

class AmazonAnalyzer
  attr :titleTable
  attr :rangeTable
  attr :startDate
  attr :endDate

  attr_writer :periodLength

  def analyzeFile(bookDataFile, periodLength)
    bookData = {}
    store = PStore.new(bookDataFile)
    store.transaction do
      store.roots.each {|dateString|
      bookData[dateString] = store[dateString]
    }
    end
    analyzeBookData(bookData, periodLength)
  end
  
  def analyzeBookData(bookData, periodLength)
    @periodLength = periodLength
    @titleTable = makeTitleTable(bookData)
    @rangeTable = analyze(bookData)
  end
  
  def isbns
    @titleTable.keys
  end
  
  def getTitleFor(isbn)
    @titleTable[isbn]
  end
  
  def getPeriodsForIsbn(isbn)
    rangeTable[isbn].keys
  end
  
  def getRange(isbn, period)
    rangeTable[isbn][period]
  end

  def makeTitleTable(dateTable)
    titleTable = {}
    dateTable.each_value {|rankingList|
      rankingList.each {|ranking|
    titleTable[ranking.isbn] = ranking.title
      }
    }
    return titleTable
  end

  def makeIsbnDateRankingTable(dateTable)
    isbnDateRankingTable = {}
    dateTable.each {
      |date, rankingList| 
      rankingList.each {|ranking|
    if (isbnDateRankingTable[ranking.isbn] == nil)
      isbnDateRankingTable[ranking.isbn] = []
    end
    isbnDateRankingTable[ranking.isbn] << [date, ranking.rank]
      }
    }
    return isbnDateRankingTable
  end

  def makeIsbnPeriodTable(isbnDateRankingTable)
    isbnPeriodTable = {}
    isbnDateRankingTable.each {
      |isbn, dateRankingList|
      dateRankingList.each {|dateRanking|
    date = dateRanking[0]
    ranking = dateRanking[1]
    period = getPeriod(date)
    if isbnPeriodTable[isbn] == nil
      isbnPeriodTable[isbn] = {}
    end
    if isbnPeriodTable[isbn][period] == nil
      isbnPeriodTable[isbn][period] = []
    end
    isbnPeriodTable[isbn][period] << ranking
      }
    }
    return isbnPeriodTable
  end

  @startDate = nil
  @endDate = nil
  @periodLength = 0

  def generatePeriods(dateStrings)
    dates = []
    dateStrings.each {
      |dateString|
      dates << makeDate(dateString)
    }
    @startDate = dates.min
    @endDate = dates.max
  end

  def makeDate(dateString)
    dateString =~ /(\d\d\d\d)-(\d\d)-(\d\d)/
    year = $1.to_i
    month = $2.to_i
    day = $3.to_i
    return Date.new(year, month, day)
  end

  def getPeriod(dateString)
    date = makeDate(dateString)
    daysSince = date - @startDate
    period = daysSince / @periodLength
    return period
  end

  def analyze(bookData)
    generatePeriods(bookData.keys)
    isbnDateRankingTable = makeIsbnDateRankingTable(bookData)
    isbnPeriodHash = makeIsbnPeriodTable(isbnDateRankingTable)
    return RangeTable.new(isbnPeriodHash)
  end

  def getPeriodStartDate(period)
    return @startDate + (period * @periodLength)
  end
end

class RangeTable # [isbn][period] => DataRange
  def initialize(isbnPeriodHash)
    @isbnPeriodHash = isbnPeriodHash
    @rangeTableHash = nil
    @rangeHash = {} # cache the ranges by isbn
  end

  def periods(isbn)
    getPeriods(isbn).keys
  end

  def [] (isbn)
    getPeriods(isbn)
  end

  def getPeriods(isbn)
    return @rangeHash[isbn] unless @rangeHash[isbn] == nil
    periodHash = {}
    periods = @isbnPeriodHash[isbn].keys
      periods.each {|period|
    ranks = @isbnPeriodHash[isbn][period]
    periodHash[period] = createDataRange(ranks)
      }
    @rangeHash[isbn] = periodHash
    return periodHash
  end

  def createDataRange(rangeArray)
    return DataRange.new(rangeArray)
  end
end

if __FILE__ == $0
  periodLength = 1
  if (ARGV[0] != nil) && (ARGV[0].to_i > 0)
    periodLength = ARGV[0].to_i
  end

  analyzer = AmazonAnalyzer.new()
  analyzer.analyzeFile("bookData", periodLength)

  isbns = analyzer.isbns
  isbns.each {|isbn|
    print "\n----- ",analyzer.getTitleFor(isbn)," -----------\n"
    periods = analyzer.getPeriodsForIsbn(isbn)
    periods.sort!
    periods.each {|period|
      print "   Period:",analyzer.getPeriodStartDate(period),
    " - ",analyzer.getRange(isbn,period),"\n"
    }
  }
end


Ranking.rb------

class Ranking
  attr_reader :isbn, :title, :rank
  def initialize(isbn, title, rank)
    @isbn = isbn
    @title = title
    @rank = rank
  end

  def print
    printf("%5d, ISBN:%s, %s\n", @rank, @isbn, @title)
  end

  def <=>(ranking)
    @rank<=>ranking.rank
  end
end

-----

You'll need the data file too.  I'll email it to you.


Robert C. Martin  | "Uncle Bob"                   
Object Mentor Inc.| unclebob @ objectmentor . com
PO Box 5757       | Tel: (800) 338-6716         
565 Lakeview Pkwy | Fax: (847) 573-1658           | www.objectmentor.com
Suite 135         |                               | www.XProgramming.com
Vernon Hills, IL, | Training and Mentoring        | www.junit.org
60061             | OO, XP, Java, C++, Python     |

"One of the great commandments of science is:
    'Mistrust arguments from authority.'" -- Carl Sagan