xls_export.rb

Alex A, 2011-06-24 16:22

Download (9.72 KB)

 
1

    
2
require 'query' 
3
require 'spreadsheet'
4

    
5
class SpentTimeQueryColumn < QueryColumn  
6
  def caption
7
    l(:label_spent_time)
8
  end
9
  
10
  def value(issue)
11
    issue.spent_hours
12
  end
13
end
14

    
15
class AttachmentQueryColumn < QueryColumn  
16
  def caption
17
    l(:label_plugin_xlse_field_attachment)
18
  end
19
  
20
  def value(issue)
21
          return '' unless issue.attachments.any?
22
          
23
          str = ''
24
          issue.attachments.each do |a|
25
                  str << a.filename
26
                  str << " - #{a.description}" unless a.description.blank?
27
                  str << "\n" unless a==issue.attachments.last
28
          end
29
          return str
30
  end
31
end
32

    
33
module Redmine
34
        module Export
35
                module XLS
36
                unloadable
37

    
38
                def issue_list(issues, &block)
39
                            ancestors = []
40
                            issues.each do |issue|
41
                              while (ancestors.any? && !issue.is_descendant_of?(ancestors.last))
42
                                ancestors.pop
43
                              end
44
                              yield issue, ancestors.size
45
                              ancestors << issue unless issue.leaf?
46
                           end
47
                  end
48
                
49
# options are
50
# :relations - export relations
51
# :watchers - export watchers
52
# :time - export time spent
53
# :description - export descriptions
54
# :attachments - export attachments info
55
# :query_columns_only - export only columns from actual query
56
# :group - group by query grouping  
57
                def issues_to_xls2(issues, project, query, options = {})
58
        
59
                        Spreadsheet.client_encoding = 'UTF-8'
60
                        
61
                        options.default=false
62
                        show_relations = options[:relations]
63
                        show_watchers = options[:watchers] 
64
                        show_time = options[:time]
65
                        show_descr = options[:description]
66
                        query_columns_only = options[:query_columns_only]
67
                        show_attachment = options[:attachments]
68
                        group_by_query = query.grouped? ? options[:group] : false
69
        
70
                        book = Spreadsheet::Workbook.new
71
            
72
                        issue_columns = []
73
            
74
            (query_columns_only == '1' ? query.columns : query.available_columns).each do |c|
75
                    if c.name == :formatted_relations
76
                            issue_columns << c unless show_relations != '1'
77
                                else                    
78
                            issue_columns << c unless !column_exists_for_project?(c,project)
79
                    end
80
                    if c.name == :estimated_hours and show_time == '1'
81
                            issue_columns << SpentTimeQueryColumn.new(:spent_time)
82
                    end
83
            end
84
            
85
            if show_watchers == '1'
86
                    issue_columns << QueryColumn.new(:watcher)
87
            end
88
            
89
            if show_attachment == '1'
90
                    issue_columns << AttachmentQueryColumn.new(:attachments)
91
            end
92
            
93
            if show_descr == '1'
94
                    issue_columns << QueryColumn.new(:description)
95
            end
96
            
97
            sheet1 = nil
98
                        group = false
99
                        columns_width = []
100
                        idx = 0
101
# xls rows                        
102
                        issue_list(issues) do |issue, level|
103

    
104
                                if group_by_query == '1'
105
                                        new_group=query_get_group_column_name(issue,query)
106
                                        if new_group != group
107
                                                group = new_group
108
                                                update_sheet_formatting(sheet1,columns_width) if sheet1
109
                                                sheet1 = book.create_worksheet(:name => (group.blank? ? l(:label_none) : pretty_xls_tab_name(group.to_s)))
110
                                                columns_width=init_header_columns(sheet1,issue_columns)
111
                                                idx = 0
112
                                        end
113
                                else
114
                                        if sheet1 == nil
115
                                                sheet1 = book.create_worksheet(:name => l(:label_issue_plural))
116
                                                columns_width=init_header_columns(sheet1,issue_columns)
117
                                        end
118
                                end
119
                        
120
                                row = sheet1.row(idx+1)
121
                                
122
                                row.replace [issue.id]
123
                                
124
                                
125
                                lf_pos = get_value_width(issue.id)
126
                                columns_width[0] = lf_pos unless columns_width[0] >= lf_pos
127
                                
128
                                last_prj = project
129

    
130
                                if level > 0
131
                                        s = s.to_s.rjust(level*3)                                         
132
                                        issue.subject = s + issue.subject
133
                                end
134
                                
135
                                boldfmt = Spreadsheet::Format.new :weight => :bold
136
                                row.set_format(0, boldfmt) if issue.children?
137
                                issue_columns.each_with_index do |c, j|        
138
                                        fmt = Spreadsheet::Format.new :weight => :bold
139
                                        v = if c.is_a?(QueryCustomFieldColumn)
140
                                                case c.custom_field.field_format
141
                                                        when "int"
142
                                                                begin
143
                                                                        fmt.number_format = "0"
144
                                                                        Integer(issue.custom_value_for(c.custom_field).to_s)
145
                                                                rescue
146
                                                                        show_value(issue.custom_value_for(c.custom_field))
147
                                                                end
148
                                                        when "float"
149
                                                                begin
150
                                                                        fmt.number_format = "0.00"
151
                                                                        Float(issue.custom_value_for(c.custom_field).to_s)
152
                                                                rescue
153
                                                                        show_value(issue.custom_value_for(c.custom_field))
154
                                                                end
155
                                                        when "date"
156
                                                                begin
157
                                                                        fmt.number_format = "dd.mm.yyyy"
158
                                                                        Date.parse(issue.custom_value_for(c.custom_field).to_s)
159
                                                                rescue
160
                                                                        show_value(issue.custom_value_for(c.custom_field))
161
                                                                end
162
                                                else
163
                                                        show_value(issue.custom_value_for(c.custom_field))
164
                                                end
165
                                        else
166
                                                case c.name
167
                                                        when :done_ratio
168
                                                                fmt.number_format = "0%"
169
                                                                (Float(issue.send(c.name)))/100
170
                                                        when :description
171
                                                                descr_str = '' 
172
                                                                issue.description.to_s.each_char do |c_a|
173
                                                                        if c_a != "\r"
174
                                                                                descr_str << c_a
175
                                                                        end
176
                                                                end
177
                                                                descr_str
178
                                                        when :formatted_relations
179
                                                                rel_str = ''
180
                                                                relations = issue.relations.select {|r| r.other_issue(issue).visible?}
181
                                                                relations.each do |relation|
182
                                                                        rel_str << l(relation.label_for(issue)) << ' '
183
                                                                        rel_str << relation.other_issue(issue).tracker.to_s << ' #'
184
                                                                        rel_str << relation.other_issue(issue).id.to_s
185
                                                                        rel_str << "\n" unless relation == relations.last
186
                                                                end unless relations.empty?
187
                                                                rel_str
188
                                                        when :watcher
189
                                                                rel_str=''
190
                                                                if(User.current.allowed_to?(:view_issue_watchers, last_prj) && !issue.watcher_users.empty?)
191
                                                                        issue.watcher_users.each do |user|
192
                                                                                rel_str << user.to_s
193
                                                                                rel_str << "\n" unless user == issue.watcher_users.last
194
                                                                        end
195
                                                                end
196
                                                                rel_str
197
                                                        when :spent_time
198
                                                                if User.current.allowed_to?(:view_time_entries, last_prj)
199
                                                                        c.value(issue)
200
                                                                else
201
                                                                        ''
202
                                                                end
203
                                                        when :attachments
204
                                                                c.value(issue)
205
                                                        when :project
206
                                                                last_prj = issue.send(c.name)
207
                                                                last_prj
208
                                                        when :start_date, :due_date
209
                                                                fmt.number_format = "dd.mm.yyyy"
210
                                                                c.value(issue)
211
                                                else                                                        
212
                                                         issue.respond_to?(c.name) ? issue.send(c.name) : c.value(issue)
213
                                                end
214
                                        end
215
                                        
216
                                        value = ['Time', 'Date', 'Fixnum', 'Float', 'Integer', 'String'].include?(v.class.name) ? v : v.to_s
217

    
218
                                        row.set_format(j+1, fmt) if issue.children?
219
                                        
220
                                        lf_pos = get_value_width(value)
221
                                        columns_width[j+1] = lf_pos unless columns_width[j+1] >= lf_pos
222
                                        row << value
223
                                end
224
                                
225
                                idx = idx + 1                                
226
                                
227
                        end
228
                        
229
                        if sheet1
230
                                update_sheet_formatting(sheet1,columns_width)
231
                        else
232
                                sheet1 = book.create_worksheet(:name => 'Issues')
233
                                sheet1.row(0).replace [l(:label_no_data)]
234
                        end
235

    
236
                        xls_stream = StringIO.new('')
237
                        book.write(xls_stream)
238
            
239
                        return xls_stream.string
240
                end
241
                
242
                def column_exists_for_project?(column, project)
243
                        return true unless (column.is_a?(QueryCustomFieldColumn) && project != nil)
244
                        
245
                        project.trackers.each do |t|
246
                                t.custom_fields.each do |c|
247
                                        if c.id == column.custom_field.id
248
                                                return true
249
                                        end
250
                                end
251
                        end
252
                        
253
                        return false
254
                end
255
                
256
                def init_header_columns(sheet1,columns)
257
                        
258
                        columns_width = [1]
259
                        sheet1.row(0).replace ["#"]
260
                        
261
                        columns.each do |c|
262
                                sheet1.row(0) << c.caption
263
                                columns_width << (get_value_width(c.caption)*1.1)
264
                        end
265
# id
266
                        sheet1.column(0).default_format = Spreadsheet::Format.new(:number_format => "0")
267
                        
268
                        opt = Hash.new
269
                        columns.each_with_index do |c, idx|
270
                                width = 0
271
                                opt.clear
272
                                
273
                                if c.is_a?(QueryCustomFieldColumn)
274
                                        case c.custom_field.field_format
275
                                                when "int"
276
                                                        opt[:number_format] = "0"
277
                                                when "float"
278
                                                        opt[:number_format] = "0.00"
279
                                        end
280
                                else
281
                                        case c.name
282
                                                when :done_ratio
283
                                                        opt[:number_format] = '0%'
284
                                                when :estimated_hours, :spent_time
285
                                                        opt[:number_format] = "0.0"
286
                                        end
287
                                end
288

    
289
                                sheet1.column(idx+1).default_format = Spreadsheet::Format.new(opt) unless opt.empty?
290
                                columns_width[idx+1] = width unless columns_width[idx+1] >= width
291
                        end
292
          
293
                  return columns_width
294
                end
295
                
296
                def update_sheet_formatting(sheet1,columns_width)
297
                        
298
                        sheet1.row(0).count.times do |idx|
299
                                
300
                                        do_wrap = columns_width[idx] > 60 ? 1 : 0
301
                                        sheet1.column(idx).width = columns_width[idx] > 60 ? 60 : columns_width[idx]
302

    
303
                                        if do_wrap
304
                                                fmt = Marshal::load(Marshal.dump(sheet1.column(idx).default_format))
305
                                                fmt.text_wrap = true
306
                                                sheet1.column(idx).default_format = fmt
307
                                        end
308

    
309
                                        fmt = Marshal::load(Marshal.dump(sheet1.row(0).format(idx)))
310
                                        fmt.font.bold=true
311
                                        fmt.pattern=1
312
                                        fmt.pattern_bg_color=:gray
313
                                        fmt.pattern_fg_color=:gray
314
                                        sheet1.row(0).set_format(idx,fmt)
315
                        end
316

    
317
                end
318

    
319
                def get_value_width(value)
320

    
321
                        if ['Time', 'Date'].include?(value.class.name)
322
                                return 18 unless value.to_s.length < 18 
323
                        end
324
                        
325
                        tot_w = Array.new
326
                        tot_w << Float(0)
327
                        idx=0
328
                        value.to_s.each_char do |c|
329
                                case c
330
                                        when '1', '.', ';', ':', ',', ' ', 'i', 'I', 'j', 'J', '(', ')', '[', ']', '!', '-', 't', 'l'
331
                                                tot_w[idx] += 0.6
332
                                        when 'W', 'M', 'D'
333
                                                tot_w[idx] += 1.2
334
                                        when "\n"
335
                                                idx = idx + 1
336
                                                tot_w << Float(0)
337
                                else
338
                                        tot_w[idx] += 1.05        
339
                                end
340
                        end
341
                        
342
                        wdth=0
343
                        tot_w.each do |w|
344
                                wdth = w unless w<wdth 
345
                        end
346
                        
347
                        return wdth+1.5
348
                end
349

    
350
                def query_get_group_column_name(issue,query)
351
                        gc=query.group_by_column
352

    
353
                        return issue.send(query.group_by) unless gc.is_a?(QueryCustomFieldColumn)
354

    
355
                        cf=issue.custom_values.detect do |c|
356
                                true if c.custom_field_id == gc.custom_field.id
357
                        end
358

    
359
                        return cf==nil ? l(:label_none) : cf.value
360
                end
361

    
362
                def pretty_xls_tab_name(org_name)
363
                        return org_name.gsub(/[\\\/\[\]\?\*:"']/, '_')
364
                end
365

    
366
        end
367

    
368
        end
369

    
370
end