比较2个excel 文件

require 'win32ole'
require 'getopts'
#ComparedTable struct
# sheet_name,owner,diff_array


class ComparedTable
    attr_accessor :filename1, :filename2
   
    def initialize(filename1,filename2 )
    @filename1 = filename1
    @filename2 = filename2
    @compared_array = []
  end
 
  def add_compared_row(compared_row)
      @compared_array.push compared_row
  end
 
  def write_to_logfile()
        logfile = File.new("log.txt", File::CREAT|File::TRUNC|File::RDWR, 0644)
        #title
        logstr = "compare #{@filename1} and #{@filename2}/n"
        logfile.write(logstr)
        templatestr = "%-20s%-50s%-15s%-20s/n"
        logstr = templatestr% ["sheet_name","owner","match?","diff cell"]
        logfile.write(logstr)
        #each row
        0.upto(@compared_array.size-1) do |row|
            if @compared_array[row].match?
              logstr = templatestr % [@compared_array[row].sheet_name,"sheet in both file","match",""]
          else
              if @compared_array[row].owner ==2
                  tempstr=""
                  0.upto(@compared_array[row].diff_array.size-1) do |x|
                      tempstr+="#{@compared_array[row].diff_array[x]} "
                  end
                     logstr = templatestr % [@compared_array[row].sheet_name,"sheet in both file","not match",tempstr]
              else
                  if @compared_array[row].owner ==0
                      logstr = templatestr % [@compared_array[row].sheet_name,"sheet only in #{@filename1}","not match","one excel don't have the sheet"]
                  else
                      logstr = templatestr % [@compared_array[row].sheet_name,"sheet only in #{@filename2}","not match","one excel don't have the sheet"]
                  end
              end
            end
            logfile.write(logstr)
        end
        logfile.close
  end
 
  def display()
      puts "In display size == #{@compared_array.size}"
      print "%-10s,%-5s,%-20s/n" % ["sheet_name","owner","compared_array"]
      1.upto(@compared_array.size) do |row|
          print "%-10s,%-5d," % [@compared_array[row-1].sheet_name,@compared_array[row-1].owner]
          if @compared_array[row-1].match?
              print "match/n"
          else
              if @compared_array[row-1].owner ==2
                  puts "not match and diff at cell"
                  0.upto(@compared_array[row-1].diff_array.size-1) do |x|
                      print "#{@compared_array[row-1].diff_array[x]}/t"
                  end
                  puts
              else
                  puts "not match,one excel don't have the sheet"
              end

          end
         
      end
  end
 
end

class ComparedRow
#  owner type
#  0 - src file
#  1 - dest file
#  2 - both file

    attr_accessor :sheet_name, :owner,:diff_array
   
    def initialize( )
    @sheet_name = ""
    @owner = 0
    @diff_array = []
  end
 
  def add_diff_cell(cell)
      @diff_array.push cell
  end
 
  def reset()
      @sheet_name = ""
    @owner = 0
      @diff_array=[]
  end
 
  def match?
      if( @owner == 2 && @diff_array.empty?)
          return true
      else
          return false
      end
  end
end


class Excel
    attr_accessor :filename
   
    def initialize(filename)
    @filename = filename
    @application = WIN32OLE.new('excel.application')
    @workbook=@application.Workbooks.Open(@filename)
    @worksheets =@workbook.worksheets
  end
 
  def getsheetcount
      @worksheets.count
  end
 
  def getcellvalue_by_sheet(sheet,cell)
      sheet.Range(cell).value
  end
  def getcellvalue_by_sheet_index(sheet_index,cell)
      getsheetname[sheet_index].Range(cell).value
  end
 
  def self.getsheet_by_name(worksheets,sheet_name)
      worksheets.each {|sheet| return sheet if sheet.name == sheet_name }
  end
 
 
  def getsheet_by_index(index)
      @worksheets[index]
  end
 
  def getsheetname(index)
      @worksheets[index].name
  end
 
  def compare (excel_name,keycol='e')
      compare_worksheets =@application.Workbooks.Open(excel_name).worksheets
      compared_table = ComparedTable.new(@filename,excel_name)
      #compared_row = ComparedRow.new
     
      for worksheet in @worksheets do
          puts "start parse sheet -- #{worksheet.name}"
          compared_row = ComparedRow.new
          compared_row.reset
          compared_row.sheet_name = worksheet.name
         
      #don't find the sheeh in compare_worksheets
          compare_sheet = Excel.getsheet_by_name(compare_worksheets,worksheet.name)
          unless compare_sheet
              compared_row.owner = 0
              compared_table.add_compared_row compared_row
              next
            end
           
        #row compare
            col = 'a'
            row = '1'
            row_temp = row.succ
        #if continues two row don't have data ,just think it as end of the file       
            while worksheet.Range("#{keycol}#{row}").value || worksheet.Range("#{keycol}#{row_temp}").value
                    'a'.upto('g') do |x|
                        cell1 = getcellvalue_by_sheet(worksheet,"#{x}#{row}")
                        cell2 = getcellvalue_by_sheet(compare_sheet,"#{x}#{row}")
                       
                        unless (cell1 == nil && cell2 ==nil)
                            if cell1 != cell2
                                compared_row.add_diff_cell("#{x}#{row}")
                                puts "cell #{x}#{row} is different,the value is"
                                puts cell1,cell2
                            end
                        end
                    end
                   
                    row.succ!
                    row_temp.succ!
            end
           
            compared_row.owner = 2
            compared_table.add_compared_row compared_row
           
        end        #end for worksheet in @worksheets do
      return compared_table
      #@application.Workbooks.Close
     
  end  #end func
 
  def release
      @application.Workbooks.Close
        @application.quit
        @application.ole_free
  end
 
end

unless getopts('s:d:', 'srcfile:', 'destfile:')
  abort "usage: #$0 [-s] file [-d] file"
end

unless ($OPT_s) && ($OPT_d)
  abort "usage: #$0 [-s] file [-d] file"
end


excel = Excel.new $OPT_s
#compared_table = ComparedTable.new($OPT_s,$OPT_d)
compared_table = excel.compare($OPT_d,'e')
compared_table.display
compared_table.write_to_logfile

excel.release