excel_sheet.rb 5.3 KB

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