|
1
|
class ExcelSheetController < ApplicationController
|
|
2
|
unloadable
|
|
3
|
# before_filter :find_project, :require_admin, :authorize,:only => :index
|
|
4
|
|
|
5
|
def index
|
|
6
|
|
|
7
|
@project = Project.find(params[:id])
|
|
8
|
session[:project_id]=params[:id]
|
|
9
|
|
|
10
|
end
|
|
11
|
|
|
12
|
def save_configuration
|
|
13
|
|
|
14
|
end
|
|
15
|
|
|
16
|
def upload_sheet
|
|
17
|
|
|
18
|
uploaded_io = params[:file]
|
|
19
|
|
|
20
|
if uploaded_io.nil? || uploaded_io.tempfile.nil?
|
|
21
|
|
|
22
|
flash[:notice] = 'Please Submit Excel File'
|
|
23
|
redirect_to :action => 'index', :id => session[:project_id]
|
|
24
|
return
|
|
25
|
end
|
|
26
|
|
|
27
|
unless File.exists?("#{Rails.root}/public/uploads")
|
|
28
|
Dir::mkdir("#{Rails.root}/public/uploads")
|
|
29
|
end
|
|
30
|
|
|
31
|
FileUtils.cp "#{uploaded_io.tempfile.to_path.to_s}", "#{Rails.root}/public/uploads/#{uploaded_io.original_filename}"
|
|
32
|
|
|
33
|
extname=File.extname("#{Rails.root}/public/uploads/#{uploaded_io.original_filename}")
|
|
34
|
|
|
35
|
case extname
|
|
36
|
#Microsoft Excel File
|
|
37
|
when ".xls"
|
|
38
|
workbook = Roo::Excel.new "#{Rails.root}/public/uploads/#{uploaded_io.original_filename}"
|
|
39
|
#Microsoft Excel Xml File
|
|
40
|
when ".xlsx"
|
|
41
|
workbook = Roo::Excelx.new "#{Rails.root}/public/uploads/#{uploaded_io.original_filename}"
|
|
42
|
#ODF Spreadsheet/OpenOffice document
|
|
43
|
when ".ods"
|
|
44
|
workbook =Roo::OpenOffice.new "#{Rails.root}/public/uploads/#{uploaded_io.original_filename}"
|
|
45
|
else
|
|
46
|
flash[:notice] = 'Please Submit Excel File'
|
|
47
|
redirect_to :action => 'index', :id => session[:project_id]
|
|
48
|
return
|
|
49
|
end
|
|
50
|
|
|
51
|
workbook.default_sheet = workbook.sheets[0]
|
|
52
|
|
|
53
|
headers = Hash.new
|
|
54
|
workbook.row(1).each_with_index {|header,i|
|
|
55
|
headers[header] = i
|
|
56
|
}
|
|
57
|
|
|
58
|
project_name=workbook.cell(1,1)
|
|
59
|
redmine_project = Project.find(session[:project_id])
|
|
60
|
if !redmine_project
|
|
61
|
redmine_project = @redmine_project
|
|
62
|
end
|
|
63
|
excel_error_message="Excel File contains following error.<br>"
|
|
64
|
excel_having_errors=false
|
|
65
|
|
|
66
|
#get plugin configuration
|
|
67
|
settings_conf=Setting.plugin_hdexpertises
|
|
68
|
logger.info "#{settings_conf}"
|
|
69
|
((workbook.first_row + 1)..workbook.last_row).each do |row|
|
|
70
|
|
|
71
|
row_content=Array.new(workbook.row(row))
|
|
72
|
row_content.each {|content| logger.info "#{content} --- #{content.class.name}" }
|
|
73
|
|
|
74
|
if row_content[settings_conf['task_column'].to_i].nil?# || row_content[settings_conf['task_description_column'].to_i].nil?
|
|
75
|
|
|
76
|
excel_error_message.concat("Excel Row ##{row} does not contain task description.<br>")
|
|
77
|
excel_having_errors=true
|
|
78
|
end
|
|
79
|
# if !row_content[settings_conf['start_date_column'].to_i].nil?
|
|
80
|
# if row_content[settings_conf['start_date_column'].to_i].class.name != "Date"
|
|
81
|
# excel_error_message.concat("Excel Row ##{row} does not contain valid Start Date<br>")
|
|
82
|
# excel_having_errors=true
|
|
83
|
# end
|
|
84
|
# end
|
|
85
|
# if !row_content[settings_conf['end_date_column'].to_i].nil?
|
|
86
|
# if row_content[settings_conf['end_date_column'].to_i].class.name != "Date"
|
|
87
|
# excel_error_message.concat("Excel Row ##{row} does not contain valid End Date<br>")
|
|
88
|
# excel_having_errors=true
|
|
89
|
# end
|
|
90
|
# end
|
|
91
|
# if row_content[settings_conf['start_date_column'].to_i].class.name != "Date" || row_content[settings_conf['end_date_column'].to_i].class.name != "Date"
|
|
92
|
# excel_error_message.concat("Excel Row ##{row} does not contain valid Start Date/End Date<br>")
|
|
93
|
# excel_having_errors=true
|
|
94
|
# end
|
|
95
|
# if row_content[settings_conf['average_hour_column'].to_i].class.name != "Float"
|
|
96
|
# excel_error_message.concat("Excel Row ##{row} does not contain valid Estimated time<br>")
|
|
97
|
# excel_having_errors=true
|
|
98
|
# end
|
|
99
|
|
|
100
|
end
|
|
101
|
|
|
102
|
|
|
103
|
|
|
104
|
unless excel_having_errors
|
|
105
|
|
|
106
|
|
|
107
|
|
|
108
|
((workbook.first_row + 1)..workbook.last_row).each do |row|
|
|
109
|
|
|
110
|
#iterate through all rows
|
|
111
|
row_content=Array.new(workbook.row(row))
|
|
112
|
#Project Name/Task Best Case Worst Case Average Case Notes Questions Start Date Due Date Total(in weeks) Asignee
|
|
113
|
unless row_content[0]== l(:label_import_issue_task) || row_content[0] == l(:label_import_issue_design) || row_content[0] == l(:label_import_issue_development) || row_content[0] == l(:label_import_issue_documentation) || row_content[0] == l(:label_import_issue_testing)
|
|
114
|
if row_content[0].nil?
|
|
115
|
issue = Issue.new
|
|
116
|
issue.author_id = User.current.id
|
|
117
|
issue.project_id = redmine_project.id
|
|
118
|
issue.subject=row_content[settings_conf['task_column'].to_i]
|
|
119
|
issue.tracker_id=settings_conf['save_task_as'].to_i #Bug/Feature/Support
|
|
120
|
issue.status_id=1 #New
|
|
121
|
issue.description=row_content[settings_conf['task_description_column'].to_i]
|
|
122
|
issue.estimated_hours=row_content[settings_conf['average_hour_column'].to_i]
|
|
123
|
#issue.start_date=row_content[settings_conf['start_date_column'].to_i]
|
|
124
|
#issue.due_date=row_content[settings_conf['end_date_column'].to_i]
|
|
125
|
issue.custom_field_values={"1"=>row_content[settings_conf['average_hour_column'].to_i].to_i}
|
|
126
|
issue.custom_field_values={"2"=>row_content[settings_conf['asignee_name_column'].to_i]}
|
|
127
|
issue.custom_field_values={"7"=>row_content[settings_conf['cf7_name_column'].to_i]}
|
|
128
|
issue.custom_field_values={"8"=>row_content[settings_conf['cf8_name_column'].to_i]}
|
|
129
|
issue.custom_field_values={"10"=>row_content[settings_conf['cf10_name_column'].to_i]}
|
|
130
|
issue.custom_field_values={"11"=>row_content[settings_conf['cf11_name_column'].to_i]}
|
|
131
|
issue.custom_field_values={"12"=>row_content[settings_conf['cf12_name_column'].to_i]}
|
|
132
|
issue.custom_field_values={"13"=>row_content[settings_conf['cf13_name_column'].to_i]}
|
|
133
|
issue.custom_field_values={"14"=>row_content[settings_conf['cf14_name_column'].to_i]}
|
|
134
|
|
|
135
|
|
|
136
|
#issue.assigned_to_id=User.current.id
|
|
137
|
# User.all.each do |user|
|
|
138
|
# if user.name.eql? row_content[settings_conf['asignee_name_column'].to_i]
|
|
139
|
# issue.assigned_to_id=user.id
|
|
140
|
# #issue.custom_field_values={"10"=>"#{user.id}"}
|
|
141
|
# end
|
|
142
|
# end
|
|
143
|
#Save issue for project
|
|
144
|
issue.save
|
|
145
|
else
|
|
146
|
#issue = Issue.new
|
|
147
|
issue = Issue.find(row_content[0])
|
|
148
|
#issue.update_attribute(:subject,row_content[settings_conf['task_column'].to_i])
|
|
149
|
issue.subject=row_content[settings_conf['task_column'].to_i]
|
|
150
|
issue.tracker_id=settings_conf['save_task_as'].to_i #Bug/Feature/Support
|
|
151
|
issue.status_id=1 #New
|
|
152
|
issue.description=row_content[settings_conf['task_description_column'].to_i]
|
|
153
|
issue.estimated_hours=row_content[settings_conf['average_hour_column'].to_i]
|
|
154
|
issue.custom_field_values={"1"=>row_content[settings_conf['average_hour_column'].to_i].to_i}
|
|
155
|
issue.custom_field_values={"2"=>row_content[settings_conf['asignee_name_column'].to_i]}
|
|
156
|
issue.custom_field_values={"7"=>row_content[settings_conf['cf7_name_column'].to_i]}
|
|
157
|
issue.custom_field_values={"8"=>row_content[settings_conf['cf8_name_column'].to_i]}
|
|
158
|
issue.custom_field_values={"10"=>row_content[settings_conf['cf10_name_column'].to_i]}
|
|
159
|
issue.custom_field_values={"11"=>row_content[settings_conf['cf11_name_column'].to_i]}
|
|
160
|
issue.custom_field_values={"12"=>row_content[settings_conf['cf12_name_column'].to_i]}
|
|
161
|
issue.custom_field_values={"13"=>row_content[settings_conf['cf13_name_column'].to_i]}
|
|
162
|
issue.custom_field_values={"14"=>row_content[settings_conf['cf14_name_column'].to_i]}
|
|
163
|
issue.update
|
|
164
|
|
|
165
|
# elsif
|
|
166
|
# issue1 = Issue.new
|
|
167
|
# issue1 = Issue.find(row_content[0])
|
|
168
|
# issue1.status_id=11
|
|
169
|
# issue1.save
|
|
170
|
# issue = Issue.new.copy_from(Issue.find(row_content[0]))
|
|
171
|
# issue.subject=row_content[settings_conf['task_column'].to_i]
|
|
172
|
# issue.tracker_id=settings_conf['save_task_as'].to_i #Bug/Feature/Support
|
|
173
|
# issue.status_id=1 #New
|
|
174
|
# issue.description=row_content[settings_conf['task_description_column'].to_i]
|
|
175
|
# issue.estimated_hours=row_content[settings_conf['average_hour_column'].to_i]
|
|
176
|
# # issue.start_date=row_content[settings_conf['start_date_column'].to_i]
|
|
177
|
# #issue.due_date=row_content[settings_conf['end_date_column'].to_i]
|
|
178
|
# issue.custom_field_values={"1"=>row_content[settings_conf['average_hour_column'].to_i].to_i}
|
|
179
|
# issue.custom_field_values={"2"=>row_content[settings_conf['asignee_name_column'].to_i]}
|
|
180
|
# issue.custom_field_values={"7"=>row_content[settings_conf['cf7_name_column'].to_i]}
|
|
181
|
# issue.custom_field_values={"8"=>row_content[settings_conf['cf8_name_column'].to_i]}
|
|
182
|
# issue.custom_field_values={"10"=>row_content[settings_conf['cf10_name_column'].to_i]}
|
|
183
|
# issue.custom_field_values={"11"=>row_content[settings_conf['cf11_name_column'].to_i]}
|
|
184
|
# issue.custom_field_values={"12"=>row_content[settings_conf['cf12_name_column'].to_i]}
|
|
185
|
# issue.custom_field_values={"13"=>row_content[settings_conf['cf13_name_column'].to_i]}
|
|
186
|
# issue.custom_field_values={"14"=>row_content[settings_conf['cf14_name_column'].to_i]}
|
|
187
|
# issue.save
|
|
188
|
|
|
189
|
end
|
|
190
|
end
|
|
191
|
|
|
192
|
end
|
|
193
|
|
|
194
|
else
|
|
195
|
|
|
196
|
flash[:error]=excel_error_message
|
|
197
|
redirect_to :action => 'index', :id => session[:project_id]
|
|
198
|
return
|
|
199
|
|
|
200
|
end
|
|
201
|
|
|
202
|
|
|
203
|
flash[:notice] = 'Issues successfully created'
|
|
204
|
redirect_to :action => 'index', :id => session[:project_id]
|
|
205
|
|
|
206
|
end
|
|
207
|
|
|
208
|
def generate_excel_sheet
|
|
209
|
|
|
210
|
headers=Hash.new
|
|
211
|
|
|
212
|
headers[params[:task_column]]="Task"
|
|
213
|
headers[params[:task_description_column]]="Task Description"
|
|
214
|
headers[params[:average_hour_column]]="Average Hours"
|
|
215
|
headers[params[:id_column]]="ID"
|
|
216
|
headers[params[:check_column]]= "Update"
|
|
217
|
headers[params[:asignee_name_column]]= "Asignee"
|
|
218
|
headers[params[:cf7_name_column]]= "Cf7"
|
|
219
|
headers[params[:cf8_name_column]]= "Cf8"
|
|
220
|
headers[params[:cf10_name_column]]= "Cf10"
|
|
221
|
headers[params[:cf11_name_column]]= "Cf11"
|
|
222
|
headers[params[:cf12_name_column]]= "Cf12"
|
|
223
|
headers[params[:cf13_name_column]]= "Cf13"
|
|
224
|
headers[params[:cf14_name_column]]= "f14"
|
|
225
|
|
|
226
|
|
|
227
|
|
|
228
|
column_headers=Array.new
|
|
229
|
(0..15).each do |i|
|
|
230
|
|
|
231
|
if headers.has_key?(i.to_s)
|
|
232
|
|
|
233
|
column_headers.push(headers.fetch(i.to_s))
|
|
234
|
else
|
|
235
|
column_headers.push("")
|
|
236
|
end
|
|
237
|
|
|
238
|
end
|
|
239
|
workbook = Spreadsheet::Workbook.new
|
|
240
|
sheet1 = workbook.create_worksheet :name => "Redmine Sample Sheet"
|
|
241
|
|
|
242
|
sheet1.row(0).replace column_headers
|
|
243
|
unless File.exists?("#{Rails.root}/public/uploads/exports")
|
|
244
|
Dir::mkdir("#{Rails.root}/public/uploads/exports")
|
|
245
|
end
|
|
246
|
excel_sheet_file_path=["public", "uploads", "exports", "Redmine_Sample_Issue_Sheet.xls"].join("/")
|
|
247
|
export_file_path = [Rails.root,excel_sheet_file_path].join("/")
|
|
248
|
workbook.write export_file_path
|
|
249
|
|
|
250
|
render :text => ["public", "uploads", "exports", "Redmine_Sample_Issue_Sheet.xls"].join("/")
|
|
251
|
return
|
|
252
|
|
|
253
|
end
|
|
254
|
|
|
255
|
def export_excel_sheet
|
|
256
|
excel_sheet_file_path=["public", "uploads", "exports", "Redmine_Sample_Issue_Sheet.xls"].join("/")
|
|
257
|
send_file excel_sheet_file_path, :content_type => "application/vnd.ms-excel", :disposition => 'attachment' ,:filename => "Redmine_Sample_Issue_Sheet.xls",:x_sendfile => true
|
|
258
|
end
|
|
259
|
|
|
260
|
def render_excel_sheet
|
|
261
|
excel_sheet_file_path=["public", "uploads", "exports", "Redmine_Sample_Issue_Sheet.xls"].join("/")
|
|
262
|
respond_to do |format|
|
|
263
|
format.html
|
|
264
|
format.xls { send_data excel_sheet_file_path }
|
|
265
|
end
|
|
266
|
end
|
|
267
|
|
|
268
|
end
|