class POI::Workbook
Constants
- CELL_STYLE
- CELL_STYLE_CONSTANTS
- FONT
- FONT_CONSTANTS
- INDEXED_COLORS
- INDEXED_COLORS_CONSTANTS
Attributes
filename[R]
Public Class Methods
create(filename, options={})
click to toggle source
# File lib/poi/workbook/workbook.rb, line 34 def self.create(filename, options={}) self.new(filename, nil, options) end
new(filename, io_stream, options={})
click to toggle source
# 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
open(filename_or_stream) { |instance| ... }
click to toggle source
# 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
Public Instance Methods
[](reference)
click to toggle source
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.
# 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)
click to toggle source
# 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)
click to toggle source
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
# 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)
click to toggle source
# 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()
click to toggle source
# File lib/poi/workbook/workbook.rb, line 235 def clear_all_formula_results formula_evaluator.clear_all_cached_result_values end
close()
click to toggle source
# File lib/poi/workbook/workbook.rb, line 72 def close #noop end
create_sheet(name='New Sheet')
click to toggle source
# File lib/poi/workbook/workbook.rb, line 76 def create_sheet name='New Sheet' # @workbook.createSheet name worksheets[name] end
create_style(options={})
click to toggle source
# 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()
click to toggle source
# File lib/poi/workbook/workbook.rb, line 51 def formula_evaluator @formula_evaluator ||= @workbook.creation_helper.create_formula_evaluator end
named_ranges()
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# 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)
click to toggle source
# File lib/poi/workbook/workbook.rb, line 68 def output_stream name java.io.FileOutputStream.new(name) end
poi_workbook()
click to toggle source
# File lib/poi/workbook/workbook.rb, line 215 def poi_workbook @workbook end
save()
click to toggle source
# File lib/poi/workbook/workbook.rb, line 55 def save save_as(@filename) end
save_as(filename)
click to toggle source
# 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) { |value| ... }
click to toggle source
# 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)
click to toggle source
ref is a POI::CELL_REF instance
# 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()
click to toggle source
# File lib/poi/workbook/workbook.rb, line 129 def worksheets @worksheets ||= Worksheets.new(self) end
Private Instance Methods
column_reference?(named_range_or_reference)
click to toggle source
# File lib/poi/workbook/workbook.rb, line 255 def column_reference? named_range_or_reference return false if named_range_or_reference.nil? reference = named_range_or_reference if NamedRange === named_range_or_reference reference = named_range_or_reference.formula end cell_reference = reference.split('!', 2).last beginning, ending = cell_reference.split(':') !(beginning =~ /\d/ || (ending.nil? ? false : ending =~ /\d/)) end