123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176 |
- # Copyright (C) 2012-2022 Zammad Foundation, https://zammad-foundation.org/
- class ExcelSheet
- def initialize(title:, header:, records:, locale:, timezone: nil)
- require 'writeexcel' # Only load this gem when it is really used.
- @title = title
- @header = header
- @records = records
- @timezone = timezone.presence || Setting.get('timezone_default')
- @locale = locale || Locale.default
- @tempfile = Tempfile.new('excel-export.xls')
- @workbook = WriteExcel.new(@tempfile)
- @worksheet = @workbook.add_worksheet
- @contents = nil
- @current_row = 0
- @current_column = 0
- @lookup_cache = {}
- @format_time = @workbook.add_format(num_format: 'yyyy-mm-dd hh:mm:ss')
- @format_date = @workbook.add_format(num_format: 'yyyy-mm-dd')
- @format_headline = @workbook.add_format
- @format_headline.set_bold
- @format_headline.set_size(14)
- @format_headline.set_color('black')
- @format_header = @workbook.add_format
- @format_header.set_italic
- @format_header.set_bg_color('gray')
- @format_header.set_color('white')
- @format_footer = @workbook.add_format
- @format_footer.set_italic
- @format_footer.set_color('gray')
- @format_footer.set_size(8)
- end
- def contents
- file = File.new(@tempfile, 'r')
- contents = file.read
- file.close
- contents
- end
- def content
- gen_header
- gen_rows
- gen_footer
- contents
- end
- def gen_header
- @worksheet.write_string(@current_row, @current_column, @title, @format_headline)
- @worksheet.set_row(0, 18)
- @current_row += 2
- @current_column = 0
- @header.each do |header|
- if header[:width]
- @worksheet.set_column(@current_column, @current_column, header[:width])
- end
- @worksheet.write_string(@current_row, @current_column, header[:display] || header[:name], @format_header)
- @current_column += 1
- end
- end
- def gen_rows
- @records.each do |record|
- gen_row_by_array(record)
- end
- end
- def gen_row_by_array(record)
- @current_row += 1
- @current_column = 0
- record.each do |item|
- begin
- if item.acts_like?(:time) || item.acts_like?(:date)
- value_convert(item, nil, { data_type: 'datetime' })
- elsif item.is_a?(Integer) || item.is_a?(Float)
- value_convert(item, nil, { data_type: 'integer' })
- else
- value_convert(item, nil, { data_type: 'string' })
- end
- rescue => e
- Rails.logger.error e
- end
- @current_column += 1
- end
- end
- def gen_row_by_header(record, additional = {})
- @current_row += 1
- @current_column = 0
- @header.each do |header|
- begin
- value_convert(record, header[:name], header, additional)
- rescue => e
- Rails.logger.error e
- end
- @current_column += 1
- end
- end
- def gen_footer
- @current_row += 2
- @worksheet.write_string(@current_row, 0, "#{Translation.translate(@locale, 'Timezone')}: #{@timezone}", @format_footer)
- @workbook.close
- end
- def timestamp_in_localtime(time)
- return if time.blank?
- time.in_time_zone(@timezone).strftime('%F %T') # "2019-08-19 16:21:52"
- end
- def value_lookup(record, attribute, object, additional)
- value = record[attribute.to_sym]
- if attribute[-3, 3] == '_id'
- ref = attribute[0, attribute.length - 3]
- if record.respond_to?(ref.to_sym)
- @lookup_cache[attribute] ||= {}
- return @lookup_cache[attribute][value] if @lookup_cache[attribute][value]
- ref_object = record.send(ref.to_sym)
- ref_name = value
- if ref_object.respond_to?(:fullname)
- ref_name = ref_object.fullname
- elsif ref_object.respond_to?(:name)
- ref_name = ref_object.name
- end
- @lookup_cache[attribute][value] = ref_name
- return ref_name
- end
- end
- value = record.try(attribute)
- # if no value exists, check additional values
- if !value && additional && additional[attribute.to_sym]
- value = additional[attribute.to_sym]
- end
- if object[:data_type] !~ %r{^(multi_)?tree_select$} && object[:data_option].present? && object[:data_option]['options'].present?
- display_values = ObjectManager::Attribute.data_options_hash(object[:data_option]['options'])
- value = Array(value).map { |v| display_values[v] }.join(',')
- end
- if value.is_a?(Array)
- value = value.join(',')
- end
- value
- end
- def value_convert(record, attribute, object, additional = {})
- value = if attribute
- value_lookup(record, attribute, object, additional)
- else
- record
- end
- case object[:data_type]
- when 'boolean', %r{^(multi)?select$}
- @worksheet.write_string(@current_row, @current_column, value) if value.present?
- when 'datetime'
- @worksheet.write_date_time(@current_row, @current_column, timestamp_in_localtime(value), @format_time) if value.present?
- when 'date'
- @worksheet.write_date_time(@current_row, @current_column, value.to_s, @format_date) if value.present?
- when 'integer'
- @worksheet.write_number(@current_row, @current_column, value) if value.present?
- else
- @worksheet.write_string(@current_row, @current_column, value.to_s) if value.present?
- end
- end
- end
|