module Workbook::Readers::XlsShared

Constants

XLS_COLORS

Public Instance Methods

html_color_to_xls_color(hex) click to toggle source

Attempt to convert html-hex color value to xls color number

@param [String] hex color @return [String] xls color

# File lib/workbook/readers/xls_shared.rb, line 74
def html_color_to_xls_color hex
  Workbook::Readers::XlsShared::XLS_COLORS.each do |k,v|
    return k if (v == hex or (hex and hex != "" and k == hex.to_sym))
  end
  return nil
end
ms_formatting_to_strftime(ms_nr_format) click to toggle source

Converts standard (ruby/C++/unix/…) strftime formatting to MS's formatting

@param [String, nil] ms_nr_format (nil returns nil) @return [String, nil]

# File lib/workbook/readers/xls_shared.rb, line 15
def ms_formatting_to_strftime ms_nr_format
  ms_nr_format = num_fmt_id_to_ms_formatting(ms_nr_format) if ms_nr_format.is_a? Integer
  if ms_nr_format
    ms_nr_format = ms_nr_format.to_s.downcase
    return nil if ms_nr_format == 'general' or ms_nr_format == ""
    translation_table = {
      'yyyy'=>'%Y',
      'dddd'=>'%A',
      'mmmm'=>'%B',
      'ddd'=>'%a',
      'mmm'=>'%b',
      'yy'=>'%y',
      'dd'=>'%d',
      'mm'=>'%m',
      'y'=>'%y',
      '%%y'=>'%y',
      'd'=>'%e',
      '%%e'=>'%d',
      'm'=>'%m',
      '%%m'=>'%m',
      ';@'=>'',
      '\\'=>''
    }
    translation_table.each{|k,v| ms_nr_format.gsub!(k,v) }
    ms_nr_format
  end
end
num_fmt_id_to_ms_formatting(num_fmt_id) click to toggle source

Convert numFmtId to msmarkup @param [String, Integer] num_fmt_id numFmtId @return [String] number format (excel markup)

# File lib/workbook/readers/xls_shared.rb, line 46
def num_fmt_id_to_ms_formatting num_fmt_id
  # from: https://stackoverflow.com/questions/4730152/what-indicates-an-office-open-xml-cell-contains-a-date-time-value
  {'0'=>nil, '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)',
    '44'=>'_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)',
    '45'=>'mm:ss', '46'=>'[h]:mm:ss', '47'=>'mmss.0', '48'=>'##0.0E+0',
    '49'=>'@', '27'=>'[$-404]e/m/d', '30'=>'m/d/yy', '36'=>'[$-404]e/m/d',
    '50'=>'[$-404]e/m/d', '57'=>'[$-404]e/m/d', '59'=>'t0', '60'=>'t0.00',
    '61'=>'t#,##0', '62'=>'t#,##0.00', '67'=>'t0%', '68'=>'t0.00%',
    '69'=>'t# ?/?', '70' => 't# ??/??'}[num_fmt_id.to_s]
end
strftime_to_ms_format(numberformat) click to toggle source

Converts standard (ruby/C++/unix/…) strftime formatting to MS's formatting

@param [String, nil] numberformat (nil returns nil) @return [String, nil]

# File lib/workbook/readers/xls_shared.rb, line 85
def strftime_to_ms_format numberformat
  return nil if numberformat.nil?
  return numberformat.gsub('%Y','yyyy').gsub('%A','dddd').gsub('%B','mmmm').gsub('%a','ddd').gsub('%b','mmm').gsub('%y','yy').gsub('%d','dd').gsub('%m','mm').gsub('%y','y').gsub('%y','%%y').gsub('%e','d')
end
xls_number_to_date(number, base_date = Date.new(1899,12,30)) click to toggle source
# File lib/workbook/readers/xls_shared.rb, line 94
def xls_number_to_date number, base_date = Date.new(1899,12,30)
  base_date + number.to_i
end
xls_number_to_time(number, base_date = DateTime.new(1899,12,30)) click to toggle source
# File lib/workbook/readers/xls_shared.rb, line 90
def xls_number_to_time number, base_date = DateTime.new(1899,12,30)
  base_date + number.to_f
end