class RubyXL::Workbook

Constants

APPLICATION
APPVERSION

Attributes

application[RW]
appversion[RW]
borders[RW]
calc_chain[RW]
cell_style_xfs[RW]
cell_styles[RW]
cell_xfs[RW]
chart_rels[RW]
charts[RW]
colors[RW]
column_lookup_hash[RW]
company[RW]
created_at[RW]
creator[RW]
date1904[RW]
defined_names[RW]
drawings[RW]
drawings_rels[RW]
filepath[RW]
fills[RW]
fonts[RW]
macros[RW]
media[RW]
modified_at[RW]
modifier[RW]
num_fmts[RW]
printer_settings[RW]
shared_strings[R]
shared_strings_XML[RW]
style_corrector[RW]
theme[RW]
worksheet_rels[RW]
worksheets[RW]

Public Class Methods

new(worksheets=[], filepath=nil, creator=nil, modifier=nil, created_at=nil, company='', application=APPLICATION, appversion=APPVERSION, date1904=0) click to toggle source
# File lib/rubyXL/workbook.rb, line 31
def initialize(worksheets=[], filepath=nil, creator=nil, modifier=nil, created_at=nil,
               company='', application=APPLICATION,
               appversion=APPVERSION, date1904=0)

  # Order of sheets in the +worksheets+ array corresponds to the order of pages in Excel UI.
  # SheetId's, rId's, etc. are completely unrelated to ordering.
  @worksheets = worksheets || []

  @worksheets << Worksheet.new(self) if @worksheets.empty?

  @filepath           = filepath
  @creator            = creator
  @modifier           = modifier
  @company            = company
  @application        = application
  @appversion         = appversion
  @num_fmts           = []
  @num_fmts_by_id     = nil
  @fonts              = []
  @fills              = nil
  @borders            = []
  @cell_xfs           = []
  @cell_style_xfs     = []
  @cell_styles        = []
  @shared_strings     = RubyXL::SharedStrings.new
  @calc_chain         = nil #unnecessary?
  @date1904           = date1904 > 0
  @media              = RubyXL::GenericStorage.new(File.join('xl', 'media')).binary
  @external_links     = RubyXL::GenericStorage.new(File.join('xl', 'externalLinks'))
  @external_links_rels= RubyXL::GenericStorage.new(File.join('xl', 'externalLinks', '_rels'))
  @style_corrector    = nil
  @drawings           = RubyXL::GenericStorage.new(File.join('xl', 'drawings'))
  @drawings_rels      = RubyXL::GenericStorage.new(File.join('xl', 'drawings', '_rels'))
  @charts             = RubyXL::GenericStorage.new(File.join('xl', 'charts'))
  @chart_rels         = RubyXL::GenericStorage.new(File.join('xl', 'charts', '_rels'))
  @worksheet_rels     = RubyXL::GenericStorage.new(File.join('xl', 'worksheets', '_rels'))
  @theme              = RubyXL::GenericStorage.new(File.join('xl', 'theme'))
  @printer_settings   = RubyXL::GenericStorage.new(File.join('xl', 'printerSettings')).binary
  @macros             = RubyXL::GenericStorage.new('xl').binary
  @colors             = {}
  @shared_strings_XML = nil
  @defined_names      = []
  @column_lookup_hash = {}

  begin
    @created_at       = DateTime.parse(created_at).strftime('%Y-%m-%dT%TZ')
  rescue
    t = Time.now
    @created_at       = t.strftime('%Y-%m-%dT%TZ')
  end
  @modified_at        = @created_at

  fill_styles()
  fill_shared_strings()
end

Public Instance Methods

[](ind) click to toggle source

Finds worksheet by its name or numerical index

# File lib/rubyXL/workbook.rb, line 88
def [](ind)
  case ind
  when Integer then worksheets[ind]
  when String  then worksheets.find { |ws| ws.sheet_name == ind }
  end
end
add_worksheet(name = nil) click to toggle source

Create new simple worksheet and add it to the workbook worksheets

@param [String] The name for the new worksheet

# File lib/rubyXL/workbook.rb, line 98
def add_worksheet(name = nil)
  new_worksheet = Worksheet.new(self, name)
  worksheets << new_worksheet
  new_worksheet
end
date_num_fmt?(num_fmt) click to toggle source
# File lib/rubyXL/workbook.rb, line 170
def date_num_fmt?(num_fmt)
  @num_fmt_date_hash ||= {}
  if @num_fmt_date_hash[num_fmt].nil?
    @num_fmt_date_hash[num_fmt] = is_date_format?(num_fmt)
  end
  return @num_fmt_date_hash[num_fmt]
end
date_to_num(date) click to toggle source
# File lib/rubyXL/workbook.rb, line 162
def date_to_num(date)
  date && (date.ajd - base_date().ajd).to_i
end
each() { |i| ... } click to toggle source
# File lib/rubyXL/workbook.rb, line 104
def each
  worksheets.each{|i| yield i}
end
get_fill_color(xf) click to toggle source
# File lib/rubyXL/workbook.rb, line 239
def get_fill_color(xf)
  fill = @fills[xf.fill_id]
  pattern = fill && fill.pattern_fill
  color = pattern && pattern.fg_color
  color && color.rgb || 'ffffff'
end
is_date_format?(num_fmt) click to toggle source
# File lib/rubyXL/workbook.rb, line 178
def is_date_format?(num_fmt)
  num_fmt = num_fmt.downcase
  skip_chars = ['$', '-', '+', '/', '(', ')', ':', ' ']
  num_chars = ['0', '#', '?']
  non_date_formats = ['0.00e+00', '##0.0e+0', 'general', '@']
  date_chars = ['y','m','d','h','s']

  state = 0
  s = ''

  num_fmt.split(//).each do |c|
    case state 
    when 0 then
      if c == '"'
        state = 1
      elsif ['\\', '_', '*'].include?(c)
        state = 2
      elsif skip_chars.include?(c)
        next
      else
        s << c
      end
    when 1 then
      state = 0 if c == '"'
    when 2 then
      state = 0
    end
  end

  s.gsub!(/\[[^\]]*\]/, '')

  return false if non_date_formats.include?(s)

  separator = ';'
  got_sep = 0
  date_count = 0
  num_count = 0

  s.split(//).each do |c|
    if date_chars.include?(c)
      date_count += 1
    elsif num_chars.include?(c)
      num_count += 1
    elsif c == separator
      got_sep = 1
    end
  end

  if date_count > 0 && num_count == 0
    return true
  elsif num_count > 0 && date_count == 0
    return false
  elsif date_count
    # ambiguous result
  elsif got_sep == 0
    # constant result
  end

  return date_count > num_count
end
num_fmts_by_id() click to toggle source
# File lib/rubyXL/workbook.rb, line 108
def num_fmts_by_id
  return @num_fmts_by_id unless @num_fmts_by_id.nil?

  @num_fmts_by_id = {}

  num_fmts.each { |fmt| @num_fmts_by_id[fmt.num_fmt_id] = fmt }

  @num_fmts_by_id
end
num_to_date(num) click to toggle source
# File lib/rubyXL/workbook.rb, line 166
def num_to_date(num)
  num && (base_date + num)
end
register_new_border(new_border, old_xf) click to toggle source
# File lib/rubyXL/workbook.rb, line 278
def register_new_border(new_border, old_xf)
  new_xf = old_xf.dup

  unless borders[old_xf.border_id].count == 1 && old_xf.border_id > 0 # If the old border not used anymore, just replace it
    new_xf.border_id = borders.find_index { |x| x == new_border } # Use existing border, if it exists
    new_xf.border_id ||= borders.size # If this border has never existed before, add it to collection.
  end

  borders[old_xf.border_id].count -= 1
  new_border.count += 1
  borders[new_xf.border_id] = new_border

  new_xf.apply_border = true
  new_xf
end
register_new_fill(new_fill, old_xf) click to toggle source
# File lib/rubyXL/workbook.rb, line 246
def register_new_fill(new_fill, old_xf)
  new_xf = old_xf.dup

  unless fills[old_xf.fill_id].count == 1 && old_xf.fill_id > 2 # If the old fill is not used anymore, just replace it
    new_xf.fill_id = fills.find_index { |x| x == new_fill } # Use existing fill, if it exists
    new_xf.fill_id ||= fills.size # If this fill has never existed before, add it to collection.
  end

  fills[old_xf.fill_id].count -= 1
  new_fill.count += 1
  fills[new_xf.fill_id] = new_fill

  new_xf.apply_fill = true
  new_xf
end
register_new_font(new_font, old_xf) click to toggle source
# File lib/rubyXL/workbook.rb, line 262
def register_new_font(new_font, old_xf)
  new_xf = old_xf.dup

  unless fonts[old_xf.font_id].count == 1 && old_xf.font_id > 1 # If the old font is not used anymore, just replace it
    new_xf.font_id = fonts.find_index { |x| x == new_font } # Use existing font, if it exists
    new_xf.font_id ||= fonts.size # If this font has never existed before, add it to collection.
  end

  fonts[old_xf.font_id].count -= 1
  new_font.count += 1
  fonts[new_xf.font_id] = new_font

  new_xf.apply_font = true
  new_xf
end
register_new_xf(new_xf, old_style_index) click to toggle source
# File lib/rubyXL/workbook.rb, line 294
def register_new_xf(new_xf, old_style_index)
  new_xf_id = cell_xfs.find_index { |xf| xf == new_xf } # Use existing XF, if it exists
  new_xf_id ||= cell_xfs.size # If this XF has never existed before, add it to collection.

  cell_xfs[old_style_index].count -= 1
  new_xf.count += 1
  cell_xfs[new_xf_id] = new_xf

  new_xf_id
end
write(filepath = @filepath) click to toggle source

filepath of xlsx file (including file itself)

# File lib/rubyXL/workbook.rb, line 119
def write(filepath = @filepath)
  validate_before_write

  extension = File.extname(filepath)
  unless %w{.xlsx .xlsm}.include?(extension)
    raise "Only xlsx and xlsm files are supported. Unsupported extension: #{extension}"
  end

  dirpath  = File.dirname(filepath)
  temppath = File.join(dirpath, Dir::Tmpname.make_tmpname([ File.basename(filepath), '.tmp' ], nil))
  FileUtils.mkdir_p(temppath)
  zippath  = File.join(temppath, 'file.zip')

  Zip::File.open(zippath, Zip::File::CREATE) { |zipfile|
    [ Writer::ContentTypesWriter, Writer::RootRelsWriter, Writer::AppWriter, Writer::CoreWriter,
      Writer::ThemeWriter, Writer::WorkbookRelsWriter, Writer::WorkbookWriter, Writer::StylesWriter
    ].each { |writer_class| writer_class.new(self).add_to_zip(zipfile) }
    
    Writer::SharedStringsWriter.new(self).add_to_zip(zipfile) unless @shared_strings.empty?

    [ @media, @external_links, @external_links_rels,
      @drawings, @drawings_rels, @charts, @chart_rels,
      @printer_settings, @worksheet_rels, @macros ].each { |s| s.add_to_zip(zipfile) }

    @worksheets.each_index { |i| Writer::WorksheetWriter.new(self, i).add_to_zip(zipfile) }
  }

  FileUtils.mv(zippath, filepath)
  FileUtils.rm_rf(temppath) if File.exist?(filepath)

  return filepath
end

Private Instance Methods

base_date() click to toggle source
# File lib/rubyXL/workbook.rb, line 152
def base_date
  if @date1904 then
    Date.new(1904, 1, 1)
  else
    # Subtracting one day to accomodate for erroneous 1900 leap year compatibility only for 1900 based dates
    Date.new(1899, 12, 31) - 1
  end
end
fill_shared_strings() click to toggle source

fills shared strings hash, contains each unique string

# File lib/rubyXL/workbook.rb, line 326
def fill_shared_strings()
  @worksheets.compact.each { |sheet|
    sheet.sheet_data.each { |row|
      row.each { |cell|
        if cell && cell.value && cell.datatype == RubyXL::Cell::SHARED_STRING then
          get_index(cell.value.to_s, :add_if_missing)
        end
      }
    }
  }
end
fill_styles() click to toggle source

Do not change. Excel requires that some of these styles be default, and will simply assume that the 0 and 1 indexed fonts are the default values.

# File lib/rubyXL/workbook.rb, line 309
def fill_styles()

  @fonts = [ RubyXL::Font.new(:name => RubyXL::StringValue.new(:val => 'Verdana'), :sz => RubyXL::FloatValue.new(:val => 10) ),
             RubyXL::Font.new(:name => RubyXL::StringValue.new(:val => 'Verdana'), :sz => RubyXL::FloatValue.new(:val => 8) ) ]

  @fills = [ RubyXL::Fill.new(:pattern_fill => RubyXL::PatternFill.new(:pattern_type => 'none')),
             RubyXL::Fill.new(:pattern_fill => RubyXL::PatternFill.new(:pattern_type => 'gray125')) ]

  @borders = [ RubyXL::Border.new ]

  @cell_style_xfs = [ RubyXL::XF.new(:num_fmt_id => 0, :font_id => 0, :fill_id => 0, :border_id => 0) ]
  @cell_xfs = [ RubyXL::XF.new(:num_fmt_id => 0, :font_id => 0, :fill_id => 0, :border_id => 0, :xfId => 0) ]
  @cell_styles = [ RubyXL::CellStyle.new({ :builtin_id => 0, :name => 'Normal', :xf_id => 0 }) ]
end
validate_before_write() click to toggle source
# File lib/rubyXL/workbook.rb, line 338
def validate_before_write
  ## TODO CHECK IF STYLE IS OK if not raise
end