Class: Tables::ExcelTableReader

Inherits:
TableReader show all
Defined in:
lib/tables/excel_table_reader.rb

Instance Attribute Summary collapse

Attributes inherited from TableReader

#tables

Instance Method Summary collapse

Methods inherited from TableReader

#extract_all_tables, #get_tables, #merge_tables

Constructor Details

#initialize(filename = nil) ⇒ ExcelTableReader

Returns a new instance of ExcelTableReader.



14
15
16
17
18
 
# File 'lib/tables/excel_table_reader.rb', line 14

def initialize(filename=nil)
  @excel=WIN32OLE.new('Excel.Application')
  open_file(filename) unless filename.nil?
  super()
end

Instance Attribute Details

#table ⇒ Object (readonly)

Returns the value of attribute table.



12
13
14
 
# File 'lib/tables/excel_table_reader.rb', line 12

def table
  @table
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook.



12
13
14
 
# File 'lib/tables/excel_table_reader.rb', line 12

def workbook
  @workbook
end

Instance Method Details

#clean ⇒ Object 



93
94
95
96
97
98
99
 
# File 'lib/tables/excel_table_reader.rb', line 93

def clean
  @tables.each do |table|
    table.remove_blank_rows!(1)
    table.remove_repeat_headers!
    table.demerge!
  end
end

#create_file(filename) ⇒ Object 



28
29
30
31
32
33
 
# File 'lib/tables/excel_table_reader.rb', line 28

def create_file(filename)
  path=get_file_path(filename)
  @workbook=@excel.Workbooks.Add
  @worksheets=[]
  @workbook.SaveAs(path)
end

#exit ⇒ Object 



105
106
107
 
# File 'lib/tables/excel_table_reader.rb', line 105

def exit
  @excel.quit
end

#extract_table(worksheet, options = {}) ⇒ Object 



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/tables/excel_table_reader.rb', line 35

def extract_table(worksheet, options={})
  rtf_columns=options[:rtf_columns]
  rtf_columns ||= []
  progress=options[:progress]
  sheet=get_worksheet(worksheet)
  range=sheet.UsedRange
  ncols=range.Columns.Count
  nrows=range.Rows.Count

  result=[]
  (1..nrows).each do |idx|
    row=range.Rows(idx)
    result << ExcelTableReader.extract_row(row,ncols,rtf_columns)
    if progress and idx.modulo(50)==0 then
      percent=(idx.fdiv(nrows)*100).round(0)
      puts ">> extracting row #{idx} (#{percent}%)"
    end
  end
  Table.new(result)
end

#open_file(filename) ⇒ Object 



20
21
22
23
24
25
26
 
# File 'lib/tables/excel_table_reader.rb', line 20

def open_file(filename)
  path=get_file_path(filename)
  @excel.Workbooks.Open(path)
  puts "Open workbook '#{path}'" if $DEBUG
  @workbook=@excel.Workbooks.Item(1)
  @worksheets=[]
end

#save ⇒ Object 



101
102
103
 
# File 'lib/tables/excel_table_reader.rb', line 101

def save
  @workbook.save
end

#table_count ⇒ Object 



89
90
91
 
# File 'lib/tables/excel_table_reader.rb', line 89

def table_count
  @workbook.WorkSheets.Count
end

#write_column(table, column_name, worksheet = 0, options = {}) ⇒ Object 



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/tables/excel_table_reader.rb', line 72

def write_column(table, column_name, worksheet=0, options={})
  progress=options[:progress]
  sheet=get_worksheet(worksheet)
  values=table.get_column(column_name)
  column_index=table.colindex[column_name]+1
  nrows=table.row_count
  values.each_with_index do |val,idx|
    if progress and idx.modulo(50)==0 then
      percent=(idx.fdiv(nrows)*100).round(0)
      puts ">> updating row #{idx} (#{percent}%)"
    end
    r=sheet.Rows(idx+1)
    c=r.Cells(column_index).Value=val.to_s
  end
end

#write_table(table, worksheet = 0, options = {}) ⇒ Object 



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
# File 'lib/tables/excel_table_reader.rb', line 56

def write_table(table, worksheet=0, options={})
  progress=options[:progress]
  sheet=get_worksheet(worksheet)
  idx=0
  nrows=table.row_count
  table.each_row do |row|
    idx+=1
    if progress and idx.modulo(50)==0 then
      percent=(idx.fdiv(nrows)*100).round(0)
      puts ">> writing row #{idx} (#{percent}%)"
    end
    r=sheet.Rows(idx)
    row.each_with_index {|val,jdx| r.Cells(jdx+1).Value=val.to_s }
  end
end