Coverage for rfpy/api/io/excel_import.py: 99%

1from typing import Tuple, Mapping 

2 

3from openpyxl import load_workbook 

4from openpyxl.worksheet.worksheet import Worksheet 

5from openpyxl.cell.read_only import ReadOnlyCell 

6 

7from rfpy.model import Project 

8from rfpy.model.questionnaire import ( 

9 Section, RadioChoices, TextInput, Label, QuestionInstance, QuestionDefinition 

10) 

11 

12 

13class XLCell(): 

14 

15 def __init__(self, col_idx: int): 

16 self.col_idx = col_idx 

17 

18 def __get__(self, obj: 'QRow', type=None) -> object: 

19 return obj.row[self.col_idx].value 

20 

21 

22class QRow: 

23 

24 section_title = XLCell(0) 

25 question_title = XLCell(1) 

26 question_text = XLCell(2) 

27 multi_choices = XLCell(3) 

28 comments_title = XLCell(4) 

29 

30 def __init__(self, row: Tuple[ReadOnlyCell]): 

31 self.row = row 

32 

33 def is_root_question(self): 

34 return not self.section_title 

35 

36 def choices_list(self): 

37 cd = [] 

38 for choice_str in self.multi_choices.split(';'): 

39 choice, autoscore = RadioChoices.split_choice_string(choice_str) 

40 cd.append( 

41 {"label": choice, "autoscore": autoscore} 

42 ) 

43 if len(cd) < 2: 

44 raise ValueError("At least two values are required for multiple choice options") 

45 return cd 

46 

47 

48def row_to_question(qrow) -> QuestionDefinition: 

49 qdef = QuestionDefinition(title=qrow.question_title) 

50 elems = qdef.elements 

51 elems.append(Label(label=qrow.question_text, row=1, col=1)) 

52 if qrow.multi_choices: 

53 choices = RadioChoices(choices=qrow.choices_list(), row=2, col=1) 

54 elems.append(choices) 

55 if qrow.comments_title: 

56 elems.append(Label(label=qrow.comments_title, row=3, col=1)) 

57 elems.append(TextInput(height=5, width=60, row=4, col=1)) 

58 else: 

59 if qrow.comments_title: 

60 elems.append(Label(label=qrow.comments_title, row=2, col=1)) 

61 elems.append(TextInput(height=5, width=60, row=3, col=1)) 

62 else: 

63 elems.append(TextInput(height=5, width=60, row=2, col=1)) 

64 return qdef 

65 

66 

67class ExcelQImporter: 

68 

69 def __init__(self, parent_section: Section) -> None: 

70 self.created_count = 0 

71 if parent_section is None: 

72 raise ValueError('missing required Section object, parent_section') 

73 self.parent_section = parent_section 

74 self.project: Project = parent_section.project 

75 self.created_sections: Mapping[str: Section] = {} 

76 

77 def read_questions_excel(self, filename): 

78 wb = load_workbook(filename=filename, read_only=True) 

79 try: 

80 self.process_rows(wb['Sheet1']) 

81 finally: 

82 wb.close() 

83 

84 def process_rows(self, ws: Worksheet): 

85 # max_row is 501 to allow a maximum of 500 rows since 

86 # rows are indexed from 1, not zero, and there is a header row 

87 for row in ws.iter_rows(min_row=2, max_row=501): 

88 qr = QRow(row) 

89 if qr.question_title: 

90 self.process_one_row(qr) 

91 self.created_count += 1 

92 

93 def process_one_row(self, qrow: QRow): 

94 qdef = row_to_question(qrow) 

95 qi = QuestionInstance(question_def=qdef, project=self.project) 

96 sec: Section = None 

97 if qrow.is_root_question(): 

98 sec = self.parent_section 

99 elif qrow.section_title in self.created_sections: 

100 sec = self.created_sections[qrow.section_title] 

101 else: 

102 sec = Section(title=qrow.section_title, project=self.project) 

103 self.parent_section.subsections.append(sec) 

104 self.created_sections[qrow.section_title] = sec 

105 sec.questions.append(qi)