max_value_importer.rb

max_value_importer.rb

#!/usr/bin/env ruby
# -*- coding: utf-8 -*-
# ruby max_value_importer.rb --file "./xlsx/xxx.xlsx" --sheet "sheet1" --date_range "A2:A100" --value_range "B2:B100" --date_cell "E3" --value_cell "F2"
require 'csv'
require 'optparse'
require 'win32ole'

class WIN32OLE
  def to_a
    [].tap do |array|
      each { |o| array.push o }
    end
  end
end

class MaxValueImporter
  attr_reader :file, :sheet, :date_range,
              :value_range, :date_cell, :value_cell
  def initialize(params)
    @file = absolute_path params['file']
    @sheet = params['sheet']
    @date_range = params['date_range']
    @value_range = params['value_range']
    @date_cell = params['date_cell']
    @value_cell = params['value_cell']
  end

  def write_excel
    open_book @file do |book|
      sheet = book.Worksheets @sheet
      dates = sheet.Range(@date_range)
                   .to_a
                   .each
                   .map(&:value)
                   .compact
      values = sheet.Range(@value_range)
                    .to_a
                    .each
                    .map(&:value)
                    .map(&:to_f)
                    .compact
      max_index = values
                  .each_with_index
                  .map { |value, i| value == values.max ? i : nil }
                  .compact
                  .first
      sheet.Range(@date_cell).Value = dates[max_index]
      sheet.Range(@value_cell).Value = values.max
      book.Save
    end
  end

  def open_book(file)
    excel = WIN32OLE.new 'Excel.Application'
    excel.visible = false

    begin
      book = excel.Workbooks.Open file
      yield book
    rescue WIN32OLERuntimeError => e
      e.to_str
    ensure
      book.Close
      excel.Quit
    end
  end

  def absolute_path(file)
    fso = WIN32OLE.new 'Scripting.FileSystemObject'
    fso.GetAbsolutePathName file
  end
end

if $PROGRAM_NAME == __FILE__
  params = ARGV.getopts('', 'file:', 'sheet:', 'date_range:',
                        'value_range:', 'date_cell:', 'value_cell:')
  win = MaxValueImporter.new(params)
  win.write_excel
end

test_max_value_importer.rb

#!/usr/bin/env ruby
# -*- coding: utf-8 -*-
require 'minitest/autorun'
require 'optparse'
require 'win32ole'
require './max_value_importer'

class TestMaxValueImporter < Minitest::Unit::TestCase
  def setup
    opt = OptionParser.new
    test_opt = ['--file',
                './xlsx/xxxx.xlsx',
                '--sheet', 'sheet1',
                '--date_range', 'A2:A100',
                '--value_range', 'B2:B100',
                '--date_cell', 'E2',
                '--value_cell', 'F2']
    @o = opt.getopts(test_opt, '', 'file:', 'sheet:', 'date_range:',
                     'value_range:', 'date_cell:', 'value_cell:')
  end

  def test_initialize
    t = MaxValueImporter.new(@o)
    assert_equal t.absolute_path(@o['file']), t.file
    assert_equal 'sheet1', t.sheet
    assert_equal 'A2:A100', t.date_range
    assert_equal 'B2:B100', t.value_range
    assert_equal 'E2', t.date_cell
    assert_equal 'F2', t.value_cell
  end

  def test_open_book
    t = MaxValueImporter.new(@o)
    t.open_book t.file do |book|
      sheet = book.Worksheets t.sheet
      str = sheet.Range('A1').Value
      assert_equal false, str.nil?
    end
  end
  
  def test_write_excel
    expected_date = '2017-10-30 19:00:00 +0900'
    expected_value = '15.379861111'
    t = MaxValueImporter.new(@o)
    t.write_excel
    max_date = t.open_book t.file do |book|
      sheet = book.Worksheets t.sheet
      max_date = sheet.Range(t.date_cell).Value
      max_value = sheet.Range(t.value_cell).Value
      assert_equal expected_date, max_date.to_s
      assert_equal expected_value, max_value.to_s
    end
  end
end