Class: Libis::Tools::Spreadsheet

Inherits:
Object
  • Object
show all
Defined in:
lib/libis/tools/spreadsheet.rb

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(file_name, opts = {}) ⇒ Spreadsheet

Spreadsheet reader.

This class supports CSV, Excel 2007-2016, Excel (pre-2007), LibreOffice/OpenOffice Calc and Google spreadsheets thanks to the Roo (github.com/roo-rb/roo) project.

The first argument is the file name to read. For spreadsheets, append ‘|’ and the sheet name to specify the sheet to read.

The second argument is a Hash with options. The options can be:

  • required: a list of headers that need to be present. The list can be an Array containing the litteral header values expected. Alternatively, a Hash is also allowed with alternative header names as keys and litteral names as values. If a :headers keys is present in the Hash with a value of true or :first, whatever is on the first row, will be used as header values, ignoring the rest of the Hash. A key of :header_search Default is empty array, meaning to use whatever is on the first row as header.

  • optional: a list of headers that may be present, but are not required. Similar format as above. Default is empty array

  • extension: :csv, :xlsx, :xlsm, :ods, :xls, :google to help the library in deciding what format the file is in.

The following options are only applicable to CSV input files and are ignored otherwise.

  • encoding: the encoding of the CSV file. e.g. ‘windows-1252:UTF-8’ to convert the input from windows code page 1252 to UTF-8 during file reading

  • col_sep: column separator. Default is ‘,’, but can be set to “t” for TSV files.

  • quote_char: character for quoting.

Resources are created during initialisation and should be freed by calling the #close method.

Parameters:

  • file_name (String)
  • opts (Hash) (defaults to: {}) 


38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/libis/tools/spreadsheet.rb', line 38

def initialize(file_name, opts = {})
  options = {
      csv_options: [:encoding, :col_sep, :quote_char].inject({}) do |h, k|
        h[k] = opts.delete(k) if opts[k]
        h
      end.merge(
          encoding: 'UTF-8',
          col_sep: ',',
          quote_char: '"',
      ),
      skip_headers: true,
      force_headers: true,
  }.merge(opts)

  required_headers = options.delete(:required) || []
  optional_headers = options.delete(:optional) || []

  file, sheet = file_name.split('|')
  @ss = ::Roo::Spreadsheet.open(file, options)
  @ss.default_sheet = sheet if sheet

  check_headers(required_headers, optional_headers)

end

Class Method Details

.foreach(file_name, opts = {}, &block) ⇒ Object

Open and iterate over sheet content.

Parameters:

  • @see —

    #initialize



93
94
95
 
# File 'lib/libis/tools/spreadsheet.rb', line 93

def self.foreach(file_name, opts = {}, &block)
  Libis::Tools::Spreadsheet.new(file_name, opts).each(&block)
end

Instance Method Details

#each(options = {}, &block) ⇒ Object

Iterate over sheet content.

The options Hash can contain the following keys:

  • :sheet - overwrites default sheet name

  • :required - Array or Hash of required headers

  • :optional - Array or Hash of optional headers

Each iteration, a Hash will be passed with the key names as specified in the header options and the corresponding cell values.

Parameters:

  • options (Hash) (defaults to: {}) 


74
75
76
77
 
# File 'lib/libis/tools/spreadsheet.rb', line 74

def each(options = {}, &block)
  @ss.default_sheet = options[:sheet] if options[:sheet]
  @ss.each(check_headers(options[:required], options[:optional]), &block)
end

#headers ⇒ Object 



97
98
99
 
# File 'lib/libis/tools/spreadsheet.rb', line 97

def headers
  (@ss.headers || {}).keys + @extra_headers
end

#parse(options = {}) ⇒ Object 



79
80
81
82
 
# File 'lib/libis/tools/spreadsheet.rb', line 79

def parse(options = {})
  @ss.default_sheet = options[:sheet] if options[:sheet]
  @ss.parse(check_headers(options[:required], options[:optional]))
end

#shift ⇒ Object 



84
85
86
87
88
 
# File 'lib/libis/tools/spreadsheet.rb', line 84

def shift
  return nil unless @current_row < @ss.last_row
  @current_row += 1
  Hash[@ss.row(@current_row).map.with_index { |v, i| [headers[i], v] }]
end