module Xsv::Helpers

Constants

A_CODEPOINT
BUILT_IN_NUMBER_FORMATS

The default OOXML Spreadheet number formats according to the ECMA standard User formats are appended from index 174 onward

EPOCH

The epoch for all dates in OOXML Spreadsheet documents

HOUR
MINUTE

Public Instance Methods

column_index(col) click to toggle source

Return the index number for the given Excel column name (i.e. “A1” => 0)

# File lib/xsv/helpers.rb, line 48
def column_index(col)
  col.each_codepoint.reduce(0) do |sum, n|
    break sum - 1 if n < A_CODEPOINT # reached a number

    sum * 26 + (n - A_CODEPOINT + 1)
  end
end
parse_date(number) click to toggle source

Return a Date for the given Excel date value

# File lib/xsv/helpers.rb, line 57
def parse_date(number)
  EPOCH + number
end
parse_datetime(number) click to toggle source

Returns a time including a date as a {Time} object

# File lib/xsv/helpers.rb, line 81
def parse_datetime(number)
  date_base = number.truncate
  time = parse_date(date_base).to_time

  time_base = (number - date_base) * 24

  hours = time_base.truncate
  minutes = (time_base - hours) * 60

  time + hours * HOUR + minutes.round * MINUTE
end
parse_number(string) click to toggle source

Returns a number as either Integer or Float

# File lib/xsv/helpers.rb, line 94
def parse_number(string)
  if string.include? '.'
    string.to_f
  elsif string.include? 'E'
    Complex(string).to_f
  else
    string.to_i
  end
end
parse_number_format(number, format) click to toggle source

Apply date or time number formats, if applicable

# File lib/xsv/helpers.rb, line 105
def parse_number_format(number, format)
  number = parse_number(number) # number is always a string since it comes out of the Sax Parser

  return number if format.nil?

  is_date_format = format.scan(/[dmy]+/).length > 1
  is_time_format = format.scan(/[hms]+/).length > 1

  if !is_date_format && !is_time_format
    number
  elsif is_date_format && is_time_format
    parse_datetime(number)
  elsif is_date_format
    parse_date(number)
  elsif is_time_format
    parse_time(number)
  end
end
parse_time(number) click to toggle source

Return a time as a string for the given Excel time value

# File lib/xsv/helpers.rb, line 62
def parse_time(number)
  # Disregard date part
  number -= number.truncate if number.positive?

  base = number * 24

  hours = base.truncate
  minutes = ((base - hours) * 60).round

  # Compensate for rounding errors
  if minutes >= 60
    hours += (minutes / 60)
    minutes = minutes % 60
  end

  format('%02d:%02d', hours, minutes)
end