Class: POI::Workbook

Inherits:
Facade
  • Object
show all
Defined in:
lib/poi/workbook/workbook.rb

Constant Summary collapse

FONT = 
org.apache.poi.ss.usermodel.Font
FONT_CONSTANTS = 
Hash[*FONT.constants.map{|e| [e.downcase.to_sym, FONT.const_get(e)]
CELL_STYLE = 
org.apache.poi.ss.usermodel.CellStyle
CELL_STYLE_CONSTANTS = 

      
        
INDEXED_COLORS =
          
        

        
org.apache.poi.ss.usermodel.IndexedColors

      
        
INDEXED_COLORS_CONSTANTS =
          
        

        

      
    

  




  
Instance Attribute Summary collapse

  




  
    

      Class Method Summary
      collapse
    


    
  
    

      Instance Method Summary
      collapse
    


    
  



  
Constructor Details

  
    

  

  
    #initialize(filename, io_stream, options = {})  ⇒ Workbook 
  

  

  




  

    

Returns a new instance of Workbook.



  





  



  

    
      


40
41
42
43
44
45
46
47
48
49

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 40

def initialize(filename, io_stream, options={})
  @filename = filename
  @workbook = if io_stream
    org.apache.poi.ss.usermodel.WorkbookFactory.create(io_stream)
  elsif options[:format] == :hssf
    org.apache.poi.hssf.usermodel.HSSFWorkbook.new
  else
    org.apache.poi.xssf.usermodel.XSSFWorkbook.new
  end
end

    		
  





  



  

    
Instance Attribute Details

    
      
      
      

  

  
    #filename  ⇒ Object  (readonly)
  

  

  




  

    

Returns the value of attribute filename.



  





  



  

    
      


38
39
40

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 38

def filename
  @filename
end

    		
  





    
  



  

    
Class Method Details


    
      

  

  
    .create(filename, options = {})  ⇒ Object 
  

  

  




  

    
      


34
35
36

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 34

def self.create(filename, options={})
  self.new(filename, nil, options)
end

    		
  





    
      

  

  
    .open(filename_or_stream)  ⇒ Object 
  

  

  




  

    
      


16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 16

def self.open(filename_or_stream)
  name, stream = if filename_or_stream.kind_of?(java.io.InputStream)
    [File.join(Dir.tmpdir, "spreadsheet.xlsx"), filename_or_stream]
  elsif filename_or_stream.kind_of?(IO) || StringIO === filename_or_stream || filename_or_stream.respond_to?(:read)
    # NOTE: the String.unpack here can be very inefficient on large files
    [File.join(Dir.tmpdir, "spreadsheet.xlsx"), java.io.ByteArrayInputStream.new(filename_or_stream.read.unpack('c*').to_java(:byte))]
  else
    raise Exception, "FileNotFound" unless File.exists?( filename_or_stream )
    [filename_or_stream, java.io.FileInputStream.new(filename_or_stream)]
  end
  instance = self.new(name, stream)
  if block_given?
    result = yield instance
    return result 
  end
  instance
end

    		
  





    
  


  

    
Instance Method Details


    
      

  

  
    #[](reference)  ⇒ Object 
  

  

  




  

    

reference can be a Fixnum, referring to the 0-based sheet or a String which is the sheet name or a cell reference.



If a cell reference is passed the value of that cell is returned.



If the reference refers to a contiguous range of cells an Array of values will be returned.



If the reference refers to a multiple columns a Hash of values will be returned by column name.



  





  



  

    
      


147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 147

def [](reference)
  if Fixnum === reference
    return worksheets[reference]
  end
  
  if sheet = worksheets.detect{|e| e.name == reference}
    return sheet.poi_worksheet.nil? ? nil : sheet
  end

  cell = cell(reference)
  if Array === cell
    cell.collect{|e| e.value}
  elsif Hash === cell
    values = {}
    cell.each_pair{|column_name, cells| values[column_name] = cells.collect{|e| e.value}}
    values
  else
    cell.value
  end
end

    		
  





    
      

  

  
    #all_cells_in_column(reference)  ⇒ Object 
  

  

  




  

    
      


239
240
241
242
243
244
245
246
247
248
249
250
251
252

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 239

def all_cells_in_column reference
  sheet_parts = reference.split('!')
  area_parts  = sheet_parts.last.split(':')
  area_start  = "#{sheet_parts.first}!#{area_parts.first}"
  area_end    = area_parts.last
  
  area = org.apache.poi.ss.util.AreaReference.getWholeColumn(area_start, area_end)
  full_ref = "#{area.first_cell.format_as_string}:#{area.last_cell.format_as_string}"
  Area.new(full_ref).in(self)
  # cell_reference = org.apache.poi.ss.util.CellReference.new( reference + "1" )
  # column         = cell_reference.get_col
  # sheet          = cell_reference.get_sheet_name
  # worksheets[sheet].rows.collect{|row| row[column]}
end

    		
  





    
      

  

  
    #cell(reference)  ⇒ Object 
  

  

  




  

    

takes a String in the form of a 3D cell reference and returns the Cell (eg. “Sheet 1!A1”)



If the reference refers to a contiguous range of cells an array of Cells will be returned



  





  



  

    
      


171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 171

def cell reference
  # if the reference is to a named range of cells, get that range and return it
  if named_range = named_ranges.detect{|e| e.name == reference}
    cells = named_range.cells.compact
    if cells.empty?
      return nil
    else
      return cells.length == 1 ? cells.first : cells
    end
  end
  
  # check if the named_range is a full column reference
  if column_reference?(named_range)
    return all_cells_in_column named_range.formula
  end
  
  # if the reference is to an area of cells, get all the cells in that area and return them
  cells = cells_in_area(reference)
  unless cells.empty?
    return cells.length == 1 ? cells.first : cells
  end
  
  if column_reference?(reference)
    return all_cells_in_column reference
  end

  ref = POI::CELL_REF.new(reference)
  single_cell ref
end

    		
  





    
      

  

  
    #cells_in_area(reference)  ⇒ Object 
  

  

  




  

    
      


210
211
212
213

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 210

def cells_in_area reference
  area = Area.new(reference)
  area.in(self)
end

    		
  





    
      

  

  
    #clear_all_formula_results  ⇒ Object 
  

  

  




  

    
      


235
236
237

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 235

def clear_all_formula_results
  formula_evaluator.clear_all_cached_result_values
end

    		
  





    
      

  

  
    #close  ⇒ Object 
  

  

  




  

    
      


72
73
74

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 72

def close
  #noop
end

    		
  





    
      

  

  
    #create_sheet(name = 'New Sheet')  ⇒ Object 
  

  

  




  

    
      


76
77
78
79

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 76

def create_sheet name='New Sheet'
  # @workbook.createSheet name
  worksheets[name]
end

    		
  





    
      

  

  
    #create_style(options = {})  ⇒ Object 
  

  

  




  

    
      


81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 81

def create_style options={}
  font = @workbook.createFont
  set_value( font, :font_height_in_points, options ) do | value |
    value.to_i
  end
  set_value font, :bold_weight, options, FONT_CONSTANTS
  set_value font, :color, options, INDEXED_COLORS_CONSTANTS do | value |
    value.index
  end

  style = @workbook.createCellStyle
  [:alignment, :vertical_alignment, :fill_pattern, :border_right, :border_left, :border_top, :border_bottom].each do | sym |
    set_value style, sym, options, CELL_STYLE_CONSTANTS do | value |
      value.to_i
    end
  end
  [:right_border_color, :left_border_color, :top_border_color, :bottom_border_color, :fill_foreground_color, :fill_background_color].each do | sym |
    set_value( style, sym, options, INDEXED_COLORS_CONSTANTS ) do | value |
      value.index
    end
  end
  [:hidden, :locked, :wrap_text].each do | sym |
    set_value style, sym, options
  end
  [:rotation, :indentation].each do | sym |
    set_value( style, sym, options ) do | value |
      value.to_i
    end
  end
  set_value( style, :data_format, options ) do |value|
    @workbook.create_data_format.getFormat(value)
  end
  style.font = font
  style
end

    		
  





    
      

  

  
    #formula_evaluator  ⇒ Object 
  

  

  




  

    
      


51
52
53

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 51

def formula_evaluator
  @formula_evaluator ||= @workbook.creation_helper.create_formula_evaluator
end

    		
  





    
      

  

  
    #named_ranges  ⇒ Object 
  

  

  




  

    
      


133
134
135
136
137

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 133

def named_ranges
  @named_ranges ||= (0...@workbook.number_of_names).collect do | idx |
    NamedRange.new @workbook.name_at(idx), self
  end
end

    		
  





    
      

  

  
    #on_delete(cell)  ⇒ Object 
  

  

  




  

    
      


230
231
232
233

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 230

def on_delete cell
  #clear_all_formula_results
  formula_evaluator.notify_delete_cell cell.poi_cell
end

    		
  





    
      

  

  
    #on_formula_update(cell)  ⇒ Object 
  

  

  




  

    
      


224
225
226
227
228

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 224

def on_formula_update cell
  #clear_all_formula_results
  formula_evaluator.notify_set_formula cell.poi_cell
  formula_evaluator.evaluate_formula_cell(cell.poi_cell)
end

    		
  





    
      

  

  
    #on_update(cell)  ⇒ Object 
  

  

  




  

    
      


219
220
221
222

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 219

def on_update cell
  #clear_all_formula_results
  #formula_evaluator.notify_update_cell cell.poi_cell
end

    		
  





    
      

  

  
    #output_stream(name)  ⇒ Object 
  

  

  




  

    
      


68
69
70

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 68

def output_stream name
  java.io.FileOutputStream.new(name)
end

    		
  





    
      

  

  
    #poi_workbook  ⇒ Object 
  

  

  




  

    
      


215
216
217

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 215

def poi_workbook
  @workbook
end

    		
  





    
      

  

  
    #save  ⇒ Object 
  

  

  




  

    
      


55
56
57

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 55

def save
  save_as(@filename)
end

    		
  





    
      

  

  
    #save_as(filename)  ⇒ Object 
  

  

  




  

    
      


59
60
61
62
63
64
65
66

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 59

def save_as(filename)
  output = output_stream filename
  begin
    @workbook.write(output)
  ensure
    output.close
  end
end

    		
  





    
      

  

  
    #set_value(on, value_sym, from, using = nil)  ⇒ Object 
  

  

  




  

    
      


117
118
119
120
121
122
123
124
125
126
127

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 117

def set_value on, value_sym, from, using=nil
  return on unless from.has_key?(value_sym)
  value = if using
    using[from[value_sym]]
  else
    from[value_sym]
  end
  value = yield value if block_given?
  on.send("set_#{value_sym}", value)
  on
end

    		
  





    
      

  

  
    #single_cell(ref)  ⇒ Object 
  

  

  




  

    

ref is a POI::CELL_REF instance



  





  



  

    
      


202
203
204
205
206
207
208

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 202

def single_cell ref
  if ref.sheet_name.nil?
    raise 'cell references at the workbook level must include a sheet reference (eg. Sheet1!A1)'
  else
    worksheets[ref.sheet_name][ref.row][ref.col]
  end
end

    		
  





    
      

  

  
    #worksheets  ⇒ Object 
  

  

  




  

    
      


129
130
131

    		
    
      
# File 'lib/poi/workbook/workbook.rb', line 129

def worksheets
  @worksheets ||= Worksheets.new(self)
end