Module: Xlsxrb::Ooxml::Utils

Defined in:
lib/xlsxrb/ooxml/utils.rb

Overview

OOXML-specific utility methods and constants.

Constant Summary collapse

EPOCH_1900 =

Excel 1900 date system epoch.

Date.new(1899, 12, 31)
BUILTIN_NUM_FMT_CODES =

Built-in number format codes defined by SpreadsheetML.

{
  0 => "General",
  1 => "0",
  2 => "0.00",
  3 => "#,##0",
  4 => "#,##0.00",
  9 => "0%",
  10 => "0.00%",
  11 => "0.00E+00",
  12 => "# ?/?",
  13 => "# ??/??",
  14 => "mm-dd-yy",
  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/yy 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 => "mmss.0",
  48 => "##0.0E+0",
  49 => "@"
}.freeze
BUILTIN_DATE_FMT_IDS =

Built-in numFmtIds that represent date/time formats.

[14, 15, 16, 17, 18, 19, 20, 21, 22].freeze
DEFAULT_DATE_FORMAT =

Default date format code used by Writer for Date cells.

"yyyy\\-mm\\-dd"
DEFAULT_DATETIME_FORMAT =

Default date-time format code used by Writer for Time cells.

"yyyy\\-mm\\-dd\\ hh:mm:ss"

Class Method Summary collapse

Class Method Details

.date_to_serial(date) ⇒ Object

Converts a Date to an Excel serial number (1900 system).



57
58
59
60
61
62
63
 
# File 'lib/xlsxrb/ooxml/utils.rb', line 57

def date_to_serial(date)
  serial = (date - EPOCH_1900).to_i
  # Lotus 1-2-3 bug: serial 60 = Feb 29, 1900 (doesn't exist).
  # Dates on or after Mar 1, 1900 (raw serial >= 60) need +1.
  serial += 1 if serial >= 60
  serial
end

.datetime_to_serial(time) ⇒ Object

Converts a Time to a fractional Excel serial number (1900 system).



73
74
75
76
77
78
79
 
# File 'lib/xlsxrb/ooxml/utils.rb', line 73

def datetime_to_serial(time)
  date = time.to_date
  day_serial = date_to_serial(date)
  # Fractional part: seconds since midnight / seconds per day
  seconds_since_midnight = (time.hour * 3600) + (time.min * 60) + time.sec
  day_serial + (seconds_since_midnight.to_f / 86_400)
end

.hash_password(password, algorithm: "SHA-512", salt: nil, spin_count: 100_000) ⇒ Object

Hashes a plain-text password for use with sheet/workbook protection. Returns { algorithm_name:, hash_value:, salt_value:, spin_count: }. Algorithm per ECMA-376 Part 4 ยง2.4.2.24.

Raises:

  • (ArgumentError) 


96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/xlsxrb/ooxml/utils.rb', line 96

def hash_password(password, algorithm: "SHA-512", salt: nil, spin_count: 100_000)
  raise ArgumentError, "password must be a String" unless password.is_a?(String)
  raise ArgumentError, "spin_count must be a positive Integer" unless spin_count.is_a?(Integer) && spin_count.positive?

  salt_bytes = (salt || SecureRandom.random_bytes(16)).b
  # OOXML sheet/workbook protection hashing uses UTF-16LE password bytes.
  password_bytes = password.encode("UTF-16LE").b

  digest_name = algorithm.tr("-", "")
  hash = OpenSSL::Digest.digest(digest_name, salt_bytes + password_bytes)

  spin_count.times do |i|
    iteration_bytes = [i].pack("V") # little-endian uint32
    hash = OpenSSL::Digest.digest(digest_name, hash + iteration_bytes)
  end

  {
    algorithm_name: algorithm,
    hash_value: [hash].pack("m0"),
    salt_value: [salt_bytes].pack("m0"),
    spin_count: spin_count
  }
end

.serial_to_date(serial) ⇒ Object

Converts an Excel serial number (1900 system) to a Date.



66
67
68
69
70
 
# File 'lib/xlsxrb/ooxml/utils.rb', line 66

def serial_to_date(serial)
  # Adjust for Lotus 1-2-3 bug.
  serial -= 1 if serial > 60
  EPOCH_1900 + serial
end

.serial_to_datetime(serial) ⇒ Object

Converts a fractional Excel serial number to a Time (1900 system, UTC).



82
83
84
85
86
87
88
89
90
91
 
# File 'lib/xlsxrb/ooxml/utils.rb', line 82

def serial_to_datetime(serial)
  int_part = serial.to_i
  frac = serial - int_part
  date = serial_to_date(int_part)
  total_seconds = (frac * 86_400).round
  hours = total_seconds / 3600
  minutes = (total_seconds % 3600) / 60
  seconds = total_seconds % 60
  Time.utc(date.year, date.month, date.day, hours, minutes, seconds)
end