Module: Xsv::Helpers

Included in:
Sheet, SheetBoundsHandler, SheetRowsHandler
Defined in:
lib/xsv/helpers.rb

Constant Summary collapse

BUILT_IN_NUMBER_FORMATS =

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

{
  1 => "0",
  2 => "0.00",
  3 => "#, ##0",
  4 => "#, ##0.00",
  5 => "$#, ##0_);($#, ##0)",
  6 => "$#, ##0_);[Red]($#, ##0)",
  7 => "$#, ##0.00_);($#, ##0.00)",
  8 => "$#, ##0.00_);[Red]($#, ##0.00)",
  9 => "0%",
  10 => "0.00%",
  11 => "0.00E+00",
  12 => "# ?/?",
  13 => "# ??/??",
  14 => "m/d/yyyy",
  15 => "d-mmm-yy",
  16 => "d-mmm",
  17 => "mmm-yy",
  18 => "h:mm AM/PM",
  19 => "h:mm:ss AM/PM",
  20 => "h:mm",
  21 => "h:mm:ss",
  22 => "m/d/yyyy h:mm",
  37 => "#, ##0_);(#, ##0)",
  38 => "#, ##0_);[Red](#, ##0)",
  39 => "#, ##0.00_);(#, ##0.00)",
  40 => "#, ##0.00_);[Red](#, ##0.00)",
  45 => "mm:ss",
  46 => "[h]:mm:ss",
  47 => "mm:ss.0",
  48 => "##0.0E+0",
  49 => "@"
}.freeze
MINUTE = 
60
HOUR = 
3600
A_CODEPOINT = 
"A".ord.freeze
EPOCH =

The epoch for all dates in OOXML Spreadsheet documents

Date.new(1899, 12, 30).freeze

Instance Method Summary collapse

Instance Method Details

#column_index(col) ⇒ Object

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

Parameters:

  • col (String) —

    Column name in A1 notation



49
50
51
52
53
54
55
56
57
 
# File 'lib/xsv/helpers.rb', line 49

def column_index(col)
  chars = col.bytes
  sum = 0
  while (char = chars.delete_at(0))
    break sum - 1 if char < A_CODEPOINT # reached the number

    sum = sum * 26 + (char - A_CODEPOINT + 1)
  end
end

#parse_date(number) ⇒ Object

Return a Date for the given Excel date value



60
61
62
 
# File 'lib/xsv/helpers.rb', line 60

def parse_date(number)
  EPOCH + number
end

#parse_datetime(number) ⇒ Object

Returns a time including a date as a Time object



84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/xsv/helpers.rb', line 84

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

Returns a number as either Integer or Float



97
98
99
100
101
102
103
104
105
 
# File 'lib/xsv/helpers.rb', line 97

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

Apply date or time number formats, if applicable



108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/xsv/helpers.rb', line 108

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

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



65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/xsv/helpers.rb', line 65

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