codephillip · September 4, 2024 11:00 · Sep 4, 2024
diff --git a/sample_excel_extractor.py b/sample_excel_extractor.py
@@ -0,0 +1,69 @@
+import asyncio
+
+import openpyxl
+import uuid
+from urllib.request import urlretrieve
+
+from unsync import unsync
+from asgiref.sync import sync_to_async
+
+from sample_django_interview.models import Category, Answer, Question
+
+
+
+@unsync
+def coroutine_create_question_and_answers(question_text, category_name, level, profession,
+                                          ans_a, ans_b, ans_c, ans_d, correct_answer, cadre):
+    """
+    This is a special background thread function(coroutine) that is only used by the bulk uploader
+    """
+    create_question_and_answers(question_text, category_name, level, profession,
+                                ans_a, ans_b, ans_c, ans_d, correct_answer, cadre)
+
+
+def create_question_and_answers(question_text, category_name, level, profession, ans_a, ans_b, ans_c, ans_d,
+                                correct_answer, cadre):
+    question = Question.objects.create(
+        level=level, question_text=question_text, category=Category.objects.get(name__icontains=category_name),
+        profession=profession, cadre=cadre)
+    question.answers.add(Answer.objects.get_or_create(
+        answer_text=ans_a, correct_answer=correct_answer == 'a', answer_tracker=uuid.uuid4())[0])
+    question.answers.add(Answer.objects.get_or_create(
+        answer_text=ans_b, correct_answer=correct_answer == 'b', answer_tracker=uuid.uuid4())[0])
+    question.answers.add(Answer.objects.get_or_create(
+        answer_text=ans_c, correct_answer=correct_answer == 'c', answer_tracker=uuid.uuid4())[0])
+    question.answers.add(Answer.objects.get_or_create(
+        answer_text=ans_d, correct_answer=correct_answer == 'd', answer_tracker=uuid.uuid4())[0])
+    return question
+
+
+@unsync
+async def extract_excel_interview_questions(url):
+    """
+    Creates questions that are provided in the Excel sheet.
+    Sheet Fields: Question, Ans_A, Ans_B, Ans_C, Ans_D, Correct_answer, Category, Level, Profession, Cadre.
+    """
+    # create Excel sheet into sheets folder
+    urlretrieve(url, 'questions.xlsx')
+    work_book = openpyxl.load_workbook('questions.xlsx')
+    active_sheet = work_book.active
+
+    for row in active_sheet.iter_rows(min_row=3, max_col=10, max_row=active_sheet.max_row):
+        try:
+            row_data = [cell.value for cell in row]
+
+            question_text = row_data[0]
+            ans_a, ans_b, ans_c, ans_d = row_data[1], row_data[2], row_data[3], row_data[4]
+            correct_answer = row_data[5].lower()
+            category_name = row_data[6].lower()
+            level = row_data[7].lower()
+            profession = row_data[8].lower()
+            cadre = row_data[9].lower()
+
+            if not question_text:
+                break
+
+            coroutine_create_question_and_answers(question_text, category_name,
+                                                  level, profession, ans_a, ans_b, ans_c, ans_d, correct_answer, cadre)
+        except Exception as e:
+            print(e)