Support for custom number formats

[Krap]

I have a customer file that contains some custom number formats - a static string concatenated to the numeric value - defined with the 'General "string"' syntax.

The current code don't seem to handle this case, and depending on the chars in the string, it may be badly identified as a date or time. In my specific case, the string is mois, that contains m and s letters, and is then identified by parse_number_format as a time format, resulting in values of 00:00 instead of 1 mois or 1, ...

I wrote a quick patch for this (see below), that solves my own problem, but as i have no knowledge of the underneath format specifications, or the correct expected result in the gem's spirit (should it be the numeric value, or the formatted text ?), i fear of adding more troubles than fixes 😅

Thanks in advance for help ! I can work on it if necessary, as far as l’m guided to prevent damages 😉

Sample file : custom-number-formats.xlsx

    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 format.index("General") && format != "General"
        CGI.unescapeHTML(format).delete('"').gsub!("General", number.to_s)
      elsif !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

[martijn]

Interesting idea. Until now, Xsv ignored any non-date/time number formats as it would lead to numbers being returned as ruby strings which is not always desirable. This could be solved with an Xsv-specific value object that contains both the number and string representation, but the idea of Xsv was always to stick as close to Ruby primitives as possible. Still, requests for richer data come in ever so often (see #59, #32, #44) so it might be interesting to include these in an opt-in feature or breaking Xsv 2.0 design.