excel_sheet.rb 5.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176
  1. # Copyright (C) 2012-2022 Zammad Foundation, https://zammad-foundation.org/
  2. class ExcelSheet
  3. def initialize(title:, header:, records:, locale:, timezone: nil)
  4. require 'writeexcel' # Only load this gem when it is really used.
  5. @title = title
  6. @header = header
  7. @records = records
  8. @timezone = timezone.presence || Setting.get('timezone_default')
  9. @locale = locale || Locale.default
  10. @tempfile = Tempfile.new('excel-export.xls')
  11. @workbook = WriteExcel.new(@tempfile)
  12. @worksheet = @workbook.add_worksheet
  13. @contents = nil
  14. @current_row = 0
  15. @current_column = 0
  16. @lookup_cache = {}
  17. @format_time = @workbook.add_format(num_format: 'yyyy-mm-dd hh:mm:ss')
  18. @format_date = @workbook.add_format(num_format: 'yyyy-mm-dd')
  19. @format_headline = @workbook.add_format
  20. @format_headline.set_bold
  21. @format_headline.set_size(14)
  22. @format_headline.set_color('black')
  23. @format_header = @workbook.add_format
  24. @format_header.set_italic
  25. @format_header.set_bg_color('gray')
  26. @format_header.set_color('white')
  27. @format_footer = @workbook.add_format
  28. @format_footer.set_italic
  29. @format_footer.set_color('gray')
  30. @format_footer.set_size(8)
  31. end
  32. def contents
  33. file = File.new(@tempfile, 'r')
  34. contents = file.read
  35. file.close
  36. contents
  37. end
  38. def content
  39. gen_header
  40. gen_rows
  41. gen_footer
  42. contents
  43. end
  44. def gen_header
  45. @worksheet.write_string(@current_row, @current_column, @title, @format_headline)
  46. @worksheet.set_row(0, 18)
  47. @current_row += 2
  48. @current_column = 0
  49. @header.each do |header|
  50. if header[:width]
  51. @worksheet.set_column(@current_column, @current_column, header[:width])
  52. end
  53. @worksheet.write_string(@current_row, @current_column, header[:display] || header[:name], @format_header)
  54. @current_column += 1
  55. end
  56. end
  57. def gen_rows
  58. @records.each do |record|
  59. gen_row_by_array(record)
  60. end
  61. end
  62. def gen_row_by_array(record)
  63. @current_row += 1
  64. @current_column = 0
  65. record.each do |item|
  66. begin
  67. if item.acts_like?(:time) || item.acts_like?(:date)
  68. value_convert(item, nil, { data_type: 'datetime' })
  69. elsif item.is_a?(Integer) || item.is_a?(Float)
  70. value_convert(item, nil, { data_type: 'integer' })
  71. else
  72. value_convert(item, nil, { data_type: 'string' })
  73. end
  74. rescue => e
  75. Rails.logger.error e
  76. end
  77. @current_column += 1
  78. end
  79. end
  80. def gen_row_by_header(record, additional = {})
  81. @current_row += 1
  82. @current_column = 0
  83. @header.each do |header|
  84. begin
  85. value_convert(record, header[:name], header, additional)
  86. rescue => e
  87. Rails.logger.error e
  88. end
  89. @current_column += 1
  90. end
  91. end
  92. def gen_footer
  93. @current_row += 2
  94. @worksheet.write_string(@current_row, 0, "#{Translation.translate(@locale, 'Timezone')}: #{@timezone}", @format_footer)
  95. @workbook.close
  96. end
  97. def timestamp_in_localtime(time)
  98. return if time.blank?
  99. time.in_time_zone(@timezone).strftime('%F %T') # "2019-08-19 16:21:52"
  100. end
  101. def value_lookup(record, attribute, object, additional)
  102. value = record[attribute.to_sym]
  103. if attribute[-3, 3] == '_id'
  104. ref = attribute[0, attribute.length - 3]
  105. if record.respond_to?(ref.to_sym)
  106. @lookup_cache[attribute] ||= {}
  107. return @lookup_cache[attribute][value] if @lookup_cache[attribute][value]
  108. ref_object = record.send(ref.to_sym)
  109. ref_name = value
  110. if ref_object.respond_to?(:fullname)
  111. ref_name = ref_object.fullname
  112. elsif ref_object.respond_to?(:name)
  113. ref_name = ref_object.name
  114. end
  115. @lookup_cache[attribute][value] = ref_name
  116. return ref_name
  117. end
  118. end
  119. value = record.try(attribute)
  120. # if no value exists, check additional values
  121. if !value && additional && additional[attribute.to_sym]
  122. value = additional[attribute.to_sym]
  123. end
  124. if object[:data_type] !~ %r{^(multi_)?tree_select$} && object[:data_option].present? && object[:data_option]['options'].present?
  125. display_values = ObjectManager::Attribute.data_options_hash(object[:data_option]['options'])
  126. value = Array(value).map { |v| display_values[v] }.join(',')
  127. end
  128. if value.is_a?(Array)
  129. value = value.join(',')
  130. end
  131. value
  132. end
  133. def value_convert(record, attribute, object, additional = {})
  134. value = if attribute
  135. value_lookup(record, attribute, object, additional)
  136. else
  137. record
  138. end
  139. case object[:data_type]
  140. when 'boolean', %r{^(multi)?select$}
  141. @worksheet.write_string(@current_row, @current_column, value) if value.present?
  142. when 'datetime'
  143. @worksheet.write_date_time(@current_row, @current_column, timestamp_in_localtime(value), @format_time) if value.present?
  144. when 'date'
  145. @worksheet.write_date_time(@current_row, @current_column, value.to_s, @format_date) if value.present?
  146. when 'integer'
  147. @worksheet.write_number(@current_row, @current_column, value) if value.present?
  148. else
  149. @worksheet.write_string(@current_row, @current_column, value.to_s) if value.present?
  150. end
  151. end
  152. end