lib/cell.rb in rxl-0.4.1 vs lib/cell.rb in rxl-0.5.0

- old
+ new

@@ -6,24 +6,41 @@ ### GET HASH CELL FROM RUBYXL CELL ### ############################################## def self.rubyxl_cell_to_hash_cell(rubyxl_cell = nil) rubyxl_cell_value = rubyxl_cell.nil? ? RubyXL::Cell.new.value : rubyxl_cell.value + rubyxl_cell_value = resolve_date_ms(rubyxl_cell_value) if rubyxl_cell_value.is_a?(Date) + format = hash_cell_format(rubyxl_cell_value) { - value: rubyxl_cell_value, - format: hash_cell_format(rubyxl_cell_value), - formula: rubyxl_cell_formula(rubyxl_cell), - h_align: rubyxl_cell_h_align(rubyxl_cell), - v_align: rubyxl_cell_v_align(rubyxl_cell), - bold: rubyxl_cell.nil? ? false : rubyxl_cell.is_bolded, - fill: rubyxl_cell.nil? ? 'ffffff' : rubyxl_cell.fill_color, - font_name: rubyxl_cell.nil? ? 'Calibri' : rubyxl_cell.font_name, - font_size: rubyxl_cell.nil? ? 12 : rubyxl_cell.font_size.to_i, - border: rubyxl_cell_to_border_hash(rubyxl_cell) + value: rubyxl_cell_value, + format: format, + formula: rubyxl_cell_formula(rubyxl_cell), + decimals: format == :number ? decimals(rubyxl_cell) : nil, + h_align: rubyxl_cell_h_align(rubyxl_cell), + v_align: rubyxl_cell_v_align(rubyxl_cell), + bold: rubyxl_cell.nil? ? false : rubyxl_cell.is_bolded, + fill: rubyxl_cell.nil? ? 'ffffff' : rubyxl_cell.fill_color, + font_name: rubyxl_cell.nil? ? 'Calibri' : rubyxl_cell.font_name, + font_size: rubyxl_cell.nil? ? 12 : rubyxl_cell.font_size.to_i, + border: rubyxl_cell_to_border_hash(rubyxl_cell) } end + def self.resolve_date_ms(value) + value_ut = value.strftime('%s') + ut = value.strftime('%L').to_i > 499 ? "#{value_ut.to_i + 1}" : value_ut + DateTime.strptime(ut, '%s') + end + + def self.decimals(rubyxl_cell) + number_format = rubyxl_cell.number_format + return nil unless number_format + format_code = number_format.format_code + i = format_code.reverse.index('.') + format_code[0 - i..-1].length if i + end + def self.rubyxl_cell_to_border_hash(rubyxl_cell) { top: rubyxl_cell.nil? ? nil : rubyxl_cell.get_border(:top), bottom: rubyxl_cell.nil? ? nil : rubyxl_cell.get_border(:bottom), left: rubyxl_cell.nil? ? nil : rubyxl_cell.get_border(:left), @@ -66,33 +83,76 @@ merge_row_index, merge_column_index = RubyXL::Reference.ref2ind(combined_hash_cell[:merge]) rubyxl_worksheet.merge_cells(row_index, column_index, merge_column_index, merge_row_index) if combined_hash_cell[:merge] rubyxl_worksheet.change_column_width(column_index, combined_hash_cell[:width]) if combined_hash_cell[:width] - rubyxl_worksheet[row_index][column_index].change_font_name(combined_hash_cell[:font_style]) if combined_hash_cell[:font_style] + rubyxl_worksheet[row_index][column_index].change_font_name(combined_hash_cell[:font_name]) if combined_hash_cell[:font_name] rubyxl_worksheet[row_index][column_index].change_font_size(combined_hash_cell[:font_size]) if combined_hash_cell[:font_size] rubyxl_worksheet[row_index][column_index].change_fill(combined_hash_cell[:fill]) if combined_hash_cell[:fill] rubyxl_worksheet[row_index][column_index].change_horizontal_alignment(combined_hash_cell[:h_align]) if combined_hash_cell[:h_align] rubyxl_worksheet[row_index][column_index].change_font_bold(combined_hash_cell[:bold]) if combined_hash_cell[:bold] - if combined_hash_cell[:border_all] - rubyxl_worksheet[row_index][column_index].change_border('top' , combined_hash_cell[:border_all]) - rubyxl_worksheet[row_index][column_index].change_border('bottom' , combined_hash_cell[:border_all]) - rubyxl_worksheet[row_index][column_index].change_border('left' , combined_hash_cell[:border_all]) - rubyxl_worksheet[row_index][column_index].change_border('right' , combined_hash_cell[:border_all]) + if combined_hash_cell[:border] + rubyxl_worksheet[row_index][column_index].change_border('top' , combined_hash_cell[:border][:top]) + rubyxl_worksheet[row_index][column_index].change_border('bottom' , combined_hash_cell[:border][:bottom]) + rubyxl_worksheet[row_index][column_index].change_border('left' , combined_hash_cell[:border][:left]) + rubyxl_worksheet[row_index][column_index].change_border('right' , combined_hash_cell[:border][:right]) end end - def self.add_rubyxl_cells(combined_hash_cell, rubyxl_worksheet, row_index, column_index) - if combined_hash_cell[:formula] - rubyxl_worksheet.add_cell(row_index, column_index, '', combined_hash_cell[:formula]).set_number_format combined_hash_cell[:dp_2] + def self.add_rubyxl_cells(hash_cell, rubyxl_worksheet, row_index, column_index) + number_format = write_format(hash_cell) + if hash_cell[:formula] + if number_format + rubyxl_worksheet.add_cell(row_index, column_index, '', hash_cell[:formula]) + .set_number_format(number_format) + else + rubyxl_worksheet.add_cell(row_index, column_index, '', hash_cell[:formula]) + end else - rubyxl_worksheet.add_cell(row_index, column_index, combined_hash_cell[:value]) + if number_format + cell_value = hash_cell[:value].is_a?(Date) ? date_to_num(hash_cell[:value]) : hash_cell[:value] + rubyxl_worksheet.add_cell(row_index, column_index, cell_value) + .set_number_format(number_format) + else + rubyxl_worksheet.add_cell(row_index, column_index, hash_cell[:value]) + end end end + def self.write_format(hash_cell) + case hash_cell[:format] + when :number + hash_cell[:decimals] ? "0.#{ '0' * hash_cell[:decimals] }" : '0' + when :date + hash_cell[:date_format] ? hash_cell[:date_format] : 'dd/mm/yyyy' + when :time + hash_cell[:date_format] ? hash_cell[:date_format] : 'hh:mm:ss' + when :percentage + hash_cell[:decimals] ? "0.#{ '0' * hash_cell[:decimals] }%" : '0%' + else + case hash_cell[:value].class.to_s + when 'Fixnum' + '0' + when 'Float' + value = hash_cell[:value].to_s + decimals = value[value.index('.') + 1..-1].length + "0.#{ '0' * decimals }" + when 'DateTime' + return 'dd/mm/yyyy hh:mm:ss' + else + nil + end + end + end + def self.date_to_num(date) + workbook = RubyXL::Workbook.new + workbook.date_to_num(date) + end + + ################################## ### VALIDATE HASH CELL ### ################################## def self.validate_hash_cell(hash_cell_key, hash_cell, trace) @@ -122,11 +182,13 @@ end def self.valid_cell_keys %i[ value - number + format + date_format formula + decimals bold h_align v_align border fill \ No newline at end of file