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)  ⇒ Workbook 
  

  

  




  

    

Returns a new instance of Workbook.



  





  



  

    
      


40
41
42
43

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

def initialize(filename, io_stream)
  @filename = filename
  @workbook = io_stream ? org.apache.poi.ss.usermodel.WorkbookFactory.create(io_stream) : org.apache.poi.xssf.usermodel.XSSFWorkbook.new
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)  ⇒ Object 
  

  

  




  

    
      


34
35
36

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

def self.create(filename)
  self.new(filename, nil)
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.



  





  



  

    
      


141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160

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

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 
  

  

  




  

    
      


233
234
235
236
237
238
239
240
241
242
243
244
245
246

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

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



  





  



  

    
      


165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193

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

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 
  

  

  




  

    
      


204
205
206
207

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

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

    		
  





    
      

  

  
    #clear_all_formula_results  ⇒ Object 
  

  

  




  

    
      


229
230
231

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

def clear_all_formula_results
  formula_evaluator.clear_all_cached_result_values
end

    		
  





    
      

  

  
    #close  ⇒ Object 
  

  

  




  

    
      


66
67
68

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

def close
  #noop
end

    		
  





    
      

  

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

  

  




  

    
      


70
71
72
73

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

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

    		
  





    
      

  

  
    #create_style(options = {})  ⇒ Object 
  

  

  




  

    
      


75
76
77
78
79
80
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

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

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 
  

  

  




  

    
      


45
46
47

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

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

    		
  





    
      

  

  
    #named_ranges  ⇒ Object 
  

  

  




  

    
      


127
128
129
130
131

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

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 
  

  

  




  

    
      


224
225
226
227

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

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

    		
  





    
      

  

  
    #on_formula_update(cell)  ⇒ Object 
  

  

  




  

    
      


218
219
220
221
222

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

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 
  

  

  




  

    
      


213
214
215
216

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

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

    		
  





    
      

  

  
    #output_stream(name)  ⇒ Object 
  

  

  




  

    
      


62
63
64

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

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

    		
  





    
      

  

  
    #poi_workbook  ⇒ Object 
  

  

  




  

    
      


209
210
211

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

def poi_workbook
  @workbook
end

    		
  





    
      

  

  
    #save  ⇒ Object 
  

  

  




  

    
      


49
50
51

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

def save
  save_as(@filename)
end

    		
  





    
      

  

  
    #save_as(filename)  ⇒ Object 
  

  

  




  

    
      


53
54
55
56
57
58
59
60

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

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 
  

  

  




  

    
      


111
112
113
114
115
116
117
118
119
120
121

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

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



  





  



  

    
      


196
197
198
199
200
201
202

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

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 
  

  

  




  

    
      


123
124
125

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

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