Class: WinExcelDb::RowSheet

Inherits:
Object
  • Object
show all
Defined in:
lib/win_excel_db/row_sheet.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(worksheet, excel_handler) ⇒ RowSheet

Returns a new instance of RowSheet.



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/win_excel_db/row_sheet.rb', line 7

def initialize(worksheet, excel_handler)
  @deepest_row_index=1 #Adding a row adds 1 later
  @worksheet=worksheet
  @name=@worksheet.Name
  @excel_handler= excel_handler
  get_column_names()
  @data_rows=Array.new
  @buffered_mode=false
  used_range_count=@worksheet.UsedRange.Rows.Count
  (0..used_range_count-2).each_with_index do |counter|
    range_string="A#{counter+2}:#{col_letter(WinExcelDb::MAX_COLUMNS)}#{counter+2}" #one line range starting with 2
    excelrange=@worksheet.Range(range_string)
    value=excelrange.Value
    if !value.empty?&&!array_of_nil?(value[0]) then #row 1 contains headers!
      data_row=DataRow.new(self, counter+2, @header)
      @data_rows[counter]=data_row
      @deepest_row_index=counter+2 # do not use used_range_count because this may include rows behind empty rows
    else
      break
    end
  end
end

Instance Attribute Details

#data_rows ⇒ Object (readonly)

Returns the value of attribute data_rows.



30
31
32
 
# File 'lib/win_excel_db/row_sheet.rb', line 30

def data_rows
  @data_rows
end

#meta_range ⇒ Object (readonly)

Returns the value of attribute meta_range.



30
31
32
 
# File 'lib/win_excel_db/row_sheet.rb', line 30

def meta_range
  @meta_range
end

#name ⇒ Object (readonly)

Returns the value of attribute name.



30
31
32
 
# File 'lib/win_excel_db/row_sheet.rb', line 30

def name
  @name
end

Instance Method Details

#buffered_mode=(value) ⇒ Object 



41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/win_excel_db/row_sheet.rb', line 41

def buffered_mode= value
  if value == true
    @buffered_mode = true
    if @deepest_row_index > 2 then
      (0..@deepest_row_index - 2).each_with_index do |counter|
        range_string = "$A$#{counter + 2}:$#{col_letter(WinExcelDb::MAX_COLUMNS)}$#{counter + 2}" #one line range starting with 2
        excelrange = @worksheet.Range(range_string)
        excel_value_array = excelrange.Value
        next if excel_value_array.nil? || excel_value_array[0].nil? # row not yet written to Excel (e.g. after create_meta_row)
        row_value_array = @data_rows[counter].row_value_array
        excel_value_array[0].each_index do |index|
          if !row_value_array[index]
            excel_value_array[0][index] = '' if excel_value_array[0][index] == nil #transfer o
            row_value_array[index] = excel_value_array[0][index]
          end
        end
      end
    end
  elsif value == false
    @buffered_mode = false
  end
end

#commit_buffer ⇒ Object 



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/win_excel_db/row_sheet.rb', line 114

def commit_buffer
  if @buffered_mode then
    dump_range=Array.new()
    (0..@deepest_row_index-2).each_with_index do |counter|
      inner_array=@data_rows[counter].row_value_array
      dump_range[counter]=inner_array
    end

    #delete old entries
    max_col = col_letter(WinExcelDb::MAX_COLUMNS)
    deletion_excelrange=@worksheet.Range("$A$2:$#{max_col}$1048576")
    fullrange=deletion_excelrange.EntireRow
    fullrange.Delete

    #write new entries
    if dump_range.empty? then
      #we need to rescue the empty line in tables with are not seen be mcp because they are empty.
      dump_range[0]=Array.new(WinExcelDb::MAX_COLUMNS, '')
      range=@worksheet.Range("$A$2:$#{max_col}$2")
      range.Value = dump_range
    else
      # the normal way if something is to commit
      upper_corner = "$A$2"
      lower_corner = "$#{max_col}$"+@deepest_row_index.to_s
      range=@worksheet.Range("#{upper_corner}:#{lower_corner}")
      range.Value = dump_range
    end
  else
    raise "Buffer commit was triggered for RowSheet #{@name} but buffered mode was not active"
  end
end

#create_meta_row ⇒ Object 



159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/win_excel_db/row_sheet.rb', line 159

def create_meta_row
  @deepest_row_index +=1
  new_row =DataRow.new(self,@deepest_row_index, @header)
  @data_rows.push(new_row)
  if !@meta_range then
    @meta_range=MetaRange.new
  end
  new_meta_row=MetaRow.new(new_row)
  @meta_range.push(new_meta_row)
  return new_meta_row
end

#delete_row(row) ⇒ Object 



146
147
148
149
150
151
152
153
154
155
156
157
 
# File 'lib/win_excel_db/row_sheet.rb', line 146

def delete_row(row)
  if row <= @deepest_row_index then
    if @buffered_mode == false then
      @worksheet.Rows(row).Delete
      @deepest_row_index = @deepest_row_index - 1
      update_index(row)
    else
      update_index(row)
      @deepest_row_index = @deepest_row_index - 1
    end
  end
end

#empty? ⇒ Boolean

Returns:

  • (Boolean) 


32
33
34
35
36
37
38
 
# File 'lib/win_excel_db/row_sheet.rb', line 32

def empty?
  if @deepest_row_index <= 1 then
    return true
  else
    return false
  end
end

#read(row, fieldname) ⇒ Object 



103
104
105
106
107
108
109
110
111
112
 
# File 'lib/win_excel_db/row_sheet.rb', line 103

def read(row, fieldname)
  raise "Read error! Column #{fieldname} not found in Worksheet #{@name} " if !@header[fieldname]
  raise "Read error! Selected row is the table header of #{@name} " if row<=1
  value = @worksheet.Cells(row, @header[fieldname]).Value
  if value then
    return value
  else
    return ""
  end
end

#read_formula(row, fieldname) ⇒ Object 



91
92
93
94
95
96
97
98
99
100
 
# File 'lib/win_excel_db/row_sheet.rb', line 91

def read_formula(row, fieldname)
  raise "Read formula error! Column #{fieldname} not found in Worksheet #{@name} " if !@header[fieldname]
  raise "Read formula error! Selected row is the table header of #{@name} " if row<=1
  value = @worksheet.Cells(row, @header[fieldname]).Formula
  if value then
    return value
  else
    return ""
  end
end

#write(row, fieldname, value) ⇒ Object 



72
73
74
75
76
77
78
79
 
# File 'lib/win_excel_db/row_sheet.rb', line 72

def write(row, fieldname, value)
  raise "Write error! Column #{fieldname} not found in Worksheet #{@name} " if !@header[fieldname]
  raise "Write error! Selected row is the table header of #{@name} " if row<=1
  raise "Write error! Currently running in READONLY MODE" if Run.mode(@excel_handler)==:readonly
  if @buffered_mode==false then
    @worksheet.Cells(row, @header[fieldname]).Value=value
  end
end

#write_formula(row, fieldname, value) ⇒ Object 



81
82
83
84
85
86
87
88
 
# File 'lib/win_excel_db/row_sheet.rb', line 81

def write_formula(row, fieldname, value)
  raise "Write formula error! Column #{fieldname} not found in Worksheet #{@name} " if !@header[fieldname]
  raise "Write formula error! Selected row is the table header of #{@name} " if row<=1
  raise "Write formula error! Currently running in READONLY MODE" if Run.mode(@excel_handler)==:readonly
  if @buffered_mode==false then
    @worksheet.Cells(row, @header[fieldname]).Formula=value if @buffered_mode==false
  end
end