Project

General

Profile

RE: XLS export plugin ยป xls_export.rb

Alex A, 2011-06-24 16:22

 
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
    (1-1/1)