def generateProjectFile(featureId): book = xlsxwriter.Workbook('tempfiles/projectList.xlsx') sheet = book.add_worksheet('Project') session = createSession('try') format_title = book.add_format(FORMAT_TITLE) format_text = book.add_format(FORMAT_TEXT) col_names = ['Project','Lead','Description','State','Jira'] sheet.set_column('A:A', 50) sheet.set_column('B:B', 20) sheet.set_column('C:C', 100) sheet.set_column('D:D', 30) sheet.set_column('E:E', 50) col = 0 for item in col_names: sheet.write(0, col, item, format_title) col += 1 projectList = session.query(Activity).filter(Activity.parent_id==featureId) row=1 for item in projectList: jiraInfo = '' for elem in re.findall('"([^"]*)"', item.jira): jiraInfo = jiraInfo + elem + ','; sheet.write(row, 0, item.name, format_text) sheet.write(row, 1, item.lead) sheet.write(row, 2, item.description, format_text) sheet.write(row, 3, item.state) sheet.write(row, 4, jiraInfo) row += 1 sheet.data_validation('D2:D50', {'validate': 'list','source': ['Unplanned-Unstaffed','Planned-Unstaffed','Planned-Partially Staffed','Scheduled']}) book.close()
def generateMemberFile(teamname): book = xlsxwriter.Workbook('tempfiles/teammember.xlsx') sheet = book.add_worksheet('Members') session = createSession('try') format_title = book.add_format(FORMAT_TITLE) sheet.set_column('A:A', 30) sheet.set_column('B:B', 60) sheet.set_column('C:G', 18) col_names = ['Name','Email','Position','Location','Type','Team','Mobile'] col = 0 for item in col_names: sheet.write(0, col, item, format_title) col += 1 memberList = session.query(Person).filter(Person.team == teamname) row = 1 for member in memberList: sheet.write(row, 0, member.display_name) sheet.write(row, 1, member.email) sheet.write(row, 2, member.position) sheet.write(row, 3, member.location) sheet.write(row, 4, member.user_type) sheet.write(row, 5, member.team) sheet.write(row, 6, member.mobile_num) row += 1 sheet.data_validation('C2:C50', {'validate': 'list','source': ['MTS']}) sheet.data_validation('D2:D50', {'validate': 'list','source': ['Acton MA US','Prague Czech Republic']}) sheet.data_validation('E2:E50', {'validate': 'list','source': ['Fulltime','Contractor','Intern']}) sheet.data_validation('F2:F50', {'validate': 'list','source': ['','Content','EMS','Gateway','Infrastructure','Networking','SQA','Sustaining']}) sheet.data_validation('G2:G50', {'validate': 'length', 'criteria': '>', 'value': 0, 'input_title': 'Mobile Number'}) book.close()
def generateFeatureFile(releaseId=''): book = xlsxwriter.Workbook('tempfiles/featureList.xlsx') sheet = book.add_worksheet('Feature') session = createSession('try') format_title = book.add_format(FORMAT_TITLE) format_text = book.add_format(FORMAT_TEXT) col_names = ['Feature','REQS','Category','Customer','Priority','Realease','Lead'] sheet.set_column('A:A', 50, format_text) sheet.set_column('B:B', 30) sheet.set_column('C:C', 30, format_text) sheet.set_column('D:D', 20, format_text) sheet.set_column('E:E', 10) sheet.set_column('F:F', 50, format_text) sheet.set_column('G:G', 20) col = 0 for item in col_names: sheet.write(0, col, item, format_title) col += 1 if releaseId == '': featureList = session.query(Activity).filter(Activity.activity_type=='Feature') else: featureList = session.query(Activity).filter(Activity.parent_id == releaseId) row=1 for item in featureList: jiraInfo = '' for elem in re.findall('"([^"]*)"', item.jira): jiraInfo = jiraInfo + elem + ','; sheet.write(row, 0, item.name) sheet.write(row, 1, jiraInfo) sheet.write(row, 2, item.category) sheet.write(row, 3, item.customer) sheet.write(row, 4, item.priority) if item.parent_id != '' and item.parent_id != None: parentname = list(session.query(Activity).filter(Activity.objectId==item.parent_id))[0].name else: parentname = '' sheet.write(row, 5, parentname) sheet.write(row, 6, item.lead) row += 1 sheet.data_validation('E2:E50', {'validate': 'list','source': ['P1','P2','P3']}) book.close()
def read(filename,act_type=''): book=xlrd.open_workbook(filename) list_data=[] #Get effective columns in excel feature_dict ={'name': ['Feature','feature','name','Name'], 'jira': ['REQS','Jira'] , 'category': ['Category'] , 'customer': ['Customer'] , 'priority': ['Priority'] , 'lead': ['Feature Lead','Lead'] , 'release' : ['Release','release'] } project_dict ={'project': ['Project','project'], 'lead': ['lead','Lead'] , 'description': ['Description','description'], 'state': ['State','state'], 'jira': ['REQS','Jira','jira'] } assignment_dict ={'assignment': ['Assignment','assignment'], 'team': ['Team','team'] , 'resources': ['Resources','resources','Resource','resource'], 'start': ['Start Date','start date','Start date'], 'week': ['Duration(Weeks)','Duration','duration','Weeks','weeks'], 'workload': ['Workload','workload','Work load','work load'], 'description': ['Description','description'] } member_dict ={ 'name': ['name','Name'], 'email': ['email','Email'] , 'position': ['position','Position'] , 'location': ['location','Location'] , 'user_type': ['user_type','User_type','User_Type','type','Type'] , 'team': ['team','Team'] , 'mobile': ['mobile','Mobile','Mobile Number','Phone Number','Phone','phone'] } if act_type=='feature': effective_dict = copy.deepcopy(feature_dict) elif act_type=='project': effective_dict = copy.deepcopy(project_dict) elif act_type=='assignment': effective_dict = copy.deepcopy(assignment_dict) else: effective_dict = copy.deepcopy(member_dict) effective_col = {} #map columns in database empty_flag = 0 effective_row = 0 sheet = book.sheet_by_index(0) for col in range(sheet.ncols): for col_name in effective_dict.keys(): if sheet.cell(0,col).value in effective_dict[col_name]: effective_col[col]=col_name del effective_dict[col_name] #get effective rows for row in range(1,sheet.nrows): empty_flag = 1 for col in effective_col: if sheet.cell(row,col).ctype != 0 : empty_flag = 0 break if empty_flag == 1: effective_row = row break #check if the excel is in format #print effective_col if(len(effective_col)==0): return '' if effective_row==0: effective_row=sheet.nrows #get data from excel book for row in range(1,effective_row): row_data={} for col in effective_col: col_name = effective_col[col] row_data[col_name]=sheet.cell(row,col).value if act_type== 'feature': if col_name == 'release': #Add new release into db session = createSession('try') release = list(session.query(Activity).filter(Activity.activity_type=="Release", Activity.name == row_data[col_name])) if len(release) == 0 and row_data[col_name] != '' : # if the release does not exist in the db new_release = Activity(name = row_data[col_name] , activity_type = "Release" , description = "Add from excel import" , state = "Planning") session.add(new_release) session.commit() if col_name == 'jira' : row_data[col_name] = row_data[col_name].split(',') if col_name == 'description': row_data[col_name] = row_data[col_name].replace("'","\u0027") if col_name == 'start': if row_data[col_name] != '': dateTuple = xlrd.xldate_as_tuple(row_data[col_name],book.datemode) row_data[col_name] = str(dateTuple[0]) + '-' + str(dateTuple[1]) + '-' + str(dateTuple[2]) list_data.append(row_data) #print list_data return json.dumps(list_data)
def generateTaskFile(projectId): session = createSession('try') book = xlsxwriter.Workbook('tempfiles/assignments.xlsx') sheet = book.add_worksheet('Assignments') format_title = book.add_format(FORMAT_TITLE) format_text = book.add_format(FORMAT_TEXT) dateformat = book.add_format({'num_format': 'yyyy-mm-dd'}) sheet.set_column('A:A', 50) sheet.set_column('B:B', 15) sheet.set_column('C:C', 20) sheet.set_column('D:D', 12) sheet.set_column('E:E', 20) sheet.set_column('F:F', 12) sheet.set_column('G:G', 20) sheet.set_column('H:H', 10) sheet.set_column('I:I', 100) col_names = ['Assignment','Team','Resources','Start Date','Duration','End Date','State','Workload','Description'] col = 0 for item in col_names: sheet.write(0, col, item, format_title) col += 1 assignmentList = session.query(Assignment).filter(Assignment.activity_id==projectId) row=1 for item in assignmentList: if item.task_phase > 100: pass else: sheet.write(row, 0, item.name,format_text) sheet.write(row, 1, item.team) sheet.write(row, 2, item.person) if item.task_phase == 0: count_date = item.planned_start_date end_date = item.forecast_end_date state = 'Planing' elif item.task_phase == 1: count_date = item.forecast_start_date end_date = item.forecast_end_date state = 'Forecasted' elif item.task_phase == 2: count_date = item.actual_start_date end_date = item.forecast_end_date state = 'Start' elif item.task_phase == 3: count_date = item.actual_start_date end_date = item.actual_end_date state = 'Complete' else: count_date = item.planned_start_date end_date = item.forecast_end_date state = 'Common Task' sheet.write(row, 3, count_date, dateformat) sheet.write(row, 4, item.planned_num_weeks) sheet.write(row, 5, end_date, dateformat) sheet.write(row, 6, state) sheet.write(row, 7, item.work_load) sheet.write(row, 8, item.description, format_text) row += 1 sheet.data_validation('B2:B50', {'validate': 'list','source': ['','Content','EMS','Gateway','Infrastructure','Networking','SQA','Sustaining']}) sheet.data_validation('D2:D50', {'validate': 'date', 'criteria': 'between', 'minimum': date(1900, 1, 1), 'maximum': date(2040, 12, 12), 'input_title': 'Start date', 'error_title': 'Invalid date', 'error_message': 'Enter a date or leave it empty!'}) sheet.data_validation('E2:E50', {'validate': 'length', 'criteria': '<', 'value': 100, 'input_title': 'Duration:', 'input_message': 'Enter number of weeks, or specified duration,ie. 5w, 5 weeks, 1 week 3 days', 'error_title': 'Input value is not valid!', 'error_message': 'It should be an integer!'}) sheet.set_column = ('F2:F50', {'validate': 'date', 'criteria': 'between', 'minimum': date(1900, 1, 1), 'maximum': date(2040, 12, 12), 'input_title': 'End date will not be used for planing from spreadsheet', 'error_title': 'Invalid date', 'error_message': 'Enter a date or leave it empty!'}) sheet.data_validation('G2:G50', {'validate': 'list','source': ['Planning','Forecasted','Start','Complete']}) sheet.data_validation('H2:H50', {'validate': 'decimal', 'criteria': 'between', 'minimum': 0.00, 'maximum': 1.00, 'input_title': 'Work Load', 'input_message': 'between 0 and 1', 'error_title': 'Input value is not valid!', 'error_message': 'It should be an number between 0 and 1'}) book.close()