excel_export.rb 4.07 KB
Newer Older
1
class Reporter::ExcelExport
2
3
4
  DEFAULT_ROW_WIDTH = 100
  DEFAULT_ROW_HEIGHT = 20

Rafael Jung's avatar
Rafael Jung committed
5
  def initialize
6
    @sample_list = Array.new
Rafael Jung's avatar
Rafael Jung committed
7
8
9
  end

  def add_sample(sample)
10
    @sample_list << sample
Rafael Jung's avatar
Rafael Jung committed
11
12
  end

13
  def generate_file(default_excluded_field, default_included_field, removed_field = [])
14
    return -1 if @sample_list.empty? || @sample_list.first == nil
PiTrem's avatar
PiTrem committed
15
16
    header = process_header(default_excluded_field, default_included_field, removed_field)
    return -1 if header.empty?
An Nguyen's avatar
An Nguyen committed
17
    p = Axlsx::Package.new
Rafael Jung's avatar
Rafael Jung committed
18
19
    p.workbook.styles.fonts.first.name = 'Calibri'
    p.workbook.add_worksheet(:name => "ChemOffice") do |sheet|
20
      sheet.add_row(fix_typo(header)) # Add header
An Nguyen's avatar
An Nguyen committed
21

Rafael Jung's avatar
Rafael Jung committed
22
      width = 0
23
      files = [] # do not let Tempfile object to be garbage collected
24
      need_images = header.index("Image")
An Nguyen's avatar
An Nguyen committed
25

26
      @sample_list.compact.each_with_index do |sample, row|
27
28
        data_hash = []
        start = 0
Rafael Jung's avatar
Rafael Jung committed
29

30
31
32
33
        if need_images
          data_hash = [""]
          start = 1
          image_data = process_and_add_image(sheet, sample, row, files)
An Nguyen's avatar
An Nguyen committed
34
        end
Rafael Jung's avatar
Rafael Jung committed
35

36
37
38
39
40
        process_row_data(start, header, sample, data_hash)
        row_width, row_height = row_geometry(image_data)
        width = row_width if row_width > width # Get the biggest image size to set the column

        sheet.add_row(data_hash, sz: 12, height: row_height * 3 / 4) # 3/4 -> The misterious ratio!
Rafael Jung's avatar
Rafael Jung committed
41
42
      end

An Nguyen's avatar
An Nguyen committed
43
44
45
      # 1/8 -> The second misterious ratio (library bug?)
      # The creator mentioned about this
      # https://github.com/randym/axlsx/issues/125#issuecomment-16834367
46
      sheet.column_info.first.width = need_images ? width / 8 : 40
Rafael Jung's avatar
Rafael Jung committed
47
48
49
50
51
    end

    p.to_stream().read()
  end

52
  def process_header(default_excluded_field, default_included_field, removed_field)
53
      header = @sample_list.first.attribute_names
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
      # Exclude field
      header.delete_if { |x| default_excluded_field.include?(x) }
      header = header.reject { |x| x.empty? }
      header = header.uniq
      # Include field
      default_included_field = default_included_field.map! {|x|
        x.slice!("molecule.")
        x
      }
      header = ["Image"] + header + default_included_field - removed_field
      return header
  end

  def process_row_data(start, header, sample, data_hash)
    (start..header.length - 1).each do |index|
      key = header[index]
      if is_molecule_attribute(key)
        asso = sample.send("molecule")
        data = asso.attributes[key]
PiTrem's avatar
PiTrem committed
73
74
      elsif key == 'molecule_name' && (nid = sample.molecule_name_id)
        data = MoleculeName.find_by(id: nid)&.attributes['name']
75
76
77
78
79
80
81
82
      else
        data = sample.attributes[key]
      end
      data_hash << data
    end
  end

  def is_molecule_attribute(key)
PiTrem's avatar
PiTrem committed
83
    ["cano_smiles", "sum_formular", "inchistring", 'inchikey',
jasonych99's avatar
jasonych99 committed
84
      "molecular_weight"].index(key)
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
  end

  def process_and_add_image(sheet, sample, row, files)
    svg_path = Rails.root.to_s + '/public' + sample.get_svg_path
    image_data = get_image_from_svg(svg_path, files)
    img_src = image_data[:path]
    sheet.add_image(image_src: img_src, noMove: true) do |img|
      img.width = image_data[:width]
      img.height = image_data[:height]
      img.start_at 0, row + 1
    end
    return image_data
  end

  def row_geometry(image_data = nil)
    row_width = DEFAULT_ROW_WIDTH
    row_height = DEFAULT_ROW_HEIGHT
    if image_data
      row_height = image_data[:height]
      row_width = image_data[:width]
    end
    return row_width, row_height
  end

109
  def get_image_from_svg(svg_path, files)
110
111
112
113
114
115
116
    png_blob, width, height = process_image(svg_path)
    file = create_file(png_blob)
    files << file # do not let Tempfile object to be garbage collected
    return {path: file.path, width: width, height: height}
  end

  def process_image(svg_path)
117
    image = Magick::Image.read(svg_path) { self.format = 'SVG'; }.first
Rafael Jung's avatar
Rafael Jung committed
118
    image.format = 'png'
119
120
    return image.to_blob, image.columns, image.rows
  end
Rafael Jung's avatar
Rafael Jung committed
121

122
  def create_file(png_blob)
Rafael Jung's avatar
Rafael Jung committed
123
124
    file = Tempfile.new(['image', '.png'])
    file.binmode
125
    file.write(png_blob)
Rafael Jung's avatar
Rafael Jung committed
126
127
    file.flush
    file.close
128
    return file
Rafael Jung's avatar
Rafael Jung committed
129
  end
130
131
132
133

  def fix_typo(inputs)
    inputs.map { |i| i == "sum_formular" ? "sum_formula" : i }
  end
Rafael Jung's avatar
Rafael Jung committed
134
end